How to work with a CSV file in Excel

Anytime you "open" a CSV using Excel, Excel tries to be helpful and format fields according to the data in them. That means that numeric values like color codes, sizes, and UPCs get formatted as numbers. When Excel formats these fields as numbers, it strips away leading zeroes and converts especially long numbers into scientific notation (e.g. "3.09E11"). That means color codes can lose their leading 0's, size "00" looks like "0", and UPCs (and even some style numbers) can get changed into scientific notation. To avoid that, you want to "import" the data into Excel as text.

Open a CSV using Excel (Mac)

1. When you download the CSV file from NuORDER, SAVE the file to your desktop. (Do not choose "open" or double-click.)

2. Open a blank spreadsheet in Excel.

3. Choose File > Import...

4. File type: CSV File 

Screen_Shot_2020-06-26_at_3.00.26_PM.png

5. Click Import.

6. Choose the CSV you have saved.

Screen_Shot_2020-06-26_at_3.02.27_PM.png

7. Click Get Data.

8. Data type: Delimited

Delimited.png

9. Click Next

10. Delimiters: Comma (no other options should be checked)

Comma.png

11. Click Next

12. In the data preview window, select ALL columns. (Click the first column. Then scroll all the way to the right, and Shift-Click on the last column.)

13. While all the columns are selected, choose Column data format = Text

If you have properly selected all the columns, you will see a label above each column in the preview that says "Text".

Text-w-scroll.png 

Important: Be sure that all columns are selected in black as shown above, including the ones which are out of view. If you are unable to scroll to the right to select the rest of the columns, this is most likely due to your computer's System Preferences for scrolling. See troubleshooting advice for Mac users.

14. Click Finish.

15. Excel will ask "Where to you want to put the data?"

16. Choose Existing Sheet

Screen_Shot_2020-06-26_at_3.26.35_PM.png

17. Click OK. Success!  

Screen_Shot_2020-06-26_at_3.30.38_PM.png

If it looks like your color codes or UPCs are still displaying incorrectly, try the import steps again, making sure you have selected ALL columns in step 12. If you're positive you've selected all columns and set them as Text, the data may have been loaded incorrectly by the brand. The file generated from NuORDER will always provide this data exactly as it exists in the system.

Open a CSV using Excel (PC)

1. When you download the CSV, SAVE the file directly to your desktop. (Do not "open" or double-click.)

2. Open a blank spreadsheet in Excel.

3. Choose Data > From Text

4. Choose the CSV you have saved.

5. Click Import.

6. Data type: Delimited

7. Click Next.

8. Delimiters: Comma (no other options should be checked)

9. Click Next.

10. In the Data preview window, select ALL columns. (Click the first column. Scroll all the way to the right, then Shift + Click on the last column.)

11. Column data format: Text

If you have properly selected all the columns, you will see a label above each column in the preview that says "Text".

12. Click Finish.

13. Excel will ask "Where to you want to put the data?"

14. Choose Existing Sheet

15. Click OK.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request