|
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 | |
Share:
|
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.
| | |
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 | | |
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
| | |
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 | | |
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 | | |
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. | | |
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 | | |
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 | | |
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. | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
12 posts
views
Thread by eddie wang |
last post: by
|
4 posts
views
Thread by Johannes Busse |
last post: by
|
4 posts
views
Thread by McKirahan |
last post: by
|
2 posts
views
Thread by Jürgen Heyn |
last post: by
|
5 posts
views
Thread by McKirahan |
last post: by
|
3 posts
views
Thread by Wade G. Pemberton |
last post: by
|
1 post
views
Thread by Jack Wright |
last post: by
|
2 posts
views
Thread by Michael Persaud |
last post: by
|
2 posts
views
Thread by Andrew |
last post: by
| | | | | | | | | | |