OpenRowset from csv/Excel file with "1,000" entries...

[StevenK]

I'm using sql syntax like the following:

select * 
from OpenRowset('MSDASQL', 
'Driver={Microsoft Text Driver (*.txt; *.csv)}; 
  DefaultDir=C:\MyDirectory\', 
 'select top 1 *  from MyFile.csv')

This works well.

Except for the case that I sometimes have numeric entries in the '.csv' file that have thousand separators so perhaps I have lines like:

(Suppose the first line is the field/column names in this instance)
CustomerCode,CustomerName,Balance
CUST1,Customer 1,250
CUST2,Customer 2,"1,000"
CUST3,Customer 3,400

When I run the above SQL syntax I get the result set

CUST1 | Customer 1 | 250
CUST2 | Customer 2 | NULL
CUST3 | Customer 3 | 400

So the value with the thousand separator in it ("1,000") is being interpreted as a NULL in my result set.

Can anyone suggest how I can get the required value of 1000?
Is there some property I can set that will enable me to do this?

Thanks in advance.
Steve

 

[StevenK]

Has anyone encountered an issue like this?
And been able to work around it?

I hope that someone is able to offer some advice.

Steve

 

[simian101]

The balance field is being interpeted as a number because the first couple of lines are numbers. Then it hits a number in quotes becuase of the comma in the number. The system interpates this as text not a number thus the null value.

You can change the field separator to something other then comma like a |.

Or you can add quotes to every field and bring them in as text then convert the fields you want as numbers to number.

Simi

 

[StevenK]

The imported files are submitted by the site users - and are essentially out of our control.

We're trying to keep the restrictions (i.e. file format to be used by the submitter) down to a bare minimum - else this may scare some users off (as not all are technical know-hows).

As such is there any means via the 'OpenRowSet' query to allow for the values as seen?

Thanks again.
Steve