Question

Writing multiple CSV to one Excel sheet

  • 4 February 2020
  • 2 replies
  • 16 views
D

I am trying to create a weekly summary report using data from multiple CSV's. Currently I have each CSV writing to multiple sheets within an xlsx file, but ideally I would like to write all of the CSV's in succession in one sheet. The issue is that the CSV's do not have the same name or number of columns and when they all write to one sheet with FeatureWriter it creates duplicate columns and blank cells.

On the attached xlsx, Sheet1 shows how I would like the final sheet formatted and Sheet2 is how the writer is currently combining the CSVs.

 

Thank you in advance!

1 Attachment

2 replies

takashi
Rank
Userlevel 2
Badge +17

It's hard to find a solution unless looking at the source tables. Could you please post the source CSV tables which should be transformed into the Sheet1?

D
Badge

It doesn't seem to be possible to use an attribute value for the Start Row under Start Position. Otherwise you might have been able to use that after having used a Counter for the previous csv rows and added some empty rows and thus gotten the Start Row for the second set of csv rows.

A workaround would be to pre-process the data in these steps:

 

1. Copy the attribute names of the first csv files to a new "header feature" and run it through a Counter set to Global. (This might seem strange, to count just one feature, but it's global so it'll make sense.)

 

2. Rename the attributes of the first csv file to simple column names: A, B, C and so on.

 

3. Put a Counter (also set to Global) on the rest of the features of the first csv file.

 

4. Repeat the three steps for the next csv file.

 

5. Put a Sorter right before the FeatureWriter.

Something like that. An ugly workaround, and I'm sure someone will give you three lines of python code that will solve it for you perfectly instead. :-)

Reply


Community Stats

28,707
Posts
109,471
Replies
36,549
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.