How to remove "" from data during BULK INSERT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to remove "" from data during BULK INSERT

Author

Topic

scantoria
Starting Member

15 Posts

Posted - 2009-12-14 : 14:20:55

each piece of data in the csv file is enclosed by "". When I uploaded the data, the data is still enclosed in "". Is there a way to upload just the data?

Bulk Insert:
USE npi_db
GO
BULK INSERT dbo.test
FROM 'E:\test.txt'
WITH
(
CODEPAGE='OEM',
DATAFILETYPE='char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS
)
GO

Sample data:
"1679576722","1","","","","WIEBE","DAVID","A","","","M.D.","","","","","","","","","","PO BOX 2168","","KEARNEY","NE","688482168","US","3088652512","3088652506","3500 CENTRAL AVE","","KEARNEY","NE","688472944","US","3088652512","3088652506","05/23/2005","07/08/2007","","","","M","","","","","","207X00000X","12637","NE","Y","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","46969","01","KS","BCBS","046969WI","04","KS","","645540","01","KS","FIRSTGUARD","1553","01","NE","BCBS","93420WI","04","NE","","B67599","02","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","X","","","","","",""

Thanks,

Stephen Cantoria
scantoria@msn.com

scantoria
Starting Member

15 Posts

Posted - 2009-12-16 : 18:19:27

I had to write a small application to strip the "" and used a stored procedure to insert the data. after 6.5 hours, i'm at 1586000 records uploaded. not bad. Thanks.

Stephen Cantoria
scantoria@msn.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources