Link to home
Start Free TrialLog in
Avatar of AIGS
AIGSFlag 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.
Avatar of Shums Faruk
Shums Faruk
Flag of India image

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

User generated imageThis is how you want?
Avatar of 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?
You can directly import the text file into Excel and during the import wizard, choose | as a delimiter.
Can you please upload your text file?
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
Avatar of 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
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AIGS

ASKER

Thanks - this worked a treat.

Also thanks to everyone else who contributed.
You're welcome AIGS! Glad it worked.