June 14, 2017 at 7:22 pm
i have a csv file to import data to SQL table test. i have kept the column names in csv as stage column names. CSV file is generated from source with a comma separated values, whenever there is a value that contains comma value begin and ends with double quote ("). when i use FIELDTERMINATOR as '","' it doesnt work ,i have tried with format file it doesnt work either. can someone please guide me.
when i used import using ssis it worked but i would like to do this in TSQL.
create table dbo.test_stg
(
[employee id] varchar(20),
[employee name] varchar(50),
[employee location] varchar(100)
)
--bulk insert without format file
BULK INSERT dbo.test_stg
FROM 'D:\Temp\test-151710.csv' WITH (
KEEPNULLS
,FIRSTROW = 2
,ERRORFILE = 'D:\Temp\errortest.log'
, FIELDTERMINATOR = '","'
,ROWTERMINATOR = '\n',
DATAFILETYPE='char'
);
GO
--bulk insert with format file
BULK INSERT dbo.test_stg
FROM 'D:\Temp\test-151710.csv' WITH (
KEEPNULLS
,FIRSTROW = 2
,FORMATFILE = 'D:\Temp\Format\format.fmt'
,ERRORFILE = 'D:\Temp\errortest.log'
);
GO
-- copy below 5 lines and save as .fmt
10.0
3
1 SQLCHAR 0 50 "\",\"" 1 [employee id]] SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "\",\"" 2 [employee name] SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 500 "\"\r\n" 3 [employee location] SQL_Latin1_General_CP1_CI_AS
--csv
1qe32,john,richmond
23srd,"rich,bill","white plains,new york"
23srd,"larry,low",new jersey
June 20, 2017 at 8:05 am
You'll need SSIS to handle that kind of file format. Your format file is from SQL 2008, so I have to ask, given that you are posting in the SQL 2012 forum, what version of SQL Server do you have? If you have SQL 2012, then you probably should at least replace the 10.0 with 11.0. Not sure if that will matter. SQL 2016 provides an additional specification in the BULK INSERT statement: FIELDQUOTE = 'char', where char is the character used to quote string values.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2019 at 6:46 am
What worked for me was changing
ROWTERMINATOR = '\n'
to
ROWTERMINATOR = '0x0a'.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply