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

Harishiyer · Sep 18, 2015

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

Harishiyer · Sep 21, 2015

Hi I don't understand one thing . How does this

0001 HARISH 785.26 856.87 1642.13 M

data split

John_w · Sep 21, 2015

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

Harishiyer · Sep 21, 2015

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

John_w · Sep 22, 2015

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.

Mankum · May 4, 2020

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 Par

Supplier Number

Site

INVOICE_NUMBER

VOUCHER_NUMBER

DUE_DATE

DAYS_DUE

UNPAID

AMOUNT_REMAINING

0-30_DAYS

31-60_DAYS

61-90_DAYS

OVER_90_DAYS

"

Please help me in this.

John_w · May 4, 2020

Mankum, welcome to Mr Excel forums, however please start a new thread.

Mankum · May 5, 2020

John_w said:
Mankum, welcome to Mr Excel forums, however please start a new thread.

Ok, Thanks, John

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

Harishiyer

Board Regular

Harishiyer

Board Regular

Excel Facts

John_w

MrExcel MVP

Harishiyer

Board Regular

John_w

MrExcel MVP

Mankum

New Member

John_w

MrExcel MVP

Mankum

New Member

Forum statistics

Share this page

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

Board Regular

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

New Member

MrExcel MVP

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock