Convert .txt to .xlsx file

Hey guys,
I want to convert multiple .txt files in a specified folder into .xlsx files.

Thank you.

How would you do this operation normally?

Can you replace the spaces between columns for a “;” or another character?

@NiranjanKN,

What is your expected output in the xlsx file, Share a sample for that as well for better understanding.

@NiranjanKN,

I Have attached you a sample workflow for one file, can you please check whether your able to meet your need.

Regards,
AnilTextToExcel.zip (11.4 KB)

Hi
Hope these steps would help you resolve this
—use a assign activity like this
arr_filepath = Directory.GetFiles(“yourfolderpath”.”*.txt”)

Where arr_filepath is a variable of type array of string
—now use a FOR EACH activity and pass the above variable as input and change the type argument as string in the property panel of for each loop
—inside the loop use READ TEXT FILE activity and mention the file path as item.ToString
And get the output with a variable of type string h named str_output
—now use a GENERATE DATATABLE ACTIVITY and pass the above string variable as input and get the output with a variable of type datatable named Finaldt
—now use WRITE RANGE activity and pass the above Finaldt as input and mention the file path as Path.GetFilenamewithoutextension(item.ToString)+”.xlsx”
And ensure that add headers property is enabled in the property panel of write range activity

Cheers @NiranjanKN

Hey @sarathi125

These are the sample Excel files.

@Palaniyappan Where’ll be the file path to mention this Path.GetFilenamewithoutextension(item.ToString)+”.xlsx”

1 Like

In excel application scope mention this file oath and inside that use write range activity where mention the datatable as Finaldt and range as “A1” and enable the add headers property in the property panel of write range activity
Cheers @NiranjanKN

@NiranjanKN,

Check this xaml,

TextToExcel.xaml (8.4 KB)

Tested from my end and working fine, check and let me know if any issues.

@Palaniyappan @sarathi125
I’ve followed as told,Text_To_Excel.xaml (8.9 KB) but textBtest.txt (473 Bytes) is not converted to excel as expected.Btest.xlsx (8.7 KB)

Hey @sarathi125
This has given the necessary output, but what to do for multiple text files in a single folder.

@NiranjanKN
Check this xaml, it has the logic to get all text files from a directory and convert that to excel files. you need to pass the directory path in a variable.

TextToExcel.xaml (11.7 KB)

@sarathi125 It means I need to pass the folder path to strFileDirectory variable ?

I also need to name the excel files same as the converted text files.

TextToExcel.xaml (13.0 KB)

Check this one, modified as you required.

@NiranjanKN,

Sorry thats my mistake only, the file extension should be “.txt” in the get files.

Check with this one, created 4-5 .xlsx files from text files.
TextToExcel.xaml (13.1 KB)

This can be used for converting multiple text files.Text_to_Excel.xaml (15.4 KB)

Thanks

Change it like this

strFileDirectory +Path.GetFileNameWithoutExtension( textFile.FullName)+".xlsx"

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