Avatar of AIGS
AIGS
Flag for Australia asked on

Import text from rows into columns?

I have been given a text file which I need to place the data into a MySQL table, Excel or Even Access.

The text file contains:
A | win
B | howdy
C | go home
D | have lunch
A | happy
B | sad
C | drunk
D | mad

The letters on the left of the | are the field names and the data is on the right.
There is no character/delimiter that represents the end of a record.
There are thousands of these records containing the same fields in the same order (obviously different data).

Any suggestions in getting it in to columns of data instead of rows would be appreciated.
Microsoft OfficeDatabasesMicrosoft ExcelMongoDBVBA

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Shums Faruk

You may use below formula to extract Fields in Excel, assuming your data starts from A1
=TRIM(MID(A1,FIND("|",A1)+2,256))

Open in new window

Extract FieldsThis is how you want?
AIGS

ASKER
Not exactly what I am after.
Trying to convert this:
A | win
B | howdy
C | go home
D | have lunch
A | happy
B | sad
C | drunk
D | mad

in to this:
A                           B                C               D
win                      howdy     go home     have lunch
happy                 sad           drunk          mad

Does that explain it a bit better?
Subodh Tiwari (Neeraj)

You can directly import the text file into Excel and during the import wizard, choose | as a delimiter.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Shums Faruk

Can you please upload your text file?
Subodh Tiwari (Neeraj)

After importing your text file into excel, try the array formulas as shown in the attached.
See if this is what you are trying to achieve.

Edit:
There are two Array Formulas, one which is extracting the fields across the columns and other which extracts the items down the rows.
TransposeData.xlsx
AIGS

ASKER
Subodh - unfortunately there is nothing to indicate the end of a record as all lines have a line break at end.

I have attached a small sample of the text file.

The field name "ID IS" is the start of each new record.
expexch_data.txt
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Subodh Tiwari (Neeraj)

Also notice 15 in the following line of code...
For i = 1 To slr Step 15
here 15 means the number of fields in each set of records. If it is different in the actual data set, change it accordingly.
Nitin Sontakke

For record, here is another approach:

First import the file MS-Excel, so you have two column A and B.

Then paste the following formula in cell C1 and replicate it in all rows.

=IF(MOD(ROW(),15)=0,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-14,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-13,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-12,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-11,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-10,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-9,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-8,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-7,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-6,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-5,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-4,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-3,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-1,-1)&"~"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),"")

Open in new window


Then either filter out all blank rows and copy the contents or first copy the contents and get rid of all blank rows. In formula used ~ as separator as it was not there is sample file.
SOLUTION
aikimark

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
AIGS

ASKER
Thanks - this worked a treat.

Also thanks to everyone else who contributed.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Subodh Tiwari (Neeraj)

You're welcome AIGS! Glad it worked.