x

Problem with Openrowset to read numeric data from CSV file

I am trying to read a CSV file using Openrowset import option.

In the CSV file I am having text and numeric data. numeric data is having thousand seperator and is in quotes (e.g. "1,123,124.510").

I am not able to read this data as it getting split on comma and openrowset read it as 3 columns "1 | 123 | 124.510".

Can any one help how to solve this problem.

Thanks in advance

I am using the format file as the data file is big and I am reading the selected columns.

In the file only numeric data is comming in quotes "" + thousand seperator

Also I am getting this file from external source. I can update it after I receive it but for that I have to write a different process.

I have checked the Import & Export data wizard of SQL Server it also split the numeric data but when we set Text Qualifier = " it works..... Is there a way by which we can set Text Qualifier in format file or in Openrowset OR there no way on SQL Server to read this kind of files via T-SQL command.

more ▼

asked Feb 25, 2010 at 04:50 AM in Default

Abhishek gravatar image

Abhishek
17 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

In the schema.ini you are using, did you specify all the columns and their types? Using quotes as the text qualifier should work perfectly if everything else is set properly.

There is one general article that I wrote some time ago that may help you with this: http://www.sqlservercentral.com/articles/OpenDataSource/61552/

And the official microsoft reference for the schema.ini is at http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

more ▼

answered Feb 25, 2010 at 02:11 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.6k 21 23 32

(comments are locked)
10|1200 characters needed characters left

Try this...

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\Temp;HDR=NO;FMT=Delimited', 'SELECT * FROM ImportTest.csv')

Of course, you need to change the "Database" and "From" clauses to match your file. The FMT=Delimited is what enables the string qualifier.

The best thing to do, however, is make whoever is giving you these files to format them correctly.

more ▼

answered Feb 26, 2010 at 10:18 AM

Jeff Moden gravatar image

Jeff Moden
1.8k 3 4 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x27

asked: Feb 25, 2010 at 04:50 AM

Seen: 3203 times

Last Updated: Feb 25, 2010 at 12:20 PM