f



Reading TXT file into MSAccess using Line Input?

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
0
manningfan (352)
2/12/2010 3:59:36 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

5 Replies
7547 Views

Similar Articles

[PageSpeed] 25

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...
0
Banana
2/12/2010 4:20:33 PM
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 ***
0
Rich
2/12/2010 4:46:29 PM
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
1
Salad
2/12/2010 4:51:34 PM
On Feb 12, 11:20=A0am, Banana <Ban...@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).
0
ManningFan
2/12/2010 6:39:12 PM
On Feb 12, 11:46=A0am, Rich P <rpng...@aol.com> wrote:
> When you say sometimes there are record separators and sometimes not -
> that doesn't make sense. =A0I 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). =A0If that is the case
> (which I doubt) then you are on your own.

Rich -
  That's exactly what it's doing.  With EBCDIC files, it's just seeing
one huge record, even though there really should be about 100,000
individual records.  I tried doing a Mid() function (because I usually
know how long the record should be and figured I could pass it in a
variable) but it kept overflowing when it hit ~32k characters.


0
ManningFan
2/12/2010 6:42:43 PM
Reply: