Unrecognized data

[randy700]

I'm receiving my data from an outside source via an Excel spreadsheet. My application links to the spreadsheet and copies the pertinent data to the fields I need it in. The problem is with one field (serial number). I have it formatted as a text field and it is also formatted as a text field in the spreadsheet. The majority of the entries are numeric but there are several that are alphanumeric. However, no matter what I do with it, Access will only copy the fields that are numeric. The rest are left blank in the database. What, if anything, can I do to make Access recognize this data and copy it to the table?

 

[Blorf]

This is an annoying problem that I have also had. There is probably an esier solution, but...

Make the first row in your spreadsheet have a text value in that field, like "Hello"

No matter how hard I try, if the first row says 123.45, the rest is treated as a number.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[SUnderwood]

sounds good

Business and Data Analyst
Database & Web Applications
International Applications Development
VB,VBA,ASP,SQL,Java,Pascal,ADA,SSADM,UML
seanunderwood1@hotmail.com

http://www.emergencydatabasesupport.port5.com

 

[randy700]

Sorry but no - it doesn't sound good.

As stated, the spreadsheet is generated by an outside source so I have no control over what is in the first record. My intent is not to ask my customer to insert an additional, bogus, record to make the database work properly.

If anyone has a solution to this problem, please respond.

 

[lespaul]

There is no other solution for importing. When you import a spreadsheet into Access, the values of the first row are what determine what kind of field it is.

Another option would be to have your table already set up and then just append the excel information to it. Then you can declare the field to be text and when you do the append query it will still be a text field.

HTH

Leslie

 

[randy700]

Unfortunately, that doesn't work either. What you have described is what I all ready have. It's possible that there is no solution to this situation, so I guess I'll have to find another method for obtaining the data.

thanks

 

[Blorf]

Can you write VBA code to launch Excel, open the file, and export it into another format?

Ascii dumb question, get a dumb Ansi

 

[randy700]

That's an interesting suggestion. I'll give it a try and post the results here.