locked
csv to txt RRS feed

  • Question

  • I have been given a csv file, this file contains financial data and since there are fields having money/currency it does not help to have it in csv

    My task is to convert this file to .txt and load it into a database.

    I want to know what is the best way to convert a , delimited csv to a tab delimited txt file...

    Thursday, June 13, 2013 1:18 PM

Answers

  • REPLACE([Current_Branch_Rpt_Balance],"/"," ")

    This worked...

    • Marked as answer by Mike Yin Tuesday, June 18, 2013 11:00 AM
    Thursday, June 13, 2013 8:03 PM

All replies

  • You really can't.  You need to have the people providing the file put it in a usable format.

    Chuck Pedretti | Magenic – North Region | magenic.com

    Thursday, June 13, 2013 1:24 PM
  • Did you try loading CSV directly in to DB? 
    Thursday, June 13, 2013 1:47 PM
  • Did you try loading CSV directly in to DB? 

    It won't work - he is saying that he has a comma separated file with commas in the data.

    1,238.00,test,1,200,345

    Is that 3 fields, 4, 5 or 6?

    There is no reliable way to import data in that format.  You have to get the provider to give it to you In a usable format.


    Chuck Pedretti | Magenic – North Region | magenic.com

    Thursday, June 13, 2013 1:54 PM
  • I tried loading it directly using import task on the db but its giving me an error.

    Just for your reference, i will paste the error here

    - Executing (Error)
    Messages
    Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Current Avg Balance" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
     (SQL Server Import and Export Wizard)
     
    Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "Current Avg Balance" (34)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Current Avg Balance" (34)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Documents and Settings\rele\Desktop\Deposit History 123112.csv" on data row 2.
     (SQL Server Import and Export Wizard)
     
    Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - Deposit History 123112_csv" (1) returned error code 0xC0202092.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
     (SQL Server Import and Export Wizard)
     

    Thursday, June 13, 2013 2:03 PM
  • Guys i figured a new way to do it...

    It works if you have ms office 2010!

    so u open the csv in excel 2010

    and simply save it as "text tab delimited"

    it will identify the separator commas and the data commas

    Fr Eg: for currency fields im getting "6,282.00"

    for address fiend i am getting "1436 East Genesee Street, Albany"

    excel is putting parentheses wherever data commas are present. this helps it to identify data commas and separation commas.

    finally i imported this data into SQL database using SSIS where i used derived columns to remove the parentheses...  

    Thursday, June 13, 2013 4:13 PM
  • Guys i figured a new way to do it...

    It works if you have ms office 2010!

    so u open the csv in excel 2010

    and simply save it as "text tab delimited"

    it will identify the separator commas and the data commas

    Fr Eg: for currency fields im getting "6,282.00"

    for address fiend i am getting "1436 East Genesee Street, Albany"

    excel is putting parentheses wherever data commas are present. this helps it to identify data commas and separation commas.

    finally i imported this data into SQL database using SSIS where i used derived columns to remove the parentheses...  

    Sounds like it might work some of the time, but how is it going to interpret data that can't be guessed at?  If you do not get your data provider to give you a file which requires no interpretation then you are just setting yourself up for unknown data corruption.


    Chuck Pedretti | Magenic – North Region | magenic.com

    Thursday, June 13, 2013 4:27 PM
  • Yes chuck is correct. You might be facing data corruption and I think this is not right way or recommended way to do.
    Thursday, June 13, 2013 6:10 PM
  • we are building an Archive database. And I had a word with them earlier, they said that they do not have .txt files.

    Now thers a new problem that i am facing. the .txt which i had created earlier has to be loaded to a db table.

    i am building a package using SSIS and in this package I need to remove all the leading and trailing " from fields.

    And I am using the expression

    REPLACE([Current_Branch_Rpt_Balance],"""," ")

    its in red when i enter parentheses inside parentheses.

    Is there any way to get rid of these?

    Is there a special way to put " inside " "???

    or " is not getting recognized as a part of data???

    Thursday, June 13, 2013 7:28 PM
  • REPLACE([Current_Branch_Rpt_Balance],"/"," ")

    This worked...

    • Marked as answer by Mike Yin Tuesday, June 18, 2013 11:00 AM
    Thursday, June 13, 2013 8:03 PM