BULK INSERT - escape " - .fmt

Hi All,
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
--
RegardsHello,
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