FAQ: eCom and OMNI product imports

JoeyJoey Posts: 169Administrator, Moderator, Lightspeed Staff moderator
edited August 2018 in Tips, Tricks & How-To's

Hello everyone!


We at support get a lot of questions about importing. Many of our merchants have difficulty importing, make mistakes or are not confident enough because they are afraid that things might go wrong. In this post I will explain what a CSV file is, what is involved when you import, and answer the most frequently asked questions that we receive here at support. I hope this will help you get started even if your knowledge of spreadsheet programs is average or even non-existent!


Note: this concerns imports into Lightspeed eCom, not Lightspeed Retail!


In this post you will find answers to the following questions:

  • Why use a product import?
  • What is a CSV file?
  • What is a delimiter? Does it matter which delimiter I use?
  • How do I open an export file?
  • How do I save a CSV file?
  • What is UTF-8?
  • I can not choose a delimiter when opening and saving the file, why?
  • When I open my CSV file, all the information is in a single column, why?
  • My entire sheet is a mess. Can it be saved or should I start over?
  • Why should I avoid Excel if I import?
  • Can I import all existing product information?
  • I just want to update my product visibility, do I have to import all the product data?
  • Which columns are required in the import file?
  • How do I import product Weights and Dimensions?
  • Can I import the Google Category?
  • Is there a limit to how much I can import at the same time?
  • Do I have to keep the names of the columns the same?
  • Why are my EAN/UPC codes not correct? Why does it show UPC + 12 everywhere?
  • It shows that an import is already in progress and I can't start a new import. What should I do?
  • I have imported the same image 10 times using the import tool. Why?
  • Can I use imports to add filters to products?
  • Why did my import go wrong? How can support help you with your import problems?
  • General tips and warnings
  • Help center

Why use a product import?

A product import is done to create* or update a large amount of product information. *Creating products is only for standalone eCom shops, see the next question for details. To perform an import you use a CSV file, it contains all the data that you want to import into your webshop. You upload this file in the back office via Tools > Imports. After uploading the file you specify what exactly you want to import (for example products or customers). You also map the columns in your file to the fields in eCom. Instructions for this last step can be found in the manual: Import wizard: New products or Editing products.


What is the difference between standalone eCom and OMNI eCom imports?

The eCom OMNI webshop integrates with either the Retail POS or the OnSite POS. With the OMNI integration, the POS system controls product creation and management of specific fields. For example the product Price, Inventory, and Brand can only be changed within the POS system. When trying to update these fields using the eCom import nothing will happen, these fields will be ignored. For this reason, you can not create products using the eCom import, only update.


What is a CSV file?

CSV stands for Comma Separated Values. This is a file with rows of text, where the values are separated by means of a delimiter, for example ,a comma or sa emicolon. If you open a CSV file in a program like Excel, Google Sheets or LibreOffice, the file is read and the data is automatically placed in columns. What determines the anumberof columns? Everytime a comma (or semicolon) is detected, the system automatically adds a new column. The delimiter indicates where data is separated from the next.

Suppose you have a csv file and you open it in a text editor like Notepad or TextEdit, you will see the following:

See what happens?

  1. All the product data is placed on a new line.

  2. You see commas separating the fields.

Now you open this file in LibreOffice, or any other spreadsheet software. The first thing LibreOffice asks you is: "how do I interpret this text? How do I arrange your columns?" You are asked to indicate the delimiter. You select comma, which is usually the default. Perfect! Now LibreOffice can display the data in a way that is readable to you.

Like this:

As you can see a CSV file contains only text.


What is a delimiter? Does it matter which delimiter I use?

A delimiter in a CSV indicates where data is separated from each other. So, does it matter which one? Yes and No. It does matter for countries where they use a comma for the decimal, which most European countries do. Suppose you want to update the prices for all products. In Europe ,chances are high that you use commas for your decimals. If you save the file and choose a comma as the delimiter, your prices will be separated into two columns. In North America ,we use a period for decimals so using a comma as a delimiter is usually fine.

Changing which delimiter is used within Lightspeed imports and exports can be done in Settings > Workflow.


How do I open an export file?

When you open a CSV file the default software is automatically opened. If you want to open your file in another program, right-click on the file and click on "Open with". Then select the program you want to use. If you work with LibreOffice you have to select the following settings:

  • Character set: UTF-8

  • Delimiter: , (use ca omma in North America, Semicolon in Europe)


How do I save a csv file?

When you are finished modifying your CSV file you have to save it with the correct settings. I recommend changing the file name to the current date and what is being changed, for example ,1-1-2018-product-visibility.csv. If you use LibreOffice, the program wants to save the file as ODF (Open Document Format). Just select CSV as the file type.


What is UTF-8?

UTF-8 is a character set that has been developed so that your computer can correctly display different languages. A computer can not handle special characters such as é ë or ø. Example: a French computer is set up so that French text is automatically detected and displayed, but it does not know what to do with an ø or ñ. That is why UTF-8 has been developed, so that all computers encode and decode all text in the same way. Do you have German and French in your webshop? Then pay extra attention to selecting the correct character set, UTF-8!


I can not choose a delimiter when opening and saving the file, why?

In this case ,you use software where this is difficult or impossible. This might the case when using (an older version of) Microsoft Excel. That's why we always recommend working with a program like LibreOffice or OpenOffice, where you can easily adjust the settings.


When I open my CSV file, all the information is in a single column, why?

Sometimes you open a file and you get to see something like this:


When opening a CSV file, you first have to select which delimiter is used so that the software can show your data properly. When you have finished editing your file, you can also indicate with which delimiter the file should be saved. A comma was set when saving this file. If you then open such a file again and then do not select a comma as a delimiter, you will see something like the example above. Again, this is more an issue for European users.


My entire sheet is a mess. Can it be saved or should I start over?

In most cases it's better to start over, imports can not be undone and it is not worth taking the risk of damaging your live shop. You can try cleaning up your sheet by removing all the rows and columns you are not modifying.


Why should I avoid Excel if I import?

Excel is a fantastic program to work with, but if you want to use it to import into eCom it is not ideal. This is mainly due to these three reasons:

  • Delimiter: If you open a CSV file and save it in Excel you can not choose a delimiter. Again, not a big issue in North America.

  • Decimal point: Excel automatically takes over the settings from your computer. This means that if you live in Europe, Excel assumes that you use a comma as a decimal point. If you then open a CSV file that contains pa eriod for all your prices, then Excel does not recognize these as prices. If you want to calculate a discount via a formula, you will receive an error message. You can work around this by setting the column differently so that it reads the prices differently, or you have to have all periods replaced by a comma. In both cases you often lose a lot of time.

  • Character set: with Excel you have to remember to explicitly set the encoding, here is how to Encode an Excel File to UTF-8.

Can I import all existing product information?

No. You can import the most important product data, but not everything. In this manual you will find an overview of all the data that you can import: List of all options in the product CSV.


I just want to update my product visibility, do I have to import all the product data?

No, this is not needed, I recommend removing all columns from the spreadsheet that you do not want to modify. You only have to leave the required columns, read the next question about required columns.

This is all you need to change the visibility of products, two ID columns and the Visible column:

Y = visible, N = hidden, S = visible when in stock


Which columns are required in the import file?

The columns you use to match data (eg Internal ID, article code, SKU or UPC) + the columns you want to update. Just remove the columns you are not modifying, this will make the file smaller and easier for you to work with.


How do I import product Weights and Dimensions?

This can be a confusing endeavour when you start out. This is because there are 3 columns for weight and 7 for dimensions. It’s actually very straightforward, to start with weight:

Weight_Value = is the weight of the product, do not add the unit (pounds, ounces, kilograms, etc.) this comes next

Weight_Unit = the unit of your choice, either lb, oz, kg or g

Weight = the above Weight_Value is converted to grams, used to calculate shipping. Leave this column blank or delete it (the red box in the example).


In the case of product dimensions, the same thing applies:

Size_X_Value = the size of your product

Size_X = the size converted to CM, Leave this column blank or delete it

Size Y and Z are the same as above.

Size_Unit = your prefered unit, for inches add in or centimeters add cm


Tip: Only import columns you need, the above examples will import just fine if you delete the red boxes. You only need the ID columns and the data you are adding or modifying.


Can I import the Google Category?

Yes, although it’s not showing in the product export (we are aware of this request). You can add a new column to your import sheet and then add the Google category to your products. You can either use the names of the categories or the number.


Is there a limit to how much I can import at the same time?

Yes. You can import 10000 rows at once in Lightspeed eCom.


Do I have to keep the names of the columns the same?

No, this is not necessary. You can map the columns during the import wizard. However, this might cause user error and data being imported into the wrong fields so be aware. Keeping the column names the same will automatically map the fields correctly.


Why are my EAN/UPC codes not correct? Why does it show UPC + 12 everywhere?

This is because your UPC codes are often 12 digits, so Excel automatically displays them in scientific notation by default. When you click on the cell and look at the function bar in the top, you will see that the original UPC code is still there. You can solve this by changing the formatting of those cells to Number:


It shows that an import is already in progress and I can't start a new import. What should I do?

Go to Tools > Imports and look for the import that says in progress, select the import and use bulk actions to delete the import(s). You can now start a new import.


I have imported the same image 10 times using the import tool. Why?

This usually happens when you have added the same image for multiple variants. For example a single product that comes in three sizes. When you add the same image for each size (variant) they will all show up on the same product. This looks like an image duplication but in fact are just the same image uploaded multiple times.


Can I use imports to add filters to products?

Yes, but not with the products import. There is a specific import called "Product Filter Values"


You need to make sure the filters and values are created in eCom first, then you can add and edit filters using a spreadsheet.

I recommend creating an export first so you can get familiar with the available columns and fieds:


Why did my import go wrong?

You can go to the errors page in your Import, click View Report and check the message that should show exactly what went wrong. If you can not find or fix the issue using this FAQ or our documentation feel free to contact support.


How can support help you with your import problems?

If after all this information you still have no idea what went (or is going) wrong with your import, please feel free to contact support! Because import problems come in all shapes and sizes, we want to ask you to approach us in the following way:

  • Always send a ticket through the help center first, even if you are looking for phone support. This way we have the right information at hand, including the file you are trying to import.

  • If you are looking for phone support, make sure you have Teamviewer installed on your computer before you contact us!
  • Always add the CSV file you are working on to the ticket. Without your CSV file it will be almost impossible to troubleshoot.
  • Add screenshots of products that are causing problems.

  • Preferably add screenshots of your entire screen and make sure the url is visible in the address bar.

  • Add links in your ticket to the relevant products.

  • Please give us five examples instead of one.

  • Be as clear as possible in your problem description.


General tips and warnings:

  • Imports can not be undone! Not even by support.

  • Lightspeed has been recommending OpenOffice for a long time when importing but the developers of this software package are slow with updates. That is why we recommend using LibreOffice. This software works almost the same as OpenOffice, and although it is a slightly heavier program, it works more stable.
  • Before you start to create or update large amount of products, always test with 5-10 products! If something goes wrong with the import, you can easily fix the issue. Even if you are very technical, save yourself the headache, manually removing 10000 products due to a small mistake is no fun.

  • Again: you are responsible for any negative consequences of improper importing. We can not restore a backup or simply reverse your mistakes.

  • The support team is not authorized to perform product imports for a merchant. Please don't ask us to do so.

  • Do you want someone to do the import for you? We have partners to help you out!


Help centre

In our help center you can find information about importing, here are the most important help articles:


Finally, take responsibility for your own import, test your import sheet so you know what's going to happen, read the documentation to try and find solutions. If you can not figure it out make sure to send us all the relevant information as explained above.

We are happy to help our customers with all their questions and problems. This applies to quick questions, import issues or if you have done something wrong and just need guidance on how to fix it. Please contact us if you need help!

If you have any questions about importing, this is the perfect place to ask them! Also if there are questions missing in this FAQ, mention them in a comment and I will add them.

Good luck and import responsibly!





Post edited by Joey on
eCom Support Team
Lightspeed HQ

4 comments

  • michaelsteelemichaelsteele Posts: 42Member
    Bookmarked. This is great, Joey. 

    My one wish is that filters are made part of the product import template. We work with omni merchants that are managing several thousand SKUs and having to edit filters 50 at a time is very time consuming.

    Thanks again for this resource.
  • JoeyJoey Posts: 169Administrator, Moderator, Lightspeed Staff moderator
    Thanks @michaelsteele, glad it's useful

    I have updated the list with the question "Can I use imports to add filters to products?". Basically, this is possible by using the specific "Product Filter Values" import.

    I recommend trying it out first before modifying lots of products!

    eCom Support Team
    Lightspeed HQ
  • michaelsteelemichaelsteele Posts: 42Member
    I did try that, but when trying to upload a csv it was only giving me one field to map to – being the filter values. There wasn't any way to tie the filter values to individual products. I wonder if I'm doing something wrong there.
  • JoeyJoey Posts: 169Administrator, Moderator, Lightspeed Staff moderator
    You should be able to assign filter values to individual products, I will create a support ticket for you so we can see what's going wrong.
    eCom Support Team
    Lightspeed HQ
Sign In or Register to comment.