Convert data from specific field excel to text file

[fairymemay (noona)]

I want to convert data from excel to txt.file
But I want only specific filed (text gree) file.xlsx (8.2 KB)

convert to text file below.
file text.txt (149 Bytes)
H|NAME|ID|JOB|ENTRY_DATE|ACCOUNT_NUMBER|ACCOUNT_NUMBER1
D|JACKSON|OFFICER|675878|01012017|453565646|878687786
D|YOUNG|HR|674578|01012014|87866755||

Please guide me about it.

[Atul_Rai]

Hi @fairymemay

Can you answer the following questions
1.Do you have any rule to select the fields from excel?
2. Is there any rule for the word file also? Data in the 3rd row of word file do not follow the pattern of 2nd row in the file.
3. What is H, D in the start of the word file?

I can try to build the logic.

Regards,
Atul Rai

[Atul_Rai]

In the Header after name you have ID but in the Data you have job after name.

[supermanPunch (Arpan)]

@fairymemay Check this workflow :
TransposeRows.zip (8.0 KB)

The Output can be easily done by using a Transpose Datatable Activity. The workflow uses an external package. You can download the highlighted package from the Manage Packages and use the workflow I have provided.

If you have any errors found, share the same.

[katharine.hardy]

This is not an elegant solution but I would suggest that you read the Excel file into a datatable and loop through this datatable to remove any unnecessary rows and separate the data (this may have to use hard coded business rules such as "does column1 contain ID? If so, keep the row. If column1=end, then create a new variable after this row). Once you have sorted the data, you can then manipulate it into the text file that you require. My other suggestion would be to perhaps change the process if possible so that the input data is more structured, making your data easier to manage.