Reading Text Files - Dev

[Roy]

I use a Access 2K application.I am trying to use Chuck
Grimsby(clsReadTextFile.txt)class utility to read a text file and then
do a import of the same into my database.The question is how to call
these in a button event or any other method.

Thanks,
Roy

[Chuck Grimsby]

Well, since I wrote that silly little bit of code, I suppose I should
be the one to answer it, but sadly, I'm not quite sure what you are
asking for here.

The class provides the method for _reading_ a file, but you'll have to
do the selecting of _which_ file to read outside of the class. You
can do that fairly easily with the code at
<http://www.mvps.org/access/api/api0001.htm>

After selecting the file to import, simply follow the code example
within the class starting where it says:
' sample useage:
'Sub ReadAFile(strFileName)

through the line that reads:
'End Sub

If that doesn't answer your question(s), feel free to ask! (Ah, here,
in the newsgroup, not in email, unless you want to pay me....)

You may also want to search Google Groups for previous discussions on
the clsReadTextFile class.
On 29 Jul 2005 12:23:36 -0700, "Roy" <pr********@yahoo.com> wrote:

I use a Access 2K application.I am trying to use Chuck
Grimsby(clsReadTextFile.txt)class utility to read a text file and then
do a import of the same into my database.The question is how to call
these in a button event or any other method.

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[Rich P]

Here is a more bare bones procedure for reading data from a textfile and
writing it to an Access table,
--------------------------------------------------
Sub GetAndParseTextFileData()
Dim db As dao.Database, i As Integer, j As Integer
Dim k As Integer, m As Integer, n As Integer, p As Integer
Dim RS1 As dao.Recordset, RS2 As dao.Recordset
Dim SourceDoc As String, str1 As String

Set db = CurrentDb
Set RS1 = db.OpenRecordset("tblDataPickup")
SourceDoc = "U:\someDir\testFile.txt
Open SourceDoc For Input As #1
i = 0
Do While Not EOF(1)
Line Input #1, str1
RS1.AddNew
RS1(0) = str1
RS1.Update
RetVal = SysCmd(acSysCmdSetStatus, i)
i = i + 1
Loop
Close #1
RS1.MoveFirst

Set RS2 = db.OpenRecordset("tblParsedData")
Do While Not RS1.EOF
str1 = RS2(0)
i = 1
j = 1
RS2.AddNew
For k = 0 To RS2.Fields.Count - 1
j = InStr(i, str1, "|")
m = Len(str1) - (Len(str1) - j)
p = m - i
str2 = Trim(Mid(str1, i, p))
RS2(k) = str2
i = j + 1
Next
j = Len(str1) - i - 1
str2 = Right(str1, j)
RS2(k) = str2 '--this is the last column in table
RS2.Update
RS1.MoveNext
RetVal = SysCmd(acSysCmdSetStatus, "Parsing " & n)
n = n + 1
Loop
RS1.Close
RS2.Close
End Sub
---------------------------------------------------

tblDataPickup only has one field - a memo field that will accept any
sized text line using the Line function. tblParsedData will have the
same number of columns as your delimited textfile. You pull the raw
data into tblDataPickup and then parse it into tblParsedData. This
example uses a pipe | as the delimeter. I have variable length fields.
If your fields all have the same length, then you are in luck - don't
have to calculate field length. I also use SycCmd to display a count of
records on the status Bar of Access (usually picking up 7000-10000
records). This should be generic enough for your use.

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Roy]

Thanks to both of you.I tried with Rich's solution in my code.I used
two tables .The data was read into the first table .but it fails to
read into the parsed table ,with an error no current data.The str1
returns Null.

I am pasting a sample of my text file below,mine is a tab delimited
text file.

1000 NULL 33497 NULL
CitiDirect Platform and Business Systems inte
Application Services Incident/Outage caused
by software failure. Incident/Outage caused by software failure.
Int CIB - Cash
NULL
NULL New Citicard customers unable to access
ATM & Citidirect
Citiphone Brisbane, Australia received a complaint from a customer on
27 July 2001 at 15:30 AEST that the transaction was declined at ATM
with error message of Invalid Card. Attempt on Citidirect also failed.
Investigation on this incident did not start until the GTMS ticket was
received by the Country CitiDirect Support Team on 30 July 2001. The
cause of the incident is found to be in the BAFES system where there is
a limitation on the file size. The customer file in BAFES is defined
as an Indexed Sequential file. Stratus has a limitation on this file
type. It cannot exceed 2GB in size. Ausstralia's BAFES's customer
file size has exceeded this number.As a result, any new additions to
the customer file will fail. This may explain the missing customer
records for those newly added. There can be 2 possible solutions. One
is to removed any unwanted customer records (i.e. some house-keeping in
GRB) to reduce the file size. The alternative solution is to redefine
the file in BAFES to be an Indexed Relative file. In this case, there
will be no limitation onthe size for this file type.
Yek-Ling Chong
With joint effort from Singapore GRBS and BAFES support, a full
customer renewal was done last Saturday- 11 August 2001 (completed
around 8pm Sydney time). No downtime was reported.

Aug 12 2001 10:58PM Non-critical (Yellow) Closed
Fe Ganuelas
[phone] NULL
NULL NULL
NULL NULL
NULL NULL NULL
NULL
NULL NULL NULL
Jul 27 2001 3:30PM Aug 11 2001
8:00PM Not Implemented NULL
NULL NULL

NULL



(GMT+10:00) East Australian Standard Time NULL
NULL NULL
NULL
NULL NULL NULL
NULL NULL

NULL



NULL

1001 NULL NULL NULL
NULL
Market Data Services NULL
NULL
NULL NULL
NULL
NULL Equity users are unable to start Reuters
Plus application on PC - Mkt Data team
aware and investigating as of 8.00am this morning.



Roy Sherman
Firewall problem corrected as of 10:00 this morning.



Aug 6 2001 2:23PM Critical (Red) Closed
Alex Lucero
212-723-1400 NULL
NULL NULL
NULL NULL
NULL NULL NULL
NULL
NULL NULL NULL
Aug 6 2001 8:00AM Aug 6 2001
10:00AM Not Implemented NULL
NULL NULL

NULL



(GMT-05:00) Eastern Standard Time NULL
NULL NULL
NULL
NULL NULL NULL
NULL NULL

NULL



NULL

1002 NULL NULL NULL
NULL
Exchange Services NULL
NULL
NULL NULL
NULL
NULL Nasdaq is having streetwide problems
updating quotes as of 10:00
Nasdaq is currently investigating.



Roy Sherman
Nasdaq has resolved their quote update issue at 10:35



Aug 6 2001 2:40PM Critical (Red) Closed
Alex Lucero
212-723-1400 NULL
NULL NULL
NULL NULL
NULL NULL NULL
NULL
NULL NULL NULL
Aug 6 2001 10:00AM Aug 6 2001
10:35AM Not Implemented NULL
NULL NULL

NULL



(GMT-05:00) Eastern Standard Time NULL
NULL NULL
NULL
NULL NULL NULL
NULL NULL

NULL



NULL

Can you shed some more light on this?

Thanks,

Roy

[Roy]

Thanks for the reply.The problem i am having is when the code bombs
saying,it cannot identify the class created,clsReadTextFile.I am using
your code in a button event
DoCmd.OpenModule "modImportAllText", ReadAFile

modImportAllText contains all the code and the ReadAFile is a function
which is called.

Thanks,

Roy

[Chuck Grimsby]

On 1 Aug 2005 06:02:13 -0700, "Roy" <pr********@yahoo.com> wrote:

Thanks for the reply.The problem i am having is when the code bombs
saying,it cannot identify the class created,clsReadTextFile.I am using
your code in a button event
DoCmd.OpenModule "modImportAllText", ReadAFile
modImportAllText contains all the code and the ReadAFile is a function
which is called.

The class needs to be a _class_ object, not a module.

Delete your existing module, as it'll just cause Access to be
confused.

Make sure the clsReadTextFile.txt file is in your "My Documents"
directory.

Open up the VBA editor. In the immediate window, type in (or better
yet, cut and paste) the following command:

Application.LoadFromText _
acModule,"clsReadTextFile", _
"clsReadTextFile.txt"

You also cannot use the DoCmd.OpenModule on the code. You need have
something in there like:

Call ReadAFile(strFileToRead)

Where strFileToRead is a string containing the file name and path to
the file you want the class to process.
--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[Roy]

Rich at this line it bombs with an error,saying No current record.

Set RS2 = db.OpenRecordset("tblParsedDat*a")
Do While Not RS1.EOF
str1 = RS2(0) ----This line

[Roy]

Rich at this line it bombs with an error,saying No current record.

Set RS2 = db.OpenRecordset("tblParsedDat*a")
Do While Not RS1.EOF
str1 = RS2(0) ----This line

[Roy]

Chuck,
Obviously I am doing something wrong here.I imported your
clsReadTextFile.txt to the MyDocuments folder.I pasted the code
Application.LoadFromText acModule,"clsReadTextFile",
"clsReadTextFile.txt"
,but it errors ,saying ,it cannot read the clsReadTextFile.txt .Can you
please detail the steps how to go about the whole thing,please.

The text file i need to use is EMSTestFeed.txt.

Where do i fit this in your code?How do i read your class from the VBA
module.

Thanks,

Roy

[Roy]

Chuck,
Obviously I am doing something wrong here.I imported your
clsReadTextFile.txt to the MyDocuments folder.I pasted the code
Application.LoadFromText acModule,"clsReadTextFile",
"clsReadTextFile.txt"
,but it errors ,saying ,it cannot read the clsReadTextFile.txt .Can you
please detail the steps how to go about the whole thing,please.

The text file i need to use is EMSTestFeed.txt.

Where do i fit this in your code?How do i read your class from the VBA
module.

Thanks,

Roy

[Rich P]

Sorry about that. It should read like this:

...
Set RS2 = db.OpenRecordset("tblParsedDat*a")
Do While Not RS1.EOF
str1 = RS1(0)

RS1 is the raw data table. Each field in RS1 contains the entire row
picked up from the textfile. It is the row that you are parsing based
on whatever your field delimeter is in the textfile.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

Sorry about that. It should read like this:

...
Set RS2 = db.OpenRecordset("tblParsedDat*a")
Do While Not RS1.EOF
str1 = RS1(0)

RS1 is the raw data table. Each field in RS1 contains the entire row
picked up from the textfile. It is the row that you are parsing based
on whatever your field delimeter is in the textfile.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Chuck Grimsby]

On 4 Aug 2005 06:01:36 -0700, "Roy" <pr********@yahoo.com> wrote:

Obviously I am doing something wrong here.I imported your
clsReadTextFile.txt to the MyDocuments folder.I pasted the code
Application.LoadFromText acModule,"clsReadTextFile",
"clsReadTextFile.txt"
,but it errors ,saying ,it cannot read the clsReadTextFile.txt .Can you
please detail the steps how to go about the whole thing,please.
The text file i need to use is EMSTestFeed.txt.
Where do i fit this in your code?How do i read your class from the VBA
module.

Well, step 1 is to get the class into your Access DB. If that can't
be done (and it only has to be done _once_), then nothing will help
you any further.

Hmmm.... _Where_ did you paste the "Application.LoadFromText..."
part? You _cannot_ do it in the code module. It _must_ be done in
the Immediate window. Nowhere else will work, or do. Note: This part
is not something you have to _every time_, it's just a one time thing
to get the class module into your project. (Rather like creating a
Form. Generally, you only have to create it once.)

The name of the text file you are trying to read doesn't matter at
this point. We'll take care of that after you have the
clsReadTextFile class module in your database.

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[Chuck Grimsby]

On 4 Aug 2005 06:01:36 -0700, "Roy" <pr********@yahoo.com> wrote:

Obviously I am doing something wrong here.I imported your
clsReadTextFile.txt to the MyDocuments folder.I pasted the code
Application.LoadFromText acModule,"clsReadTextFile",
"clsReadTextFile.txt"
,but it errors ,saying ,it cannot read the clsReadTextFile.txt .Can you
please detail the steps how to go about the whole thing,please.
The text file i need to use is EMSTestFeed.txt.
Where do i fit this in your code?How do i read your class from the VBA
module.

Well, step 1 is to get the class into your Access DB. If that can't
be done (and it only has to be done _once_), then nothing will help
you any further.

Hmmm.... _Where_ did you paste the "Application.LoadFromText..."
part? You _cannot_ do it in the code module. It _must_ be done in
the Immediate window. Nowhere else will work, or do. Note: This part
is not something you have to _every time_, it's just a one time thing
to get the class module into your project. (Rather like creating a
Form. Generally, you only have to create it once.)

The name of the text file you are trying to read doesn't matter at
this point. We'll take care of that after you have the
clsReadTextFile class module in your database.

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

[Roy]

Rich,
Almost there!!That took care of it.But i get another error at this
line saying that
"Item not found in this collection"

Error is at this point:

RS2(k) = str2 ' --this is the last column in table
RS2.Update
RS1.MoveNext
RetVal = SysCmd(acSysCmdSetStatus, "Parsing " & n)
n = n + 1

Loop
RS1.Close
RS2.Close

Thanks,
Roy

Rich P wrote:

Sorry about that. It should read like this:

..
Set RS2 = db.OpenRecordset("tblParsedDat*a")
Do While Not RS1.EOF
str1 = RS1(0)

RS1 is the raw data table. Each field in RS1 contains the entire row
picked up from the textfile. It is the row that you are parsing based
on whatever your field delimeter is in the textfile.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Roy]

Rich,
Almost there!!That took care of it.But i get another error at this
line saying that
"Item not found in this collection"

Error is at this point:

RS2(k) = str2 ' --this is the last column in table
RS2.Update
RS1.MoveNext
RetVal = SysCmd(acSysCmdSetStatus, "Parsing " & n)
n = n + 1

Loop
RS1.Close
RS2.Close

Thanks,
Roy

Rich P wrote:

Sorry about that. It should read like this:

..
Set RS2 = db.OpenRecordset("tblParsedDat*a")
Do While Not RS1.EOF
str1 = RS1(0)

RS1 is the raw data table. Each field in RS1 contains the entire row
picked up from the textfile. It is the row that you are parsing based
on whatever your field delimeter is in the textfile.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

Hi Roy,

Try adding one more column to your ParsedData table. I think you are
probably short one column.

Rich

*** Sent via Developersdex http://www.developersdex.com ***