CSV spreadsheet files are suitable for storing tabular data in a relatively portable way.

The CSV format is flexible but somewhat ill-defined.

For present purposes, authors may assume that the data fields contain no commas, backslashes, or quotation marks.



Task

Read a CSV file, change some values and save the changes back to a file.

For this task we will use the following CSV file:

C1,C2,C3,C4,C5
1,5,9,13,17
2,6,10,14,18
3,7,11,15,19
4,8,12,16,20

Suggestions


 Show how to add a column, headed 'SUM', of the sums of the rows.
 If possible, illustrate the use of built-in or standard functions, methods, or libraries, that handle generic CSV files.



// Assumes a CSV file exists and has been sprayed to a Thor cluster
MyFileLayout := RECORD
STRING Field1;
STRING Field2;
STRING Field3;
STRING Field4;
STRING Field5;
END;							
 
MyDataset := DATASET ('~Rosetta::myCSVFile', MyFileLayout,CSV(SEPARATOR(',')));
 
MyFileLayout Appended(MyFileLayout pInput):= TRANSFORM 
  SELF.Field1 := pInput.Field1 +'x';
  SELF.Field2 := pInput.Field2 +'y';
  SELF.Field3 := pInput.Field3 +'z';
  SELF.Field4 := pInput.Field4 +'a';
  SELF.Field5 := pInput.Field5 +'b';
END ; 
 
MyNewDataset := PROJECT(MyDataset,Appended(LEFT)); 
OUTPUT(myNewDataset,,'~Rosetta::myNewCSVFile',CSV,OVERWRITE);