Home > SI5 Documentation > User Guide > Product Data > Tips and Tricks > CSV Auto-formatting on Export
CSV Auto-formatting on Export
CSV Auto-formatting on Export
Table of contents
CSV Auto-formatting on Export
Issue: some of your fields are being converted to date format when exporting data to Excel, namely Model number fields. Importing the CSV file then creates new products for any Model that was converted to date format.
Cause: the issue here is in Excel. Even though CSV format is a text file, Excel formats certain data based on what it thinks the format should be, especially with dates.
Example: Inside of SI5.5, this is how the Model display for the following two products:
When exported to Excel as a CSV and opened, those two model number are set to a date format:
Solution: You must perform the steps below in order to successfully export/import data that is experiencing the format issue.
- In Product Explorer, filter to the data you wish to export and then click the [Export to Excel] button.
- On the form, select a name and location for your file and uncheck the “Open the file after export” option:
- Open Excel to a new Workbook and from the Data tab select Get External Data->From Text:
- Browse to the CSV file you exported in step 2.
- On the first step of import wizard, click the “Delimited” radio button then click Next:
- Uncheck “Tab” and check “Comma” on the next step and then click Next:
- Highlight the Model column and then click the “Text” radio button then click Finish:
- The last step is selecting where to put the data. It should default to “=$A$1” but if it does not go ahead and change this and click OK:
- Make your edits to the spreadsheet. Do not close and reopen the file as Excel will convert the fields to date format.
- Save the file, make sure you choose to save as a CSV file (the default will be to save to a .xlsx file).
- You will be prompted with a message stating you can’t have multiple sheets, click OK:
- You will then be prompted whether you want to keep the file in the current format, click Yes:
- Close the file, click No when prompted to save the file:
- Import the file into SI5.5 using either CSV Import or EZ Match functions.
Note: the next time you open this CSV file, the fields will convert to the date format so following the steps above in order is important each time you export the data.
- © Copyright 2024 D-Tools