loading of .csv files into sql server

Question

loading of .csv files into sql server

surya-434952

SSC Eights!

Points: 952
More actions
July 10, 2007 at 11:24 am

#372766

I am trying to export a CSV file into sql server. I wrote a query to perform select fields from csv file using OPENROWSET. I am getting all the values in a single column, but I need to get in three different columns. Any can body can help me

The query is as follows
Select * from OpenRowset ('MSDASQL','Driver= {Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=” Directory name” \;Extended properties=''ColNameHeader=True; Format=Delimited;''','select * from Sample.csv')

Sample.csv
col1, col2, col3 ------------ all the values are in single cell of excel csv file
1, a, abc
2, sfasf, sdgagas

Output of query is
col1, col2, col3 --------------- all the values are coming in one column and with comma between them
1, a, abc
2, sfasf, sdgagas

Viewing 15 posts - 1 through 15 (of 43 total)

You must be logged in to reply to this topic. Login to reply

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 1

I would use BCP or SSIS to import the records. If you must do it this way try specifying column names instead of * in your select...

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

bledu SSChampion Points: 12335 More actions · Answer 2

the problem with your script is you have put too many single qoutes

Delimited;''','select

here is you corrected script

Select

* from OpenRowset ('MSDASQL',

'Driver= {Microsoft Text Driver (*.txt; *.csv)};

DefaultDir=C:\;Extended properties='' ColNameHeader=True;

Format=Delimited;'

,'select * from Sample.csv')

Everything you can imagine is real.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

Surya;

I am not sure that you can have those parameters "in-line" like your code shows, or you need to have a SCHEMA.INI file (http://msdn2.microsoft.com/en-us/library/ms709353.aspx) in same directory as your CSV file. Assuming your can do that, I think your error is just the Format parameter needs to be CSVDelimited (e.g. Format=CSVDelimited ). Format=Delimited is to indicate a custom delimiter, e.g. Format=Delimited("\")

Hope this helps

Mark

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Why wouldn't someone just use Bulk Insert on this one?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

surya-434952 SSC Eights! Points: 952 More actions · Answer 5

hi jeff

would like to help me how to write query with openrowset

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

I guess I just don't understand why you are using OpenRowSet when Bulk Insert will load the file in about two heart beats. You wanna try that, instead? Lemme know...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

surya-434952 SSC Eights! Points: 952 More actions · Answer 7

I followed the steps provided by Mark. I created a Schema.ini file as suggested. The content of Schema.ini file is

Schema.ini

[testcsv_sa.csv]----- name of csv file

Format=CSVDelimited

ColNameHeader=True

MaxScanRows=0

CharacterSet=ANSI

Col1=col1 Text Width 10

Col2=col2 Text Width 10

Col3=col3 Text Width 10

I wrote an openrowset query. It is as follows

Select * from OpenRowset ('MSDASQL',

'Driver= {Microsoft Text Driver (*.txt; *.csv)};

DefaultDir=c:\; Extended properties='' ColNameHeader=true; Format=CSVDelimited;','select * from testcsv_sa.csv')

The result I got is as shown below

Col1 Col2 Col3

1,a,b Null Null

2,c,d Null Null

In the result I am getting all the values in the one column (in this case Col1).

How to get values in different columns.

I want to have result as shown below

Col1 Col2 Col3

1 a b

2 c d

Any one has recommendations so that I can have desired results

Thanks in advance

Surya

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Hi Surya

<<all the values are in single cell of excel csv file>>

Not entirely sure what you mean by this...would it be possible for you to open the csv file in Notepad, and copy and paste the first few lines into a message window?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bledu SSChampion Points: 12335 More actions · Answer 9

surya,

did you try my solution above?

Everything you can imagine is real.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

<Psssst! Bulk Insert...>

BULK INSERT dbname.dbowner.tablename

FROM '\\machinename\path\Sample.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

FIRSTROW = 2,

ROWS_PER_BATCH = 50000

)

No need for format files or anything else for simple CSV files. FirstRow = 2 skips the header row. All you need is a table to recieve the data (identified in the line with BULK INSERT in it).

It's faster that BCP... It's faster than OPENROWSET... and it's faster than DTS. See Books Online for more details.

Of course, SQL Server must be able to see the full UNC to the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

surya-434952 SSC Eights! Points: 952 More actions · Answer 11

Yes, I ran the query provided by Bledu. I got the results as shown below.

col1,col2,col3 --------note that all the three are coming in a single column

1,a,abc

2,sfas,dft

surya-434952 SSC Eights! Points: 952 More actions · Answer 12

Hi Morris, Here is the data of my csv file in excel spread sheet.

col1,col2,col3

1,a,abc

2,sfas,dft

bledu SSChampion Points: 12335 More actions · Answer 13

i am failing to understand where they are [all the three are coming in a single column]

where are you running this script, in QA??

Everything you can imagine is real.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Surya, Excel does nasty things when it opens files, like hiding characters which are essential for us to see when we're figuring out how to import data. Try opening your source file in Notepad, or some other simple editor, then copy and paste the first three lines - the header and two data rows - into a message window so we can all see it. Chances are, it will look different to what you are expecting.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden