Text file different format data to Excel file

Hi All,

I have text file with having data format.

i want to extract data from text file and create excel file with numbers , characters and special characters into 3 different columns.

can any one have any idea.

1 Like

@PRADIPPHARNE you can use regular expressions

Use read text file and store the values in a string

And then try to split the values like split(environment.newline.ToArray) and then use for each item and process and then use regex expression again split again and then use write range

You may want to take a look at this

Hope these steps would help you resolve this
—if that is the string in a text file then use a READ TEXT FILE activity and pass the file path the text as input and get the output with a variable of type string named str_input
—now use a assign activity like this
arr_lines = str_input.Split(Environment.NewLine.ToArray())
Where arr_lines is a variable of type array of string
—now use a BUILD DATATABLE ACTIVITY where create three columns name Number,Word,SpecialCharacters all of type string and get the output with a variable of type datatable named Finaldt
—next use CLEAR DATATABLE ACTIVITY and mention the variable Finaldt as input
—now use FOR EACH loop and pass the above variable arr_lines as input and change the type argument as string in the property panel
—inside the loop use a assign activity like this
Str_num = IF(String.IsNullOrEmpty(System.Text.RegularExpressions.Regex.Match(item.ToString,”[\d].+”).ToString),String.Empty,System.Text.RegularExpressions.Regex.Match(item.ToString,”[\d].+”).ToString)

Where this str_num will have the digit alone from the string

Similarly two more assign activity like this

Str_alpha = IF(String.IsNullOrEmpty(System.Text.RegularExpressions.Regex.Match(item.ToString,”[a-z—Z].+”).ToString),String.Empty,System.Text.RegularExpressions.Regex.Match(item.ToString,”[a-zA-Z].+”).ToString)

and for special characters

Str_spec = IF(String.IsNullOrEmpty(System.Text.RegularExpressions.Regex.Match(item.ToString,”[\W].+”).ToString),String.Empty,System.Text.RegularExpressions.Regex.Match(item.ToString,”[\W].+”).ToString)

—now next to these three activities inside the loop use a ADD DATAROW a activity and mention the datatable as Finaldt and ArrayRow as
{Str_num.ToString, Str_alpha.ToString, Str_spec.ToString}

—then next to this for each loop use WRITE RANGE activity inside the EXCEL APPLICATION SCOPE and mention the datatable as input and enable ADD HEADERS property


Yap its works.

Thank you.