extract rows data in multiple text files and name files basis a row cell value

pardeepnaik · November 9, 2018, 2:19pm

Hi all,

I have a csv file which contains data as shown below:

Now I want to generate 10 output files (one for each row), where each file is named as column1 value e.g. file1 name should be A1.txt and it must contain data B1,C1,D1. Similarly for second row, generate a file with name A2.txt and with data B2,C2,D2 and so on. Please let me know if someone have any leads on how to do it in Knime. Thanks in advance.

Yush · November 9, 2018, 2:44pm

Hi pardeepnaik,

I’m not sure how to write to txt, but to write to csv you can use csv writer.

I think you can do the process you have explained above using the following nodes:

CSV Reader (to read your current csv)
Group Loop Start (Group by Column1)
Table to Row Variable (to put column 1 into a flow variable)
Table Creator (To put your file path in e.g. C:\Users[your user]\Documents)
Joiner (Combine Table to Row Variable and Table Creator)
String Manipulation (Create a new field which combines file path and Column1 to create full path e.g. C:\Users[your user]\Documents\A1.csv)
CSV Writer (for the output location, use the new field created in String Manipulation)
Loop End

Good luck!

Kind regards,
Yush

izaychik63 · November 9, 2018, 2:59pm

CSV Writer supports .txt extension.
Also, Create File Name node could be used

mlauber71 · November 9, 2018, 3:15pm

I prepared this based on the discussion

kn_example_row_to_csv_files.knwf (35.7 KB)

pardeepnaik · January 22, 2019, 3:27pm

Thanks a lot @mlauber71. This solution is somewhat similar to the solution I needed, but not exact. According to your solution, only one text file is created which includes all row values of our excel file. However, I want different file for each row. e.g. if we have 10 rows then 10 text files should be created where each file have name as the values of first column and each file should contain a row data. For more clarity, please refer to the question. Thanks again

mlauber71 · January 22, 2019, 6:52pm

I modified the workflow so it would use every entry and convert it into a CSV file. If you want less columns just edit the workflow.

kn_example_row_to_csv_files2.knar (43.8 KB)

pardeepnaik · January 25, 2019, 3:17pm

Thanks a lot @mlauber71. I really appreciate your help. I am able to do what I needed to do using the workflow attached by you. However, when I am creating workflow myself using the same nodes and with similar settings, I am facing a problem. In “CSV Writer” node when I am configuring it using created flow variable (var_file_name), then it is giving an error “directory of output file does not exist”. I added a screenshot for your reference also. For future work, please let me know how to use this var_file_name variable (created on “Java Edit Variable (simple)” node) in “CSV Writer” node.

mlauber71 · January 25, 2019, 4:32pm

in the Java snippet I create the necessary file path and name dynamically. I like to use the KNIME-protocol that works with relative paths which I strongly recommend you would use. It makes sure you can pack and copy your workflow ensemble everywhere and it will still work.

knime://knime.workflow/…/result/

In you case the syntax in the Java Snippet might look like this

return “knime://knime.workflow/task2/result/” + $${Scombined string}$$ + “.txt” ;

if indeed your task2 folder is beneath your current KNIME workflow and has a subfolder called result. If not you would have to specify the location where your CSV files should be stored.

pardeepnaik · February 8, 2019, 4:00pm

thanks a lot @mlauber71