Bulk Insert without involving DTS

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.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Bulk Insert without involving DTS

Author

Topic

Chrome Orange
Starting Member

6 Posts

Posted - 2005-11-11 : 17:07:15

Hi all

is this possible

I want to use BULK INSERT to import a csv with a similar format to

1,1,"my name is"

there is an unknown number of columns so the table is created by reading the first line of the csv and then I bulk insert into the table.

the problem with the csv as above is that, using FIELDTERMINATOR=',' I get "my name is" inserted into the 3rd column, including the "" which is a problem

I could use a replace function to get rid of the "" but is takes a very long time on some of the csv files - last one was 280,000 lines.

any ideas??

TIA

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-13 : 10:14:44

see
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

It shows how to deal with quote delimitted strings.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Chrome Orange
Starting Member

6 Posts

Posted - 2005-11-13 : 10:44:30

so basically you are saying use a 'formatfile' to sort out the "" as its loaded?

So can I create a format file on the fly?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-13 : 13:40:32

Yep - see
http://www.nigelrivett.net/FTP/s_ftp_PutFile.html
for creating a text file.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Chrome Orange
Starting Member

6 Posts

Posted - 2005-11-14 : 19:22:33

thank you for that. I've just finished beating my head against a wall for not thinking of that before!

Chrome Orange
Starting Member

6 Posts

Posted - 2005-11-15 : 09:30:08

ok

I've ended up with a csv that looks like

"FIELD1","FIELD2","FIELD3","FIELD4","FIELD5","FIELD6"
1,1,"data","data","data","data"
2,1,"data","data","data","data"
3,1,"data","data","data","data"

a fmt file that looks like

8.0
6
1 SQLCHAR 0 60 "," 1 FIELD1 SQL_Latin1_General_Cp1_CI_AS
2 SQLCHAR 0 60 ","" 2 FIELD2 SQL_Latin1_General_Cp1_CI_AS
3 SQLCHAR 0 60 "","" 3 FIELD3 SQL_Latin1_General_Cp1_CI_AS
4 SQLCHAR 0 60 "","" 4 FIELD4 SQL_Latin1_General_Cp1_CI_AS
5 SQLCHAR 0 60 "","" 5 FIELD5 SQL_Latin1_General_Cp1_CI_AS
6 SQLCHAR 0 60 ""\n" 6 FIELD6 SQL_Latin1_General_Cp1_CI_AS

and I'm getting

Invalid destination table column number for source column 2 in format file

any pointers greatefully received

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-15 : 10:22:56

You have to escape the double quotes.

2 SQLCHAR 0 60 ",\"" 2 FIELD2 SQL_Latin1_General_Cp1_CI_AS
have a look at the last example of
http://www.mindsdoor.net/SQLTsql/BCP_quoted_CSV_Format_file.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Chrome Orange
Starting Member

6 Posts

Posted - 2005-11-15 : 10:35:22

perfect! thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources