Random missing data after importing from Excel to Access

[teleute]

Hello everyone,

I've got an Excel sheet with +700k rows and 20 columns that I wanted to import to Access. All fields are text except the field that I want to use as a primary key, but I planned to import that as a text as well.

When I used the import wizard, I set all fields to import as text except for three that I set to memo. The wizard didn't say there was any error after importing the data, but when I checked the table, I noticed there were *a lot* of records where many fields where blank. Some fields where completely unaffected by this problem throughout the entire table, but in the rest of them, there is data missing in many records, and when there is data missing, it is not always the same fields that are missing. I have been unable to find any pattern that explains why sometimes the records were imported correctly, and why sometimes they were not.

I've tried to find an answer to this problem on the internet without any success. Hope you have some ideas . Thanks in advance!

Edit: I use Microsoft Office 2010 64bits

 

[boerbende]

When you import with the wizard which have problems, you should also have a table extra indicating the errors and locations.

But maybe try it differently first
Try in Excel <CTRL><A>, <CTRL><C> (copy all)
And in the table
<CTRL><V> (paste)
I had issues once with the import, while copy paste worked

Ben

 

[gemma-the-husky]

although you think your fields are text, access looks at the first few rows to determine what it thinks is the data type. if some columns have numbers at the top, then access MAY think the column is numeric, not text.

the easiest solution is to add a row 2 to the spreadsheet, and put the word TEXT (eg) in any text column. Then the column WILL come in as text.