formating lost on import

[cavscout]

I'm importing an excel spreadsheet into a table in access97. The field in question has text formating though most of the contents are numeric.

When the import script runs, I've told it to delete the existing Access table and import the new Excel version to replace it. This happens as expected except for the one field. The Excel text field becomes a Access Numeric field. When I change the format of the access field back to numeric and run the script, it changes again.

I don't want the access table to append each time, I only want the new version of the Excel table imported.

Is there a setting that is defaulting to numeric format? Am I doing this the most efficient way (delete and recreate)?

 

[cavscout]

formating Lost on import

BINGO! I guess mine is looking at the first record it imports. I slipped a mock record into the first line to force the format and all looks good.

It would have taken me forever to think of that.

Thanks for your help and fast reply.

 

[indyaries]

Just a thought...

Pat,

I've been in cavscout's situation. I've had to deal with importing data with over 100k records or more. The tables become HUGE, and after a time would refuse to delete all the records at once.

Ended using an import spec, and just deleting the table.

Just my 2/100 worth.

Bob in Indy

 

[cavscout]

Thanks for the input. I've not heard of an Import Spec. I did a quick search, is this new to Access 2000? I'll have to look around to find it in 97. Always good to hear something new.

Thanks.

 

[indyaries]

Just a thought...

Thinking it might be worthwhile to convert the Excel spreadsheet to a CVS file, then create an import spec, and use the transfer text method.

 

[indyaries]

Why not append the spreadsheet to an existing table?

Pat, I agree this could be done. I was thinking of import errors. I had Excel data that I could not get to import properly. Access would generate import errors on this field, as it contained a mix of text and numbers on some records, and just numbers on others. I needed this field to import at text only.

The only way I could import the data error-free was to use the transfer text method, first saving the .xls to .csv

 

[Friday]

indyaries:

Do you have control over the spreadsheet? Who creates it and where does the data come from? I had a similar problem on data that I was bringing down from a mainframe database. What I did was have the programmer in charge of the mainframe data place code in the export phase to append a semi-colon to the front of each 'field' in column one. Then I wrote a query that strips this out after it is in Access. That way, Access always sees it as a text field. To get rid of the errors, set warning false at the start of your code but remember to turn them back on. You can then write code to get rid of those pesky tables Access creates showing you any errors in the transfer.

 

[indyaries]

Friday,

Unfortunately, I have no control over the formatting of the data I receive. I've been able to work around the problems, though.

Bob in Indy