BULK INSERT - escape " - .fmt - SQL Server Forums

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

Transact-SQL (2005)

BULK INSERT - escape " - .fmt

Author

Topic

anxcomp
Starting Member

41 Posts

Posted - 2007-09-04 : 13:53:02

Hi,

Simple table:

TABLE:
create table employee (
name char(20),
title char(120)
)

DATAFILE(.csv):
name;title
Gustavo;"Marketing Assistant"
Catherine;"Engineering Manager"

I'd like use BULK INSERT command to insert this data. Tell me please,
how should look formatfile (.fmt) for this example (SQL 2005). I need
data after BULK INSERT looks like this:

name title
-------------------- ---------------------
Gustavo Marketing Assistant
Catherine Engineering Manager

NOT like this:

name title
-------------------- ----------------------
Gustavo "Marketing Assistant"
Catherine "Engineering Manager"

So, " should be escape.

Thank you

--
Regards,
anxcomp

anxcomp
Starting Member

41 Posts

Posted - 2007-09-06 : 15:25:14

Hello,

I've done this, it's almost finished. You can tray this:

/***************************************************************************************************/
--table
CREATE TABLE [dbo].[ImportTest](
[fname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[lname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[organization] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[address] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[zip] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[city] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[state] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[email] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

--data file (ImportTest.csv), REMEMBER at the end you HAVE TO add ENTER
fname;lname;organization;address;zip;city;state;email;
Shirley;Birosik;;;90013;"Los Angeles";"CA";"sbirosik@gmail.com";
Dennis;Eschen;;;94920;"Alhambra";"CA";"de@hotmail.com";
Valerie;Chambers;;;91803;"Long Beach";"CA";"vweber@verizon.net";

--fmt file(ImportTest.Fmt), add ENTER at the end
9.0
8
1 SQLCHAR 0 510 ";" 1 fname Latin1_General_CI_AS
2 SQLCHAR 0 510 ";" 2 lname Latin1_General_CI_AS
3 SQLCHAR 0 510 ";" 3 organization Latin1_General_CI_AS
4 SQLCHAR 0 510 ";" 4 address Latin1_General_CI_AS
5 SQLCHAR 0 510 ";\"" 5 zip Latin1_General_CI_AS
6 SQLCHAR 0 510 "\";\"" 6 city Latin1_General_CI_AS
7 SQLCHAR 0 510 "\";\"" 7 state Latin1_General_CI_AS
8 SQLCHAR 0 510 "\r\n" 8 email Latin1_General_CI_AS

bulk insert ImportTest
from 'd:\bulk\ImportTest.csv'
with (
formatfile = 'd:\bulk\ImportTest.Fmt',
firstrow = 2
)

--(2 row(s) affected)

select*
from ImportTest

Dennis Eschen NULL NULL 94920 Alhambra CA de@hotmail.com";
Valerie Chambers NULL NULL 91803 Long Beach CA vweber@verizon.net";

/***************************************************************************************************/

For me now most important is why didn't insert first row (data row, not column definition)?
When I added quotation mark to column definition works fine, but I can't modify .csv file, I received it from third part company :( is it possible add all rows without modification .csv file?

Second, how remove quotation mark and semicolon from last column?

--
Regards,
anxcomp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources