How to extract columns from an un-structured CSV file

I have an excel CSV file and I want to extract Columns from it based on labels(Document, Sales type, Doc. Type, etc) and then want to store it on another excel sheet
The output should be like this

The screenshot of the actual excel file is this


Read the csv using “Read Text File Activity” then check the output and split the text after word "Document " then use a generate datatable activity and give the splitted text to the activity and give a delimitter as tab or space and it will be getting as a datatable , Test and Evaluate with different delimitter for accurate table

@Sami_Rajput ,
Can u share the csv, then i can share you the code

CWE003.xls (19.5 KB)

I think we cannot upload the CSV file here. so i converted into XLS

@Sami_Rajput ,

Please find attached
The output of read range gives you your datatable
Convert to (2.9 KB)

@Sami_Rajput ,
Please use the new Zip file attached

Updated (9.9 KB)

Let me check this!

It works fine, but is it possible to achieve the same without using a lookup activity?
for example with simple if else logic?
plus I also want to skip the last portion(starting from sales invoices)

Please find the below code
Look up range activity is way better than If loop condition as it will need logical loopings to identify the specific cell address

In this new code i have updated the code for removing the last columns also

If this satisfy your need please provide as solution so that it will be useful to others too

Cheers (9.7 KB)

Thankyou so much!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.