Excel and CSV Files
StoreTender can export data into CSV or Excel files, using the Grid function. If your data entry screen has the Grid button, you can launch it to see your data in a grid and export that data quickly. We recommend only exporting data in CSV format. It is quick and exports raw data only. Exporting data to Excel will generate a formatted file that will have visual appeal, but might not render PLU numbers with leading zeroes correctly.
When using the Grid you can filter data by entering your filter criteria in the box under each header. For example, if you wanted to only export Products belonging to Department #1, enter '1' in the filter box. You'll see that only Products with that Department number now appear in the Grid. When you go to File > Export > CSV, only the Department 1 items will export. You can also drag columns to the left or right, so that you can order the exported CSV to your liking. Exporting to CSV does NOT export headers, so you'll want to keep note of what each column is for.THE PROBLEM
It is quite common for StoreTender users to create exported CSV files for use in programs like Microsoft Excel. However, when Microsoft Excel opens a CSV file, it attempts to interpret the data in that file and starts making changes to it.
For example, if Excel sees something in the CSV file that it identifies as a date (often incorrectly), Excel will change the date format in the CSV file to suit the way that it prefers to dates to be formatted. Since many UPC codes start with leading zeros, the data is misinterpreted by Excel and the leading zeros are dropped. Unfortunately, this generally has the unintended consequence of breaking the CSV file for the use CSV was specifically created for.
THE SOLUTIONIf you need to open a CSV file in Excel without breaking it, here's how to do it:
- Rename the CSV file extension from '.csv' to '.txt'.
- Open a new Excel sheet and navigate to File > Open.
- In the Open dialog box, change the file type to 'All Files (*.*)'.
- Browse to the text file file and select 'Open'.
- In step 1 of the Import Wizard choose 'Delimited' as the original data type. Click 'Next'.
- In step 2 of the Import Wizard choose Comma as the delimiter (deselect the Tab check box) and click 'Next'.
- In step 3 of the Import Wizard, you tell Excel not to change your formats. With the first column in the Data Preview selected, scroll across to the last column and select it while holding the SHIFT key (all columns should now be selected, in black). Then select 'Text' as the Column Data Format and click 'Finish'.
- Click OK to insert the data into cell A1
You should now have a spreadsheet with the imported data but without Excel breaking the formatting. Remember to export the file from Excel to CSV again before exiting, by navigating to File > Export > Change File Type > CSV (Comma delimited) (*.csv). Click 'Save As' and name your newly exported file.