Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
iloveorangesoda
Starting Member
30 Posts |
 Posted - 2007-09-03 : 09:35:09
|
| HiI am trying to bulk insert a comma delimited csv file into sql server 2000. For the bulk insert syntax I have used:SET @BulkInsertCommand = 'BULK INSERT t_Untyped FROM ''' + @FileLocation + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'')' This inserts fine into the table apart from the last field in each row which contains the last comma.The file is set up as follows: field1,field2,field3,So the bulk insert is including the last comma. How do I get around this? |
|
|
sbalaji
Starting Member
48 Posts |
Posted - 2007-09-03 : 09:58:23
|
| use comma and newline as terminator,like thisSET @BulkInsertCommand = 'BULK INSERT t_Untyped FROM ''' + @FileLocation + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = '',\n'')' |
 |
|
|
iloveorangesoda
Starting Member
30 Posts |
Posted - 2007-09-03 : 10:06:44
|
| Thanks that has worked apart from the last row in the file which still contains a comma in the last field? |
|
|
|
sbalaji
Starting Member
48 Posts |
Posted - 2007-09-03 : 10:21:02
|
| Just give newline at the end of last line,in u r file.How many files do u have for inserting? |
|
|
|
iloveorangesoda
Starting Member
30 Posts |
Posted - 2007-09-03 : 10:31:41
|
| HiThe file will always be different sizes and will be coming from an outside source |
|
|
|
sbalaji
Starting Member
48 Posts |
Posted - 2007-09-03 : 11:11:47
|
| ok,there is no problem with file size.if u have one or two file u can open up the file press enter in the last line,save it and can do bulk insert.If u have more files like 100s,then u cannot open up all the files and do the same. |
|
|
|
sbalaji
Starting Member
48 Posts |
Posted - 2007-09-03 : 11:18:29
|
| Do u have same number of columns in the file and in table?if so then try this,SET @BulkInsertCommand = 'BULK INSERT t_Untyped FROM ''' + @FileLocation + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = '','')' |
|
|
|
|
|
|
|
|
ROWTERMINATOR Problem