The CSV import/export function is helpful when it comes to changing or creating a large amount of data. However, there are a few things to keep in mind when using it – especially when opening, editing, and saving CSV files. Below, we'll show you step by step for Microsoft Excel and LibreOffice Calc what you need to consider so that the CSV file is displayed correctly in your spreadsheet program and can also be imported correctly back into your Pickware Admin.
What is a CSV file and how does it "work"?
A CSV file is a simple text file that stores data in table form. Each line in the file corresponds to a row of data, and the values within a line are separated from each other by semicolons or other characters. Below you can see an example of the content of a CSV file in which the absolute stock of a product on different storage bins is specified. The first line indicates the respective column names.
Produktname;Produktnummer;Lager;Lagerkürzel;Lagerplatz;Standardlagerplatz;Bestand
"Aerodynamic Concrete CoffeeMatic";PW10071;Hauptlager;HL;unbekannt;nein;-24
"Aerodynamic Concrete CoffeeMatic";PW10071;Nachfülllager;NL;C-03-006;ja;150
"Aerodynamic Concrete CoffeeMatic";PW10071;Hauptlager;HL;B-02-002;nein;90
"Aerodynamic Concrete CoffeeMatic";PW10071;Hauptlager;HL;C-02-007;ja;50
"Aerodynamic Concrete CoffeeMatic";PW10071;Hauptlager;HL;C-03-004;nein;125
How is a CSV file read and processed?
1. The file is read line by line – so the order of the entries matters. This means that if an entry appears multiple times with different values, the later value will overwrite the previous one. Here's an example:
The last line of the sample CSV file above reads as follows:
"Aerodynamic Concrete CoffeeMatic";PW10071;Hauptlager;HL;C-03-004;nein;125
When reading this line, the stock of 125 is written for product PW10071 on storage bin C-03-004 in the main warehouse. Suppose another line followed in the CSV file that refers to the same combination of product number, warehouse, and storage bin, but contains a different stock value. For example:
"Aerodynamic Concrete CoffeeMatic";PW10071;Hauptlager;HL;C-03-004;nein;100
When this line is read, the 100 units would not be added to the already written 125 units. Instead, the stock for product PW10071 on storage bin C-03-004 in the main warehouse would be overwritten. The final stock would therefore be 100 units and not 225!
2. Only what is in the file is processed. No independent logical assumptions are made. For example, if you want to transfer stock from storage bin A to storage bin B, your CSV file must contain two lines for this.
Here, as an example within an absolute stock import, 90 units of a product are booked to storage bin A-01-001 in the first line (absolute stock = 90). In the second line, 90 units of the product are removed from storage bin B-02-002 (absolute stock = 0)
Produktname;Produktnummer;Lager;Lagerkürzel;Lagerplatz;Standardlagerplatz;Bestand
"Aerodynamic Concrete CoffeeMatic";PW10071;Hauptlager;HL;A-01-001;nein;90
"Aerodynamic Concrete CoffeeMatic";PW10071;Hauptlager;HL;B-02-002;nein;0
With a relative stock import, the same change would look like this
Produktname;Produktnummer;Lager;Lagerkürzel;Lagerplatz;Standardlagerplatz;Änderung
"Aerodynamic Concrete CoffeeMatic";PW10071;Hauptlager;HL;A-01-001;nein;90
"Aerodynamic Concrete CoffeeMatic";PW10071;Hauptlager;HL;B-02-002;nein;-90
If only the first line is included in the CSV file, stock would only be booked in on the new storage bin, but no stock would be booked out on the old storage bin. As a result, your actual stock would be 90 units too high.
3. The location of the import determines which data can be changed. For example, in the stock overview, a CSV import only allows you to change the stock on the respective storage bins. If you change the product name in the import file, this has no effect on the actual product. If you specify a storage bin that doesn't exist, it will not be automatically created. Instead, the relevant record will not be imported, and an error message will appear in the import report. There is a separate import interface for creating storage bins, where accordingly no stock can be changed either.
General procedure
Regardless of the program used, the following points must be considered when editing the CSV file for the import/export tool:
Export the corresponding profile as a CSV file from your Pickware Admin and use it as a template.
Open the file as a CSV file in a spreadsheet program.
Important: the encoding must be UTF-8 and the separator must be a semicolon.
Finally, the file must be exported/saved again as a semicolon-separated CSV file in UTF-8 format.
CSV import/export with Excel (from Excel 2010)
First, perform a CSV export with the desired profile in your admin.
Open a new document in Excel.
Import the CSV file by navigating to the Data tab and clicking on Get Data → From Text.
Select Delimited and choose Unicode (UTF-8) as the file origin, then click Next below.
Select the semicolon as the delimiter.
When editing the file, make sure to use the same decimal separator that you also configured in the import/export tool. In Excel, you can set the decimal separator via the advanced options.
The exported file can now be used as a template and filtered and edited accordingly.
Save the edited file as a CSV file in UTF-8 format
Finally, import the CSV file into your shop.
CSV import/export with LibreOffice Calc
Open LibreOffice.
Select the CSV file via Open File.
Similar to Excel, a dialog opens where you need to make the settings as described above.
Confirm the settings via OK.
The exported file can be used as a template and filtered and edited accordingly. Make sure to use the same decimal separator as in your admin.
Save the file as a CSV file in UTF-8 format. To do this, select Save As and check the box next to Edit filter settings. Then click Use CSV Format and select UTF-8 format again in the dialog that appears.
What to do if my spreadsheet program doesn't support UTF-8 encoding?
If your spreadsheet program doesn't support valid UTF-8 encoding, you can ensure UTF-8 encoding as follows: Open the edited CSV file in a text editor and save it there as a UTF-8-encoded CSV file. This detour via the editor allows the file to be saved correctly in UTF-8 formatting.
For the import in the admin, the CSV file must be UTF-8 encoded. To be sure, test the import beforehand in a test environment or in the staging system. Before performing the import on the live system, it's advisable to create a database backup first.