General discussion

Locked

import excel bombs

By dief05 ·
Hopefully a simple question for Access 2000: I'm importing (through a VBA script and transferSpreadsheet) an excel spreadsheet. One of the columns is a cost center ID which may contain numbers and/or letters. If the first 25 Excel rows contain nothing but numeric cost center IDs, the import wizard seems to assume the column should be numeric (even though both the spreadsheet column and the receiving table column are text fields) and drops any subsequent IDs that have letters in them. If it sees an ID in the first 25 with a letter, all's good. Any way to fix this? Is there a setting that I can change so that the Access import wizard looks at more than 25 rows? Any help would be greatly appreciated. Thanks

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by rkuhn In reply to import excel bombs

Scratch the whole idea of Excel. Sure it can probably be done, but just save the Excel as a text delimited file and then do the import. Then, all fields can be formatted and imported as "text".

Collapse -

by dief05 In reply to

Can't save the file as delimited - we're dealing with a warehouse, any extra step would make it too difficult, and the save as delimited file would be too complicated. have to import directly from Excel as a 1 step (click a button once) process

Collapse -

by dief05 In reply to import excel bombs

Point value changed by question poster.

Collapse -

by mslizny In reply to import excel bombs

I do a lot of data conversion, and my
experience with Excel to Access is that if the
entire column in Excel is formatted as text
before you use the import wizard it transfers it
as a text field. It is when you have not
specified a format that it uses "General" which
tries to discern a format from the Excel file.

Also, I know this is automated in a VBA script,
but it is always more useful for me to import
the information into a new table and then use
copy/paste-append to add it to an existing
table so that any errors in format, etc. can be
caught before the data is in the ongoing table.

Collapse -

by dief05 In reply to

Poster rated this answer.

Collapse -

by dief05 In reply to import excel bombs

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums