splitting data into multiple sheets - excel...

[dbaril]

I have an Excel spreadsheet with client names and addresses on it. It has grown to over 30,000 rows (one row per record). I am now trying to import it into Access, but Access will only import 448 records before it says there is too much data. Is there any way that I can easily split my data so that sheets are created with 400 rows per sheet? And the column headers are retained per sheet?

 

[sheetspread]

Something's wrong, you should be able to import far more than this. Did you try copying the spreadsheet and pasting to an Access table?

 

[dbaril]

Tried that too...Error on copy (from excel) is "The field is too small to accept the amount of data you attempted to add, try inserting or pasting less data".
When I try to Import (from Excel), error is "Microsoft Access was unable to append all the data to the table"

I'm at a loss - also just realized that the 448 records that it does import are from random places in the excel sheet, not in order...

 

[sheetspread]

1. Do the data types in your excel columns match those in the access sheet exactly?
2. Are the number of columns exactly the same?
3. Do you have Excel cells with an insane length of data (e.g. a paragraph or two in a single cell)?

 

[dbaril]

Well, I thought I had it - Your question #3 made me realize that the Access "Phone Number" field was set to 10 characters, and the vast majority of my data had 12 (included dashes). I removed the dashes, and truncated all data in that row to 10 characters -> On import, same error, but this time 896 (double the number) records were added...

 

[sheetspread]

You said random records in the sheet were being added, what characteristics distinguish them from what was skipped?

(a question I ask myself when encountering similar Excel to Access transfer problems).

 

[dbaril]

Well, oddly, every single record that does get imported has a "Fax" number in the field (only about 10% of the total excel file has a value in this column). So a bit of an anomoly that EACH record (either when it has imported 448 or 896 records) has a value in that field...other than that, I can't find a distinguishing characteristic...Also, "Fax" is not set as required...

 

[sheetspread]

Maybe replace blanks in the fax field with a zero and see if it goes in (then replace the 0s with nulls once they're in access)?