Do While Not EOF(1) 'CSV files

[drom]

Hi and Thanks in advance

I have whithin a Folder lot of .CSV files. "I get this files from a Traders Application."

I have to work with this files and get different numbers, names, etc.

I know for sure (If I open those files I will get files with over 150 rows "always")

but if I use the following Code:

Dim T As String:  T = "C:\Germany\123 122.csv" 'For this eg
Dim F As Integer: F = FreeFile
Dim Textline
Dim X As Long


Open T For Input As #1       ' Open file.
  
  Do While Not EOF(1)         ' Loop until end of file.
    X = X + 1
    Line Input #1, Textline    ' Read line into variable.
    '..................
     Debug.Print Textline       ' Print to the Immediate window.
  Loop

Debug.Print X

X someTimes is equal to 1, but If I open the File it contains >150Rows

What's Going ON???

What am I missing??

Are those files Corrupted?

This is happening with 10% of the Files

 

[wigi]

Are these files "equal" in terms of separator for the lines? What character (or code) separates two lines in the offending files? Is it the same as in the good files?

What do you get in the Immediate Window when outputting Textline: 1 big string?

 

[drom]

When Debug.Print X, Textline

I get One big string with over 150 lines so:

1 6/22/2011 5:31,CAD2,SELL,1.4352,N,,,,,,,,8,26,10,8,0,0,0,0,0,0,0,0
6/22/2011 6:20,CTD2,SELL,1.4324,N,,,,,,,,-8,-8,-8,-8,0,0,0,0,0,0,0,0
6/22/2011 6:44,CAD,BUY,1.4338,N,,,,,,,,-8,-8,-8,-8,0,0,0,0,0,0,0,0

and so on

When the file goes OK:

1 6/22/2011 5:31,CAD2,SELL,1.4352,N,,,,,,,,8,26,10,8,0,0,0,0,0,0,0,0
2 6/22/2011 6:20,CTD2,SELL,1.4324,N,,,,,,,,-8,-8,-8,-8,0,0,0,0,0,0,0,0
3 ... and so ON




Are these files "equal" in terms of separator for the lines? I think so "YES"

What character (or code) separates two lines in the offending files? Is it the same as in the good files? YES

 

[wigi]

Are these files "equal" in terms of separator for the lines? I think so "YES"

What character (or code) separates two lines in the offending files? Is it the same as in the good files? YES

You're wrong, the answer should be 2 times NO, otherwise you would not get a very big string in the Immediate Window...
Inspect thoroughly the separator for subsequent lines in a "bad" and a "good" file. Something's got to be different there...

 

[drom]

on the Good Ones:

"3/21/2011 12:52,RT+2S2,SELL,1.4187,N,,,,,,,,8,1,10,0,0,0,0,0,0,0,0,0"
"3/21/2011 15:02,CTD2,BUY,1.4207,NT,,,,,,,,-8,-8,-8,0,0,0,0,0,0,0,0,0"

on the bad Ones:

"6/10/2011 3:33,CAD2,SELL,1.4412,N,,,,,,,,8,1,1,8,0,0,0,0,0,0,0,0"
"6/10/2011 4:01,RT+2S,BUY,1.4416,N,,,,,,,,8,8,10,8,0,0,0,0,0,0,0,0"

 

[wigi]

You will probably need a text editor like Notepad++ to find the differences. Also, delete the text characters and numbers as much as possible, so as to end with only the separator of the lines (which might not be visible), and then try to paste it in Excel. Using the CODE function try to get the ASCII code of the line separators - both in bad and good files. Compare.

Or paste the fully contents of a good file over on top of the entries in a bad file. You will see that the first part of the file will be done correctly, contrary to the lower part of the file.

 

[Ruddles]

I'm not 100% keen on the code you're using: you don't close the file after you've finished with it and you re-use the same file handle without testing its availability - something which the original author of the routine appears to have allowed for (blue bits).

Try these changes (red bits):-

Dim T As String:  T = "C:\Germany\123 122.csv" 'For this eg
[COLOR=blue][B]Dim F As Integer: F = FreeFile[/B][/COLOR]
Dim Textline
Dim X As Long
 
[COLOR=red][B]Close[/B][/COLOR]
 
Open T For Input As #[COLOR=red][B]F[/B][/COLOR]       ' Open file.
 
  Do While Not EOF([COLOR=red][B]F[/B][/COLOR])         ' Loop until end of file.
    X = X + 1
    Line Input #[COLOR=red][B]F[/B][/COLOR], Textline    ' Read line into variable.
    '..................
     Debug.Print Textline       ' Print to the Immediate window.
  Loop
 
[COLOR=red][B]Close #F[/B][/COLOR]
 
Debug.Print X