Page 1 of 1
Extract data from a csv file and save in a new file
Posted: Thu Aug 14, 2014 9:18 am
by nishantmathur
Hi,
We have a requirement to split a csv (|) file into multiple files based on data in file
For example all lines with code "A100" goes to a file with name "A100_extract"
and all lines with code "A200" goes to another file with name "A200_extract"
Is this possible to do in director?
Thanks
Re: Extract data from a csv file and save in a new file
Posted: Thu Aug 14, 2014 10:47 am
by Support_Rick
Nishant,
This situation is a good reason why the modifyRowset Task was added to GoAnywhere Director in version 4.6. Below, I provided an example of how this can be accomplished utilizing that task.
Data
Code: Select allA101| Field 2| Field 3| Field 4| Field 5
A101| Field 2| Field 3| Field 4| Field 5
A102| Field 2| Field 3| Field 4| Field 5
A102| Field 2| Field 3| Field 4| Field 5
A101| Field 2| Field 3| Field 4| Field 5
A102| Field 2| Field 3| Field 4| Field 5
A101| Field 2| Field 3| Field 4| Field 5
A102| Field 2| Field 3| Field 4| Field 5
A101| Field 2| Field 3| Field 4| Field 5
Code: Select all<project name="Filter Pipe Delimited File" mainModule="Main" version="2.0" logLevel="debug">
<module name="Main">
<readCSV label="Read PIPE file" inputFile="C:\Pipe Delimited File.csv" outputRowSetVariable="MyData" fieldDelimiter="pipe" version="1.0" />
<modifyRowSet label="Create A101 Rowset" inputRowSetVariable="${MyData}" outputRowSetVariable="MyA101Data" existingColumns="true" includeRow="${MyData[1] == 'A101'}" version="1.0">
<modifyColumn index="1" name="C1" value="${MyData[1]}" />
</modifyRowSet>
<modifyRowSet label="Create A102 Rowset" inputRowSetVariable="${MyData}" outputRowSetVariable="MyA102Data" existingColumns="true" includeRow="${MyData[1] == 'A102'}" version="1.0">
<modifyColumn index="1" name="C1" value="${MyData[1]}" />
</modifyRowSet>
<writeCSV label="Create A101 File" inputRowSetVariable="${MyA101Data}" outputFile="C:\MyA101Data.csv" whenFileExists="overwrite" fieldDelimiter="pipe" version="1.0" />
<writeCSV label="Create A102 File" inputRowSetVariable="${MyA102Data}" outputFile="C:\MyA102Data.csv" whenFileExists="overwrite" fieldDelimiter="pipe" version="1.0" />
</module>
</project>
If your unique "known" values (from column 1) are available via a rowset (read from a Table, or CSV, etc) you could easily do this inside a ForEach loop and substitute the compare values with variables.
Please let us know if you have any other questions..