loading of .csv files into sql server

Question

Post reply

loading of .csv files into sql server

Viewing 14 posts - 31 through 43 (of 43 total)

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

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

Ok... I made a spreadsheet that looked like this...

col1

col2

col3

1

a

abc

2

sfas

dft

I "exported" the data to C:\surya.csv using {File}{Save AS} and saved the file as "CSV (comma delimited). The file that created looks like this in NotePad...

col1,col2,col3

1,a,abc

2,sfas,dft

Then, I created a table that looks like this...

CREATE TABLE TestCsvTable (Col1 INT PRIMARY KEY, Col2 VARCHAR(10), Col3 VARCHAR(10))

GO

Then, I ran the following (which is exactly what you posted)...

BULK INSERT testcsvtable

FROM 'c:\surya.csv '

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

ROWTERMINATOR ='\n' )

... and it ran fine with 2 rows reported.

Then, I ran this to see what was in the table (just as you did)...

SELECT * FROM TestCsvTable

... and this is what I got...

Col1 Col2 Col3

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

1 a abc

2 sfas dft

(2 row(s) affected)

Now, at the risk of being rude, exactly what is your problem with this simple process???

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

hi jeff,

I am sorry to if i have disturbed people here , but i am hitting the wall from the past 15 days, the excel file is as shown in below.It came from the client with 55 columns , i am just giving sample only.

Col1,col2,col3

A,b,c

D,e, f

steve smith-401573 SSChampion Points: 10889 More actions · Answer 3

If your 'comma' is not working as the delimiter (which is what you illustrated), what IS the delimiter for fields on the file?

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

surya-434952

SSC Eights!

Points: 952

July 19, 2007 at 12:05 pm

#720875

comma is delimiter of file

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

Ok, fourth and last try, after this I'm giving up. Surya, please, open the file in a text editor and post the first three or so lines. This will show us the structure of your file.

Steve, Jeff and everybody else who's tried hard to help you won't have to guess any more.

If the file contents are sensitive then you can always change the alpha characters, but leave quotes, double quotes, tabs etc alone.

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

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

Ah... now I understand... lemme see what I can do...

By the way... what is the "extension" used on the filenames you are receiving???

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

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

Heh... I made a spreadsheet with all of the data in one column and exported it as a TEXT (OS/2 or MS-DOS) file, and lo-and-behold! Guess what I got?

"Col1,col2,col3"

"A,b,c"

"D,e, f"

Looky there... double quotes just like you said...

I'm still working on it but I really need to know the extension on the file names they're sending you...

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

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

Ok... I've got the simple fix... you have to change the way you are exporting the data. Because all of the data is in a single column and it contains commas, it's adding the quotes around the data as if it were meant to be a single column of data. So, here's the required steps...

You have a spreadsheet that looks like this (comma separated values contained in a single column)...

Col1,col2,col3

A,b,c

D,e,f

Instead of try to export as CSV or anything else having to do with CSV or Tab or just plain text, you must export by saving the file as Formatted Text (Space Delimited). When you do that, the file name will be given the extension of "PRN" and will look like this when opened using NotePad (file name is C:\surya.prn) ...

Col1,col2,col3

A,b,c

D,e,f

Now that we've gotten rid of the surrounding quotes, everything else works just fine as I said it should (note the minor changes in the comment due to the method and data changes)--DROP TABLE TestCsvTable

--===== Create the test table (changed column 1 to varchar because data example changed)

CREATE TABLE TestCsvTable (Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10))

GO

--===== Import the data as before but using the prn file extension instead

BULK INSERT testcsvtable

FROM 'c:\surya.prn'

WITH (

FIRSTROW = 2,

FIELDTERMINATOR =',',

ROWTERMINATOR ='\n'

)

--===== Display the results of the import

SELECT * FROM TestCsvTable...

That should do it... funny thing... you mentioned it was all in one column and we all ignored that fact ... I'm very sorry about that. Thanks for hanging in there with us dummies.

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

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

Oh yeah... almost forgot... if the file extension is "CSV", you may be able to import the data directly using the methods shown without ever going through Excel provided that it doesn't have double quotes in the file... open it in Notepad to see for sure...

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

Thanks you Jeff, it worked. I changed my file into .prn format, then i got the solution. I am once saying sorry for giving trouble to people here.

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

Nope... not your fault, Surya... you had a problem, you described all the symptoms, and we all missed it. We should be apologizing to you...

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

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

Spot on, Jeff, and apologies to you, Surya.

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

steve smith-401573 SSChampion Points: 10889 More actions · Answer 13

At the risk of creating clutter, I also want to apologize, Surya. Sometimes we are so certain that we know the answer, we forget to read the question CLEARLY. You were clear, we just didn't hear you. While usually the burden of proof is on the speaker, in this case you provided the proof but we ignored it.

Side Question - how is it that the comma delimited lists are entered into single cells of the spreadsheet to begin with?

Side Observation - had your examples had other than 3 entries in each row, it would have been harder for us to miss what you were saying. The content screamed a 3x3 matrix and drowned out what you were really saying. Do the entries ALWAYS have 3 elements?

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

I am thanking every body in this forum because all them spend their valuable time for me. Especially I am very much thankful to Jeff guided me all the way with patience.

Hi Steve Here are answers for you:

Answer to Side Question #1: Our client sent us the spreadsheet as a sample, may be they opened csv file in excel spreadsheet.

Answer to Side Observation: we have 55 fields in original file, I created a sample with 3X 3 matrix which is similar to our sample file