locked
How to remove double quotation marks from an CSV file RRS feed

  • Question

  • Hi there ,

    I Have a little WinApp that imports an CSV file to SQL 2005 DB So far it worked fine, but then the CSV file Changed( It has " "  around the telephone number field so on import comes as varchar and displays in this format(7.98446e+009), and i need it in this format(07506107111),

    What is the workaround??


    thanks
    Tuesday, September 8, 2009 10:07 AM

Answers

All replies

  • Are you sure one of the phone numbers wasn't imported in an improper format?  Sometimes when there is an improper format in one of the items being brought in it will change it since it doesn't know what number it should be.
    Mike
    Tuesday, September 8, 2009 12:33 PM
  • I dont know cos i get the file via email from the client
    Tuesday, September 8, 2009 12:43 PM
  • You could do a find and replace in notepad - replace "" with blank.

    But if there is actually quotes inteneded to be in the middle of a string they will be removed also.
    Tuesday, September 8, 2009 12:55 PM
  • How you are importing the CSV file into table?
    I just did a quick chekc with OPENROWSET and Jet driver, it is working fine for me.  07506107111 coming out as it is.
    Mangal Pardeshi
    SQL With Mangal
    Tuesday, September 8, 2009 12:56 PM
  •  I use this :

    OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\

     

    ; Extensions=CSV; HDR=No;','SELECT * FROM text.csv'

    this is the data in the csv :

    "Name",","07958407600","07958407600",,,"

    If i remove the quotes it works, but how fo i do that?

    • Edited by deti Tuesday, September 8, 2009 1:25 PM
    Tuesday, September 8, 2009 1:06 PM
  • Try this
    select *
    FROM OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
    'TEXT;Database=C:\;HDR=No','SELECT * from text.csv')

    Mangal Pardeshi BI
    SQL With Mangal
    • Marked as answer by Zongqing Li Monday, September 14, 2009 8:36 AM
    Tuesday, September 8, 2009 1:19 PM
  • It works but it cuts out the 0 From the phone field
    Tuesday, September 8, 2009 1:27 PM
  • For that the table column's datatype must be of char/varchar type. 
    What is the datatype of  column in table?
    Mangal Pardeshi
    SQL With Mangal
    Tuesday, September 8, 2009 1:30 PM
  • FLOAT but when i change it to varchar i get this: 7.98446e+009

    Tuesday, September 8, 2009 1:42 PM
  • You have to create a format file that strips the double quotation marks from the text.  You can find a sample using the following link,
    http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html.  You should also have a look at using format files, http://msdn.microsoft.com/en-us/library/ms190393.aspx
    http://jahaines.blogspot.com/
    Tuesday, September 8, 2009 3:04 PM
  • I used cast and now it's working,

    thank you all
    Wednesday, September 9, 2009 12:47 PM