Vba to loop on a text file and extract data into excel ( reading text file from vba)

H

Harishiyer

Board Regular
Joined
Apr 19, 2015
Messages
61
Hi All,

I have a query. I have a text file in a path . I want to read the text file from excel . the text file looks as below

Code: 
------START---------
DATE_OF_ACTIVITY : 09182015_1107_WED
CLASS : 4D        SEC : C
REPORT : MONTH_WISE_REPORT           USER : ARJUN
S.NO      NAME              MATHS_MARK     SCIENCE_MARK     TOTAL     AVERAGE
----      ----              ----------     ------------     -----     -------
  JOUID:443345  SERVICE : NAT
0001    HARISH   785.26   856.87   1642.13           M
0002    GANSDD    45.65    10.00     55.65           T
  JOUID:441122  SERVICE : VAT
0003    HASASA    12.00    10.00     22.00           M
0004    HAAFFG    11.00    10.00     21.00           Y
     END OF SERVICE = DATA

CLASS : 5E        SEC : U
REPORT : DAY_WISE_REPORT           USER : SYSTEM
S.NO      NAME              MATHS_MARK     SCIENCE_MARK     TOTAL     AVERAGE SIGN LOGINID    PUBLIC
----      ----              ----------     ------------     -----     -------   ----    -------    ------
  JOUID:AS1234  SERVICE : CAT
0444    MANJIK    18.91    12.11     50.11           I    OPO      LOPOL         Y
0555    LOPING    40.11    10.00     50.11           O    WPO      MOPOL         N
  JOUID:567654  SERVICE : QAT
0666    QWERTY    40.11    10.00     50.11           P    OBB      LUUOL         N
0888    ZXCVBN    40.11    10.00     50.11           R    OPO      LOUOL         N
     END OF SERVICE = GYANN
 
CLASS : 4E        SEC : F
REPORT : MONTH_WISE_REPORT           USER : MANOJ
S.NO      NAME              MATHS_MARK     SCIENCE_MARK     TOTAL     AVERAGE
----      ----              ----------     ------------     -----     -------
  JOUID:44AA11  SERVICE : LAT
0045    gurtyy   785.26   856.87   1642.13           U
0056    MAJOIU    88.65    10.00     98.65           I
0072    LKPOIU    90.00    10.00    100.00           O
0091    ASDEWQ    45.00    10.00     55.00           P
     END OF SERVICE = DATA
 
------END---------

This is just a sample . The text file will be more tha 40000 lines. The output i want to acheive is looks as below



Code: 
[TABLE="width: 996"]
<TBODY>[TR]
[TD]DATE_OF_ACTIVITY</SPAN>
[/TD]
[TD]CLASS</SPAN>
[/TD]
[TD]SEC</SPAN>
[/TD]
[TD]USER</SPAN>
[/TD]
[TD]JOUID</SPAN>
[/TD]
[TD]SERVICE</SPAN>
[/TD]
[TD]S.NO</SPAN>
[/TD]
[TD]NAME</SPAN>
[/TD]
[TD]MATHS_MARK</SPAN>
[/TD]
[TD]SCIENCE_MARK</SPAN>
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]AVERAGE</SPAN>
[/TD]
[TD]END OF SERVICE</SPAN>
[/TD]
[TD]PARAMETER-1</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]443345</SPAN>
[/TD]
[TD]NAT</SPAN>
[/TD]
[TD]0001</SPAN>
[/TD]
[TD]HARISH</SPAN>
[/TD]
[TD]785.26</SPAN>
[/TD]
[TD]856.87</SPAN>
[/TD]
[TD]1642.13</SPAN>
[/TD]
[TD]M</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]HARISH0001</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]443345</SPAN>
[/TD]
[TD]NAT</SPAN>
[/TD]
[TD]0002</SPAN>
[/TD]
[TD]GANSDD</SPAN>
[/TD]
[TD]45.65</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]55.65</SPAN>
[/TD]
[TD]T</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]GANSDD0002</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]441122</SPAN>
[/TD]
[TD]VAT</SPAN>
[/TD]
[TD]0003</SPAN>
[/TD]
[TD]HASASA</SPAN>
[/TD]
[TD]12</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]22</SPAN>
[/TD]
[TD]M</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]HASASA0003</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4D</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD]ARJUN</SPAN>
[/TD]
[TD]441122</SPAN>
[/TD]
[TD]VAT</SPAN>
[/TD]
[TD]0004</SPAN>
[/TD]
[TD]HAAFFG</SPAN>
[/TD]
[TD]11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]21</SPAN>
[/TD]
[TD]Y</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]HAAFFG0004</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0045</SPAN>
[/TD]
[TD]gurtyy</SPAN>
[/TD]
[TD]785.26</SPAN>
[/TD]
[TD]856.87</SPAN>
[/TD]
[TD]1642.13</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]gurtyy0045</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0056</SPAN>
[/TD]
[TD]MAJOIU</SPAN>
[/TD]
[TD]88.65</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]98.65</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]MAJOIU0056</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0072</SPAN>
[/TD]
[TD]LKPOIU</SPAN>
[/TD]
[TD]90</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]100</SPAN>
[/TD]
[TD]O</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]LKPOIU0072</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]4E</SPAN>
[/TD]
[TD]F</SPAN>
[/TD]
[TD]MANOJ</SPAN>
[/TD]
[TD]44AA11</SPAN>
[/TD]
[TD]LAT</SPAN>
[/TD]
[TD]0091</SPAN>
[/TD]
[TD]ASDEWQ</SPAN>
[/TD]
[TD]45</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]55</SPAN>
[/TD]
[TD]P</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[TD]ASDEWQ0091</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


This data should be moved to a sheet called MONTH_WISE_REPORT


Code: 
[TABLE="width: 1035"]
<TBODY>[TR]
[TD]DATE_OF_ACTIVITY</SPAN>
[/TD]
[TD]CLASS</SPAN>
[/TD]
[TD]SEC</SPAN>
[/TD]
[TD]USER</SPAN>
[/TD]
[TD]JOUID</SPAN>
[/TD]
[TD]SERVICE</SPAN>
[/TD]
[TD]S.NO</SPAN>
[/TD]
[TD]NAME</SPAN>
[/TD]
[TD]MATHS_MARK</SPAN>
[/TD]
[TD]SCIENCE_MARK</SPAN>
[/TD]
[TD]TOTAL</SPAN>
[/TD]
[TD]AVERAGE</SPAN>
[/TD]
[TD]SIGN</SPAN>
[/TD]
[TD]LOGINID</SPAN>
[/TD]
[TD]PUBLIC</SPAN>
[/TD]
[TD]END OF SERVICE</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]AS1234</SPAN>
[/TD]
[TD]CAT</SPAN>
[/TD]
[TD]0444</SPAN>
[/TD]
[TD]MANJIK</SPAN>
[/TD]
[TD]18.91</SPAN>
[/TD]
[TD]12.11</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]I</SPAN>
[/TD]
[TD]OPO</SPAN>
[/TD]
[TD]LOPOL</SPAN>
[/TD]
[TD]Y</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]AS1234</SPAN>
[/TD]
[TD]CAT</SPAN>
[/TD]
[TD]0555</SPAN>
[/TD]
[TD]LOPING</SPAN>
[/TD]
[TD]40.11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]O</SPAN>
[/TD]
[TD]WPO</SPAN>
[/TD]
[TD]MOPOL</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]567654</SPAN>
[/TD]
[TD]QAT</SPAN>
[/TD]
[TD]0666</SPAN>
[/TD]
[TD]QWERTY</SPAN>
[/TD]
[TD]40.11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]P</SPAN>
[/TD]
[TD]OBB</SPAN>
[/TD]
[TD]LUUOL</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
[TR]
[TD]09182015_1107_WED</SPAN>
[/TD]
[TD]5E</SPAN>
[/TD]
[TD]U</SPAN>
[/TD]
[TD]SYSTEM</SPAN>
[/TD]
[TD]567654</SPAN>
[/TD]
[TD]QAT</SPAN>
[/TD]
[TD]0888</SPAN>
[/TD]
[TD]ZXCVBN</SPAN>
[/TD]
[TD]40.11</SPAN>
[/TD]
[TD]10</SPAN>
[/TD]
[TD]50.11</SPAN>
[/TD]
[TD]R</SPAN>
[/TD]
[TD]OPO</SPAN>
[/TD]
[TD]LOUOL</SPAN>
[/TD]
[TD]N</SPAN>
[/TD]
[TD]GYANN</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

this data should move a sheet called to DAY_WISE_REPORT

i Dont want to import the text and work on excel.
I want to read from the excel sheet.

I would be vary happy if some one can help.
Thanks,
Harish
 
Sort by date Sort by votes

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
The line might be

0001 HARISH 785.26 856.87 1642.13 M

Or

0444 MANJIK 18.91 12.11 50.11 I OPO LOPOL Y

This line splits it:
Code: 
        parts = Split(Application.WorksheetFunction.Trim(fileLine), " ")
The Trim compresses multiple spaces to single spaces (the forum has removed them above) and the Split function puts the 6 or 9 values into the parts array.

If the first part, parts(0), is numeric then the line is considered to contain data values (not the headings) and the values are put in the monthData or dayData array, as appropriate, by these sections of code:
Code: 
        If UBound(parts) = 5 Then
            If IsNumeric(parts(0)) Then


        If UBound(parts) = 8 Then
            If IsNumeric(parts(0)) Then
 
Upvote 0
Actuallly can we do it by Selection.TextToColumns coloumns because sometimes instead of
0001 HARISH 785.26 856.87 1642.13 M
it may actualy be
0001 785.26 856.87 1642.13 M
 
Upvote 0
TextToColumns would require a complete redesign and rewrite.

I can only write the code according to the data supplied, and this new variation complicates matters. If there is no NAME, what should the PARAMETER-1 value be? Does this variation also apply to the day data lines? Questions, questions: you should supply all relevant details and variations at the outset, instead of drip-feeding it, which requires additional thought and coding.
 
Upvote 0
Hi All,

I have same query but different data in txt file. could anyone please help me in extracting data from txt file to excel.

please do let me know if above code can be customize for my requirement.

below is structure of my data -

"
Ledger: PFI_CORE_USD_BOOK Report Date: 28-APR-2020 17:10
Operating Unit: PFI Domestic Page: 2
Invoice Aging Report

Invoice Voucher Due Days % Amount 0-30 Days 31-60 Days 61-90 Days Over 90 Days
Number Number Date Due Unpaid Remaining
------------- ---------- --------- ------ -------- --------------- --------------- --------------- --------------- ---------------
Trading Par: ABC Company1
Supplier Number: 12345
Site: 004-SAN FRANCIS SAN FRANCISCO CA

031820008 18-MAR-20 41 100.0 348,887.68 0.00 348,887.68 0.00 0.00
--------------- ------------------------------ --------------- ---------------
Total: 348,887.68 0.00 348,887.68 0.00 0.00
0% 100% 0% 0%

Trading Par: ABC Company2
Supplier Number: 11234
Site: HOME

PFI2841828 24-SEP-18 582 100.0 -118.45 0.00 0.00 0.00 -118.45
--------------- ------------------------------ --------------- ---------------
Total: -118.45 0.00 0.00 0.00 -118.45
0% 0% 0% 100%

Trading Par: ABC Company3
Supplier Number: 100600
Site: 008-CHICAGO CHICAGO IL

BLS- 08-SEP-19 233 100.0 7,500.00 0.00 0.00 0.00 7,500.00
SAMDECCL19PRD
--------------- ------------------------------ --------------- ---------------
Total: 7,500.00 0.00 0.00 0.00 7,500.00
0% 0% 0% 100%

Trading Par: ABC Company4
Supplier Number: 119900
Site: 002-HICKSVILLE HICKSVILLE NY .TEL 822-6230

APPS 7-19 30-AUG-19 242 100.0 152.80 0.00 0.00 0.00 152.80
Shirley_Willi
ams
2003313640 31-MAR-20 28 100.0 28,181.53 28,181.53 0.00 0.00 0.00
--------------- ------------------------------ --------------- ---------------
Total: 28,334.33 28,181.53 0.00 0.00 152.80
99% 0% 0% 1%

Trading Par: ABC Company5
Supplier Number: 500123
Site: HOME

PFI3539066 08-JUL-19 295 100.0 -8.00 0.00 0.00 0.00 -8.00
--------------- ------------------------------ --------------- ---------------
Total: -8.00 0.00 0.00 0.00 -8.00
0% 0% 0% 100%

Trading Par: ABC Company6
Supplier Number: 101192
Site: 001-PHILADELPHI PHILADELPHIA PA

100000889012- 06-APR-20 22 100.0 811,679.40 811,679.40 0.00 0.00 0.00
2020
--------------- ------------------------------ --------------- ---------------
Total: 811,679.40 811,679.40 0.00 0.00 0.00
100% 0% 0% 0%
Ledger: PFI_CORE_USD_BOOK Report Date: 28-APR-2020 17:10
Operating Unit: PFI Domestic Page: 3
Invoice Aging Report

Invoice Voucher Due Days % Amount 0-30 Days 31-60 Days 61-90 Days Over 90 Days
Number Number Date Due Unpaid Remaining
------------- ---------- --------- ------ -------- --------------- --------------- --------------- --------------- ---------------
Trading Par: ABC Company7
Supplier Number: 110098
Site: 008-NEWARK NEWARK NJ

266693R 05-DEC-19 145 100.0 68,888.02 0.00 0.00 0.00 68,888.02
1047 14-DEC-19 136 100.0 96,445.70 0.00 0.00 0.00 96,445.70
1048 14-DEC-19 136 100.0 13,726.17 0.00 0.00 0.00 13,726.17
266862 14-DEC-19 136 100.0 3,341.37 0.00 0.00 0.00 3,341.37
1049 17-JAN-20 102 100.0 33,118.48 0.00 0.00 0.00 33,118.48
267840 20-FEB-20 68 100.0 77,929.86 0.00 0.00 77,929.86 0.00
1061 16-MAR-20 43 100.0 530,813.91 0.00 530,813.91 0.00 0.00
1071 16-MAR-20 43 100.0 1,079,488.29 0.00 1,079,488.29 0.00 0.00
--------------- ------------------------------ --------------- ---------------
Total: 1,903,751.80 0.00 1,610,302.20 77,929.86 215,519.74
0% 85% 4% 11%

I want below data in excel -

Trading ParSupplier NumberSiteINVOICE_NUMBERVOUCHER_NUMBERDUE_DATEDAYS_DUEUNPAIDAMOUNT_REMAINING0-30_DAYS31-60_DAYS61-90_DAYSOVER_90_DAYS
"

Please help me in this.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,909
Messages
6,175,314
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