Text Document Conversion into Excel - Row to Column
I have 1000's of records in a semi structured text file and need help importing the data into Excel. The structure of the data is as follows;
First Name: Bob
Last Name: Smith
Branch: Finance Contractor
First Name: Jane
Last Name: Williams
Branch: Finance Employee
First Name: Mark
Last Name: Doe
Branch: Human Resources Employee
How do I go about importing this efficiently into Excel with 4 columns (First Name, Last Name, Branch, Emp Type). I tried Googles OpenRefine but I haven't had any success.
Some sample .net c# code is also appreciated.
First Name: Bob
Last Name: Smith
Branch: Finance Contractor
First Name: Jane
Last Name: Williams
Branch: Finance Employee
First Name: Mark
Last Name: Doe
Branch: Human Resources Employee
How do I go about importing this efficiently into Excel with 4 columns (First Name, Last Name, Branch, Emp Type). I tried Googles OpenRefine but I haven't had any success.
Some sample .net c# code is also appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked perfectly! I needed to tweak the script
Do these - skipping all " marks, of course
FIND "First Name: " - REPLACE WITH "" ie nothing
"^lLast Name: " - REPLACE WITH "," ie a comma
"^lBranch: " - REPLACE WITH ","
"^l^l" - REPLACE WITH "^p"
Explanation
The first one just deletes your unnecessary First Name: text. Note, there's a space after the colon.
The second one does the same for Last Name: , but also removes the Line Break - indicated by the ^l (that's a lower case L) character, and inserts a comma in its place as a delimiter.
the third one does the same for Branch:
the fourth one replaces the double blank line that you have with a single Paragraph mark instead - the ^p symbol.
Sounds fiddly, but is pretty flexible in a hurry for simple tasks.
Word is better than Excel here as it has the additional formatting tools to remove your line breaks, etc.
For more info on what else Word can replace, check the More.. Special options in that dialog box.
copy the resulting list back to Excel, and use Text To Columns to split it up.