Skip to main content

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 2 weeks ago

Link My Books creates CSV files that are compliant with the UTF-8 standard. We do this to ensure files work well for customers using different operating systems and different software packages to process these CSV files, and to ensure that the processed CSV files can be imported back into Link My Books.

If you use Microsoft Excel to open the CSV files, everything works fine when the file contains just English characters and if your SKUs are not solely made up of numbers.

However, you may run into an issue when your CSV file also contains non-English characters (such as é, ç, ü, etc) or if your SKUs are solely made up of numbers:

  • Microsoft Excel is unable to properly display UTF-8 compliant CSV files when they contain non-English characters.

  • Microsoft Excel detects fields that are solely numbers and treats them as a number field, which can cause issues.

To resolve this issue, please do the following after saving the CSV file from Link My Books.

Click here for new Excel versions 16+

Click here for older Excel versions

New Excel version 16+

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

A screenshot of Microsoft Excel with a blank spreadsheet open, titled "Book2". The Excel ribbon is visible at the top, and the "Data" tab is selected and highlighted in orange. Within the "Data" tab, various groups of tools are displayed: "Get & Transform Data" "Queries & Connections" "Data Types" "Forecast" "Outline" "Analysis Tools" A red rectangular outline highlights the "Get Data (Power Query)" icon within the "Get & Transform Data" group. This icon is on the far left of the "Data" ribbon. The main part of the screen shows a standard Excel grid, with column headers A, B, C... and row numbers 1, 2, 3... to 50, indicating a new, empty worksheet. The sheet tab at the bottom shows "Sheet1".

Select "Text/CSV" as the data source:

A screenshot of a "Get Data (Power Query)" dialog box. The dialog box has a dark grey background and presents options to "Choose data source". Two main options are displayed as large clickable cards: Excel workbook: Icon: Excel "X" logo. Description: "Import data from a Microsoft Excel workbook." Text/CSV: Icon: Document icon. Description: "Import data from a text or CSV file." This option is highlighted with a red rectangular border, indicating it is selected or about to be selected. In the bottom right corner of the dialog box, there's a "Cancel" button.

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

A screenshot of the "Connect to data source" screen in the "Get Data (Power Query)" interface. On the left panel, "Text/CSV" is selected, indicating the chosen file type. The main section, titled "Connection settings," prompts the user to "Select local file:". Below this, a "Browse..." button is visible, accompanied by an upward-pointing arrow icon, with a red arrow pointing to it, indicating the next step for the user. In the center of the screen, there's a grey graphic of an open folder with documents flying out, visually representing file selection. At the bottom of the dialog box, there are "Back," "Cancel," and "Next" buttons, though "Next" appears to be inactive (greyed out).

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

A screenshot of a file selection dialog box, typical of a macOS Finder window, with the title "Test". The left sidebar shows "Favourites" and "Locations" for navigation (e.g., Dropbox, Applications, Documents, Downloads, Desktop, iCloud Drive, etc.). The main file display area shows a single file named "sku-export.csv". This file is highlighted in green, indicating it is selected. Its size is "7 KB", "Date Modified" is "Today, 15:31", and "Kind" is "CSV...ment". Two red arrows are present: One arrow points from the center of the screen towards the highlighted "sku-export.csv" file. The second arrow points from the center of the screen towards the "Get Data" button located at the bottom right of the dialog box. Text next to the arrows instructs: "Select the file and click Get Data". There is also a "Cancel" button next to "Get Data".

With the file now selected, click "Next":

A screenshot of the "Connect to data source" screen in the "Get Data (Power Query)" interface, similar to a previous image, but with the file selection completed. On the left panel, "Text/CSV" is selected. The "Connection settings" section now shows the selected local file: Next to the "Browse..." button, the filename "sku-export.csv" is displayed. Below the filename, the full file path is shown: "/Users/Dan/Dropbox/LinkMyBooks/Partners/Test/sku-export.csv". Both the filename and path are enclosed within a red rectangular outline. In the center of the screen, the grey graphic of an open folder with documents remains. At the bottom of the dialog box, the "Next" button is now active and highlighted in green, indicating that the user can proceed. A red arrow points to this "Next" button. "Back" and "Cancel" buttons are also present.

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

A screenshot of the "Preview file data" screen within the "Get Data (Power Query)" interface. At the top, the "File path" is displayed: "/Users/Dan/Dropbox/LinkMyBooks/Partners/Test/sku-export.csv". Below the file path, there are settings for data import: File origin: "65001: Unicode (UTF-8)" is selected. Delimiter: "Comma" is selected. Data type detection: A dropdown is visible, showing "Do not detect data types" selected. This dropdown is highlighted by a red rectangle, and a red arrow points to it from above with the instruction "Select 'Do not detect data types'". The main area of the screen shows a preview of the data in a table format with columns. The headers include "Column1," "Column2," "Column3," "Column4," and what appear to be automatically detected headers "Sku," "Product Name," "Landed C...", and "Product Group." The data preview itself is empty. At the bottom of the dialog box, "Back," "Cancel," and "Load" buttons are present. The "Load" button is green and active.

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.

A screenshot of a "Save As" dialog box overlaid on an Excel spreadsheet, which appears to be displaying data from "sku-export". The "Save As" dialog box is titled "sku-export" and is set to save the file. Save As: The filename is pre-filled as "sku-export" in a text field. Tags: An empty field for tags is present. A dropdown labeled "Test" appears to indicate the current folder. Below this, the file list shows "sku-export.csv" with its size, modification date, and kind. There are buttons for "Online Locations" and "Options...". File Format: A dropdown labeled "File Format" is present, and "Comma-separated Values (.csv)" is selected and highlighted with a red rectangular border. At the bottom right of the dialog, "Cancel" and "Save" buttons are visible, with "Save" being green and active. The background Excel window shows the "Data" tab selected in the ribbon and a spreadsheet with numerical data. A warning banner at the top of the Excel sheet says: "Possible Data Loss Some features might be lost if you save this workbook in the comma-delimited (.csv) format. To preserve these features, save it in an Excel file format."

Older Excel versions

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

A screenshot of Microsoft Excel with a blank spreadsheet open, titled "Book4". The Excel ribbon is visible at the top. The "Data" tab is selected and highlighted with a red box. Within the "Data" tab, on the far left, there's a group of "Get & Transform Data" tools. Specifically, the "From Text" button is highlighted with a red rectangular outline. Other options in this group include "From HTML", "From Database", and "New Database Query". The main part of the screen shows a standard Excel grid, with column headers A, B, C... and row numbers 1, 2, 3... to 28, indicating a new, empty worksheet. The sheet tab at the bottom shows "Sheet1".

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

A screenshot of a file selection dialog box, typical of a macOS Finder window, with the current folder "tmp". The left sidebar shows "Favourites" and iCloud sections for navigation (e.g., Dropbox, Movies, Applications, Documents, Downloads, Music, Desktop, Recents, Pictures, iCloud Drive). The main file display area shows a single file named "sku-export.csv". This file is visible but not highlighted. Its size is "3 KB", "Date Modified" is "Today, 13:35", and "Kind" is "com...lues" (likely a truncated "comma-separated values" description). Two red arrows are present: One arrow points from the center of the screen towards the "sku-export.csv" file, indicating it should be selected. The second arrow points from the center of the screen downwards towards the "Get Data" button located at the bottom right of the dialog box. There is also a "Cancel" button next to "Get Data".

In Step-1 of this wizard:

  • Select Delimited radio button

  • In File origin field - select Unicode (UTF-8)

  • Click Next > button

A screenshot of "Text Import Wizard - Step 1 of 3" in a data import tool. The wizard states: "The Text Wizard has determined that your data is Fixed Width." It then offers two radio button options for data type: Delimited: "Characters such as commas or tabs separate each field." This option is selected, and a red arrow points to it. Fixed width: "Fields are aligned in columns with spaces between each field." Below these options: "Start import at row:" is set to "1". "File origin:" is set to "Macintosh". A red rectangle highlights this field, and text below it instructs: "Change to Unicode (UTF-8)". The "Preview of selected data" section shows a snippet of the CSV file: "1 Sku,"Product Name", "Landed Cost", "Product Group"" At the bottom of the dialog, "Cancel", "< Back", "Next >", and "Finish" buttons are visible. A large red arrow points to the "Next >" button, indicating the next step.

In Step-2 of this wizard:

  • Select Comma checkbox

  • Deselect Tab checkbox

  • Click Next > button

A screenshot of "Text Import Wizard - Step 2 of 3", which allows setting delimiters for the data. The top section, "Delimiters", provides several checkboxes: Tab (unchecked, with a red arrow pointing to it, suggesting it should be selected or is being considered) Semicolon (unchecked) Comma (checked, with a red arrow pointing to it, indicating it is correctly selected) Space (unchecked) Other: (unchecked, with an empty text box) On the right side of the "Delimiters" section: "Treat consecutive delimiters as one" checkbox (unchecked) "Text qualifier:" dropdown is set to a double quote " character. The "Preview of selected data" section shows a tabular preview with "Sku" and "Product Name" as headers, indicating the data is being correctly separated by commas. At the bottom of the dialog, there are "Cancel", "< Back", "Next >", and "Finish" buttons. A large red arrow points to the "Next >" button, which is highlighted in blue, indicating it's the next step.

In Step-3 of this wizard:

  • Select Text radio button for each column

  • Click Finish button

A screenshot of "Text Import Wizard - Step 3 of 3", which allows selecting each column and setting the Data Format. Under "Column data format", there are radio button options: General Text: This option is selected, and a red arrow points to it. Date: With a dropdown set to "DMY". Do not import column (skip) Below this section, there's a "Preview of selected data" area, showing column headers "Sku" and "Product Name" with "Text" indicated below them, suggesting their data format. Two red arrows point downwards from the "Text" radio button towards the preview area, emphasizing the impact of the selection. At the bottom of the dialog, "Cancel", "< Back", "Next >" (greyed out), and "Finish" buttons are visible. A large red arrow points to the "Finish" button, which is highlighted in blue, indicating it's the final step.

Click OK to insert the data into cell A1

A screenshot of the "Import Data" dialog box in Excel. The dialog asks "Where do you want to put the data?" and provides three radio button options: Existing sheet: This option is selected. Next to it, a text field displays "=A1", indicating that the data will be imported starting from cell A1 of the current sheet. New sheet Pivot Table At the bottom of the dialog, from left to right, there are buttons for "Properties...", "Parameters...", "Cancel", and "OK". The "OK" button is highlighted in blue, and a large red arrow points to it, indicating the next action.

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.

A screenshot showing an Excel spreadsheet titled "Book4" in the background, with a "Save As" dialog box overlaid on top. The Excel spreadsheet partially shows column headers like "Sku" and "Product", and some row numbers on the left. The "Save As" dialog box is central to the image. Save As: The field is pre-filled with "Book4". Tags: An empty text field for tags. The current directory displayed is "tmp". Below the directory, a file list is visible, which includes "sku-import.csv". At the bottom of the "Save As" dialog: "Online Locations" button is on the left. "File Format:" dropdown shows "CSV UTF-8 (Comma-delimited) (.csv)". A red arrow points down from the top-center of the dialog towards this dropdown, indicating it is a key setting. "Options..." button. "Cancel" button. "Save" button: This button is highlighted in blue, and another red arrow points to it from the right, indicating it is the next action to perform.

If you have any questions about this article or feedback on how we could make it better please reach out to the support team via the blue chat icon on the bottom right of the page or via email to [email protected].

Related Articles
Navigating the Product Costs page
How to use product groups
How to bulk upload your product costs
How to upload costs from SellerBoard to Link My Books
Common issues with bulk upload files and how to fix them
Did this answer your question?