loading of .csv files into sql server

Question

Post reply

loading of .csv files into sql server

Viewing 15 posts - 16 through 30 (of 43 total)

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

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

I followed the steps provided by Moden. I executed the bulk insert statement you provided me

The result I got is as shown below

Col1 Col2 Col3

“col1 col2 col3”

“1 a b”

“ 2 c d”

In the result I am getting (“) in the first column and last column. I don’t want to use any string manipulations to get proper values in the columns. I want to have values 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

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

surya-434952

SSC Eights!

Points: 952

July 11, 2007 at 7:55 am

#718630

Yes, Bledu . I ran in QA

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

It looks like your incoming file is setup for data in one column.

"col1, col2, col3"

"1,a,b"

"2,c,d"

Can you confirm that your csv file has the double-quotes around each line like I show above? If so, you need to remove those double quotes, either before you read in the file (whichever method listed), or run a query or two on the data you did import into SQL server to strip the double-quotes and parse the data into separate values. Unfortunately, if you choose the latter, there is no easy SPLIT or PARSE function in TSQL, you will probably have to use combinations of SUBSTRING and CHARINDEX to extract the parts.

Hope this helps

Mark

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

any can help me how many rowterimator characters availble. i knew about '\n\,'\r' is there any other characters for row termination in the case of bulk insert

Thanks in advance

PHILISPART Grasshopper Points: 13 More actions · Answer 5

Select * from OpenRowset ('MSDASQL',

'Driver= {Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\Import;

Extended properties='';ColNameHeader=True;Format=CSVDelimited'

,'select * from sample.csv')

/* -- one difference delimited with ,

files in same dir

sample.csv

col1,col2,col3

1,a,abc

2,sfasf,sdgagas

schema.ini

[sample.csv]

Format=Delimited(,)

ColNameHeader=True

MaxScanRows=0

Col1= COL1 text Width 30

Col2= COL2 text Width 30

Col3= COL3 text Width 30

CharacterSet=ANSI

RESULT

COL1 COL2 COL3

------------------------------ ------------------------------ ------------------------------

1 a abc

2 sfasf sdgagas

(2 ligne(s) affectée(s))

*/

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

I followed the steps provided by Moden. I executed the bulk insert statement you provided me

The result I got is as shown below

Col1 Col2 Col3

“col1 col2 col3”

“1 a b”

“ 2 c d”

In the result I am getting (“) in the first column and last column. I don’t want to use any string manipulations to get proper values in the columns

Please post the actual code you ran because it works just fine on the data you posted...

--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

Mark, I am pretty much sure that there is no double quotations in my csv file. i am puzzled how they are coming in the result

surya

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 8

I am curious why you do not use Jeff Moden's suggestion bulk insert.

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

...or post a few lines of the file opened with a text editor so we can see those pesky double-quotes!

^{“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

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

The query i ran is as follows.

BULK INSERT dbname.dbowner.tablename

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

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n',

FIRSTROW = 2,

ROWS_PER_BATCH = 50000

)

The sample file i obtained from the client is opened in excel with the following columns.

col1,col2,col3

1, a , b

2, c , d

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

Surya,

Notice the following lines...

BULK INSERT dbname.dbowner.tablename

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

Did you make the appropriate substitutions for the lower case items?

--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 12

surya-434952

SSC Eights!

Points: 952

July 16, 2007 at 8:49 pm

#719848

Yes modem

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

Would appreciate two things please... since you obviously can't get my last name correct, please call me "Jeff".

Second... I asked you to post the code you ran... you did not. Please post the code you ran that came up with the error. That should include the correct replacements we just talked about in the code.

--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 14

First of all i plead sorry for writing ur last name wrong, second thing i query i ran was

BULK INSERT testcsvtable

FROM 'c:\surya.csv '

WITH ( FIRSTROW = 2, FIELDTERMINATOR =',', --KEEPNULLS,

ROWTERMINATOR ='\n' )

select * from testcsvtable

Thanks in advance

surya

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

Hi Surya

It would sure help us all if you could post the first few lines of your file 'surya.csv', opened with Notepad...

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