There is a flaw in the Excel ODBC driver and this is basically out of our control. The excel ODBC driver reads the first 8 rows to guess the data type of the column. The solution for this is to put ' (apostrophe or single quote) symbol before the number so that Excel doesn’t spit it out as a number (e.g. '2000) - this will be treated as text type instead of numerical data.
Description of the problem:
The Catalog number with numeric entries are being treated as "Number" type having decimal places rather than Text type.
The field has been formatted with TEXT as data type.
The Item Number has been changed from number to text format in Microsoft Excel. However, it is still displaying the same result.
How to resolve this issue?
This is a flaw in the Excel ODBC driver, it reads the first 8 rows to guess what the data type is of the column, so you can either re-arrange the sheet so that the row with text comes on one of the first rows
or
insert ' (single quote symbol) before the number entry per product (e.g. at the start of the number). Setting the type to TEXT will NOT help in any way. You must put a ' (single quote) at the beginning of the Catalog number entry.
Once you have edited your spreadsheet, then you can import products into your shop again
You can also convert your database into Tab delimited (*.txt) format. To do this, please follow the procedure below:
1. Open up an existing spreadsheet in Microsoft Excel
2. Click File menu, select "Save As" to open the Save As dialog box.
3. Select "Text (Tab delimited)" from the Save As Type pull-down menu in the Save As dialog box.
4. Click "OK" on the first dialog box warning, which advises that Text (Tab delimited) does not support multiple sheets.
5. Click "Yes" on the second dialog box warning, which advises that the
Text (Tab delimited) format does not preserve formatting and features of
the spreadsheet.
6. Check that your final conversion file is correct by opening Notepad and finding the .txt extension version of your Excel file.
After converting the database to tab delimited .txt format, please check this Knowledgebase article for the steps on how to import to ShopFactory using import wizard add-on:
