Import Specific fields in *.txt to excel

[junyi.ho]

Hi, I am fairly new to macro coding. I have an issue with a text file in which the data improrted into excel do no give me any information whatsoever.

My file will always be in such a format

Trader Account,
,
Account,Exchange,Type,Date,Option,
Currency,P and L,Tot Posn,SOD Short,SOD Long,Pos Long,Pos Short,Working Buys,Working Sells,
P and L Price,SOD Price,
SOD NLV,Posn Margin Req,% BP Used,% BP Remaining,% Open Exposure,% P+L Burn Rate,
,
CSGL000003A,SGXQ,QNK,SEP11,,
JPY,-5000.00,+2,0,0,2,0,0,2,
10045.0000,10085.0000,
827428.32,0.00,-0.60,99.40,0.00,-0.60,
,
CSGL000003A,,QNK,,,
USD,-64.00,+2,0,0,2,0,0,2,
,,
10591.00,0.00,-0.60,99.40,0.00,-0.60,
,
CSGL000003A,SGXQ,QTW,JUL11,,
USD,0.00,0,0,0,0,0,0,0,
308.7000,308.2000,
10591.00,0.00,0.00,100.00,0.00,0.00,
,
CSGL000003A,,QTW,,,
USD,0.00,0,0,0,0,0,0,0,
,,
10591.00,0.00,0.00,100.00,0.00,0.00,

However when i import the data into excel, the header is imported into 3 rows which is not what I want. Also a number of data such as Tot Posn,SOD Short,SOD Long,Pos Long,Pos Short,Working Buys,Working Sells is not needed for my analysis.

What I want will be something like that

<table border="0" cellpadding="0" cellspacing="0" width="640"><col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="width:48pt" span="7" width="64"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:61pt" align="left" height="20" width="81">Account </td> <td style="width:48pt" align="left" width="64">Exchange</td> <td style="width:48pt" align="left" width="64">TYPE</td> <td style="width:48pt" align="left" width="64">Date</td> <td style="width:48pt" align="left" width="64">Option</td> <td style="width:48pt" align="left" width="64">Currency </td> <td style="width:48pt" align="left" width="64">P&L</td> <td style="width:48pt" align="left" width="64">SOD NLV</td> <td style="width:83pt" width="111">Posn margin Req</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="left" height="20">CSGL00003A</td> <td align="left">SGXQ</td> <td align="left">QNK</td> <td class="xl63" align="left">Sep-11</td> <td align="left">
</td> <td align="left">JPY</td> <td align="left">-5000</td> <td align="left">827428</td> <td align="left">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" align="left" height="20">CSGL00003A</td> <td align="left">QNK</td> <td align="left">
</td> <td align="left">
</td> <td align="left">
</td> <td align="left">USD</td> <td align="left">-64</td> <td align="left">10591</td> <td align="left">0</td> </tr> </tbody></table>..... cont.

 

[bertie]

Hi and welcome to the forum.

For ease of editing I have created a constant variable for your input file.

[COLOR=green]'EDIT the the location of the import file[/COLOR]
[COLOR=darkblue]Const[/COLOR] sFile = "C:\Temp\junyi.ho\tracker account.txt"

The main procedure is ImportTrackerAccount().
This procedure reads in the text file and builds up an output string which consists on one record in the text file.
This record is split into an array which is then processed to output the required fields to the spreadsheet.
For ease of editing I have created a separate procedure for the output.

To use.
Open a new Excel spreadsheet.
Press Alt+F11 to open the VBA editor.
Double click on the ThisWorkbook module in the Project Window on the left hand side.
Copy and paste the code below.
Edit where highlighted.
Press F5 to run.

[COLOR=green]'EDIT the the location of the import file[/COLOR]
[COLOR=darkblue]Const[/COLOR] sFile = "[COLOR=Red]C:\Temp\junyi.ho\tracker account.txt[/COLOR]"


[COLOR=darkblue]Public[/COLOR] [COLOR=darkblue]Sub[/COLOR] ImportTrackerAccount()
   [COLOR=darkblue]Dim[/COLOR] aRecord [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sRecord [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sTemp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] fNum [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] counter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
   
   [COLOR=green]'check the file exists[/COLOR]
   [COLOR=darkblue]If[/COLOR] Dir(sFile) = "" [COLOR=darkblue]Then[/COLOR]
      MsgBox "File does not exist!", vbCritical
      [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] errHandler
   
   fNum = FreeFile()
   [COLOR=darkblue]Open[/COLOR] sFile [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Input[/COLOR] [COLOR=darkblue]As[/COLOR] #fNum
   
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] [COLOR=darkblue]Not[/COLOR] EOF(fNum)              [COLOR=green]'loop through the input file[/COLOR]

      Line [COLOR=darkblue]Input[/COLOR] #fNum, sTemp       [COLOR=green]'read in a line, assign it to variable[/COLOR]
      
      [COLOR=green]'don't process the first row of the text file[/COLOR]
      [COLOR=darkblue]If[/COLOR] InStr(1, sTemp, "Trader", vbTextCompare) > 0 [COLOR=darkblue]Then[/COLOR]
         rw = rw + 1
         Sheets("Sheet1").Cells(rw, i + 1).Value = sTemp
      
      [COLOR=darkblue]ElseIf[/COLOR] counter <> 0 And counter Mod 4 = 0 [COLOR=darkblue]Then[/COLOR]
         
         [COLOR=green]'out put the record to the spreadsheet[/COLOR]
         aRecord = Split(sRecord, ",")   [COLOR=green]'split line into array by deliminator[/COLOR]
         rw = rw + 1
         OutputToSpreadsheet rw, aRecord
         counter = 0
         sRecord = ""
      [COLOR=darkblue]Else[/COLOR]
         [COLOR=green]'build up the record string, which consists of four rows of data[/COLOR]
         [COLOR=darkblue]If[/COLOR] Len(Trim(sTemp)) <> 1 [COLOR=darkblue]Then[/COLOR]
            sRecord = sRecord & sTemp
            counter = counter + 1
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Loop[/COLOR]
   
errHandler:
   [COLOR=green]'close open text files[/COLOR]
   [COLOR=darkblue]Close[/COLOR] #fNum
         
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]




[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] OutputToSpreadsheet([COLOR=darkblue]ByVal[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], _
                                [COLOR=darkblue]ByVal[/COLOR] aRecord [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR])
   
   [COLOR=green]'=========================[/COLOR]
   [COLOR=green]'Array fields[/COLOR]
   '[COLOR=SeaGreen]aRecord(0) = Account[/COLOR]
   [COLOR=green]'aRecord(1) = Exchange[/COLOR]
   [COLOR=green]'aRecord(2) = Type[/COLOR]
   [COLOR=green]'aRecord(3) = Date[/COLOR]
   [COLOR=green]'[/COLOR]
   '[COLOR=SeaGreen]etc - you can fill this list at your leisure if you need to[/COLOR]
   [COLOR=green]'=========================[/COLOR]
   [COLOR=darkblue]With[/COLOR] Sheets("Sheet1")
      .Range("A" & rw).Value = aRecord(0) [COLOR=green]'Account[/COLOR]
      .Range("B" & rw).Value = aRecord(1) '[COLOR=SeaGreen]Exchange[/COLOR]
      .Range("C" & rw).Value = aRecord(2) [COLOR=green]'Type[/COLOR]
      .Range("D" & rw).Value = aRecord(3) [COLOR=SeaGreen]'Date[/COLOR]
      .Range("E" & rw).Value = aRecord(4) [COLOR=green]'Option[/COLOR]
      .Range("F" & rw).Value = aRecord(5) [COLOR=SeaGreen]'Currency[/COLOR]
      .Range("G" & rw).Value = aRecord(6) [COLOR=green]'P & L[/COLOR]
      .Range("E" & rw).Value = aRecord(16) [COLOR=SeaGreen]'SOD NLV[/COLOR]
      .Range("F" & rw).Value = aRecord(17) [COLOR=green]'Pos Market Req[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[junyi.ho]

Hi Bertie,

Your reply is very much appreciated. The code works like magic. I have hard time sourcing for subsitute that works.

Thank a million for your guidence.

 

[junyi.ho]

I have a new problem. If there are a few thousand entries, it will take ages to load. Is there any way where by instead of generating all the data in the file, I can instead instruct the accounts which i would like to extract out.

For example, I would like to extract CSGR000005A, i can input this value somewhere and the macro will run and generate the data as follows.