SQL Query and .CSV files

Question

Post reply

SQL Query and .CSV files

jacob.ostop

SSC Enthusiast

Points: 148
More actions
October 30, 2007 at 9:42 am

#176622

I have a .csv file that will be imported into SQL server. I have wrote a query below that does this but i need to get rid of the quotes that are around each datafield. below is an example of what the data looks like when i try to import it into my database
"Datafieldhere"
I need to remove the quotes so it looks like this:
Datafieldhere
I am using sql 2005, and below is the query that i have wrote for this so far
use "washco redtitan"
Bulk Insert test2
FROM 'C:\Documents and Settings\test\Desktop\WashingtonXMLScriptssample\washco911.csv'
WITH
(
FirstRow=2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '/n'
)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

You can get the data in, and run an UPDATE with REPLACE to remove the double qutoes.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

jacob.ostop SSC Enthusiast Points: 148 More actions · Answer 2

Could you show me how to do this as a sample.. not that familiar with sql. The column the quote is showing up in is the program field

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

You can build an UPDATE statement like...

UPDATE AnyTable SET Col1 = REPLACE( Col1, '"', '' ), Col2 = REPLACE( Col2, '"', '' )

Note: It will not only update the leading and trailing double quotes but also in the middle of the text.

--Ramesh

jacob.ostop SSC Enthusiast Points: 148 More actions · Answer 4

I am getting this error

Msg 8116, Level 16, State 1, Line 13

Argument data type text is invalid for argument 1 of replace function.

This is the SQL code i have:

use "washco redtitan"

Bulk Insert test2

FROM 'C:\Documents and Settings\test\Desktop\WashingtonXMLScriptssample\washco911.csv'

WITH

(

FirstRow=2,

FIELDTERMINATOR = '","',

ROWTERMINATOR = ''

)

UPDATE dbo.test2 SET program = REPLACE(program, '"', ' ' )

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

jacob.ostop (10/30/2007)
I am getting this error
Msg 8116, Level 16, State 1, Line 13
Argument data type text is invalid for argument 1 of replace function.

This is because the column program is of text datatype and like most of the string functions, REPLACE is not capable of handling such columns.

Is the column program really needs to be text? Since you are 2K5 forum, you have the option to change it from text to varchar(max) which is a good replacement of text.

--Ramesh

jacob.ostop SSC Enthusiast Points: 148 More actions · Answer 6

THANKS! I got it to work fine now... sorry i know my question was a pretty easy one i just couldnt find it anywhere on google!