vba line input not recognizing end of line
in [Visual Basic]
|
From: Beancounter on 7 Jun 2010 12:17 I had this problem, and saw others had it as well; I just wanted to post my solution which turned out to be simple (in code). For years I've been parsing a csv download file using the standard vba line input function. Well, the download file is still a csv, but longer has CRLF, just LF. As a result, the line input function now reads the entire file at once, instead of line by line. To solve it, I just used the replace function: sub mproFileLineInput. Open "c:\NeededFile.csv" For Input As #1 'Open download file Line Input #1, txtline 'read line input, which reads entire file because no CR Chr(13), just LF Chr(10) NewText=Replace(txtline, Chr(10), vbCrLf) 'Find all Chr(10) LF and replace with CRLR Close #1 Open "c:\NeededFile.csv" For output As #1 'Open file again, but for output this time Print #1, NewTxtLine 'use print function to write contents, but with LF replaced by CRLF Close #1 End sub Hope it helps anyone with same problem.
From: Henning on 7 Jun 2010 13:48 "Beancounter" <jonescpa(a)gmail.com> skrev i meddelandet news:b9395c52-f604-4c9d-9ce4-f3e255ca7a45(a)d37g2000yqm.googlegroups.com... >I had this problem, and saw others had it as well; I just wanted to > post my solution which turned out to be simple (in code). > > For years I've been parsing a csv download file using the standard vba > line input function. Well, the download file is still a csv, but > longer has CRLF, just LF. As a result, the line input function now > reads the entire file at once, instead of line by line. To solve it, > I just used the replace function: > > sub mproFileLineInput. > > Open "c:\NeededFile.csv" For Input As #1 'Open download file > > Line Input #1, txtline 'read line input, which reads entire file > because no CR Chr(13), just LF Chr(10) > NewText=Replace(txtline, Chr(10), vbCrLf) 'Find all Chr(10) LF and > replace with CRLR > > Close #1 > > Open "c:\NeededFile.csv" For output As #1 'Open file again, but for > output this time > Print #1, NewTxtLine 'use print function to write contents, but with > LF replaced by CRLF > Close #1 > > End sub > > Hope it helps anyone with same problem. Just read here or there that someone deliberately saved files in that format. For me it's a big why?? to save some CR's? Or is it to ease for mac'ers to read the files? Your example shows the outcome of same row-delimiter-change. /Henning
From: Helmut Meukel on 7 Jun 2010 14:18 "Henning" <computer_hero(a)coldmail.com> schrieb im Newsbeitrag news:hujbe3$bk8$1(a)news.eternal-september.org... > > Just read here or there that someone deliberately saved files in that format. > For me it's a big why?? to save some CR's? Or is it to ease for mac'ers to > read the files? > > Your example shows the outcome of same row-delimiter-change. > > /Henning > Henning, a LF without a CR is UNIX, Linux, AmigaOS and now Mac - since Mac OS X. Until Mac OS version 9 the Mac was usung a single CR without a LF. Same for the Apple II. CR+LF is Windows, DOS, OS/2, CP/M, Atari TOS. For the old teletypes you needed both CR + LF. In the mid-seventies I saw a program using a teletype as "printer". They used multiple CR without a LF to write columns. With a teletype you had to send first the CR (carriage return) then the LF (line feed) due to the latency time of the mechanical equipment. If you did it the other way, the first character of the new line was printed somewhere to the right, not in the leftmost position. Helmut.
From: dpb on 7 Jun 2010 14:59 Henning wrote: .... > Just read here or there that someone deliberately saved files in that > format. For me it's a big why?? to save some CR's? Or is it to ease for > mac'ers to read the files? > > Your example shows the outcome of same row-delimiter-change. .... It's Unix-ish; I commented on the same posting not long ago that it's not wise to do so for Windows platforms in that there is much that doing so may unexpectedly break when the files are used for anything other than an app that knows to expect it. --
From: Jim Mack on 7 Jun 2010 15:44
Beancounter wrote: > I had this problem, and saw others had it as well; I just wanted to > post my solution which turned out to be simple (in code). > > For years I've been parsing a csv download file using the standard > vba line input function. Well, the download file is still a csv, > but longer has CRLF, just LF. As a result, the line input > function now reads the entire file at once, instead of line by > line. To solve it, I just used the replace function: > > sub mproFileLineInput. > > Open "c:\NeededFile.csv" For Input As #1 'Open download file > > Line Input #1, txtline 'read line input, which reads entire file > because no CR Chr(13), just LF Chr(10) > NewText=Replace(txtline, Chr(10), vbCrLf) 'Find all Chr(10) LF and > replace with CRLR > > Close #1 > > Open "c:\NeededFile.csv" For output As #1 'Open file again, but for > output this time > Print #1, NewTxtLine 'use print function to write contents, but > with LF replaced by CRLF > Close #1 > FWIW, if you need to do this often or quickly, there's a faster way (actually, a few faster ways). The least faster, but most straightforward way, is (AIR CODE): Open "c:\NeededFile.csv" For Binary As #1 Buf$ = String$(LOF(1), 0) Get 1,,Buf$ Buf$ = Replace$(Buf$, vbLf, vbCr) ' NOT vbCrLf Put 1,1,Buf$ Close #1 This works because while VB won't deal with files having only a LF delimiter, it does just fine with only a CR delimiter (VB essentially ignores LFs). It's faster because the string (and the file) doesn't need to expand. More speed if you avoid strings (and Unicode): Dim Idx As Long Dim Buf() As Byte Open "c:\NeededFile.csv" For Binary As #1 Idx = LOF(0) ReDim Buf(1 To Idx) As Byte Get 1,,Buf() For Idx = 1 To Idx If Buf(Idx) = 10 Then Buf(Idx) = 13 End If Next Put 1,1,Buf() Close #1 Of course any method, including the one you propose, should only be used if you know that the file contains only LF delimiters. -- Jim Mack Twisted tees at http://www.cafepress.com/2050inc "We sew confusion" |