Prev: GotoControl in a Navigation Form
Next: Access 2010 white paper about manageability and migration
From: ManningFan on 12 Feb 2010 10:59
I need to read txt files into MSAccess and get a count of the number
of records in the text files. The problem is, sometimes they're
delimited and sometimes they're fixed width. Sometimes they have
record seperators and sometimes they don't.

Can anyone point me somewhere that might help me with this project?
Here's the function I have, and it works sometimes but not all the
time:

Public Function readWizardFile(ByVal FileName As String) As Integer

Dim fhandle As Integer
Dim fline As String
Dim db As Database

Set db = CurrentDb

fhandle = FreeFile()
Open FileName For Input Access Read Lock Write As #fhandle

' reset counter
readWizardFile = 0

While (Not (EOF(fhandle)))
Line Input #fhandle, fline

' count lines
readWizardFile = readWizardFile + 1

Wend

Close #fhandle

MsgBox "There are " & readWizardFile & " records in this file"

End Function
From: Banana on 12 Feb 2010 11:20
ManningFan wrote:
> Can anyone point me somewhere that might help me with this project?
> Here's the function I have, and it works sometimes but not all the
> time:

What happens when "it works ... but not all the time"? Need more info
what actually happens when it goes wrong...
From: Salad on 12 Feb 2010 11:51
ManningFan wrote:
> I need to read txt files into MSAccess and get a count of the number
> of records in the text files. The problem is, sometimes they're
> delimited and sometimes they're fixed width. Sometimes they have
> record seperators and sometimes they don't.

I doubt the problem is with delimited or fixed. The issue would be the
record separators.

In Line Input it is looking for the first Chr(13) (carriage return) or
Chr(13)Chr(10) cr+linefeed.

There has to be a record separator if you want to break them out.
Here's a simple program I wrote.
Public Function GetBody(strFileName As String) As Variant
Dim lngLen As Long
Open strFileName For Input As #1
lngLen = FileLen(strFileName)
GetBody = Input(lngLen, #1)
Close #1
End Function

Here I simply grab the entire file. I'm not looking for an enter key or
any delimter.

You could modifiy that to grab a chuck of data (you determine the size
to grab) and grab the enter file if filelen if less than 5k or the first
5k of data,and look for the existence of a chr(13). If not there, you
have a different record separator. I would assume that would be a line
feed or chr(10)....but who knows what it is. If you don't know, how
would the program know? But in a text file, you can't see the chr(13)
or chr(10) as they are low order chrs.

Here's a simple program to split a string into an array.
Sub WordCount()
Dim ar As Variant
Dim s As String
s = "Hello, how are you?"
ar = Split(s)
msgbox "Word Count: " & UBound(ar) + 1 'returns 4.
End Sub

I can't remember how many chars that can be read in using Input...if
there's a limit like 64K or more. It isn't in help. Anyway, if there's
no chr(13)'s found in an Instr() search, then the delimiter is chr(10)
or whatever you determine it is. Then either split it or read the file
and count the number of delims in it.

>
> Can anyone point me somewhere that might help me with this project?
> Here's the function I have, and it works sometimes but not all the
> time:
>
> Public Function readWizardFile(ByVal FileName As String) As Integer
>
> Dim fhandle As Integer
> Dim fline As String
> Dim db As Database
>
> Set db = CurrentDb
>
> fhandle = FreeFile()
> Open FileName For Input Access Read Lock Write As #fhandle
>
> ' reset counter
> readWizardFile = 0
>
> While (Not (EOF(fhandle)))
> Line Input #fhandle, fline
>
> ' count lines
> readWizardFile = readWizardFile + 1
>
> Wend
>
> Close #fhandle
>
> MsgBox "There are " & readWizardFile & " records in this file"
>
> End Function
From: Rich P on 12 Feb 2010 11:46
It appears that you are reading each line of text in your text files
using Line Input...

So the tast would be to parse out the fields. You can use Instr to
check if a line contains a delimeter character. If the line does not
and it is fixed width then just parse based on field length for each
field.

When you say sometimes there are record separators and sometimes not -
that doesn't make sense. I you are reading a file using Line Input -
that would be the record separator - each line (each carriage return).
Or, if the text file is just one continuous line - then Line Input would
only read one line (with say - 100,000 chars). If that is the case
(which I doubt) then you are on your own.

Ideally, you will read each line from the text file(s) using Line
Input... into your table(s). Then determine if a line contains a
delimter char (vbTab, ",", "|", " ", ...)
or it it is fixed width. If a line contains a delimeter char - then use
the Split function to read that line into a string array then read each
element of the array to a respective field in a table. If the file is
fixed width then use the Mid function to read each fixed width into a
respective field in a table.

HTH

Rich

*** Sent via Developersdex http://www.developersdex.com ***
From: ManningFan on 12 Feb 2010 13:39
On Feb 12, 11:20 am, Banana <Ban...(a)Republic.com> wrote:
>
> What happens when "it works ... but not all the time"? Need more info
> what actually happens when it goes wrong...

When it doesn't work, it will report that there is only 1 record in
the file. It's not seeing any breaks or carriage returns, just one
huge file (usually only when it's EBCDIC).
 |  Next  |  Last
Pages: 1 2
Prev: GotoControl in a Navigation Form
Next: Access 2010 white paper about manageability and migration