[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

bulk insert with format file sql 2008

Posted on 2011-10-11
6
Medium Priority
?
526 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:CASorter
  • 3
  • 2
6 Comments
 

Author Comment

by:CASorter
ID: 36951565
the sql database is microsoft sql 2008 r2
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36951656
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
 

Author Comment

by:CASorter
ID: 36951738
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:lcohan
ID: 36951857
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
 

Author Comment

by:CASorter
ID: 36952022
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
 
LVL 9

Accepted Solution

by:
sachinpatil10d earned 2000 total points
ID: 36956668
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question