Firstly to get individual csv files in the same folder you need to apply a feature type fanout. From your description it sound like you have a dataset fanout. Open the CSV writer feature type parameters and in the CSV File Name parameter select the attribute(s) you want to fanout by:
Don't forget to turn off the Dataset fanout (Navigator window > CSV Writer > Fanout Dataset: No)
(for excel a feature type fanout would give you multiple worksheets in the same file. Boils down to the difference between file-based and folder-based datasets)
To get a different value in the first column and row of all your csv files is a little trickier. As writing a CSV will create one line per feature, we are going to have to create a new feature for each unique value of your fanout attribute(s). Each of these will have the fanout attribute plus the specified value in the first column's attribute name. Ensuring these feature enter the csv writer feature type first (e.g., a FeatureHolder can delay the other features) they will form the first row then the rest of the features will form the remaining rows.
Others may have a better solution to the first row problem, but that would be my first approach.
Firstly to get individual csv files in the same folder you need to apply a feature type fanout. From your description it sound like you have a dataset fanout. Open the CSV writer feature type parameters and in the CSV File Name parameter select the attribute(s) you want to fanout by:
Don't forget to turn off the Dataset fanout (Navigator window > CSV Writer > Fanout Dataset: No)
(for excel a feature type fanout would give you multiple worksheets in the same file. Boils down to the difference between file-based and folder-based datasets)
To get a different value in the first column and row of all your csv files is a little trickier. As writing a CSV will create one line per feature, we are going to have to create a new feature for each unique value of your fanout attribute(s). Each of these will have the fanout attribute plus the specified value in the first column's attribute name. Ensuring these feature enter the csv writer feature type first (e.g., a FeatureHolder can delay the other features) they will form the first row then the rest of the features will form the remaining rows.
Others may have a better solution to the first row problem, but that would be my first approach.
Thanks tomf - one minor thing i missed that made all the difference with the fanout :-)
Still struggling with having a specified value in cell A1 - basically i want the first row to have the same value in cell A1 for every fanout attribute and then the attributes written to the same file starting on row 2
eg
100980.csv
Value in R1C1,,,,,,,,,,,,
ass_num,rte_cde,rte_dif,bal_cde,rte_yer,rte_unt,amount,tax,amount_inc,lev_num,dte_lev,trn_cde,Max_tran
100980,1,5,21,2004,366,914.39,0,914.39,1,00:00.0,1701,8901293
123467.csv
Value in R1C1,,,,,,,,,,,,
ass_num,rte_cde,rte_dif,bal_cde,rte_yer,rte_unt,amount,tax,amount_inc,lev_num,dte_lev,trn_cde,Max_tran
123467,1,5,21,2018,365,1477.48,0,1477.48,1,00:00.0,1701,8901293
so in actual fact the values in the first two rows are identical - the values in row 2 are output by the fanout as is the data in row 3 - just need to have the value in row 1 added to each file
Here's a workspace (FME2020) that achieves what you need. You will need to populate/set the constant value for R1C1 in AttributeManager_3, text_line_data, currently set to "Constant value". I'm then using ass_num as the dataset fanout in the FeatureWriters. The first FeatureWriter writes text_line_data into the first line of the CSV file overwriting any existing files, then in the second FeatureWriter write the data out in the CSV format without overwriting the existing files and always writing the column headers
In the attached zip are the output files the workspace created as well.
Hope this helps.
Here's a workspace (FME2020) that achieves what you need. You will need to populate/set the constant value for R1C1 in AttributeManager_3, text_line_data, currently set to "Constant value". I'm then using ass_num as the dataset fanout in the FeatureWriters. The first FeatureWriter writes text_line_data into the first line of the CSV file overwriting any existing files, then in the second FeatureWriter write the data out in the CSV format without overwriting the existing files and always writing the column headers
In the attached zip are the output files the workspace created as well.
Hope this helps.
Thanks so much Tom. Works an absolute treat
Here's a workspace (FME2020) that achieves what you need. You will need to populate/set the constant value for R1C1 in AttributeManager_3, text_line_data, currently set to "Constant value". I'm then using ass_num as the dataset fanout in the FeatureWriters. The first FeatureWriter writes text_line_data into the first line of the CSV file overwriting any existing files, then in the second FeatureWriter write the data out in the CSV format without overwriting the existing files and always writing the column headers
In the attached zip are the output files the workspace created as well.
Hope this helps.
Hi Tom
Trying to use the fanout csv that you showed me on another data set and theres something a bit wierd. I run the workbench and the text_line csv files are correctly created but the second featurewriter (FeatureWriter_4) doesnt seem to be writing or its writing first and then being overwritten by the text_line feature writer(FeatureWriter_3). If i run just the FeatureWriter_4 step then the data correctly appends to each individual file. Feature_Writer3 is before FeatureWriter_4 in the Transformer list.
I've compared the parameters on this new workbench to those from the one that works and I can't see any difference.
The Translation - Order Writers By: is set to Position in Workbench navigator.
Any ideas what else I can look for?
Thanks in advance
Hi Tom
Trying to use the fanout csv that you showed me on another data set and theres something a bit wierd. I run the workbench and the text_line csv files are correctly created but the second featurewriter (FeatureWriter_4) doesnt seem to be writing or its writing first and then being overwritten by the text_line feature writer(FeatureWriter_3). If i run just the FeatureWriter_4 step then the data correctly appends to each individual file. Feature_Writer3 is before FeatureWriter_4 in the Transformer list.
I've compared the parameters on this new workbench to those from the one that works and I can't see any difference.
The Translation - Order Writers By: is set to Position in Workbench navigator.
Any ideas what else I can look for?
Thanks in advance
Hmm... that's odd. I tried @tomf 's workspace and it's performing like a charm for me. @left65 , would you mind sharing the workspace where you are experiencing this issue? What version/build of FME are you using?
Hi Tom
Trying to use the fanout csv that you showed me on another data set and theres something a bit wierd. I run the workbench and the text_line csv files are correctly created but the second featurewriter (FeatureWriter_4) doesnt seem to be writing or its writing first and then being overwritten by the text_line feature writer(FeatureWriter_3). If i run just the FeatureWriter_4 step then the data correctly appends to each individual file. Feature_Writer3 is before FeatureWriter_4 in the Transformer list.
I've compared the parameters on this new workbench to those from the one that works and I can't see any difference.
The Translation - Order Writers By: is set to Position in Workbench navigator.
Any ideas what else I can look for?
Thanks in advance
Hi @nampreetatsafe I did make one minor change to the example from @tomf as my data has multiple lines per record. the Fanout_example is one that works (blue bookmark), the Misc_debtor_trans_new is the one thats not working quite right
Hi Tom
Trying to use the fanout csv that you showed me on another data set and theres something a bit wierd. I run the workbench and the text_line csv files are correctly created but the second featurewriter (FeatureWriter_4) doesnt seem to be writing or its writing first and then being overwritten by the text_line feature writer(FeatureWriter_3). If i run just the FeatureWriter_4 step then the data correctly appends to each individual file. Feature_Writer3 is before FeatureWriter_4 in the Transformer list.
I've compared the parameters on this new workbench to those from the one that works and I can't see any difference.
The Translation - Order Writers By: is set to Position in Workbench navigator.
Any ideas what else I can look for?
Thanks in advance
and heres the Fanout_example that does work
Hi Tom
Trying to use the fanout csv that you showed me on another data set and theres something a bit wierd. I run the workbench and the text_line csv files are correctly created but the second featurewriter (FeatureWriter_4) doesnt seem to be writing or its writing first and then being overwritten by the text_line feature writer(FeatureWriter_3). If i run just the FeatureWriter_4 step then the data correctly appends to each individual file. Feature_Writer3 is before FeatureWriter_4 in the Transformer list.
I've compared the parameters on this new workbench to those from the one that works and I can't see any difference.
The Translation - Order Writers By: is set to Position in Workbench navigator.
Any ideas what else I can look for?
Thanks in advance
You might want to take advantage of the FeatureWriters ability to be placed in sequence in one stream. Not being familiar with the data you are using, I'm hoping this attached workspace I modified from your workspace gives you some ideas to build on.
Specifically, I think you need to include the text_line_data attribute in the AttributeKeeper as it contains content that you are writing out. You want to set the Aggregator to Use Attributes From One Feature so it doesn't lose the text_line_data:
If your workflow happens to end at the last FeatureWriter, you might consider replacing the FeatureWriter_4 in the screenshot above with a CSV Writer.
Hi Tom
Trying to use the fanout csv that you showed me on another data set and theres something a bit wierd. I run the workbench and the text_line csv files are correctly created but the second featurewriter (FeatureWriter_4) doesnt seem to be writing or its writing first and then being overwritten by the text_line feature writer(FeatureWriter_3). If i run just the FeatureWriter_4 step then the data correctly appends to each individual file. Feature_Writer3 is before FeatureWriter_4 in the Transformer list.
I've compared the parameters on this new workbench to those from the one that works and I can't see any difference.
The Translation - Order Writers By: is set to Position in Workbench navigator.
Any ideas what else I can look for?
Thanks in advance
Hi @nampreetatsafe that wont work as I the second feature writer needs to write a header row and multiple lines of data. I've attached the data that is coming out of TestFilter_4. it needs to write a file that looks like this
FORMAT TRANSACTION APPLICATION MIGRATION , STANDARD 1.0
DebtorSystem,AccountId,TransactionApplicationId,DebtorTransactionId,AppliedDebtorTransactionId,AppliedDate,AppliedAmount,Book_name
CAN,132,117,5001881,5000297,1996-07-12,-23,MISC_2021
CAN,132,219,5001882,5000796,1996-07-22,-11.5,MISC_2021
CAN,132,246,5000349,5001047,1996-08-06,-11.5,MISC_2021
CAN,132,633,5002906,5002393,1996-10-22,-23,MISC_2021
for each AccountID
Hi Tom
Trying to use the fanout csv that you showed me on another data set and theres something a bit wierd. I run the workbench and the text_line csv files are correctly created but the second featurewriter (FeatureWriter_4) doesnt seem to be writing or its writing first and then being overwritten by the text_line feature writer(FeatureWriter_3). If i run just the FeatureWriter_4 step then the data correctly appends to each individual file. Feature_Writer3 is before FeatureWriter_4 in the Transformer list.
I've compared the parameters on this new workbench to those from the one that works and I can't see any difference.
The Translation - Order Writers By: is set to Position in Workbench navigator.
Any ideas what else I can look for?
Thanks in advance
I couldnt work out how to attach multiple files so here is one of the output files that is correct but only if i run FeatureWriter_4 again, after the workbench has finished.
I did notice in the Translation log that the csv files from FeatureWriter_4 are being written before the txt files of FeatureWriter_3 - is there a parameter somewhere that sets the order????
I'm using FME Workbench 2020.1
Hi Tom
Trying to use the fanout csv that you showed me on another data set and theres something a bit wierd. I run the workbench and the text_line csv files are correctly created but the second featurewriter (FeatureWriter_4) doesnt seem to be writing or its writing first and then being overwritten by the text_line feature writer(FeatureWriter_3). If i run just the FeatureWriter_4 step then the data correctly appends to each individual file. Feature_Writer3 is before FeatureWriter_4 in the Transformer list.
I've compared the parameters on this new workbench to those from the one that works and I can't see any difference.
The Translation - Order Writers By: is set to Position in Workbench navigator.
Any ideas what else I can look for?
Thanks in advance
Hi @left65 you could check out this on controlling the writer order https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_Workbench/Workbench/Order-Writers-By.htm
Hi Tom
Trying to use the fanout csv that you showed me on another data set and theres something a bit wierd. I run the workbench and the text_line csv files are correctly created but the second featurewriter (FeatureWriter_4) doesnt seem to be writing or its writing first and then being overwritten by the text_line feature writer(FeatureWriter_3). If i run just the FeatureWriter_4 step then the data correctly appends to each individual file. Feature_Writer3 is before FeatureWriter_4 in the Transformer list.
I've compared the parameters on this new workbench to those from the one that works and I can't see any difference.
The Translation - Order Writers By: is set to Position in Workbench navigator.
Any ideas what else I can look for?
Thanks in advance
Hi @daraghatsafe
The Order-Writers-By is set to "Position in Workbench Navigator" and the FeatureWriters are in the Navigator in the order I want to output them but as you can see from the attached log file the features are being written in a different sequence. Any other ideas?
