Note pad VBA

[smistretta]

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 ******************************************

[Bob Phillips]

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 = Replace(Replace(Right(vLineIn, Len(vLineIn) - 7), ",", ";"), ";", " ", , 1)

'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 ******************************************

[smistretta]

Hi XLD, many many thanks for your quick response. I have tried to run the script that you suggested but I am getting an error msg. It is showing:
Line:11
Char: 1
Error: File not found
Code: 800A0035
Source: Microsoft VBScript runtime error

Please be aware that I am a novice...so i may be missing something very simple.

So here is an example of my data:

<TICKER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>
USDCHF,20010102,230100,1.5989,1.5990,1.5988,1.5988,4
USDCHF,20010102,230200,1.5988,1.5988,1.5988,1.5988,4
USDCHF,20010102,230300,1.5990,1.5991,1.5990,1.5991,4
USDCHF,20010102,230400,1.5991,1.5991,1.5991,1.5991,4
USDCHF,20010102,230500,1.5991,1.5991,1.5990,1.5990,4
USDCHF,20010102,230600,1.5989,1.5989,1.5989,1.5989,4
USDCHF,20010102,230700,1.5989,1.5989,1.5989,1.5989,4
USDCHF,20010102,230800,1.5989,1.5989,1.5989,1.5989,4
USDCHF,20010102,230900,1.5988,1.5991,1.5988,1.5991,4
USDCHF,20010102,231000,1.5991,1.5991,1.5989,1.5989,4
USDCHF,20010102,231100,1.5990,1.5992,1.5990,1.5990,4
USDCHF,20010102,231200,1.5989,1.5990,1.5989,1.5990,4
USDCHF,20010102,231400,1.5990,1.5990,1.5990,1.5990,4
USDCHF,20010102,231500,1.5993,1.5993,1.5993,1.5993,4
USDCHF,20010102,231600,1.5995,1.5997,1.5994,1.5994,4
USDCHF,20010102,232100,1.5995,1.5995,1.5994,1.5994,4
USDCHF,20010102,232200,1.5994,1.5995,1.5994,1.5995,4
USDCHF,20010102,232400,1.5996,1.5996,1.5996,1.5996,4
USDCHF,20010102,232500,1.5996,1.5996,1.5996,1.5996,4
USDCHF,20010102,232600,1.5996,1.5996,1.5996,1.5996,4




The basic thing I am trying to do is to delete the top line completely:
<TICKER>,<DTYYYYMMDD>,<TIME>,<OPEN>,<HIGH>,<LOW>,<CLOSE>,<VOL>


then format the rows:
USDCHF,20010102,230100,1.5989,1.5990,1.5988,1.5988,4


so that it looks like this:
20010102 230100;1.5989;1.5990;1.5988;1.5988;4


Please can you help me try to solve this issue...or at least tell me what I am doing wrong...

Once again many many thanks
Steve

[Bob Phillips]

I had to change that to get it to run in my test, I thought it might be a VBScript thing, but I didn't test it, just changed it back after testing.

Try changing



vPathToFileOt = "C:\$USDCHF2.txt"


to



vPathToFileOt = "C:\USDCHF2 v2.txt"

[smistretta]

Hi XLD,

Thanks for your help again. What you are suggesting is changing the name of the file? But the file i have is called USDCHF and when I rename the file as you suggest above it simply creates a new file called USDCHF v2 but with nothing in it.

When I open the VBS file now it doesn't give an error but it says tranferred 0 lines. stopping now, so something suggests to me it is working but it isn't changing the format so that it reads with semicolons instead of commas as below...

20010102 230100;1.5989;1.5990;1.5988;1.5988;4

Sorry to be a pain xld...thanks again
Steve

[Bob Phillips]

It is the output Steve, not the input file, so it should make no difference.

Maybe you could post your input file?

[smistretta]

The core data that i have, is held in notepad as it is >186mb big, approx 3.5mln rows of data. I have copy and pasted a shortened version into excel for you if that helps. Alternatively if you have an email address I can send it to you in notepad, the shorter version...

Many thanks
Steve

[GTO]

Greetings Steve,

You can post a textfile, you just need to zip it.

Mark

[smistretta]

OK here is the zipped file

Many thanks

Steve

[Bob Phillips]

Try this



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
if flg then
vLineCnt = vLineCnt + 1
'if vLineCnt > 10 Then Exit Do
vLineOt = ""
vLineOt = Right(vLineIn, Len(vLineIn) - 7)
vLineOt = Replace(Replace(vLineOt, ",", ";"), ";", " ", 1, 1)

'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
Else
flg = True
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 ******************************************

[smistretta]

XLD...you sir, are a true scholar and genius. You have managed to help me out there in a tremendous way...I am very greatful and humbled by your expertise...many many thanks

A very happy Steve..