smistretta
03-07-2011, 03:34 PM
I am in the process of making a study and I am really hoping that somone can possibly help me. I am a novice when it comes to VBA but i am interested in formatting some data and I am giving it a go. My data looks like this (about 3million rows of data):
USDCHF,20010102,230100,1.5989,1.5990,1.5988,1.5988,4
I am looking at it in Notepad and I am interested to get the data to look like this:
20010102 230100;1.5989;1.5990;1.5988;1.5988;4
So far I have managed to write the below script but it doesn't seem to work. Is there anyone out there that can correct or help mw with my script please, I would be very grateful. Many thanks in advance
vPathToFileIn = "C:\USDCHF2.txt"
vPathToFileOt = "C:\$USDCHF2.txt"
'rename the output file to the standard Ninja Trader file name, then Import
Dim oFSO, oFileIn, oFileOt, vLineIn, vLineCnt, vLineOt
'get link to file system support
Set oFSO = CreateObject("Scripting.FileSystemObject")
'open both files (in and out)
Err = 0
Set oFileIn = oFSO.OpenTextFile(vPathToFileIn, 1) '1=read
Set oFileOt = oFSO.CreateTextFile(vPathToFileOt, True) 'true=overwrite
If Err <> 0 Then
Wscript.Echo "Error on file open: " & Err.Number & ", " & Err.Description
Wscript.Echo "stopping"
Wscript.Quit
End If
'
'do the reformat
'input format
'USDCHF,20010102,230100,0.9507,0.9507,0.9507,0.9507,4'
'output format
'20090819 230600;1.4225;1.4225;1.4223;1.4224;171100000
vLineCnt = 0
Do Until oFileIn.AtEndOfStream
vLineIn = ""
vLineIn = oFileIn.ReadLine
vLineCnt = vLineCnt + 1
'if vLineCnt > 10 Then Exit Do
vLineOt = ""
vLineOt = Mid(vLineIn,8,8) & " " 'date
vLineOt = vLineOt (vLineIn,17,22) & "." 'time
vLineOt = vLineOt & Mid(vLineIn,18,99) 'O,H,L,C,V
'Wscript.Echo "about to write: " & vLineOt
Err = 0
oFileOt.WriteLine(vLineOt)
If Err <> 0 Then
Wscript.Echo "Error on file write: " & Err.Number & ", " & Err.Description
Wscript.Echo "stopping"
Wscript.Quit
End If
Loop
'
'close the files
oFileIn.Close
oFileOt.Close
'
'tell user count and quit
Wscript.Echo "transferred " & vLineCnt & " lines. stopping now."
Wscript.Quit
'
' **************************** END OF SCRIPT ******************************************
USDCHF,20010102,230100,1.5989,1.5990,1.5988,1.5988,4
I am looking at it in Notepad and I am interested to get the data to look like this:
20010102 230100;1.5989;1.5990;1.5988;1.5988;4
So far I have managed to write the below script but it doesn't seem to work. Is there anyone out there that can correct or help mw with my script please, I would be very grateful. Many thanks in advance
vPathToFileIn = "C:\USDCHF2.txt"
vPathToFileOt = "C:\$USDCHF2.txt"
'rename the output file to the standard Ninja Trader file name, then Import
Dim oFSO, oFileIn, oFileOt, vLineIn, vLineCnt, vLineOt
'get link to file system support
Set oFSO = CreateObject("Scripting.FileSystemObject")
'open both files (in and out)
Err = 0
Set oFileIn = oFSO.OpenTextFile(vPathToFileIn, 1) '1=read
Set oFileOt = oFSO.CreateTextFile(vPathToFileOt, True) 'true=overwrite
If Err <> 0 Then
Wscript.Echo "Error on file open: " & Err.Number & ", " & Err.Description
Wscript.Echo "stopping"
Wscript.Quit
End If
'
'do the reformat
'input format
'USDCHF,20010102,230100,0.9507,0.9507,0.9507,0.9507,4'
'output format
'20090819 230600;1.4225;1.4225;1.4223;1.4224;171100000
vLineCnt = 0
Do Until oFileIn.AtEndOfStream
vLineIn = ""
vLineIn = oFileIn.ReadLine
vLineCnt = vLineCnt + 1
'if vLineCnt > 10 Then Exit Do
vLineOt = ""
vLineOt = Mid(vLineIn,8,8) & " " 'date
vLineOt = vLineOt (vLineIn,17,22) & "." 'time
vLineOt = vLineOt & Mid(vLineIn,18,99) 'O,H,L,C,V
'Wscript.Echo "about to write: " & vLineOt
Err = 0
oFileOt.WriteLine(vLineOt)
If Err <> 0 Then
Wscript.Echo "Error on file write: " & Err.Number & ", " & Err.Description
Wscript.Echo "stopping"
Wscript.Quit
End If
Loop
'
'close the files
oFileIn.Close
oFileOt.Close
'
'tell user count and quit
Wscript.Echo "transferred " & vLineCnt & " lines. stopping now."
Wscript.Quit
'
' **************************** END OF SCRIPT ******************************************