Rank: Member
Joined: 8/21/2019
Posts: 10
Location: United Kingdom
|
Hi,
I hope you can help.
=== EDITED VERSION ===
I'll ask a slightly different question...
If I have 10 Excel files, all files have a different unique reference in column B...
File 1 = Ref111
File 2 = Ref222
etc
How could I split these Excel files into smaller files that only contains 10 rows each?
If File 1 has 3 rows, then only one file should exist...
If File 2 has 65 rows, then 7 files should exist (6 files containing 10 rows each, and 1 file containing 5 rows).
I've tried a couple of different techniques ... But MapForce seems to be taking my source files, and creating files that are 30 rows (despite the reference).
Resulting in my target file looking like this:
Ref111
Ref111
Ref111
Ref222
Ref222
Ref222
Ref222
Ref222
etc
Whereas I want Ref111 to be in it's own file... And Ref222 to be in it's own 'set' of files.
File 1
Ref111
Ref111
Ref111
File 2 (Part 1 of 7)
Ref222
Ref222
Ref222
Ref222
Ref222
Ref222
Ref222
Ref222
Ref222
Ref222
File 2 (Part 2 or 7)
Ref222
Ref222
Ref222
Ref222
Ref222
Ref222
Ref222
Ref222
Ref222
Ref222
etc etc
I really hope that simplifies things? :-(
=== ORIGINAL POST ===
I have been provided with a small chunk of CSV data containing just 400 rows.
Within the CSV file is a reference number. This reference number can be spread across 5 rows or even 300 rows.
I have managed to take the CSV file, and create seperate Excel files for each of the unique references. Each Excel file will hold data relating to only that reference too.
But I would like to take this a step further.
I need to split the Excel files into several files which only contains 30 rows of data.
Meaning that if my original Excel file for Reference: "ISS130721", contained 100 rows of data, then I should have 4 Excel files.
3 Excel files containing 30 records each, and 1 remaining Excel file containing 10 records.
I would like the file name to be:
ISS130721- Part 1 of 4.xlsx
ISS130721- Part 2 of 4.xlsx
ISS130721- Part 3 of 4.xlsx
ISS130721- Part 4 of 4.xlsx
I could potentially have 200 to 300 excel files, so I would like the mapping to run through each file without having to change paths etc.
Here's a couple of screenshots which might help:
What the CSV file looks like:
What the Excel files look like after splitting by Reference:
Depending on the amount of rows inside the Excel files, this is how I would like them split... Using the same filename structure if possible.
If the middle step of just creating the Excel files by reference can be skipped, then that would be ideal.
So going from the CSV file, straight to generating Excel files by 'unique reference' and '30 rows each' would be awesome!!
But if that's not possible, that's fine.
I have included a zip file containing my files. Since it would be easier to work with these rather than creating your own :)
https://1drv.ms/u/s!Am2W_9SKfEw1kFxlj0WvyC4PwpND?e=RrGVcD
Thank you so much.
|
Rank: Advanced Member
Joined: 2/27/2009
Posts: 565
|
Because your input component is processing multiple files (*.csv), but you have nothing coming out of that input component's File item, you're grouping all the rows from all the input files.
If you want, per input file to create one or more output files, you have to connect the File item from the input component to the output component's File item one way or another.
|
