Oh, I did not know, I though when it is divided by rows in Excel, it will be same in txt. But yeah, the source data should be divided into rows as it is in the sheet "original" in #3
Printable View
Oh, I did not know, I though when it is divided by rows in Excel, it will be same in txt. But yeah, the source data should be divided into rows as it is in the sheet "original" in #3
Kenneth,
It opens fine in Excel and UltraEdit. And In my NotePad, too. :dunno
SamT
I changed decimal symbol to a dot accordingly, but no change, the code runs without an error, but no output is shown
OK. I just ran sub #19 with path C:\CSVs\ with two folder in it. The results were
The second 0 is because there is only one formula in the code.
0 0 table_aapl.csv -9E+30 0 table_abbv.csv
When I said earler to change the msgbox trigger from 10 to 1, you need to cahnge it to 0 unless you run 2 files. Fn starts at zero. My bad.
OK. I swapped the files in their folders and edited the code to eliminate the extra zero and put the filename in column A.
The results
table_abbv.csv -9E+30 table_aapl.csv 0
If Maxxino can't temporarily change the decimal seperator, we will have to Replace the comma with a semicolon, then replace the dot with a comma. Then WE can't test ti wihtout changing our Regional Settings :)
Show us what you have in this line of code
From Code # 16
From Code #19Code:Const FolderPath As String = "The Path to your folder must go here\" 'include ending \
From my Workbook, where it ran fineCode:Const FolderPath As String = "X:\snb\maxxino\" 'include ending \
Code:Const FolderPath As String = "C:\CSVs\" 'include ending \
Maybe you used Notepad+ Sam?
It is obvious that since Excel opens it ok, and a short test that I did, that your CSV files M are vbLF delimited. As such, one line of code needs to be changed.
IF the decimal separator is an issue, you can change it from the hard coding in the code to use the regional setting by:Code:Sub M_snb3()
Dim sn() As String, c00() As String, j As Long, jj As Long
Dim sp() As String, y As Double
sn = Filter(Split(CreateObject("wscript.shell").exec _
("cmd /c Dir ""X:\snb\maxxino\table\*.csv"" /b /s").stdout.readall, vbCrLf), ".")
ReDim c00(1 To UBound(sn) + 1, 1 To 2) As String
With CreateObject("scripting.filesystemobject")
For j = 0 To UBound(sn)
y = 0
sp = Split(.OpenTextFile(sn(j)).readall, vbLf)
For jj = 0 To UBound(sp) - 1
sp(jj) = Log(Split(sp(jj), ",")(5)) / Log(10)
If jj > 0 Then y = y + 100 * (sp(jj) - sp(jj - 1)) ^ 2
Next jj
'c00(j + 1, 1) = sn(j) 'CSV Full Filename
c00(j + 1, 1) = .GetBasename(sn(j)) 'CSV Base Filename
c00(j + 1, 2) = y 'Result
Next j
End With
Range("A1").Resize(UBound(c00), 2).Value = c00
Columns("A:B").AutoFit
End Sub
Code:Application.DecimalSeparator
In the code from post #19, insert the two tests as shown below. The sub will stop as soon as you click on the second MsgBox. Note what the messages are. The first will give you the full path and name of the file that is opening.
Code:'''''''''''''For testing only
MsgBox "Opening file: " & FileNameArray(Fn)
'Exit Sub
'''''''''''''End test
''''Put all lines from one file in Arrays
FileLinesArray = Split(.opentextfile(FileNameArray(Fn)).readall, vbCrLf)
Formula1LinesArray = FileLinesArray
Formula2LinesArray = FileLinesArray
'Initializ
Formula1Result = 0
Formula2Result = 0
''''Calcuate first result for one file
For Fl = 0 To UBound(FileLinesArray) - 1
'''''''''''''For testing only
MsgBox "The first line in the file is " & Formula1LinesArray(Fl)
Exit Sub
'''''''''''''End test
'''' Calculate first formula
Kenneth,
MS Notepad v 5.1. I tried Notepad+ a few years back, but didn't keep it, because I had UltraEdit and was/am very familiar with it.
Did you notice? I ran your last version of my code just fine on his two zipped files.
Ureka! I unzipped them again, this time with 7zip, and reproduced the problem. The first time I used AlZip.
So. I dug thru my archives and installed WinZip 7. Same Issue: one long string of numbers.
Alzip is free.
:dunno :dunno :dunno
Here is the contents of one of those files
Quote:
20130102,855,35.49,35.49,35.49,35.49,217,1,0,0
20130102,905,34.57,34.6,34.57,34.6,300,1,0,0
20130102,915,34.7,34.7,34.7,34.7,27092,1,0,0
20130102,925,34.66,34.71,34.62,34.62,1800,1,0,0
20130102,930,34.92,34.92,34.4,34.62,192824,1,0,0
20130102,935,34.7563,34.86,34.625,34.81,132380,1,0,0
20130102,940,34.805,35.2,34.805,35.2,79402,1,0,0
20130102,945,35.2,35.2,34.8,34.95,316021,1,0,0
20130102,950,34.83,34.9463,34.6,34.65,34657,1,0,0
20130102,955,34.639,34.83,34.52,34.72,76530,1,0,0
20130102,1000,34.72,34.72,34.66,34.7,61116,1,0,0
20130102,1005,34.6793,34.6997,34.6,34.6365,159095,1,0,0
20130102,1010,34.6289,34.7,34.49,34.7,153262,1,0,0
20130102,1015,34.6999,34.6999,34.4,34.53,80633,1,0,0
20130102,1020,34.53,34.53,34.48,34.491,101987,1,0,0
20130102,1025,34.495,34.51,34.38,34.39,113451,1,0,0
20130102,1030,34.3862,34.46,34.18,34.29,149975,1,0,0
20130102,1035,34.3,34.36,34.12,34.3201,95972,1,0,0
20130102,1040,34.335,34.37,34.14,34.365,101404,1,0,0
20130102,1045,34.36,34.42,34.36,34.37,135165,1,0,0
20130102,1050,34.37,34.37,34.2,34.281,74406,1,0,0
20130102,1055,34.29,34.4,34.27,34.4,164469,1,0,0
20130102,1100,34.4,34.4,34.24,34.3,111185,1,0,0
20130102,1105,34.3,34.38,34.27,34.37,190205,1,0,0
20130102,1110,34.37,34.37,34.27,34.34,125935,1,0,0
20130102,1115,34.33,34.35,34.25,34.25,209730,1,0,0
20130102,1120,34.27,34.3,34.23,34.29,78973,1,0,0
20130102,1125,34.29,34.35,34.26,34.35,73156,1,0,0
20130102,1130,34.35,34.45,34.34,34.4,178697,1,0,0
20130102,1135,34.4,34.4,34.25,34.29,126977,1,0,0
20130102,1140,34.2999,34.38,34.28,34.335,96976,1,0,0
20130102,1145,34.315,34.34,34.12,34.27,83276,1,0,0
20130102,1150,34.264,34.27,34.185,34.23,158084,1,0,0
20130102,1155,34.24,34.24,34.12,34.1935,107202,1,0,0
20130102,1200,34.195,34.21,34.1,34.2,103130,1,0,0
20130102,1205,34.21,34.23,34.19,34.225,50471,1,0,0
20130102,1210,34.225,34.34,34.22,34.321,95644,1,0,0
20130102,1215,34.34,34.5,34.25,34.47,302964,1,0,0
20130102,1220,34.47,34.68,34.36,34.48,228406,1,0,0
20130102,1225,34.49,34.51,34.42,34.4932,277869,1,0,0
20130102,1230,34.5,34.7,34.48,34.69,125386,1,0,0
20130102,1235,34.69,34.7,34.46,34.54,104569,1,0,0
20130102,1240,34.53,34.54,34.46,34.46,28719,1,0,0
20130102,1245,34.47,34.47,34.43,34.445,65092,1,0,0
20130102,1250,34.44,34.51,34.44,34.505,72991,1,0,0
20130102,1255,34.51,34.7,34.47,34.57,218060,1,0,0
20130102,1300,34.59,34.65,34.55,34.65,39236,1,0,0
20130102,1305,34.65,34.69,34.53,34.63,320401,1,0,0
20130102,1310,34.63,34.69,34.55,34.65,58373,1,0,0
20130102,1315,34.6593,34.69,34.606,34.65,102362,1,0,0
20130102,1320,34.64,34.81,34.64,34.8,364901,1,0,0
20130102,1325,34.8,34.82,34.57,34.72,69342,1,0,0
20130102,1330,34.72,34.85,34.63,34.75,76775,1,0,0
20130102,1335,34.74,34.88,34.7,34.88,309787,1,0,0
20130102,1340,34.88,34.95,34.8,34.88,146592,1,0,0
20130102,1345,34.89,34.93,34.81,34.92,156436,1,0,0
20130102,1350,34.92,34.95,34.836,34.9,116088,1,0,0
20130102,1355,34.895,34.94,34.84,34.89,102850,1,0,0
20130102,1400,34.9,34.96,34.89,34.96,95476,1,0,0
20130102,1405,34.945,35.03,34.9,34.94,227484,1,0,0
20130102,1410,35,35.05,34.87,34.91,213695,1,0,0
20130102,1415,34.92,35.02,34.92,34.96,218564,1,0,0
20130102,1420,34.96,35.06,34.87,35.05,106760,1,0,0
20130102,1425,35.055,35.14,35.04,35.08,62760,1,0,0
20130102,1430,35.072,35.23,35.07,35.2,102741,1,0,0
20130102,1435,35.2,35.23,35.12,35.12,74425,1,0,0
20130102,1440,35.13,35.25,35.12,35.12,117779,1,0,0
20130102,1445,35.12,35.17,35,35.014,119569,1,0,0
20130102,1450,35,35.07,35,35.005,56973,1,0,0
20130102,1455,35.01,35.01,34.99,35,271361,1,0,0
20130102,1500,35,35,34.9495,34.9901,77380,1,0,0
20130102,1505,35,35.05,34.99,35.03,1.17473e+06,1,0,0
20130102,1510,35.03,35.25,35.03,35.05,103381,1,0,0
20130102,1515,35.055,35.07,35,35.05,163033,1,0,0
20130102,1520,35.05,35.13,34.96,34.99,181895,1,0,0
20130102,1525,34.99,35.03,34.83,34.88,58188,1,0,0
20130102,1530,34.88,34.99,34.88,34.985,62392,1,0,0
20130102,1535,34.99,35.07,34.98,35.05,375413,1,0,0
20130102,1540,35.05,35.11,35.04,35.11,188005,1,0,0
20130102,1545,35.1,35.19,35,35,537742,1,0,0
20130102,1550,35.01,35.15,35,35.05,338168,1,0,0
20130102,1555,35.05,35.4,35,35.3,454502,1,0,0
20130102,1600,35.3,35.3,35.12,35.12,961747,1,0,0
20130102,1605,35.14,35.14,35.12,35.12,60500,1,0,0
20130102,1610,35.12,35.12,35.12,35.12,2717,1,0,0
20130102,1620,35.15,35.15,35.15,35.15,900,1,0,0
20130102,1650,35.12,35.12,35.1,35.1,25047,1,0,0
20130102,1655,35.1,35.12,35.1,35.12,4400,1,0,0
20130102,1710,35.12,35.12,35.12,35.12,600,1,0,0
20130102,1720,35.07,35.07,35.07,35.07,300,1,0,0
20130102,1730,35.1,35.1,35.07,35.1,956,1,0,0
20130102,1740,35.06,35.06,35.06,35.06,125,1,0,0
20130102,1825,35.12,35.12,35.12,35.12,100,1,0,0
20130102,1955,35.04,35.04,35.04,35.04,100,1,0,0
See y'all tomorrow
I have not used Alzip. I was running WinZip but uncompressed the files with Win8 and viewed in NotePad v6.3. One could split and check ubound to see which is greater I guess and use that one. In the end though, it would be easier if M knew what his file format was and then use the code accordingly.
The translation is not uncommon. Back when I dealt with Unix files in the 80's and 90's, vbLF was used but vbCrLf was used in DOS. I ran some programs to convert back and forth.
Correct code based on your sample files:
Code:Sub M_snb()
sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir G:\OF\table_*.csv /b /s").stdout.readall, vbCrLf), ".")
With CreateObject("scripting.filesystemobject")
For j = 0 To UBound(sn)
sp = Split(.opentextfile(sn(j)).readall, vbLf)
For jj = 0 To UBound(sp) - 1
sp(jj) = Log(Split(sp(jj), ",")(5)) / Log(10)
If jj = 0 Then
y = sp(jj)
Else
y = y + 100 * (sp(jj) - sp(jj - 1)) ^ 2
End If
Next
c00 = c00 & vbLf & sn(j) & "|" & y
Next
End With
MsgBox c00
End Sub
@ Superman,
Yeppers.
Robin
Success: Decimal Separater set to dot.
Results:
table_aapl.csv -4.8E+48 table_abbv.csv -9E+30
Code:Option Explicit
Sub SNB__KenH_SamT()
Dim Filename As String
Dim NameLength As Long
Dim FileNameArray As Variant 'sn
Dim FileLinesArray As Variant 'sp
Dim Formula1LinesArray As Variant
Dim Formula1Result As Double 'y
'Repeat these two lies for each Formula. Edit the numbers to suit
Dim Formula2LinesArray As Variant
Dim Formula2Result As Double
Dim Fn As Long 'Fn = Index number for FileNameArray 'jj
Dim Fl As Long 'Fl = FileLinesArray Index number 'j
Const FolderPath As String = "C:\CSVs\" 'include ending \
'''' Put all the file names in the path in Array
FileNameArray = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _
FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".")
'''' Open one file at a time
With CreateObject("scripting.filesystemobject")
For Fn = 0 To UBound(FileNameArray)
''''Put all lines from one file in Arrays
FileLinesArray = Split(.opentextfile(FileNameArray(Fn)).readall, vbLf)
Formula1LinesArray = FileLinesArray
Formula2LinesArray = FileLinesArray
'Initializ
Formula1Result = 0
Formula2Result = 0
''''Calcuate first result for one file
For Fl = 0 To UBound(FileLinesArray) - 1
'''' Calculate first formula
'Replace file line with Log of 6th value. Split(BlahBlah)(5)
Formula1LinesArray(Fl) = Log(Split(Formula1LinesArray(Fl), ",")(5))
'After the first line
If Fl > 0 Then Formula1Result = Formula1Result + Formula1Result + _
(Formula1LinesArray(Fl) - Formula1LinesArray(Fl - 1) ^ 2) * 100
'''' Calculate second Formula here
'Replace file line with first part of formula. Think carefully
'Formula2LinesArray (Fl) = Your formula here
Next Fl
'''' Put results in sheet
'Get FileName
NameLength = Len(FileNameArray(Fn)) - InStrRev(FileNameArray(Fn), "\")
Filename = Right(FileNameArray(Fn), NameLength)
'Place result
With ActiveSheet.Rows(Fn + 1)
.Columns(2) = Formula1Result 'Column B
'.Columns(3) = Formula2Result
.Columns(1) = Filename
End With
'Initialize Arrays
FileLinesArray = ""
Formula1LinesArray = FileLinesArray
Formula2LinesArray = FileLinesArray
Next Fn 'Work on next File
End With
End Sub
If only a dot is a problem:
Code:Sub M_snb()
sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir G:\OF\table_*.csv /b /s").stdout.readall, vbCrLf), ".")
With CreateObject("scripting.filesystemobject")
For j = 0 To UBound(sn)
sp = Split(.opentextfile(sn(j)).readall, vbLf)
For jj = 0 To UBound(sp) - 1
sp(jj) = Log(Replace(Split(sp(jj), ",")(5), ".", ",")) / Log(10)
If jj = 0 Then
y = sp(jj)
Else
y = y + 100 * (sp(jj) - sp(jj - 1)) ^ 2
End If
Next
c00 = c00 & vbLf & sn(j) & "|" & y
Next
End With
MsgBox c00
End Sub
Hi guys,
SamT - I always put the file path in this way (for #19): Const FolderPath As String = "C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102" 'include ending \
When I ran this testing code you proposed, in the message box was: Compile error: Invalid Next control variable reference
When I use your code from #53 it shows the same result as before - no error, however no output
Kenneth - I used WinZip to decompress as well, then I open the csv files in Excel
snb - I ran code according to your #52 and #54 - message box is empty in both cases
Where is the trailing backslash?
Quote:
"C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102" 'include ending \
Ye sorry, the file path did not copy-pasted the whole, but I use ending "\", as Sam pointed out
Code:Option Explicit
Sub SNB__KenH_SamT()
Dim Filename As String
Dim NameLength As Long
Dim FileNameArray As Variant 'sn
Dim FileLinesArray As Variant 'sp
Dim Formula1LinesArray As Variant
Dim Formula1Result As Double 'y
'Repeat these two lies for each Formula. Edit the numbers to suit
Dim Formula2LinesArray As Variant
Dim Formula2Result As Double
Dim Fn As Long 'Fn = Index number for FileNameArray 'jj
Dim Fl As Long 'Fl = FileLinesArray Index number 'j
Const FolderPath As String = "C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\" 'include ending \
'''' Put all the file names in the path in Array
FileNameArray = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _
FolderPath & "*.csv /b /s").stdout.readall, vbCrLf), ".")
'''' Open one file at a time
With CreateObject("scripting.filesystemobject")
For Fn = 0 To UBound(FileNameArray)
MsgBox "Working on File " & FileNameArray(Fn)
''''Put all lines from one file in Arrays
FileLinesArray = Split(.opentextfile(FileNameArray(Fn)).readall, vbLf)
Formula1LinesArray = FileLinesArray
Formula2LinesArray = FileLinesArray
'Initializ
Formula1Result = 0
Formula2Result = 0
''''Calcuate first result for one file
For Fl = 0 To UBound(FileLinesArray) - 1
MsgBox " Line #" & Fl + 1 & " is" & vbCrLf & FileLinesArray(Fl)
'''' Calculate first formula
'Replace file line with Log of 6th value. Split(BlahBlah)(5)
Formula1LinesArray(Fl) = Log(Split(Formula1LinesArray(Fl), ",")(5))
'After the first line
If Fl > 0 Then Formula1Result = Formula1Result + Formula1Result + _
(Formula1LinesArray(Fl) - Formula1LinesArray(Fl - 1) ^ 2) * 100
Dim MsgAnswer
MsgAnswer = MsgBox("The Result is " & Formula1Result & vbCrLf & vbCrLf _
& "Press Cancel to stop run.", vbOkCancel)
If MsgAnswer = vbCancel Then Exit Sub
'''' Calculate second Formula here
'Replace file line with first part of formula. Think carefully
'Formula2LinesArray (Fl) = Your formula here
Next Fl
'''' Put results in sheet
'Get FileName
NameLength = Len(FileNameArray(Fn)) - InStrRev(FileNameArray(Fn), "\")
Filename = Right(FileNameArray(Fn), NameLength)
'Place result
With ActiveSheet.Rows(Fn + 1)
.Columns(2) = Formula1Result 'Column B
'.Columns(3) = Formula2Result
.Columns(1) = Filename
End With
'Initialize Arrays
FileLinesArray = ""
Formula1LinesArray = FileLinesArray
Formula2LinesArray = FileLinesArray
Next Fn 'Work on next File
End With
End Sub
Hi Sam,
I am sorry, but it is the same as before, no error, but no output, where exactly do you get the results as you described in #53? In the new excel? New sheet?
I describe step by step how I run the code:
1) open one excel from the folder C:\Users\dbrandejs\david\skola\IES\Diplomka\adjusted data\allstocks_20130102\
2) open VBA
3) insert Module into VBAProject
4) past the code #58
5) use F5
I don't know what could be done another way..