Text to excel conversion

Help
1

Hi all,

I am trying to achieve text to excel conversion wherein my Input.txt data is unstructured which contains spaces and commas, I am trying with space delimiter but as in the input text file name column contains sometimes first name only and sometimes first and last names, and because of this it is generating two name columns in output.xlsx which is misleading the next column data as well and as per required output both names should come in one Name Column only.

Please find attached workflow, input.txt, output.xlsx and RequiredOutput.xlsx file for reference.

Thanks in advance :slight_smile:

Project.zip (22.2 KB)

2

@Karun

Kindly check the below XAML i have made some changes in your code.
SplitTextFileWriteIntoExcel.xaml (13.8 KB)

3

Thank you so much @Ranjith for taking the time and effort for providing this solution…Your work flow worked like a charm.

But I’m not understanding this line

system.Text.RegularExpressions.Regex.Match(item,“(?<=”+Type+“\s+)(\w+)”)

Can you give little explanation?

1 Like
4

@Karun

The regex pattern check for the match after the TYPE value . You can go through lookahead and lookbehind concepts in regex for better understanding.

In your case

AA ABC123 AEDAN GOFF 0.00 0.00M1

TYPE variable will be assigned to AA at the runtime, pattern looks like (?<=AA\s+)(\w+) . While matching, the regex engine will search for the sequence of characters which is followed by the value “AA”.

\s - stands for whitespace character
\w - sequence of characters
+ - one or more occurrence

(?<=pattern to ldentify)(pattern to extract value) - Lookbehind syntax

1 Like
5

Thanks a lot for the brief explanation.
I got it now. :slight_smile:
Thanks for the reply :slight_smile:

1 Like
6

@Karun The pleasure is all mine :slight_smile:

Also refer this document for depth understanding of regex
Regular-Expressions.pdf (920.0 KB)

2 Likes
7

@ranjith Thanks for the document :slight_smile:

1 Like
8

Thanks @ranjith! I was with the same problem, saved the day!:grin:

1 Like
9

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