Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Input Not behaving...!
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 | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Input Not behaving...!
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 Andrew wrote: 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 | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line Input not finding Chr10 or 13 | Excel Programming | |||
Multi line merged cell - adapting to different size input - possible? | Excel Discussion (Misc queries) | |||
diagonal line and data input | Excel Discussion (Misc queries) | |||
line input problem-need to return 5 lines after a string is found | Excel Programming | |||
Reads entire *.txt file into string opposed to a desired line by line input. | Excel Programming |