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.
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
Last Comment
Subodh Tiwari (Neeraj)
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?
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
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
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
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
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.
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 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.
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.
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),"")
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
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.
ASKER
Thanks - this worked a treat.
Also thanks to everyone else who contributed.
Also thanks to everyone else who contributed.
You're welcome AIGS! Glad it worked.
Open in new window