This topic contains 1 reply, has 2 voices, and was last updated by  Jeff Drumm 2 months, 4 weeks ago.

How to remove specific lines in CSV

  • Hi,
    I have a csv file where the CSV file format is below

    timestamp(yyyymmddhhmiss)
    header
    <payload data>
    footer

    I am parsing this csv file and mapping to an XML format.

    Before I map to XML, I must ignore/delete the first two lines of the file and also the footer.
    How this can be achieved?

    1. While reading the file, can I delete it. If so which api and how to use it
    2. If it is while parsing, do I need to change the csv module.

    Thanks in Advance for the help.

    If the complete file is being read into the translator (and therefore exists as the Data variable), split Data into a table and work with just the 3rd through the last – 1 record*:

    local tmpRecs = Data:split('\r\n') -- assuming DOS/Windows CRLF line endings
    local csvRecs = ''
    local firstRec = 3
    local lastRec = #tmpRecs - 2 -- table length minus 2, if last record ends with CRLF
    for i=firstRec,lastRec do
       csvRecs = csvRecs .. tmpRecs[i] .. '\r\n'
    end

    The csvRecs variable now contains just the CSV records, with the timestamp and header/trailer removed. Use that with the CSV parser rather than Data.

    *If there’s a trailing CRLF at the end of the trailer, you’ll have to set the lastRec variable to the number of table entries – 2.

    Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com

You must be logged in to reply to this topic.