Line Input Not behaving...!

[Andrew]

*** 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

 

[Joel]

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

 

[keiji kounoike]

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]

Thanx Guys,

Will use Similar..!

Will use somthing like:

Do While Not EOF(1)
theChr = Input(1,#1)..
then Check for EOL..( Chr(10) )
and "," (CSV const)
<< Not Coding.. Havnt had the Time to Do it yet.. But you get the
Jist.. >>

Thanx again for the Assist.
Andrew.

"keiji kounoike" <"kounoike A | T ma.Pikara.ne.jp"> wrote in message
| 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
| >
| >

 

[keiji kounoike]

I don't know what you want to do with cvs data, but I think it's not
good idea to parse data character by character, because it's not so
simple to deal with in any case. I would read the data into cells, say
column1, line by line and after that, apply TextToColumns method to this
column to get comma separate data.

Keiji