retrieve data from a text file to excel

T

tpriyadi

New Member
Joined
Oct 11, 2015
Messages
2
Hello, first time i would say Sorry if similar question is already posted by another users. I got text file which is i will retrieve to excel table structure. But i don't understand what is logic to solve this.i will appreciate for anyone that can help my issue.

Below is data from text file:

ORDMSGSUPPLIE R NAMEAIA IAIAIAIAI CV. PONUMBER12015100920151013CUSTOMERGROUP BLABLABLA BLABL 111111A010
ORDDTL11111111 0002500240026400010003086PRODUCTNAME111111111 0 0 7078500
ORDDTL22222222222220001300480065760020013772PRODUCTNAME222222222 0 0 9168588
ORDDTL33333333333330000600960032640020017057PRODUCTNAME333333333 0 0 2100384
ORDDTL44444444444440000800720037440020017974PRODUCTNAME444444444 0 0 3212352
ORDMSGSUPPLIE R NAMEAIA IAIAIAIAI CV. PONUMBER22015100920151013CUSTOMERGROUP BLABLABLA BLABL 111111A010
ORDDTL55555555555550001300480030720020017349PRODUCTNAME111111111 0 0 4283136
ORDDTL66666666666660002500240017280020042026PRODUCTNAME222222222 0 0 4633200
ORDMSGSUPPLIE R NAMEAIA IAIAIAIAI CV. PONUMBER32015100920151013CUSTOMERGROUP BLABLABLA BLABL 111111A010
ORDDTL55555555555550000302400105960010000665PRODUCTNAME111111111 0 0 3409263
ORDDTL33333333333330000600960092448010008588PRODUCTNAME222222222 0 0 5949028,8
ORDMSGSUPPLIE R NAMEAIA IAIAIAIAI CV. PONUMBER42015100920151013CUSTOMERGROUP BLABLABLA BLABL 111111A010
ORDDTL77777777777770002500240037920020008415PRODUCTNAME111111111 0 0 10167300
ORDDTL88888888888880002500240034680020008420PRODUCTNAME222222222 0 0 9298575
ORDMSGSUPPLIE R NAMEAIA IAIAIAIAI CV. PONUMBER52015100920151013CUSTOMERGROUP BLABLABLA BLABL 111111A010
ORDDTL99999999999990001300480069120010026930PRODUCTNAME111111111 0 0 9637056


i have tried as far as i understand with vba code but it's only worked for 1 PO number. Here is the result:

NO_PO TGL_PO EXPRD Barcode QTY Store_Code
PONUMBER1 20151009 20151013 11111111 25 1A010
PONUMBER1 20151009 20151013 2222222222222 13 1A010
PONUMBER1 20151009 20151013 3333333333333 6 1A010
PONUMBER1 20151009 20151013 4444444444444 8 1A010


Here is the VBA code
'-----------------------------------------------------------------

Sub readTextFile()

Dim No_PO As String
Dim TGL_PO As String
Dim EXPRD As String
Dim BARCODE As String
Dim QTY As String
Dim STR_CODE As String
Dim f_text2 As String


f_text2 = ActiveWorkbook.Name
Workbooks(f_text2).Activate
Worksheets("raw_csv").Select
ClearData

Range("A2").Activate

Dim MyFolder As String
Dim txtfile2 As String
Dim folderName As String

txtfile2 = "order_data.txt"
folderName = "C:\local_data\raw_data\"

Workbooks.OpenText Filename:=folderName & "\" & txtfile2

Workbooks(txtfile2).Activate
Range("A1").Activate

No_PO = ActiveCell.Offset(0, 0).Value
TGL_PO = ActiveCell.Offset(0, 0).Value
RDD = ActiveCell.Offset(0, 0).Value
STR_CODE = ActiveCell.Offset(0, 3).Value

Do Until ActiveCell.Offset(1, 3).Value <> ""
ActiveCell.Offset(1, 0).Activate
BARCODE = ActiveCell.Offset(0, 0).Value
QTY = ActiveCell.Offset(0, 0).Value


'Personal_Responsibility = ActiveCell.Offset(51, 0).Value
'Position = ActiveCell.Offset(53, 0).Value

'ActiveCell.Offset(60, 0).Activate 41
Workbooks(f_text2).Activate
Worksheets("raw_csv").Select
ActiveCell.Value = Mid(Trim(No_PO), 41, 10)
ActiveCell.Offset(0, 1).Value = Mid(Trim(TGL_PO), 51, 8)
ActiveCell.Offset(0, 2).Value = Mid(Trim(EXPRD), 59, 8)
ActiveCell.Offset(0, 3).Value = Mid(Trim(BARCODE), 7, 13)
ActiveCell.Offset(0, 4).Value = Mid(Trim(QTY), 20, 5)
ActiveCell.Offset(0, 5).Value = Mid(Trim(STR_CODE), 6, 6)
ActiveCell.Offset(1, 0).Activate
Workbooks(txtfile2).Activate
Loop

End Sub
'----------------------------------------------------------------

i want to expect result in excel table like below:


NO_PO TGL_PO EXPRD Barcode QTY Store_Code
PONUMBER1 20151009 20151013 11111111 25 1A010
PONUMBER1 20151009 20151013 2222222222222 13 1A010
PONUMBER1 20151009 20151013 3333333333333 6 1A010
PONUMBER1 20151009 20151013 4444444444444 8 1A010
PONUMBER2 20151009 20151013 5555555555555 13 1A010
PONUMBER2 20151009 20151013 6666666666666 25 1A010
PONUMBER3 20151009 20151013 5555555555555 3 1A010
PONUMBER3 20151009 20151013 3333333333333 6 1A010
PONUMBER4 20151009 20151013 7777777777777 25 1A010
PONUMBER4 20151009 20151013 8888888888888 25 1A010
PONUMBER5 20151009 20151013 9999999999999 13 1A010
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Hi, i have decide to change source data to excel. i open txt file to excel with "fixed width". and below is the result:

Code: 
[TABLE="width: 832"]
<tbody>[TR]
[TD]ORDMSG[/TD]
[TD]SUPPLIE R NAM[/TD]
[TD]EAIA[/TD]
[TD]IAIA[/TD]
[TD]IAIAI CV.[/TD]
[TD]PONUMBER1[/TD]
[TD="align: right"]20151009[/TD]
[TD="align: right"]20151013[/TD]
[TD]CUSTOMERGROUP[/TD]
[TD]BLABLABLA[/TD]
[TD]BLABL[/TD]
[TD="align: right"]11111[/TD]
[TD]1A010[/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]11111111[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]2.64E+10[/TD]
[TD]086PRODUC[/TD]
[TD]TNAME111[/TD]
[TD="align: right"]111111[/TD]
[TD]0    0[/TD]
[TD="align: right"]7078500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]2.22E+12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]6.58E+10[/TD]
[TD]772PRODUC[/TD]
[TD]TNAME222[/TD]
[TD="align: right"]222222[/TD]
[TD]0    0[/TD]
[TD="align: right"]9168588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]3.33E+12[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]3.26E+10[/TD]
[TD]057PRODUC[/TD]
[TD]TNAME333[/TD]
[TD="align: right"]333333[/TD]
[TD]0    0[/TD]
[TD="align: right"]2100384[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]4.44E+12[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]3.74E+10[/TD]
[TD]974PRODUC[/TD]
[TD]TNAME444[/TD]
[TD="align: right"]444444[/TD]
[TD]0    0[/TD]
[TD="align: right"]3212352[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDMSG[/TD]
[TD]SUPPLIE R NAM[/TD]
[TD]EAIA[/TD]
[TD]IAIA[/TD]
[TD]IAIAI CV.[/TD]
[TD]PONUMBER2[/TD]
[TD="align: right"]20151009[/TD]
[TD="align: right"]20151013[/TD]
[TD]CUSTOMERGROUP[/TD]
[TD]BLABLABLA[/TD]
[TD]BLABL[/TD]
[TD="align: right"]11111[/TD]
[TD]1A010[/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]5.56E+12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]3.07E+10[/TD]
[TD]349PRODUC[/TD]
[TD]TNAME111[/TD]
[TD="align: right"]111111[/TD]
[TD]0    0[/TD]
[TD="align: right"]4283136[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]6.67E+12[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]1.73E+10[/TD]
[TD]026PRODUC[/TD]
[TD]TNAME222[/TD]
[TD="align: right"]222222[/TD]
[TD]0    0[/TD]
[TD="align: right"]4633200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDMSG[/TD]
[TD]SUPPLIE R NAM[/TD]
[TD]EAIA[/TD]
[TD]IAIA[/TD]
[TD]IAIAI CV.[/TD]
[TD]PONUMBER3[/TD]
[TD="align: right"]20151009[/TD]
[TD="align: right"]20151013[/TD]
[TD]CUSTOMERGROUP[/TD]
[TD]BLABLABLA[/TD]
[TD]BLABL[/TD]
[TD="align: right"]11111[/TD]
[TD]1A010[/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]5.56E+12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]1.06E+11[/TD]
[TD]665PRODUC[/TD]
[TD]TNAME111[/TD]
[TD="align: right"]111111[/TD]
[TD]0    0[/TD]
[TD="align: right"]3409263[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]3.33E+12[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]9.24E+10[/TD]
[TD]588PRODUC[/TD]
[TD]TNAME222[/TD]
[TD="align: right"]222222[/TD]
[TD]0    0[/TD]
[TD="align: right"]594902[/TD]
[TD]8,8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDMSG[/TD]
[TD]SUPPLIE R NAM[/TD]
[TD]EAIA[/TD]
[TD]IAIA[/TD]
[TD]IAIAI CV.[/TD]
[TD]PONUMBER4[/TD]
[TD="align: right"]20151009[/TD]
[TD="align: right"]20151013[/TD]
[TD]CUSTOMERGROUP[/TD]
[TD]BLABLABLA[/TD]
[TD]BLABL[/TD]
[TD="align: right"]11111[/TD]
[TD]1A010[/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]7.78E+12[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]3.79E+10[/TD]
[TD]415PRODUC[/TD]
[TD]TNAME111[/TD]
[TD="align: right"]111111[/TD]
[TD]0    0[/TD]
[TD="align: right"]1016730[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]8.89E+12[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]3.47E+10[/TD]
[TD]420PRODUC[/TD]
[TD]TNAME222[/TD]
[TD="align: right"]222222[/TD]
[TD]0    0[/TD]
[TD="align: right"]9298575[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDMSG[/TD]
[TD]SUPPLIE R NAM[/TD]
[TD]EAIA[/TD]
[TD]IAIA[/TD]
[TD]IAIAI CV.[/TD]
[TD]PONUMBER5[/TD]
[TD="align: right"]20151009[/TD]
[TD="align: right"]20151013[/TD]
[TD]CUSTOMERGROUP[/TD]
[TD]BLABLABLA[/TD]
[TD]BLABL[/TD]
[TD="align: right"]11111[/TD]
[TD]1A010[/TD]
[/TR]
[TR]
[TD]ORDDTL[/TD]
[TD="align: right"]1E+13[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]6.91E+10[/TD]
[TD]930PRODUC[/TD]
[TD]TNAME111[/TD]
[TD="align: right"]111111[/TD]
[TD]0    0[/TD]
[TD="align: right"]9637056[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Kindly help please. What's looping code for this source file.
I need final result as below:
Code: 
[TABLE="width: 384"]
<colgroup><col style="width:48pt" span="6" width="64">  </colgroup><tbody>[TR]
   [TD="width: 64"]NO_PO[/TD]
   [TD="width: 64"]TGL_PO[/TD]
   [TD="width: 64"]EXPRD[/TD]
   [TD="width: 64"]Barcode[/TD]
   [TD="width: 64"]QTY[/TD]
   [TD="width: 64"]Store_Code[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER1[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]11111111[/TD]
   [TD="align: right"]25[/TD]
   [TD]1A010[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER1[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]2.22E+12[/TD]
   [TD="align: right"]13[/TD]
   [TD]1A010[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER1[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]3.33E+12[/TD]
   [TD="align: right"]6[/TD]
   [TD]1A010[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER1[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]4.44E+12[/TD]
   [TD="align: right"]8[/TD]
   [TD]1A010[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER2[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]5.56E+12[/TD]
   [TD="align: right"]13[/TD]
   [TD]1A010[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER2[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]6.67E+12[/TD]
   [TD="align: right"]25[/TD]
   [TD]1A010[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER3[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]5.56E+12[/TD]
   [TD="align: right"]3[/TD]
   [TD]1A010[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER3[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]3.33E+12[/TD]
   [TD="align: right"]6[/TD]
   [TD]1A010[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER4[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]7.78E+12[/TD]
   [TD="align: right"]25[/TD]
   [TD]1A010[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER4[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]8.89E+12[/TD]
   [TD="align: right"]25[/TD]
   [TD]1A010[/TD]
  [/TR]
  [TR]
   [TD]PONUMBER5[/TD]
   [TD="align: right"]20151009[/TD]
   [TD="align: right"]20151013[/TD]
   [TD="align: right"]1E+13[/TD]
   [TD="align: right"]13[/TD]
   [TD]1A010[/TD]
  [/TR]
 </tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Text Value from Textbox to Integer
Replies
1
Views
322
Kaper
Kaper
C
  • Question Question
VBA: open and copy data from multiple (txt. files) to excel
Replies
1
Views
505
NdNoviceHlp
NdNoviceHlp
H
  • Question Question
Macro: Convert pdf into Excel and extract certain data to put together with file name
Replies
1
Views
3K
HeinrichPaul
H
M
  • Question Question
Extract data from Word to Excel
Replies
1
Views
1K
MHamid
M
E
  • Question Question
Looking for VBA to help with data collection
Replies
0
Views
642
ExcelNewbie2009
E

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

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