Post reply

SQL Query and .CSV files

  • 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'

    )

  • October 30, 2007 at 10:04 am

    #746729

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

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

  • October 30, 2007 at 10:51 am

    #746757

    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

  • October 30, 2007 at 11:05 am

    #746769

    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

  • October 30, 2007 at 11:17 am

    #746776

    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, '"', ' ' )

  • October 30, 2007 at 11:29 am

    #746781

    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

  • October 30, 2007 at 11:53 am

    #746797

    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!

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

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