Import EAN-13 from Excel into Access

Question

I need to import data from an EXCEL-Sheet into Access. However Access access imports EAN-13 as Double (scientific notation) instead of a string, thus loosing the last digits.

I tried to do this import by means of DoCmd.TransferSpreadsheet acImport .... If I do so, Access will interpret the data and assign a data type.

1) This will lead to problems, when you have a column containing blank fields for the first few rows and string data later. I solved this by adding an additional row after the heading (fieldNames). In this filled data of the wanted datatype. This cured the problem for string data.

2) However I was not able to solve this for EAN-13 data (an EXCEL field containing 13 digits). EXCEL stores this sequence as a string. Sometimes it will display this string-value in scientific notation: "7340041127001" will become "7.340041127e012". However the entry filed will still show "7340041127001" in those moments - so the full value remains untouched. Using a leading "'" will even allow to avoid the scientific notation. So I think the data in Excel is ok.

If I try to import the EAN-13 data from Excel to Access this will occur: "7340041127001" becomes "7.340041127e012". Importing from "'7340041127001" works, but that would be really to tedious to edit all Excel fields!

I tried to import the EAN-13 into a predfined empty table. But this will not work with EAN: The value from Excel will be converted to a string, but the value stored is "7.340041127e012"...

The import does not need to be fully automatic, it will be done by a trained person.

Answer 1

Either apply a format to the column in Excel for Number and zero decimal.

Or force the cells to text by prefixing a quote to the cell value:

7340041127001 -> '7340041127001

Both works for me with Office 365 and linking the Excel sheet.

Then use a query to convert and append the data to the destination table.

Answer 2

1) Linking to the Excel-spreadsheet and using 'DoCmd.TransferSpreadsheet' seem to work identically in Access 2010 / Excel 2010. The data type of the fields in the access table seems not influenced by the 'Format Cell' functionality of Excel, but by the first few values (=first few rows) of the data in Excel. One (1) row is not enough, fourteen (14) are...

2) If the first few rows contain string-data and numeric-data Access will assign those fields the data type 'Text'

3) Prefixing the EAN-13 strings in Excel, will result in a correct import but is not feasible for my data. I found no way to import EAN-13 strings directly into the access table. However

4) Adding a column in Excel and using the following function will result in a value, that will be imported correctly: =Text(D2; 0) where cell D2 contains the EAN-13 string. I used this formula for all 2500 rows. Empty EAN-13 fields will be assigned a value of '0' - which is not a valid EAN-13 value. This will later allow to remove unwanted values in Access!