bulk insert with format file sql 2008
need to do a bulk insert with a csv file that has double quotes that are not part of the data.
i understand that i would need to use a format file.
here are a couple of rows of the data.
"0002417E63"," ","13","6.00"
"0007637C6A"," ","13","6.00"
"0081A48DC3"," ","13","6.00"
"0084B8B352"," 00267254","13","6.00"
"00809C7885"," 00276254","13","6.00"
"0081A3D716"," 00276258","13","6.00"
"00809CDD61"," 00276260","13","6.00"
"0081A4EBB0"," 00276270","13","6.00"
"0081A3B754"," 00276271","13","6.00"
"0081A3BED3"," 00276276","13","6.00"
"0081A343DE"," 00276281","13","6.00"
"0081A3D22A"," 00276284","13","6.00"
"00822726D7"," 00276285","13","6.00"
"0081A4E30F"," 00276289","13","6.00"
the data types the are going to are
varchar(50),varchar(50),in
can somone give me how to set up the format file ?
10Q
i understand that i would need to use a format file.
here are a couple of rows of the data.
"0002417E63"," ","13","6.00"
"0007637C6A"," ","13","6.00"
"0081A48DC3"," ","13","6.00"
"0084B8B352"," 00267254","13","6.00"
"00809C7885"," 00276254","13","6.00"
"0081A3D716"," 00276258","13","6.00"
"00809CDD61"," 00276260","13","6.00"
"0081A4EBB0"," 00276270","13","6.00"
"0081A3B754"," 00276271","13","6.00"
"0081A3BED3"," 00276276","13","6.00"
"0081A343DE"," 00276281","13","6.00"
"0081A3D22A"," 00276284","13","6.00"
"00822726D7"," 00276285","13","6.00"
"0081A4E30F"," 00276289","13","6.00"
the data types the are going to are
varchar(50),varchar(50),in
can somone give me how to set up the format file ?
10Q
If the file is in the format you posted above you don't realy need a import with format but you can use the code below and remember the file location is relative to the SQL server not the client running the query:
CREATE TABLE CSVTest
(
Col1 VARCHAR(50),
Col2 VARCHAR(50),
Col3 int,
Col4 float
)
GO
--Place theCSV file in C:\csvtest.txt
--Now run following script to load all the data from CSV to database table.
--If there is any error in any row it will be not inserted but other rows will be inserted.
BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO
CREATE TABLE CSVTest
(
Col1 VARCHAR(50),
Col2 VARCHAR(50),
Col3 int,
Col4 float
)
GO
--Place theCSV file in C:\csvtest.txt
--Now run following script to load all the data from CSV to database table.
--If there is any error in any row it will be not inserted but other rows will be inserted.
BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO
ASKER
that will include the quotes in the data... dont want them
actually getting errors truncate data and such.
if i go through the file and remove all the double quotes, then the above will work.
the file is being created with the quotes thogh, and i cant control that.
actually getting errors truncate data and such.
if i go through the file and remove all the double quotes, then the above will work.
the file is being created with the quotes thogh, and i cant control that.
I think you can workaround that by just issue a SET QUOTED_IDENTIFIER OFF before the import
"When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers.'
http://msdn.microsoft.com/en-us/library/ms174393.aspx
"When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers.'
http://msdn.microsoft.com/en-us/library/ms174393.aspx
ASKER
tried that now getting
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (category).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (category).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 3 (category).
Msg 4864, Level 16, State 1, Line 1
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 3 (category).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (category).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 3 (category).
Msg 4864, Level 16, State 1, Line 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER