Check Each Line of a CSV File and Output Specific Information according to Certain Requirements #53

Problem description & analysis

Below is text file txt.txt, where the second column contains quantities and the third column contains unit prices:

111111,34,24.5,Apple

222222,53,22.0,Mango

333333,,32.0,Orange

44444,22,12.6,

We are trying to handle this text file according to requirements below:

1. If there is any null field in the current row, output the words “Error in line# N: insufficient/invalid data”;

2. If the current row is empty, output the words “Line# N is empty”;

3. Simple calculations;

4. Append an empty row and a row of total sales, as shown below:

Transactions

Sold 34 Apple at £24.50

Line#2 is empty

Sold 53 Mango at £22.00

Line#4 is empty

Error in line#5: insufficient/invalid data

Line#6 is empty

Error in line#7: insufficient/invalid data
Total sales: £1999.00

Solution

Write the following script p1.dfx in esProc:

Explanation:

A1   @c option enables separating columns with the comma; @w option enables returning a sequence of sequences.

A2  Define cellset variable total and assign 0 to it as the initial value.

A3  Loop through A1’s sequence, during which if the current sequence is empty, output “Line# N is empty”; and if the current sequence contains any null members, output “Error in line# N: insufficient/invalid data”, otherwise calculate Quantity * Unit Price.

A4  The heading information to be output.

A5  The tail information, which is total sales, to be output.

A6  Export the concatenation of A4, A1, and A5 to result.txt.

SPL open source address

Download