May 26, 2008 at 11:05 am
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
May 26, 2008 at 12:10 pm
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]
May 26, 2008 at 5:49 pm
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
May 27, 2008 at 12:52 am
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....
May 27, 2008 at 4:59 am
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)
May 27, 2008 at 5:59 am
pduplessis (5/27/2008)
BuwahahahahahaImagine 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
May 27, 2008 at 6:02 am
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
May 27, 2008 at 7:46 am
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]
May 28, 2008 at 8:57 am
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.
May 28, 2008 at 2:00 pm
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
May 28, 2008 at 9:06 pm
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.
May 29, 2008 at 9:24 am
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
May 29, 2008 at 9:57 am
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?
May 29, 2008 at 12:10 pm
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.
May 29, 2008 at 9:31 pm
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply