Unanswered: Problem importing Excel into Access
I have a problem when importing an Excel workbook into an Access database.
Basically Access does not seem to completely import any row whose total number of characters is more than approx. 1800? I know this doesn't really make sense but the example should make things clearer.
You'll need to create a spreadsheet with 200+ columns each containing a fixed number of characters (say 10 characters). I created the following file :-
Now copy A1 across 220 columns (I decided against using all 255 columns might cause unforeseen problems)
Then copy B2 to fill in the remaining cells for the table.
If I link this spreadsheet into an Access database everything is OK.
If I import this spreadsheets into an Access database a couple of strange things happen
- The order of the imported data is not the same as the data in the spreadsheet
- I also have an "Import Errors" table
- some of the data is missing from the first couple of rows
I have no idea whay the order of the data has chanegd (although it does not ALWAYS change and even when it does the changes are not consistent)
The "Import Errors" table shows that a couple of rows have an "Unparsable Record" which is strange coming from a spreadsheet. I could understand having some limit on text files (originally I was using tab delimited text files but moved to spreadsheets when this issue occurred) but spreadsheets?
The 1st row has 179 columns of data (179 x 10 = 1790 charters)
The 2nd row has 199 columns of data (199 x 9 = 1791 charters)
The 3rd row onwards are perfectly fine
I'm not sure that this is really an error, it might simply be a gap in my understanding but I would really appreciate some help here as I cannot even tell my users and maximum number of column?
The attached files containing the original data plus how it looks after importing into Access.
"If at first you don't succeed, go to sleep"