Problem with number in CSV file

[huyhk]

Hi all,
I have a problem with numeric values when importing data from a CSV file
This is my code

   oCnn.ConnectionString = "Provider=Microsoft.Jet" _
            & ".OLEDB.4.0;Data Source=" & path _
            & ";Extended Properties='text;HDR=YES;" _
            & "FMT=Delimited'"

    Set oRs = oCnn.Execute("select s.* from [" & filename & "] s")

This is my CSV file

    Code,Quantity (header row)
    "A","1"
    "B","2"
    "C","3"
    "D","1,000"

After running above code, the quantity value of row 4 is null. But if i have only row 4 in my CSV file, the quantity value will be 1000. I dont knnow how to resolve this problem.Anyone can help me?

[DerFarm]

Could the database be making an assumption about the variable
type?

I know that if you go thru Access and create a template, if the
first X lines (I don't know how many) are integer, then it
automatically assigns the type of integer and you have to change
it manually.

In this case, it could be assigning a byte type to it. this would
mean that since 1000 > 255 it would come out as null?

It would also explain why you can properly read line 4 by itself. I
suspect that it you move line 4 to line 1, they will all read.

One solution would be to use an append instead of a select. This
would force the data into a pre-defined data type.

I doubt that you can do this, however, because it sounds like you
are reading in several of these things and you have no idea what
the structure will look like.


If this is the case, you could read the first few lines and make
your own assumptions, for example a numeric field will always be
defined as double, .....

HTH