Import and Linking Table Problem!

[kidrobot]

I'm linking an Excel Table to Access, but when I link it Access doesn't read some of the fields because Access pre-assigns the data type based on the first couple of records. The fields I have are Numbers and Text combined and Access declares them as TEXT, so when the fields are all #'s Access shows them as "#Num!." Also I have tried to import the sheet but again Access dictates the fields so it'll declare some columns has NUMBER so the fields with text have an error. How can I solve both problems?

 

[gemma-the-husky]

always a problem

i tend to expot xls files to csv, then use an import spec unless i am absolutely certain the data is what i want

an other issue is if there are only numbers in a column, access will import the cells as numbers even if you want them to be text. formatting excel cells as text doesnt seem to help

 

[kidrobot]

Thanks gemma, I don't understand why the import wizard doesn't let you choose the data type. Maybe 2007 does?

 

[kidrobot]

I tried the CSV thing and it worked great, but I don't understand why Excel wouldn't be as compatible to link as CSV is.

 

[boblarson]

Also I have tried to import the sheet but again Access dictates the fields so it'll declare some columns has NUMBER so the fields with text have an error.

For this problem another solution is to build the table first, with your data definitions, and then import it.

 

[kidrobot]

For this problem another solution is to build the table first, with your data definitions, and then import it.

Yes, I've also tried that. Although, instead of importing it I just copied and pasted it from excel, is there a downside to doing this or does it not matter?

 

[boblarson]

Yes, I've also tried that. Although, instead of importing it I just copied and pasted it from excel, is there a downside to doing this or does it not matter?

Well, if you're going to be doing it alot then the downside is that it's more work. But, if you build the table and just use a delete query to empty it and then use code to import it again it should be fine.

 

[kidrobot]

Well, if you're going to be doing it alot then the downside is that it's more work. But, if you build the table and just use a delete query to empty it and then use code to import it again it should be fine.

Hmm, very smart. I won't be here forever so making it automatic would be better. thanks bob.

 

[KKilfoil]

You could add a dummy record at the top of your Excel data that contains alphanumeric info in all of the fields that need to be of type text.

If necessary, filter the dummy record out before you use the rest of the data.

 

[boblarson]

You could add a dummy record at the top of your Excel data that contains alphanumeric info in all of the fields that need to be of type text.

If necessary, filter the dummy record out before you use the rest of the data.

Maybe for a one-time-import, however, if the data will be the same over and over again - why not build the table once and never have to touch the Excel file over and over again?