ExcelBanter - Line Input Not behaving...!

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Line Input Not behaving...! (https://www.excelbanter.com/excel-programming/432475-line-input-not-behaving.html)

Line Input Not behaving...!

*** Trying to read an External CSV file ***
however Have found the EOL marker in the file is CHR(10) and not
Chr(10)+Chr(13).
The CSV file is from an external Source and i am unable to Change the
Format (EOL character etc.).

Sub Import ( )
CSVLine = 0
CsvFile = Application.GetOpenFilename("Comma Sep Values (*.csv),
*.csv")
Open CsvFile For Input As #1

While Not EOF(1)
Line Input #1, aRecord
' Other Parsing Code Is Here..
' ...
' ...
CSVLine = CSVLine + 1
Wend

Close #1
msgbox Str(CSVLine)+": Lines Read" '*** CSVLine Only ever reaches 1
***

End Sub

Is there another Setting to Change the behaviour of Line Input #1
.... ?
the Lines are not a set length so am unable to read a set number of
Characters etc.

Any Feedback would be Appreciated.
Andrew

Line Input Not behaving...!

Here is a program I wrote probably 20 years ago in c language and converted
it to VBA a few years ago. Your file was probably generate in a Unix/Linux
operating system. Yo need to read the file as a single character at a time
instead of a line at a time.

Sub FixEOL()

Const ForReading = 1, ForWriting = -2, _
ForAppending = 3

CR = vbCR
LF = vbLF

ReadFile = Application _
.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Read File")
If ReadFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If

WriteFile = Application _
.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Write File")
If WriteFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If

Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(ReadFile, _
ForReading, TristateFalse)
Set fout = fs.CreateTextFile _
(Filename:=WriteFile, overwrite:=True)

FoundCR = False
Do While fin.AtEndOfStream < True
ReadData = fin.read(1)
Select Case ReadData

Case CR:
If FoundCR = True Then
'two CR in a row write LF inbeteen the two CR
fout.write LF
fout.write CR
Else
FoundCR = True
fout.write CR
End If
Case LF:
If FoundCR = True Then
''Normal sequence CR foloowed by LF
fout.write LF
FoundCR = False
Else
'Bad Sequence LF without CR, Write CR
fout.write CR
fout.write LF
End If
Case Else
If FoundCR = True Then
'Bad Sequence CR without LF, wite LF
fout.write LF
fout.write ReadData
FoundCR = False
Else
'Normal dequence of two character in middle of line
fout.write ReadData
End If

End Select
Loop
fin.Close
fout.Close
End Sub

"Andrew" wrote:

*** Trying to read an External CSV file ***
however Have found the EOL marker in the file is CHR(10) and not
Chr(10)+Chr(13).
The CSV file is from an external Source and i am unable to Change the
Format (EOL character etc.).

Sub Import ( )
CSVLine = 0
CsvFile = Application.GetOpenFilename("Comma Sep Values (*.csv),
*.csv")
Open CsvFile For Input As #1

While Not EOF(1)
Line Input #1, aRecord
' Other Parsing Code Is Here..
' ...
' ...
CSVLine = CSVLine + 1
Wend

Close #1
msgbox Str(CSVLine)+": Lines Read" '*** CSVLine Only ever reaches 1
***

End Sub

Is there another Setting to Change the behaviour of Line Input #1
.... ?
the Lines are not a set length so am unable to read a set number of
Characters etc.

Any Feedback would be Appreciated.
Andrew

Line Input Not behaving...!

This may be something similar to Joel's code. But I'm not sure. this
code converts only Chr(10) into Chr(13) + Chr(10), but doesn't converts
the sequence like Chr(13) or Chr(13) + Chr(10). First run the macro
below, this will change your file's EOL with Chr(10) into
Chr(13)+Chr(10) and make a new file with the name adding Tmp_ to your
original file name. for example, if your file name is "test.csv", then
the new file name is "Tmp_test.csv".
after this, apply your macro to this new file.

Sub Convert_file()
Dim MyChar, Csvfile, tmp
Dim Tmpfile As String
Dim ar
Dim n As Long

Csvfile = Application.GetOpenFilename _
("Comma Sep Values (*.txt;*.csv;*.prn),*.txt;*.csv;*.prn")
If VarType(Csvfile) = vbBoolean Then Exit Sub
ar = Split(Csvfile, "\")
ar(UBound(ar)) = "Tmp_" & ar(UBound(ar))
Tmpfile = Join(ar, "\")
Open Csvfile For Input As #1
Open Tmpfile For Output As #2
Do While Not EOF(1)
MyChar = Input(1, #1)
If MyChar = Chr(13) Then
tmp = Chr(13)
ElseIf tmp < "" Then
MyChar = tmp & MyChar
tmp = ""
Print #2, MyChar;
ElseIf MyChar = Chr(10) Then
Print #2, Chr(13) & MyChar;
Else
Print #2, MyChar;
End If
Application.StatusBar = n
On Error GoTo ex:
n = n + 1
Loop
Close #1
Close #2
Exit Sub
ex:
n = 0
Resume Next
End Sub

Keiji

Andrew wrote:
*** Trying to read an External CSV file ***
however Have found the EOL marker in the file is CHR(10) and not
Chr(10)+Chr(13).
The CSV file is from an external Source and i am unable to Change the
Format (EOL character etc.).

Sub Import ( )
CSVLine = 0
CsvFile = Application.GetOpenFilename("Comma Sep Values (*.csv),
*.csv")
Open CsvFile For Input As #1

While Not EOF(1)
Line Input #1, aRecord
' Other Parsing Code Is Here..
' ...
' ...
CSVLine = CSVLine + 1
Wend

Close #1
msgbox Str(CSVLine)+": Lines Read" '*** CSVLine Only ever reaches 1
***

End Sub

Is there another Setting to Change the behaviour of Line Input #1
... ?
the Lines are not a set length so am unable to read a set number of
Characters etc.

Any Feedback would be Appreciated.
Andrew

Line Input Not behaving...!

Line Input Not behaving...!