load a csv file

Question

load a csv file

balamurugan.ganesan

SSC-Addicted

Points: 455
More actions
May 26, 2008 at 11:05 am

#93313

Hi,
I need to load a csv file in to SQL 2005 which has comma within the data. How to load this I am fed up trying this:angry:
Some help on this.
Regards,
Balamurugan

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Show us an example of your csv file.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

balamurugan.ganesan (5/26/2008)
Hi,
I need to load a csv file in to SQL 2005 which has comma within the data. How to load this I am fed up trying this:angry:
Some help on this.
Regards,
Balamurugan

Heh.. and I'm pretty well fed up with people that don't post enough for us to help. Get a grip on it, eh? 😉

If you're not totally fed up, please read about BULK INSERT in Books Online... if you really want some help, post the CREATE TABLE statement for the target table and attach a 100 rows of data as a file. Make sure that you include at least the first ten lines of the file including any header that may be present.

If you really are totally fed up, I suggest selling hamburgers instead of writing code... there's no such thing as "CSV Burgers". 😀

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

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 3

Buwahahahahaha

Imagine selling csv burgers at the next microsoft launch....

c - chips

s - soda

v - viennas

Maybe we have a hit here that will feed a lot of developers and make them all fed up....

Sangeeta Satish Jadhav-362431 SSC Enthusiast Points: 106 More actions · Answer 4

Use bulk insert...

--Step 1:create table to insert data from file

Create Table TmpTbl

(Column names

)

--Step 1: Build Valid BULK INSERT Statement

Set @FilePath = 'c:\test.txt'

EXEC Master..xp_fileexist @FilePath, @File_Exists OUT

IF @File_Exists = 1

Begin

Select @SQL = "BULK INSERT TmpTbl FROM '"+@FilePath+"' WITH (FIELDTERMINATOR = '","') "

End

ELSE

Begin

Select 'c:\test.txt Not Found'

Return 0

End

--Step 2: Execute BULK INSERT statement

Exec (@SQL)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

pduplessis (5/27/2008)
Buwahahahahaha
Imagine selling csv burgers at the next microsoft launch....
c - chips
s - soda
v - viennas
Maybe we have a hit here that will feed a lot of developers and make them all fed up....

That'll work! Grand opening at PASS! 🙂

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

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

Sangeeta Satish Jadhav (5/27/2008)
Use bulk insert...
--Step 1:create table to insert data from file
Create Table TmpTbl
(Column names
)
--Step 1: Build Valid BULK INSERT Statement
Set @FilePath = 'c:\test.txt'
EXEC Master..xp_fileexist @FilePath, @File_Exists OUT
IF @File_Exists = 1
Begin
Select @SQL = "BULK INSERT TmpTbl FROM '"+@FilePath+"' WITH (FIELDTERMINATOR = '","') "
End
ELSE
Begin
Select 'c:\test.txt Not Found'
Return 0
End
--Step 2: Execute BULK INSERT statement
Exec (@SQL)

Yep... that'll probably work for comma's... what if it has quote text separators or a header or or it's ragged right or... ??? That's why we want to see a description of the file and, perhaps, the file itself.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

I have to admit, over the years I have consistently found this to be the single most frustrating aspect of SQL Server, bar none. I cannot even begin to count how many customer hours (and my own personal time) has been wasted trying to get one of SQL Server's many half-baked, half-*ssed CSV solutions to work even half as well as MS-Access (let alone MS-Excel!).

BULK INSERT, BCP, Dts (with who knows how many different Text drivers), ... Oh they all "work" and can be wicked fast, IF the CSV file is formatted along certain very narrow rules. Rules that virtually no CSV creation source follows 100%, not even SQL Server itself. And in virtually every case, after weeks (actually 3 months the first time) of trying to get the SLQ Server tools to work right, I gave up and wrote a custom app to do it.

Those never took more than 2 to 3 days to write, test, debug and deploy, though they were not nearly as fast.

Jeff Moden (5/27/2008)
Yep... that'll probably work for comma's... what if it has quote text separators or a header or or it's ragged right or... ??? That's why we want to see a description of the file and, perhaps, the file itself.

And that's the essence of it right there. Balamurugan, if you are still listening, this is why we are so fussy about the format of the input file. Because the CSV import facilities of SQL Server are very fragile and each one only works for certain cases.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 8

balamurugan.ganesan (5/26/2008)
Hi,
I need to load a csv file in to SQL 2005 which has comma within the data. How to load this I am fed up trying this:angry:
Some help on this.
Regards,
Balamurugan

You can use DTS to load the file.

If there is a comma within the data, you need to enclose that field in quotes.

Veena Pande SSC Enthusiast Points: 194 More actions · Answer 9

You can create a batch file with the following code. Replace the path,tablename,userid, password,server with your path.

bcp db.dbo.tablename in c:\bcpdata\filename.txt -Uuser -Ppassword -server -c

pause

bob-685288 SSC Rookie Points: 37 More actions · Answer 10

Not every one knows everything. No need to be rude, ask for what you need to answer the question or ignore the question. I am getting tired of rude comments to what might be newbies. We were all newbies once.

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

Yep, I agree... but newbies also have a responsibility... they need to leave their frustration and bad attitude at home. Walking into a forum with words like "I'm fed up" is not the best way to make friends. 😉 Then, to provide absolutely no useful information about what they need to do just puts the frosting on the cake.

I sure don't mind newbies... Like you said, I was a newbie once. But there's a way to ask and a way not to ask...

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

rbarryyoung (5/27/2008)
I have to admit, over the years I have consistently found this to be the single most frustrating aspect of SQL Server, bar none. I cannot even begin to count how many customer hours (and my own personal time) has been wasted trying to get one of SQL Server's many half-baked, half-*ssed CSV solutions to work even half as well as MS-Access (let alone MS-Excel!).
BULK INSERT, BCP, Dts (with who knows how many different Text drivers), ... Oh they all "work" and can be wicked fast, IF the CSV file is formatted along certain very narrow rules. Rules that virtually no CSV creation source follows 100%, not even SQL Server itself. And in virtually every case, after weeks (actually 3 months the first time) of trying to get the SLQ Server tools to work right, I gave up and wrote a custom app to do it.
Those never took more than 2 to 3 days to write, test, debug and deploy, though they were not nearly as fast.

I guess my big question would be - who is at fault there? The import process who's not smart enough to handle the screwed up CSV, or the export process too stupid to properly create a CSV file as it's supposed to be?

I'm not sure how narrow the rules are - XML seems to have a lot more rules than CSV. What rules am I missing:

- fixed number of columns

- optional header line (to be determined between exporter and importer) with column names.

- character values are quoted. As such, quotes WITHIN data should be escaped or suppressed in some way.

- date values not quoted but formatted in some pre-determined format (again - to be determined between exporter and importer).

What else am I missing?

Its biggest weakness might be that because there are some moving parts - you might need to actually define the options, so that the import doesn't "guess" wrong. Then again - I don't use SQL server for its mind-reading abilities either, so I'm okay with having to tell it how to read a file.

Of all of the apps you mentioned - Excel is the most frustrating to me, since it always seems to want to second-guess whatever gets sent into it (as in - if I quote a numeric value, it should be treated as a CHARACTER VALUE and not as a $#@##$%^ number). DTS, SSIS, OPENDATASOURCE, Ms Access seem quite capable of dealing with any file worthy of being called a CSV. Comedy-separated is not a data file format - that's junk. You might as well have tossed it in a blender before packaging it up.

Not trying to take you to task by any stretch of the imagination - just curious what you see would improve the situation. Scrapping CSV? Better support somehow?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

bob-685288 SSC Rookie Points: 37 More actions · Answer 13

You know something, I should have read the initial post better. Asking a question is why we have the forums but the initial question wanted a miracle. Fix my problem without knowing exactly what it is. So I guess I owe you an apology.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 14

Thanks for the feedback, Bob. Yeah, it's sometimes very difficult to simply ignore such a post... but I should get better at just blowing those types of posts off...

Thanks for the "courtesy" reminder... 🙂

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