Linefeed to CrLf

Bob Quintal

I'm receiving data in text files from a unix sustem that onlu puts
a Lf character at the end of each row. Access insists on Cr or CrLf
or it sees the file as 1 row 700Kb wide and chokes.

Is there a better way of importing this file than using the open #1
command and building strings in a do loop one character at a time?

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Mar 9 '07 #1

Tom van Stiphout

On 09 Mar 2007 21:26:51 GMT, Bob Quintal <rq******@sPAmpatico.ca>
wrote:

So you write some code to pre-process the file before importing it,
replacing CR with CRLF.
-Tom.

>I'm receiving data in text files from a unix sustem that onlu puts
a Lf character at the end of each row. Access insists on Cr or CrLf
or it sees the file as 1 row 700Kb wide and chokes.

Is there a better way of importing this file than using the open #1
command and building strings in a do loop one character at a time?

--
Bob Quintal

PA is y I've altered my email address.

Mar 10 '07 #2

rkc

Bob Quintal wrote:

I'm receiving data in text files from a unix sustem that onlu puts
a Lf character at the end of each row. Access insists on Cr or CrLf
or it sees the file as 1 row 700Kb wide and chokes.

Is there a better way of importing this file than using the open #1
command and building strings in a do loop one character at a time?

Function ReadAll(path As String) As String
Dim fh As Long
fh = FreeFile
Dim s As String
'add error handling
Open path For Binary As #fh
s = String$(LOF(fh), vbNullChar)
Get #fh, , s
Close fh

ReadAll = s
End Function

Sub test()
Debug.Print Replace(ReadAll("c:\bigFile.txt"), vbLf, vbCrLf)
End Sub

Mar 10 '07 #3

Tom van Stiphout

On Fri, 09 Mar 2007 23:14:07 -0500, rkc <rk*@rkcny.yabba.dabba.do.com>
wrote:

Sweet. Unless the file was more than 2GB.
Nah, no Unix system that bad !

-Tom.

>Bob Quintal wrote:

>I'm receiving data in text files from a unix sustem that onlu puts
a Lf character at the end of each row. Access insists on Cr or CrLf
or it sees the file as 1 row 700Kb wide and chokes.

Is there a better way of importing this file than using the open #1
command and building strings in a do loop one character at a time?

Function ReadAll(path As String) As String
Dim fh As Long
fh = FreeFile
Dim s As String
'add error handling
Open path For Binary As #fh
s = String$(LOF(fh), vbNullChar)
Get #fh, , s
Close fh

ReadAll = s
End Function

Sub test()
Debug.Print Replace(ReadAll("c:\bigFile.txt"), vbLf, vbCrLf)
End Sub

Mar 10 '07 #4

storrboy

Found some alternatives here, but I haven't tried them...

http://groups.google.ca/group/comp.d...5f1f81bb18cff2

Mar 10 '07 #5

Bob Quintal

That is impressive! I'll test it out on monday.

I should be much faster than the Do while LOC < LOP() loop I was
using.

Thanks for the time.

rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:45***********************@roadrunner.com:

Bob Quintal wrote:

>I'm receiving data in text files from a unix sustem that onlu
puts a Lf character at the end of each row. Access insists on
Cr or CrLf or it sees the file as 1 row 700Kb wide and
chokes.

Is there a better way of importing this file than using the
open #1 command and building strings in a do loop one
character at a time?

Function ReadAll(path As String) As String
Dim fh As Long
fh = FreeFile
Dim s As String
'add error handling
Open path For Binary As #fh
s = String$(LOF(fh), vbNullChar)
Get #fh, , s
Close fh

ReadAll = s
End Function

Sub test()
Debug.Print Replace(ReadAll("c:\bigFile.txt"), vbLf,
vbCrLf)
End Sub

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Mar 10 '07 #6

Bob Quintal

rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:45***********************@roadrunner.com:

Bob Quintal wrote:

>I'm receiving data in text files from a unix sustem that onlu
puts a Lf character at the end of each row. Access insists on
Cr or CrLf or it sees the file as 1 row 700Kb wide and
chokes.

Is there a better way of importing this file than using the
open #1 command and building strings in a do loop one
character at a time?

Function ReadAll(path As String) As String
Dim fh As Long
fh = FreeFile
Dim s As String
'add error handling
Open path For Binary As #fh
s = String$(LOF(fh), vbNullChar)
Get #fh, , s
Close fh

ReadAll = s
End Function

Sub test()
Debug.Print Replace(ReadAll("c:\bigFile.txt"), vbLf,
vbCrLf)
End Sub

Would be faster to write back to disk and do a file import, or
just use mid() to split the string and stuff it into the table

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Mar 10 '07 #7

rkc

Bob Quintal wrote:

rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:45***********************@roadrunner.com:

>Bob Quintal wrote:

>>I'm receiving data in text files from a unix sustem that onlu
puts a Lf character at the end of each row. Access insists on
Cr or CrLf or it sees the file as 1 row 700Kb wide and
chokes.

Is there a better way of importing this file than using the
open #1 command and building strings in a do loop one
character at a time?

Function ReadAll(path As String) As String
Dim fh As Long
fh = FreeFile
Dim s As String
'add error handling
Open path For Binary As #fh
s = String$(LOF(fh), vbNullChar)
Get #fh, , s
Close fh

ReadAll = s
End Function

Sub test()
Debug.Print Replace(ReadAll("c:\bigFile.txt"), vbLf,
vbCrLf)
End Sub

Would be faster to write back to disk and do a file import, or
just use mid() to split the string and stuff it into the table

I can't answer that because I do not know what you have to do with
the file. I posted assuming you would just write it back to a file
and go from there.

Mar 10 '07 #8

Bob Quintal

rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:45**********************@roadrunner.com:

Bob Quintal wrote:

>rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:45***********************@roadrunner.com:

>>Bob Quintal wrote:
I'm receiving data in text files from a unix sustem that
onlu puts a Lf character at the end of each row. Access
insists on Cr or CrLf or it sees the file as 1 row 700Kb
wide and chokes.

Is there a better way of importing this file than using the
open #1 command and building strings in a do loop one
character at a time?
Function ReadAll(path As String) As String
Dim fh As Long
fh = FreeFile
Dim s As String
'add error handling
Open path For Binary As #fh
s = String$(LOF(fh), vbNullChar)
Get #fh, , s
Close fh

ReadAll = s
End Function

Sub test()
Debug.Print Replace(ReadAll("c:\bigFile.txt"), vbLf,
vbCrLf)
End Sub

Would be faster to write back to disk and do a file import,
or just use mid() to split the string and stuff it into the
table

I can't answer that because I do not know what you have to do
with the file. I posted assuming you would just write it back
to a file and go from there.

I'll know monday. Currently I'm reading the fileup to a line
feed, and parsing that into the destination table. It should not
take long to code both ways and test which is faster.

I'll report back.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Mar 10 '07 #9

rkc

Bob Quintal wrote:

rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:45**********************@roadrunner.com:

>Bob Quintal wrote:

>>rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:45***********************@roadrunner.com:

Bob Quintal wrote:
I'm receiving data in text files from a unix sustem that
onlu puts a Lf character at the end of each row. Access
insists on Cr or CrLf or it sees the file as 1 row 700Kb
wide and chokes.
>
Is there a better way of importing this file than using the
open #1 command and building strings in a do loop one
character at a time?
>
Function ReadAll(path As String) As String
Dim fh As Long
fh = FreeFile
Dim s As String
'add error handling
Open path For Binary As #fh
s = String$(LOF(fh), vbNullChar)
Get #fh, , s
Close fh

ReadAll = s
End Function

Sub test()
Debug.Print Replace(ReadAll("c:\bigFile.txt"), vbLf,
vbCrLf)
End Sub
Would be faster to write back to disk and do a file import,
or just use mid() to split the string and stuff it into the
table

I can't answer that because I do not know what you have to do
with the file. I posted assuming you would just write it back
to a file and go from there.

I'll know monday. Currently I'm reading the fileup to a line
feed, and parsing that into the destination table. It should not
take long to code both ways and test which is faster.

If that's the case I might just try leaving it in memory after the
vbcrlf replace and doing something like:

dim lines as variant
dim count as long
lines = Split(WholeThing, vbcrlf)

for count = 0 to ubound(lines)
call Parse(lines(count))
next

Mar 10 '07 #10

rquintal

On Mar 10, 1:04 pm, rkc <r...@rkcny.yabba.dabba.do.comwrote:

Bob Quintal wrote:

rkc <r...@rkcny.yabba.dabba.do.comwrote in
news:45**********************@roadrunner.com:

Bob Quintal wrote:
rkc <r...@rkcny.yabba.dabba.do.comwrote in
news:45***********************@roadrunner.com:

>>Bob Quintal wrote:
I'm receiving data in text files from a unix sustem that
onlu puts a Lf character at the end of each row. Access
insists on Cr or CrLf or it sees the file as 1 row 700Kb
wide and chokes.

>>>Is there a better way of importing this file than using the
open #1 command and building strings in a do loop one
character at a time?

>>Function ReadAll(path As String) As String
Dim fh As Long
fh = FreeFile
Dim s As String
'add error handling
Open path For Binary As #fh
s = String$(LOF(fh), vbNullChar)
Get #fh, , s
Close fh

>> ReadAll = s
End Function

>>Sub test()
Debug.Print Replace(ReadAll("c:\bigFile.txt"), vbLf,
vbCrLf)
End Sub
Would be faster to write back to disk and do a file import,
or just use mid() to split the string and stuff it into the
table
I can't answer that because I do not know what you have to do
with the file. I posted assuming you would just write it back
to a file and go from there.

I'll know monday. Currently I'm reading the fileup to a line
feed, and parsing that into the destination table. It should not
take long to code both ways and test which is faster.

If that's the case I might just try leaving it in memory after the
vbcrlf replace and doing something like:

dim lines as variant
dim count as long
lines = Split(WholeThing, vbcrlf)

for count = 0 to ubound(lines)
call Parse(lines(count))
next- Hide quoted text -

- Show quoted text -

Reading the whole file into a variable and parsing it down to lines
with mid() is 150 times faster than reading the file one byte at a
time and concatenating the bytes into the string

I don't know where I got the impression a variable was limited to 64Kb
but I'm sure glad you pointed out that it isn't.

Thanks again.

Mar 12 '07 #11