All Collections
FAQ's
How to open CSV files safely with Microsoft Excel
How to open CSV files safely with Microsoft Excel

Useful if you are having issues with bulk uploads for product costs or groups

Daniel Little avatar
Written by Daniel Little
Updated over a week ago

When using Link My Books to bulk upload your product costs or product groups some users encounter errors when trying to upload them again due to Microsoft Excel not liking the CSV format very much.

THE PROBLEM

It is quite common for Link My Books users to open these exported CSV files using programs like Microsoft Excel to check their contents before editing and uploading them back to Link My Books.  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 number (often the SKU column), Excel will change the SKU format in the CSV file to suit the way that it prefers numbers to be formatted.  Unfortunately, this generally has the unintended consequence of breaking the CSV file for the upload back to Link My Books.

THE SOLUTION

If you need to open a CSV file in Excel without breaking it, here's how to do it:

New Excel version 16+

Open a new Excel sheet, select the Data tab, then click "Get Data (Power Query)":

Select "Text/CSV" as the data source:

Click "Browse" to select the file you downloaded form Link My Books:

Find the file you downloaded from Link My Books and select it then click "Get Data":

With the file now selected, click "Next":

IMPORTANT: Select "Do not detect data types" in the Data type detection dropdown:

You should now have a spreadsheet with the imported data but without Excel breaking the formatting required for the intended re-upload to Link My Books.

Remember to "Save As..." the file from Excel to a CSV again before uploading to Link My Books.

Older Excel versions

1. Open a new Excel sheet, select the Data tab, then click 'From Text' in the Get External Data group.

2. Browse to the CSV file and select 'Get Data'.

3. In step 1 of the Import Wizard choose 'Delimited' as the original data type. Click 'Next'.

4. In step 2 of the Import Wizard choose Comma as the delimiter (deselect the Tab check box) and click 'Next'.

5. 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).  Then select 'Text' as the Column Data Format and click 'Finish'.

6. 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 required for the intended re-upload to Link My Books. 

Remember to "Save As..." the file from Excel to a CSV again before uploading to Link My Books.

Did this answer your question?