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
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.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Show us what you have in this line of code
From Code # 16
From Code #19Const FolderPath As String = "The Path to your folder must go here\" 'include ending \
From my Workbook, where it ran fineConst FolderPath As String = "X:\snb\maxxino\" 'include ending \
Const FolderPath As String = "C:\CSVs\" 'include ending \
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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: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
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.
'''''''''''''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
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Here is the contents of one of those files
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
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
See y'all tomorrow
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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:
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
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
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
If only a dot is a problem:
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?
"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
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
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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..
Posting Permissions
Reply With Quote
