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
|