locked
How to replace all comma in an EDI file RRS feed

  • Question

  • Hello Folks,

     I am trying to parse an EDI file using SSIS and finally convert it to a csv file. I am able to do the parsing and get the csv file. But unfortunately for few elements within that file, there is a comma in the address field. The comma can also be in any field.  So when i open the generated csv file, this extra comma messes up the column structure. I can open that file in notepad and use Control H, but would like to do this automatically as a pre-task before the parsing processes start. 

    Is there a way to parse the input file and do a Control H with replacing a comma with a space.  I'm using 2017 version. 

    Thanks in advance. 


    Tuesday, May 12, 2020 6:08 PM

All replies

  • Use Text Qualifier in Flat file Connection Manager as ". This will separate only values out of quotes by commas. Your data from db will be enclosed in quotes.

    http://uk.linkedin.com/in/ramjaddu

    • Proposed as answer by COZYROC Wednesday, May 13, 2020 3:24 PM
    Tuesday, May 12, 2020 6:10 PM
  • I highly recommend against converting an EDI file to CSV and trying to parse it.  It is much easier to simply parse the EDI file directly.

    There are multiple ways of handling multiple row types in the same file in SSIS:

    http://www.sqlis.com/sqlis/post/Handling-different-row-types-in-the-same-file.aspx

    https://www.timmitchell.net/post/2015/04/13/handling-mixed-format-data-files-in-ssis/

    Tuesday, May 12, 2020 6:27 PM
  • Tom,

     I am parsing the EDI using CozyRoc and then my end result is a csv as a Flat file destination. My issue is when i open the CSV to upload to other application manually, the comma in that csv messes up the columns. 

    Ex: the address is in the EDI 

    N3*10 South Street, Suite 201~
    N4*SAN FRANCISCO*CA*94102~

    Post parsing, the csv gives me

    10 South Street, Suite 201, SAN FRANCISCO,CA,94102. 

    So when i open the csv, the Suite 201 moves to the city column which messes up the other columns. 

    Tuesday, May 12, 2020 11:38 PM
  • Hi,

    Please try to parse the EDI file in Script Component.

    The following link will be helpful:

      How to load a flat file with header and detail data into a database using SSIS package?

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by AV111 Wednesday, May 13, 2020 1:38 PM
    Wednesday, May 13, 2020 3:24 AM
  • Hi,

    Just wrap the columns with double quotes as advised by RamJaddu and everything should be fine.


    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    Wednesday, May 13, 2020 3:26 PM
  • Hi COZYROC,

    The very next issue will be double quotes inside actual data.

    *.csv files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, and line breaks are in the middle of the actual data.

    The most reliable format for data feeds is XML.

    Wednesday, May 13, 2020 4:09 PM
  • You have only scratched the surface of the problems you are going to have. 

    If you are going to do this, you are better off using something other than comma as a separator, like tab or |, which is unlikely to occur in the data itself.

    Wednesday, May 13, 2020 4:56 PM
  • Yitzhak,

    Agreed. However, for some processes the XML format is overkill and much slower to process. Perhaps wrapping the columns might be the solution needed here.


    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    Wednesday, May 13, 2020 5:00 PM