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),int,float

can somone give me how to set up the format file   ?

10Q

CASorterAsked:
Who is Participating?
 
sachinpatil10dConnect With a Mentor Commented:
As csv file is having double quotes it gives the error of column 3 (Category)

lcohan script changed and it works fine now
drop table CSVTest
go
CREATE TABLE CSVTest
(
Col1 VARCHAR(500),
Col2 VARCHAR(500),
Col3 VARCHAR(500),
Col4 VARCHAR(500)
)
GO

declare @table table (Col1 VARCHAR(50), Col2 VARCHAR(50), Col3 int, Col4 float)

BULK INSERT CSVTest
FROM 'd:\test.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

insert into @table
SELECT ltrim(rtrim(replace(Col1,'"',''))), ltrim(rtrim(replace(Col2,'"',''))), ltrim(rtrim(replace(Col3,'"',''))), ltrim(rtrim(replace(Col4,'"','')))
FROM CSVTest

select * from @table

Open in new window

0
 
CASorterAuthor Commented:
the sql database is microsoft sql 2008 r2
0
 
lcohanDatabase AnalystCommented:
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
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
CASorterAuthor Commented:
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.
0
 
lcohanDatabase AnalystCommented:
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
0
 
CASorterAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.