CSV carriage return problem

[mharry]

Hi guys!

I have a VB6 program that uses FileSystemObject to read a csv file,Basically I want to add commas into a specified locations of the csv. The program works fine on a generic csv file however the files I need to use were created on an AS/400 and have garbage at the end of the file. UltraEdit says it's Hex 1A (&H1A) Dec(26) which I believe is the carriage returns. The program even works on these files so long as I first manually remove the "garbage" or add a line to the begging of the file, remove it and save..strange. With this type of file ReadLine is reading the entire file as one line and not recognizing the CR.
Any ideas would be greatly appreciated....


Code Snippet:.....

Private Sub cmdRun_Click()
Me.MousePointer = vbArrowHourglass

Dim fso As New FileSystemObject, ts As TextStream 'to open file for reading
Dim Source As String
Dim Str As String
Dim LastLine As String
Dim PartStr As String
Dim NewFile As Integer 'REFRENCE new file #
Dim ChangeFile As Integer
Dim NewCSV As String
Dim fs As New FileSystemObject 'to open of create file to write to
Dim pos
Dim endoffile
Dim A
Dim CharCount As Long, Expression As String, FindString As String
Dim badloop As Long
Dim iPos As Long
Dim Countrows
Str = "A"

If (cmbSourceFile <> "") Then
txtNewFileName = "New" + cmbSourceFile
End If

endoffile = Len(Str)
NewFile = FreeFile
ChangeFile = FreeFile
NewCSV = txtNewFileName.Text

Set ts = fso.OpenTextFile(txtDir + cmbSourceFile, ForReading)

Do While (ts.AtEndOfLine = False)
Str = ts.ReadLine

Expression = Str
FindString = ","
CharCount = 2
iPos = InStr(Expression, FindString)

Do Until (CharCount = txtFieldNo.Text)
If iPos > 0 Then
CharCount = CharCount + 1
iPos = iPos + Len(FindString)
iPos = InStr(iPos, Expression, FindString)
Loop

endoffile = Len(Str) - iPos

If fs.FileExists(txtSourcePath + txtNewFileName) = False Then

Set fs = CreateObject("Scripting.FileSystemObject")
Set A = fs.CreateTextFile(txtSourcePath + txtNewFileName, True)
A.Close
Open txtSourcePath + txtNewFileName For Append As #NewFile
Print #NewFile, Mid(Str, 1, iPos) + Mid(Str, iPos, endoffile)
Close #NewFile
Else:
Open txtSourcePath + txtNewFileName For Append As #NewFile
Print #NewFile, Mid(Str, 1, iPos) + Mid(Str, iPos, endoffile)
Close #NewFile
End If

Loop
ts.Close
Set fso = Nothing
Me.MousePointer = vbDefault
End Sub


CSV File example...

A,42ad96,428xxx428-02.NMMC01.002,4281addf28-02,NMMrrC01,NMMrrC01,12/31/9999,10/01/2006,,FAML,NMMrrrC,,,,,,,,MED DNT VIS,,20060818141703
A,42ad97,42cccc428-03.NMMC01.002,428adfff28-03,NMMrrC01,NMMrrC01,12/31/9999,10/01/2006,,FAML,NMMrrrC,,,,,,,,MED DNT VIS,,20060818141744
A,4ff398,428aaa428-04.NMMC01.001,428adfff28-04,NMMrrC01,NMMrrC01,12/31/9999,10/01/2006,,FAML,NMMrrrC,,,,,,,,MED DNT VIS,,20060818142035
A,424dd9,4281ccc28-05.NMMC01.001,42addfffv8-05,NMMrrC01,NMMrrC01,12/31/9999,10/01/2006,,FAML,NMMrrrC,,,,,,,,MED DNT VIS,,20060818142049
             

[Killer42]

What you are seeing there is not exactly "garbage". Character 26 is Ctrl-Z, which means end-of-file (EOF). Some systems use it, some don't. I don't know why there would be a bunch of them, though - normally there's just one, found (obviously enough) at the end of the file.

Also, there are a number of different EOL (end of line) characters or combinations used by various system. DOS (sorry, Windows!) based systems use CR+LF (carriage return & linefeed), or CHR(13) & Chr(10). Some use just CR, some use just LF, some even use LF+CR.

You could just use the ReadAll method then scan through the string and deal with the data format it in your code. (Oops! My goof - the ReadLine method is already giving you the whole thing. So, you could just go with that.)

Why not read the whole file into a string, and create a function you can call to return each line in turn from the string. That might simplify your code, anyway.

[Killer42]

... The program even works on these files so long as I first manually remove the "garbage" or add a line to the begging of the file, remove it and save..strange.

Not so strange if you think about it. Notepad, or whatever you are using to edit the file, must be able to interpret the different record delimiter when it reads the file in. When you save, it writes out the lines in the same format as usual, which is with CRLF.

At a guess, you would probably get the same effect by simply reading and saving the file, without even making any change. I suppose that depends on what you are using to edit - it might not allow you to save if you haven't changed anything. (Hm... actually, I'll bet it's Excel, right?)

[mharry]

Not so strange if you think about it. Notepad, or whatever you are using to edit the file, must be able to interpret the different record delimiter when it reads the file in. When you save, it writes out the lines in the same format as usual, which is with CRLF.

At a guess, you would probably get the same effect by simply reading and saving the file, without even making any change. I suppose that depends on what you are using to edit - it might not allow you to save if you haven't changed anything. (Hm... actually, I'll bet it's Excel, right?)

Hi Killer thanks for the speedy response.
No not Excel, never touch the stuff :o)… These files I am attempting to manipulate are input files for a SQL DB, which sometimes needs to be modified to meet new table structures. One problem is these files are often 500,000 lines or more and up to 640MB in size so I really need to read a line at a time, otherwise I imagine it would crash. The file example I posted was only the end of a file with several thousands records and I think that’s why it contained more squares than you would expect. As I said testing with generic CSV’s
(abc,123
def,456)
works fine and if I open the file in any text editor (or notepad and WordPad at least) add a line to the beginning then remove it save the file, my program then reads line by line and splits “each” line appropriately..

Another thing I thought was read the file to a var. then adding a line then overwrite the contents “without” the blank line but again that is just too cumbersome when dealing with so many records.

[Killer42]

Hi Killer thanks for the speedy response.
No not Excel, never touch the stuff :o)… These files I am attempting to manipulate are input files for a SQL DB, which sometimes needs to be modified to meet new table structures. One problem is these files are often 500,000 lines or more and up to 640MB in size so I really need to read a line at a time, otherwise I imagine it would crash.

You might be surprised what a typical PC can handle, these days. :)

...
Another thing I thought was read the file to a var. then adding a line then overwrite the contents “without” the blank line but again that is just too cumbersome when dealing with so many records.

It might not work, too. If you read the file into a string and write it out again, you'll be copying out the same delimiters and everything.

Have you tried using VB's built-in file processing? In other words, you might have a play and see whether you can get a line at a time like this...

Dim Text as String
Open "YourFile.csv" for input access read shared as #1
line input #1,Text
Close #1
 

If this works, you're on your way. If not, I guess you're no worse off than before.

If worse comes to worst, you could always read the file a byte at a time and scan for end-of-line. I wrote a program a few years back which used this method to convert files we were receiving from a Unix system which used different delimiters.

Do you know exactly what delimiters are in the files now? And is it definitely going to be consistent, or will your code need to adapt to different formats on the fly?

Oh! Also, what version of VB are you using? From the code, it looked like VB6, or maybe 5.

[mharry]

I’m using VB6.
The files are created for IBM 400. I opened a file with ultra edit (ctrl H) to see the Hex/Dec.
Windows sees it as VB(&H1A) dec (26)

Attached link is what it looks like in Ultra…. http://picspirate.com/links/hexss
I will tinker with your suggestions ty..

[Killer42]

I’m using VB6.
The files are created for IBM 400. I opened a file with ultra edit (ctrl H) to see the Hex/Dec.
Windows sees it as VB(&H1A) dec (26)

Just a minute - that's the stuff at the end of the file, isn't it? I want to know what delimiter is between the records (lines).

Attached link is what it looks like in Ultra…. http://picspirate.com/links/hexss I will tinker with your suggestions ty..

Thanks, I'll take a look.
...
No I won't, that page refuses to load.
...
Hang on! I was able to get the page by adding "www" onto the front - weird. Ok, I see the 1A's on the end, but no record delimiters. Um... from the look of that single record though, it seems as though you have a carriage return (CR) character as the record delimiter.

I've just run a quick test, and Line Input will correctly break at a CR. So that should solve (or at least bypass) your problem.

Um... except for the Ctrl-Z's at the end. You may be able to just skip over them somehow.

[mharry]

Hey man It works! I stepped away from trying to use fso and readline method as you suggested and just used the old fashioned OPEN command and reformatted so I’m well on my way to completing this project. Thanks for all your help! I still need to clean it up some but please pass along this code sample for anyone else wanting to add fields to a csv.
Again Thanks

[code]
Private Sub Command1_Click()
Me.MousePointer = vbArrowHourglass
Dim Str As String
Dim NewFile As Integer 'REFRENCE new file #
Dim endoffile
Dim CharCount As Long, Expression As String, FindString As String
Dim iPos As Long
Dim fs As New FileSystemObject, ts As TextStream 'to open of create file to write to

On Error GoTo ErrHandler
If fs.FileExists("C:\newcsv.csv") = False Then
Set fs = CreateObject("Scripting.FileSystemObject")
Set ts = fs.CreateTextFile("C:\newcsv.csv", True)
ts.Close
NewFile = FreeFile()
Open "C:\newcsv.csv" For Append As #NewFile
Else:
NewFile = FreeFile()
Open "C:\newcsv.csv" For Append As #NewFile
End If

SourceF = FreeFile()
Open "C:\MEMBCOVR.CSV" For Input Access Read Shared As #SourceF
Do While Not EOF(SourceF)
Line Input #SourceF, Str

endoffile = Len(Str)
Expression = Str
FindString = ","

CharCount = 2
iPos = InStr(Expression, FindString)
Do Until (CharCount = 5) 'And Not EOF(F)
If iPos > 0 Then
CharCount = CharCount + 1
iPos = iPos + Len(FindString)
iPos = InStr(iPos, Expression, FindString)
End If
Loop

endoffile = Len(Str) - iPos
Print #NewFile, Mid(Str, 1, iPos) + Mid(Str, iPos, endoffile)
Loop

Me.MousePointer = vbDefault
CloseFiles:
Close #SourceF 'source file
Close #NewFile 'new file being written to
Exit Sub
ErrHandler:
MsgBox "Error # " & Err & ": " & Error(Err)
Me.MousePointer = vbDefault
Resume CloseFiles
End Sub

[code]

[Killer42]

Glad to help. I'll just re-post your code with the tags around it, for readability. (You have to use a slash (/) in the closing tag, like in HTML).

...pass along this code sample for anyone else wanting to add fields to a csv.

Private Sub Command1_Click()
Me.MousePointer = vbArrowHourglass
Dim Str As String
Dim NewFile As Integer ' REFRENCE new file #
Dim endoffile
Dim CharCount As Long, Expression As String, FindString As String
Dim iPos As Long
Dim fs As New FileSystemObject
Dim ts As TextStream ' To open or create file to write to
 
On Error GoTo ErrHandler
If fs.FileExists("C:\newcsv.csv") = False Then
  Set fs = CreateObject("Scripting.FileSystemObject")
  Set ts = fs.CreateTextFile("C:\newcsv.csv", True)
  ts.Close
  NewFile = FreeFile()
  Open "C:\newcsv.csv" For Append As #NewFile
Else
  NewFile = FreeFile()
  Open "C:\newcsv.csv" For Append As #NewFile
End If
 
SourceF = FreeFile()
Open "C:\MEMBCOVR.CSV" For Input Access Read Shared As #SourceF
Do While Not EOF(SourceF)
  Line Input #SourceF, Str
 
  endoffile = Len(Str)
  Expression = Str
  FindString = ","
 
  CharCount = 2
  iPos = InStr(Expression, FindString)
  Do Until (CharCount = 5)
    If iPos > 0 Then
      CharCount = CharCount + 1
      iPos = iPos + Len(FindString)
      iPos = InStr(iPos, Expression, FindString)
    End If
  Loop
 
  endoffile = Len(Str) - iPos
  Print #NewFile, Mid(Str, 1, iPos) + Mid(Str, iPos, endoffile)
Loop
 
Me.MousePointer = vbDefault
CloseFiles:
      Close #SourceF      ' Source file
      Close #NewFile      ' New file being written to
      Exit Sub
 
ErrHandler:
      MsgBox "Error # " & Err & ": " & Error(Err)
      Me.MousePointer = vbDefault
      Resume CloseFiles
End Sub