By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 2,512 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

Linefeed to CrLf

P: n/a
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
Share this Question
Share on Google+
10 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a

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

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

Mar 10 '07 #5

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.