Data360 Analyze

 View Only
  • 1.  How to load fields from csv file, using a CSV/Delimited input node, that contain an apostrophe

    Posted 06-06-2022 10:37
    Edited by Scott PERRY 06-06-2022 10:43
    Hi Support team, 

    Please can you advise how to load fields from csv file, using a CSV/Delimited input node, that contain an apostrophe?

    example field name - "Participant's LEI".

    It can load the data if I specify all the field names in the "Field Names" option in the node but remove the apostrophe from the name e.g. "Participants LEI" (i.e. without the apostrophe). As i dont know all the field names up front for all my files that i want to load then this approach wont work for me so need a better solution. 

    thanks
    Scott


  • 2.  RE: How to load fields from csv file, using a CSV/Delimited input node, that contain an apostrophe

    Employee
    Posted 06-16-2022 09:00
    Hi Scott,

    you can use the 'SubstituteInvalidCharacters' property:


    Regards,
    Adrian

    ------------------------------
    Adrian Williams
    Precisely Software Inc.
    Naperville IL
    ------------------------------



  • 3.  RE: How to load fields from csv file, using a CSV/Delimited input node, that contain an apostrophe

    Posted 06-16-2022 09:35
    Edited by Scott PERRY 06-16-2022 10:28
    Hi Adrian

    I don't see SubstituteInvalidCharachters as an option on the CSV/Delim node. (v3.6.3.6520). 

    Is there a way for me to manually define it or other approach?

    Thanks
    Scott




  • 4.  RE: How to load fields from csv file, using a CSV/Delimited input node, that contain an apostrophe

    Employee
    Posted 06-16-2022 10:36
      |   view attached
    There is no generic, built-in, way to do it in that release. For a particular file you could us this approach:

    The CSV/Delimited node is configured to read in the data as if the data did not have a header record:

    The note will then auto-generate field names:

    A Transform node then configured to clean up the field names in the first data record:


    Then an Output CSV/Delimited node is used to write the data to a new file. The node is configured to not write the header record (the auto-generated field names) so the first data line will become the header record in the output file:


    See the attached zipped .lna file.

    ------------------------------
    Adrian Williams
    Precisely Software Inc.
    Naperville IL
    ------------------------------

    Attachment(s)