Version: 7.0 & Up
Elliott Business Software has extensive features with CSV file export and import. It provides a gateway to view Elliott's data in Excel spreadsheets and optionally allow users to edit the data in spreadsheets and import back to Elliott. In essence, we leverage the flexibility of Excel spreadsheets to expand users' ability to process Elliott data. Elliott does not support importing from Excel XLS or XLSX files directly. Instead we support importing from CSV files, which can be handled by Excel as well. The following are common issues/problems a user may experience when importing CSV files into Elliott.
Do Not Edit CSV File Directly
If you have data in Excel XLS or XLSX format, then you should continue editing in Excel format. Do not change the format to CSV and edit in a CSV file. When Excel edits a CSV file, it will drop leading zeroes of a value that it considers numeric. For example, if you have an item number that is "012345," in Excel format you can flag the item column as a "Text" and the leading zero will be preserved. When you save the file as CSV, initially the leading zero is saved in the CSV file. But when you bring up that CSV file in Excel to edit, Excel will automatically drop the leading zero and your item will become "12345" if you proceed to save it. Since item number "012345" is different from "12345," when you import the CSV file, it will encounter errors in Elliott.
Edit in Excel File Format and Save as CSV Before Import
So the key lesson is that you should edit your data in Excel file format initially. When you are ready to import the data into Elliott, save it as a CSV file while still keeping a copy of the original Excel file. If the Elliott pre-interface of the CSV file shows errors, then go back and edit the original Excel file to correct the mistake. When done, save as the CSV file to import to Elliott again.
Since Elliott provides extensive support of exporting its data to CSV files, what if the exported CSV file already has the leading zero? How are you going to prevent Excel from dropping the leading zero when you bring up the CSV file? Please refer to the following KB article for more details:
Save As What Kind of CSV?
When you try to save a CSV file, depending on your Excel version, there can be many types of CSV files you can save to. Below is a sample screen for Excel"s "Save as":
Excel 2019 supports the following different types of CSV files:
- CSV UTF-8 (Comma delimited)(*.csv)
- CSV (Comma delimited)(*.csv)
- CSV (Macintosh)(*.csv)
- CSV (MS-DOS)(*.csv)
You should choose "CSV (Comma delimited)(*csv)." Choosing other CSV file options may result in strange characters in your CSV file and cause Elliott to import errors.
Use Notepad to Verify Errors in the CSV File
In many cases, when you edit the spreadsheet in Excel, after you drop the rows at the end, Excel considers them to be "blank" rows and still part of the spreadsheet. You may not see this issue in Excel. But when you import to Elliott, it may skip those blank rows and consider them as errors. If you run into this kind of explainable problem in Elliott's CSV import, the best way to detect it is to use Notepad to bring up the CSV file. The following is an example of how Notepad can show the blank rows of the CSV file:
Avoid Long File Names
Elliott's CSV file path and name must be less than or equal to 50 characters. We do not recommend that you use long file name formats. In particular, do not use spaces in your path or file name. Making sure the folder and file name is in the 8.3 format will ensure it works all the time.
File Name Exceeds 20 Characters
In many cases, when you use the long file name and do not use spaces in the your path or file name, Elliott will support it. However, in some situations, if the file name exceeds 20 characters, it may result in Error 013. See the following KB article for details:
Invalid Date Format
You should store the date column as MM/DD/YY or MM/DD/CCYY. For example, 12/31/2021 should be stored as 12/31/2021 or 12/31/21. If you store the value as 12312021, 31122021 or 20211231, then the import utility will detect this as invalid date format. However, if you are using older Elliott version (older than V8.28.418,) then system would import the invalid date as 00/00/00 instead of stopping it. In the mean time, if you do not pay attention and directly post the 00/00/00 transaction, it will cause issue.