Editing text (CSV) files with Excel containing leading zeros for SNC File Transfer
Editing a text file (TXT or CSV, but only CSV is allowed in File Transfer) in Microsoft Excel could lead to data loss, when leading zero is important, as it is in the File Transfer function of SAP Supply Network Collaboration. If you need to use Excel for editing big amount of data together, you need a solution to keep leading zeros in your files for upload.
The File Transfer in SAP SNC technically designed to process data with leading zeros, where it was exported with leading zeros. This requirement can be covered by multiple options.
The most general option is to edit the CSV files only in Notepad, or a general text editor that does not have any automatic formatting functions. This can be helpful if you download a file that is ready for uploading (Purchase Order confirmation), or if a single value or just few elements of the file should be changed. But it is not optimal for mass data handling, since the field values may vary on their length, and therefore the overview of the content is not easy and the risk of incorrect data would be higher with the size of the file.
If you want to edit CSV files in a spreadsheet editor quickly, and you don’t want to add leading zeros every time you edit the content, you can use the BAdi /SCA/FTR_FIELD_SERVICE_CSV to implement your custom logic to fill leading zeros where necessary. For details about this solution, you can read the Troubleshooting Guide FTR Upload missing leading zeros on the SAP Community WIKI. In this case, the upload file might have no leading zeros, but you should prepare the custom logic to handle data with partial leading zeros or if the data is correct and no further adjustment needed.
You can also open the CSV file directly in Excel and keep leading zeros with the “Text Import Wizard“. This is a built in Excel functionality, that can be used to set the format of the columns when the file is opened from the Excel application. Important, that it does not work if the CSV file is opened from File Explorer, from a website, or from an e-mail attachment for example. At Step 2 select “Comma” as the delimiter, then on Step 3, the wizard will ask you to set the column format types. Here, you can select all the columns at once (Shift+click), then set the format to “Text”.
Using these options could ensure that the file you upload via File Trasnfer can be processed and passed through to the upload profile’s plugin.
Once you have saved the file, make sure that every data stands between quote marks, or if quote marks are removed, the separator must be comma. Semicolon is not accepted by the system. To replace semicolon with comma fast, a simple text editor can be used with CTRL+H (replace) function, or implement this change in the custom logic you use to fill leading zeros. If you upload a file where “Note” column is expected, avoid using commas in that text.
I have been using and often recommend using LibreOffice's scalc for two reasons.
You can download it at https://www.libreoffice.org/download/download/
Good advice and great tool too.