Convert to excel/text

D

drjpb

Board Regular
Joined
Dec 11, 2013
Messages
66
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi, i am trying to convert the text file (extracted from an old database) into excel.
Unfortunately i am unable to preform it as a single row data is divided to multiple lines, and then the page break.

I once remember one of our IT expert provided me a solution with a similar problem using xmls , but it was in different format and that IT expert is no longer with us.

So how can i convert the following data into excel.
I am posting two pages of it, whereas i have multiple pages:-




D_BRANCH_NO D_GL_NO D_SL_NO D_EN_YEAR D_EN_MONTH D_EN_DAY D_ENTRY_TIME
----------- ------- ---------- --------- ---------- -------- ------------
D_TR_YEAR D_TR_MONTH D_TR_DAY D_TRANS_CODE D_TRANS_AMOUNT
--------- ---------- -------- ------------ ---------------------
D_TRANS_DESCRIPTION D_USER_NO D_TERMINAL_NO D_BANK_NO D_VOUCHER_NO
------------------- --------- ------------- --------- ------------
D_INVTRSF_NO D_TRAN_NO D_TRANS_TYPE D_INVOICE_NO D_DOCUMENT_NO
------------ ---------- ------------ ------------ -------------
D_TRANS_BALANCE
---------------------


200 666 329 89 12 2 17364101
1989 12 2 2 6600.000
è 000014134 614 20 0 0
0 63 61 0 0
6600.000
200 666 329 89 12 2 20001384
1989 12 2 2 3645.000
è 000014122 614 20 0 0
0 108 61 0 0
10245.000
200 666 329 89 12 2 20013684
1989 12 2 2 7590.375
è 000014123 614 20 0 0
0 110 61 0 0
17835.375
200 666 329 89 12 2 20043347
1989 12 2 2 3745.000
è 000014124 614 20 0 0
0 112 61 0 0
21580.375
200 666 329 89 12 2 20052978
1989 12 2 2 3645.000
è 000014125 614 20 0 0
0 114 61 0 0
25225.375
200 666 329 89 12 2 20060264
1989 12 2 2 3645.000
è 000014126 614 20 0 0
0 116 61 0 0
28870.375
200 666 329 89 12 2 20095566
1989 12 2 2 4393.200
è 000014127 614 20 0 0
0 122 61 0 0
33263.575
200 666 329 89 12 2 20102294
1989 12 2 2 4393.200
è 000014128 614 20 0 0
0 124 61 0 0
37656.775
200 666 329 89 12 2 20112564
1989 12 2 2 3163.200
è 000014129 614 20 0 0
0 126 61 0 0
40819.975
200 666 329 89 12 2 20120918
1989 12 2 2 4003.200




D_BRANCH_NO D_GL_NO D_SL_NO D_EN_YEAR D_EN_MONTH D_EN_DAY D_ENTRY_TIME
----------- ------- ---------- --------- ---------- -------- ------------
D_TR_YEAR D_TR_MONTH D_TR_DAY D_TRANS_CODE D_TRANS_AMOUNT
--------- ---------- -------- ------------ ---------------------
D_TRANS_DESCRIPTION D_USER_NO D_TERMINAL_NO D_BANK_NO D_VOUCHER_NO
------------------- --------- ------------- --------- ------------
D_INVTRSF_NO D_TRAN_NO D_TRANS_TYPE D_INVOICE_NO D_DOCUMENT_NO
------------ ---------- ------------ ------------ -------------
D_TRANS_BALANCE
---------------------


è 000014130 614 20 0 0
0 128 61 0 0
44823.175
200 666 329 89 12 2 20130301
1989 12 2 2 5278.500
è 000014131 614 20 0 0
0 134 61 0 0
50101.675
200 666 329 89 12 2 20135220
1989 12 2 2 7600.000
è 000014132 614 20 0 0
0 136 61 0 0
57701.675
200 666 329 89 12 3 16291853
1989 12 3 2 6650.000
è 000014141 614 20 0 0
0 284 61 0 0
64351.675
200 666 329 89 12 3 17474267
1989 12 3 2 5950.000
è 000014135 614 20 0 0
0 332 61 0 0
70301.675
200 666 329 89 12 3 17560781
1989 12 3 2 4772.400
è 000014151 614 20 0 0
0 336 61 0 0
75074.075
200 666 329 89 12 3 18124199
1989 12 3 2 4200.000
è 000014136 614 20 0 0
0 350 61 0 0
79274.075
200 666 329 89 12 3 18203483
1989 12 3 2 3745.000
è 000014137 614 20 0 0
0 366 61 0 0
83019.075
200 666 329 89 12 3 18230093
1989 12 3 2 3745.000
è 000014138 614 20 0 0
0 376 61 0 0
86764.075
200 666 329 89 12 3 18515809
1989 12 3 2 7300.000
è 000014140 614 20 0 0
0 408 61 0 0




D_BRANCH_NO D_GL_NO D_SL_NO D_EN_YEAR D_EN_MONTH D_EN_DAY D_ENTRY_TIME
----------- ------- ---------- --------- ---------- -------- ------------
D_TR_YEAR D_TR_MONTH D_TR_DAY D_TRANS_CODE D_TRANS_AMOUNT
--------- ---------- -------- ------------ ---------------------
D_TRANS_DESCRIPTION D_USER_NO D_TERMINAL_NO D_BANK_NO D_VOUCHER_NO
------------------- --------- ------------- --------- ------------
D_INVTRSF_NO D_TRAN_NO D_TRANS_TYPE D_INVOICE_NO D_DOCUMENT_NO
------------ ---------- ------------ ------------ -------------
D_TRANS_BALANCE
---------------------
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
I am not at a computer right now, but I would say go to the Insert tab and over to the left is where you import documents, there is one part for importing a text file...
 
Upvote 0
Thank you for your quick reply. But I doubt that will work for such a format. I will give it a try and will let you know
 
Upvote 0
The "From Text" option didn't work well for me. let me explain my data is split into 5 rows, I can remove the header from each page (Replace option), but how can i transform my data into one row?
For example first line of my data should be:-

[TABLE="width: 1332"]
<tbody>[TR]
[TD="width: 77, align: right"]200[/TD]
[TD="width: 35, align: right"]666[/TD]
[TD="width: 28, align: right"]329[/TD]
[TD="width: 21, align: right"]89[/TD]
[TD="width: 70, align: right"]12[/TD]
[TD="width: 14, align: right"]2[/TD]
[TD="width: 63, align: right"]17364101[/TD]
[TD="width: 64, align: right"]1989[/TD]
[TD="width: 64, align: right"]12[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]6600[/TD]
[TD="width: 64, align: right"]14134[/TD]
[TD="width: 64, align: right"]614[/TD]
[TD="width: 64, align: right"]20[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]63[/TD]
[TD="width: 64, align: right"]61[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]6600[/TD]
[/TR]
</tbody>[/TABLE]
as you can see the header (Title) of my data is also divided into 5 rows.

Any suggestion guys?
Thanks in Advance.
 
Upvote 0
drjpb said:
The "From Text" option didn't work well for me. let me explain my data is split into 5 rows, I can remove the header from each page (Replace option), but how can i transform my data into one row?
For example first line of my data should be:-

[TABLE="width: 1332"]
<tbody>[TR]
[TD="width: 77, align: right"]200[/TD]
[TD="width: 35, align: right"]666[/TD]
[TD="width: 28, align: right"]329[/TD]
[TD="width: 21, align: right"]89[/TD]
[TD="width: 70, align: right"]12[/TD]
[TD="width: 14, align: right"]2[/TD]
[TD="width: 63, align: right"]17364101[/TD]
[TD="width: 64, align: right"]1989[/TD]
[TD="width: 64, align: right"]12[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]6600[/TD]
[TD="width: 64, align: right"]14134[/TD]
[TD="width: 64, align: right"]614[/TD]
[TD="width: 64, align: right"]20[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]63[/TD]
[TD="width: 64, align: right"]61[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]0[/TD]
[TD="width: 64, align: right"]6600[/TD]
[/TR]
</tbody>[/TABLE]
as you can see the header (Title) of my data is also divided into 5 rows.

Any suggestion guys?
Thanks in Advance.
Click to expand...

Is this the result you're looking for?

[TABLE="width: 2218"]
<tbody>[TR]
[TD]D_BRANCH_NO D_GL_NO D_SL_NO D_EN_YEAR D_EN_MONTH D_EN_DAY D_ENTRY_TIME[/TD]
[TD]D_TR_YEAR D_TR_MONTH D_TR_DAY D_TRANS_CODE D_TRANS_AMOUNT[/TD]
[TD]D_TRANS_DESCRIPTION D_USER_NO D_TERMINAL_NO D_BANK_NO D_VOUCHER_NO[/TD]
[TD]D_INVTRSF_NO D_TRAN_NO D_TRANS_TYPE D_INVOICE_NO D_DOCUMENT_NO[/TD]
[TD]D_TRANS_BALANCE[/TD]
[/TR]
[TR]
[TD]200 666 329 89 12 2 17364101[/TD]
[TD]1989 12 2 2 6600.000[/TD]
[TD]è 000014134 614 20 0 0[/TD]
[TD]0 63 61 0 0[/TD]
[TD="align: right"]6600[/TD]
[/TR]
[TR]
[TD]200 666 329 89 12 2 20001384[/TD]
[TD]1989 12 2 2 3645.000[/TD]
[TD]è 000014122 614 20 0 0[/TD]
[TD]0 108 61 0 0[/TD]
[TD="align: right"]10245[/TD]
[/TR]
[TR]
[TD]200 666 329 89 12 2 20013684[/TD]
[TD]1989 12 2 2 7590.375[/TD]
[TD]è 000014123 614 20 0 0[/TD]
[TD]0 110 61 0 0[/TD]
[TD="align: right"]17835.375[/TD]
[/TR]
[TR]
[TD]200 666 329 89 12 2 20043347[/TD]
[TD]1989 12 2 2 3745.000[/TD]
[TD]è 000014124 614 20 0 0[/TD]
[TD]0 112 61 0 0[/TD]
[TD="align: right"]21580.375[/TD]
[/TR]
[TR]
[TD]200 666 329 89 12 2 20052978[/TD]
[TD]1989 12 2 2 3645.000[/TD]
[TD]è 000014125 614 20 0 0[/TD]
[TD]0 114 61 0 0[/TD]
[TD="align: right"]25225.375[/TD]
[/TR]
[TR]
[TD]200 666 329 89 12 2 20060264[/TD]
[TD]1989 12 2 2 3645.000[/TD]
[TD]è 000014126 614 20 0 0[/TD]
[TD]0 116 61 0 0[/TD]
[TD="align: right"]28870.375[/TD]
[/TR]
[TR]
[TD]200 666 329 89 12 2 20095566[/TD]
[TD]1989 12 2 2 4393.200[/TD]
[TD]è 000014127 614 20 0 0[/TD]
[TD]0 122 61 0 0[/TD]
[TD="align: right"]33263.575[/TD]
[/TR]
[TR]
[TD]200 666 329 89 12 2 20102294[/TD]
[TD]1989 12 2 2 4393.200[/TD]
[TD]è 000014128 614 20 0 0[/TD]
[TD]0 124 61 0 0[/TD]
[TD="align: right"]37656.775[/TD]
[/TR]
[TR]
[TD]200 666 329 89 12 2 20112564[/TD]
[TD]1989 12 2 2 3163.200[/TD]
[TD]è 000014129 614 20 0 0[/TD]
[TD]0 126 61 0 0[/TD]
[TD="align: right"]40819.975[/TD]
[/TR]
</tbody>[/TABLE]

If so, ASAP Utilities has a function named "Transpose column in multiple steps..."
 
Upvote 0
Solution
Yes this is acceptable. In-fact this the best result i have seen so far.
Let me give ASAP Utility a try. Thanks tonyyy in advance.
 
Upvote 0
I tried ASAP Utilities, as advised, it worked like a charm. How did the utility find out automatically that the data consists on 5 rows. Amazing.
Thanks a lot tonyyy, problem solved.
 
Upvote 0
You must log in or register to reply here.

Similar threads

tezza
  • Solved
Calculate time from two columns
Replies
4
Views
200
tezza
tezza
O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
295
Skyybot
Skyybot
R
  • Solved
Ignore blanks for a SUM
Replies
2
Views
115
Rex987456
R
N
  • Solved
VBA auto fill in information from the list in one sheet to another sheet and save as the excel file.
Replies
5
Views
199
ngochien251088
N
Sufiyan97
  • Solved
Filter all the data for multiple IDs on separate tab Excel 2013
2
Replies
19
Views
649
Georgiboy
Georgiboy

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top