Help! Importing large number from csv file...

[sugarflux]

Hello all.

Hoping someone may be able to help me with this one...

I've had an ASP/VBScript import routine setup for a long time which [enormously simplified] collects a csv file from a server and imports the contents into an Access table. There are usually approx 14 columns and around 640k rows.

This has been working perfectly for a long time but recently I started noticing some strange null values where I was expecting large numbers. I've tracked the problem down to the fact that Access is returning a #NUM! error when a number is above a certain amount. (around 4bn currently).

Now the Access table has the field set to DOUBLE which should be plenty big enough to store the data - the problem is occurring when reading the CSV file using:

SELECT MyBigNumber
FROM [Text;HDR=Yes;FMT=Delimited;DATABASE=C:\Folder].[MyFile.csv]

Access states that when reading from a file it "guesses" the correct data-type based on the first few rows. I've tried importing sorted by the big number column. I've also tried converting the big number column to text. But as all this processing happens after the initial read I've had no success. I still just get the #NUM! error.

So my question is this - how can I read my big number from the csv file (i can't do it in script line by line - it takes far too long)?

Any ideas at all welcome!

Thanks in advance,

~S~

 

[sugarflux]

Well - it looks like I've managed to solve this issue before anyone else got round to helping out!

For information in case anyone else is searching for something similar -
I created a Schema.ini file in the same location as my import csv file. Within the file i specified:

[MyFile.csv]
ColNameHeader=True
Format=CSVDelimited
Col1=MyBigNumber Double

Now when I import I get the big number included!!

Hope this helps someone out

~S~