TransferSpreadsheet Import error?

[jonman03]

Hey all,

I'm using the TransferSpreadsheet action to import data from a .xls to "tbl_master" in Access. I have the field and column headings match so it should be a direct import. All data but 1 field/column is working correctly...

I have a column in Excel that is on General formatting that contains numbers and some "N/A" 's. Since it is mixed, I have the format of the field in the Access table set to Text. However, when I run the macro, it's dropping out all the N/A's and only importing the rows with numbers, leaving the N/A spots blank in the table.

Shouldn't it import everything since the field is set to Text? Please help!

Thank you all!

-Jon

 

[SOS]

It is because it is text and not numeric and because Access sees the majority of the first lines as numeric it will do that.

I would create the table you want it to go in and set the datatype to text and then import to that table instead of letting it create one when importing.

 

[jonman03]

It is because it is text and not numeric and because Access sees the majority of the first lines as numeric it will do that.

I would create the table you want it to go in and set the datatype to text and then import to that table instead of letting it create one when importing.

I have already created the table I want the data imported to and have set the Field type to Text before running the macro. However it's still dropping out the non-numeric values?

Am I missing something simple? Thanks!

 

[SOS]

What field size did you select for the field?

 

[jonman03]

What field size did you select for the field?

The properties for the field are as follows:

Data Type: Text
Field Size: 50
Required: No
Allow Zero Length: Yes
Indexed: No
Unicode Comp: Yes
IME Mode: No control
IME Sentence Mode: None
All others: (blank)

Thanks for your help SOS. I have a decent amount of experience with Access and this isn't making too much sense..

 

[SOS]

Can you post a copy of the spreadsheet? (I know that is probably not possible as you probably have sensitive data in it, but it could help potentially)

 

[jonman03]

Can you post a copy of the spreadsheet? (I know that is probably not possible as you probably have sensitive data in it, but it could help potentially)

Yes I can, I'll remove sensitive information and post momentarily...

 

[jonman03]

I've attempted to attach the .xls file.

The column/field that is giving me issues is column D. As you can see its mostly numbers with a few N/A's throughout.

I have my table in access with these exact column headings.

 

[jonman03]

To all-

Found the answer here:

http://support.microsoft.com/kb/109376

No real solution other than putting a dummy row of data in the Excel spreadsheet. Thanks for the help all.

 

[SOS]

Thanks for posting back with your answer.