By default, when importing or opening a file in Excel that is not a native Excel file (.xl*), it wants to format the cell according to some assumptions about the data. If it is not certain, it makes it a general format. This interpretation can make the underlying data totally different than what you see based on this formatting conversion. This can lead to issues with dates, large numbers, and other data that would not be usable by SAM Pro and will lead to a failed import. The ultimate goal is to make sure when you save the file for import, the data in Excel is all text so that it will save valid data.
A simple example is date formats. If Excel thinks that a field is a date, it does a conversion to the underlying data. It looks correct, but it exports with unusable data.
These are two dates in an Excel spreadsheet. They look fine and are even in the format required by SAM Pro – Year-Month-Date. But this is misleading
Copied and pasted these cells into new cells
Then changed the format to text
This is how the real data looks like. How does that number relate to a date?
Sometimes, even numbers can be displayed in a manner not useable
To ensure what you see is what you get on your import file, there have to be some steps taken.
First is to make sure the Excel spreadsheet has define all the cells as text. You can do this by selecting the corner of the spreadsheet (which will select all the cells) and right-clicking inside the spreadsheet. Select format cells, select text, and click OK.
When you save the file, save it as a .csv format.
If you will be going back in to edit this file again before importing, you must change the file name from ******.csv to ******.txt (the extension needs to be .txt NOT .csv). This will insure you can define all the fields as text before it is imported and Excel makes some formatting decisions. When you go into Excel to open it up, you need to change the type of file to Open to a .txt format.
After selecting the text file, Excel will go through an import wizard. On the first screen, make sure it is marked as delimited.
On the second screen, make sure you choose comma as the delimiter.
On the third screen, you need to select all of the columns. You can do this by selecting the first column, scrolling to the last column, holding the Shift Key + Left mouse click on the last column. This will select all of the columns. Then choose Text and Finish.
This will ensure that all the cells are text. You can make your changes and then save as a .csv (this will insure that the saved file has the data separated by commas). If you are going to have to edit this again, you will have to rename as a .txt file again and go through the Import Wizard process.