Excel to Access data problem

[smack1]

I have a client’s spreadsheet in Excel 2k. I am trying to import it into a new table in Access 2k. one of the fields in the spreadsheet is like a serial number. It contains letters, numbers, dashes, etc. They are generally between 5 and 20 characters in length. When I import it into Access some of the numbers convert to scientific notation.

In the spreadsheet the cell format is text as well as the data type of the field it is going into in Access.

The majority of the numbers are imported fine, but as you scan down the list individual numbers and large sections are converted to Scientific notation.

I would like to avoid having to move these manually because there are around 20k records.

Sorry, if this is already discussed in this forum somewhere; I made several searches and found nothing pertaining this problem.

Thanks in advance,
Matt

 

[AlexMidd]

Any examples of what kind of numbers are being converted and into what, specifically? Just some examples, not every occasion - for instance, are there any common factors evident between any of the items being converted? Have fun! )

Alex Middleton

 

[linrudland]

Hi
I've found that converting the file type from Excel into CSV normally resolves this type of issue. The text type format in Excel just does not seem to be recognized when importing to Access. If it doesn't work first time then force a wrapper of double quotes around the field and then try - Access has to see it as text then you can delete the quotes en masse in access using search and replace

 

[Geekmomz]

For what it's worth, I had a similar problem, but the other way around. Serial numbers and shipment tracking numbers exported from Access to Excel were getting converted. I ended up solving the problem by putting the following into the Format property for the relevant fields:

&&&&&&&&&&&&&&&;"None"

That keeps both the serial numbers (of varying lengths) and tracking numbers from getting coverted, and shows None if no value has been entered. You might want to experiment with the length of your format string, but I will bet that this imple change will resolve your problem.

 

[smack1]

Hey Guys, Thanks for the prompt response! Sorry mine hasn’t been as quick. I had another issue to solve first. The client I am working for had purchased this customized spreadsheet from someone else and it was password protected so I could not save it out as a csv. So I just copied out the data into a new spreadsheet then saved it as a csv. My problem there is that many of the values in the sheet have commas in them. However, from there I didn’t have the scientific notation problem. Well just a few which was manageable. For the rest if the data I just made an OBDC connection to the new spreadsheet and am querying it with ColdFusion where I can check the data when I pull it in.

Thanks for the help!
Matt