importing from txt flie

[brokerluke]

Can someone tell me how to import multiple records (50 per text file) in this
format into my table? i can import everything but i get everything in only 2
fields.. the stuff to the left of the : and the right so i get like
field1 field2
customer name: Bob

but the problem is the entire records are like that and there is no way for
me to differentiate amongst the records.


ontract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1

ontract Information:
Customer Name: client
Contract File Number: 1111111111111
Status: Expired
Master BTN: 9999999999999

Customer Information:
Customer Contact Name: bob bob
Customer Contact TN: 1111111111111

Contract Dates:
Term Months: 60
Start: 10/29/1998
End: 10/29/2003
Received Date: 10/27/1998
Effective Date: 10/29/1998
Expiration Date: 10/29/2003
Input: 10/29/1998
Signed: 10/19/1998

Revenue and Contract Information:
MARC: $4,713.80
Business Unit: Large Business Services
Type of Customer:
Authorized Distributor:
Revenue: $23,569.00
Promotion: 0
Product: DS1

 

[pietlinden]

From the looks of your text file, you're going to have to write code
to parse the data into a useable format. Basically, you will have to
read a line, and check the text to the left of the colon to see what
field the data belongs in. Here's the code I came up with that works
on your sample data... (no guarantees on the real data, though!)

Option Compare Database
Option Explicit

Dim lngFileNo As Long

Private Sub Command2_Click()
ReadFile Me.Text0

End Sub

Private Sub ReadFile(ByVal strFile As String)

Dim strLine As String
Dim strField As String
Dim varValuePair As Variant
Dim rs As DAO.Recordset


Set rs = DBEngine(0)(0).OpenRecordset("Contract", dbOpenTable,
dbAppendOnly)

Open strFile For Input As #1
Do Until EOF(1)

Line Input #1, strLine
strLine = Trim(strLine)

Select Case strLine
Case "Contract Information:" '--- we are at a new
record
rs.AddNew

Case "Customer Information:", "Contract Dates:", "Revenue
and Contract Information:", ""
'don't do anything...
Case Else
If InStr(1, strLine, ":") > 0 Then
varValuePair = Split(strLine, ":")
'Debug.Print strLine
'Debug.Print varValuePair(0), "--->", varValuePair
(1)
'Debug.Print
If Not IsNull(varValuePair(1)) Then
rs.Fields(varValuePair(0)) = Trim(varValuePair
(1))
End If
End If

End Select
If strLine <> "" Then
'---We're at the end of a "record", so save it to the
table.
If Left$(strLine, InStr(1, strLine, ":") - 1) = "Product"
Then
rs.Update
End If
End If
Loop

Close #1
rs.Close
Set rs = Nothing
End Sub

basically, I used the colon to split the two parts of the record - the
field/value pairs. Then I just named my fields in my table
accordingly. Then you can clean the data in the table up any way you
want - just append the data to a new table and delete the data in the
temp table if you want.

Not pretty, but it works.

Pieter

 

[brokerluke]

so i run this as a VBA program in access? its halting me on readfile me.text
how do i link my file to this?

 

[brokerluke]

my programming knowledge is EXTREMELY limited...

i'm assuming there is a string i must add to the code that links my txt file
in question?>

 

[brokerluke]

i can import my TXT files fine into the table and i receive 2 fields.. field
1 for left of the colon and field 2 for right of the colon

everything looks great....
is there a simple script to run that will convert the data from the 2 fields
in to records? so that i can have them on my table?

like the field columns would look like..
Company|Status|BTN|Contact|Term|Start|End|MARC

and the data from the right of the colon would parse into those fields?..

using
Contract Information:

as the delimiter to begin a new record?

 

[pietlinden]

In short, no, there is no simple way to do this.

the problem you will run into is that data in tables is *unordered*.
Order in a table should have zero meaning. In your case, if you
import everything into a single two-column table, you still have to
turn the two columns into one multi-column record. It's going to be a
hassle no matter how you look at it. So if you read the data a line
at a time, parse it and write it in code to a field within a record,
your problems go away.

If you are going to do this frequently with a single file, you can use
the OpenFile API to choose a text file to import and then process the
file by passing the file name to the parsing and saving routine.

In order to make the assigning of values to fields easier, the names
in my table correspond to the "field names" (to the left of the colon)
in the data.

To make the selection of a file easier, I included code for
OpenFileAPI, which uses a standard open file dialog.

Okay, on to the code...

The OpenFileAPI stuff goes in a standard code module. (open a new code
module and paste the code in.)

Then the rest goes in the form you're running the import from.

The code I have written writes the data to a table that looks like
this (order doesn't matter):

Customer Name
Contract File Number
Status
Master BTN
Customer Contact Name
Customer Contact TN
Term Months
Start
End
Received Date
Effective Date
Expiration Date
Input
Signed
MARC
Business Unit
Type of Customer
Authorized Distributor
Revenue
Promotion
Product

Basically, I read through the file a line at a time, and match up the
field name/field value pairs. When I get to "Product", the record is
complete and I save it.

Here's the code behind the button that starts all this:

Private Sub cmdGetFile_Click()
Dim strFileToRead As String
'GetOpenFile is the API call... so you need the code for it in a
standard code module.
'code is from www.mvps.org/api/api0001.htm
strFileToRead = GetOpenFile("C:\", "Choose a file to import")

'just show the filename returned by GetOpenFile on the form (not
necessary)
Me.txtFileToImport = strFileToRead

' starts the code to read through the file and save the parsed
parts to the Contract table.
ReadFile strFileToRead
' feedback to let you know the process is complete
MsgBox "Import complete.", vbOKOnly + vbInformation
End Sub



Private Sub ReadFile(ByVal strFile As String)

Dim strLine As String
Dim strField As String
Dim varValuePair As Variant
Dim rs As DAO.Recordset

'--Open a recordset so I can write the parsed data to a record in
the table
Set rs = DBEngine(0)(0).OpenRecordset("Contract", dbOpenTable,
dbAppendOnly)

'--Open the text file for reading
Open strFile For Input As #1
Do Until EOF(1)

Line Input #1, strLine
strLine = Trim(strLine)

Select Case strLine
Case "Contract Information:" '--- we are at a new
record (in the text file)
'--add a new record to the table
rs.AddNew

Case "Customer Information:", "Contract Dates:", "Revenue
and Contract Information:", ""
'don't do anything... just ignore these since they're
section identifiers and not data.
Case Else
'---the rest are data.
'---so split the data into two parts on the colon
'---and write the value pairs (Field,Value) to the
table
If InStr(1, strLine, ":") > 0 Then
varValuePair = Split(strLine, ":")
If Not IsNull(varValuePair(1)) Then
rs.Fields(varValuePair(0)) = Trim(varValuePair
(1))
End If
End If

End Select
If strLine <> "" Then
'---Because 'Product' is the last field in the table, we
must be at the end of a record here.
If Left$(strLine, InStr(1, strLine, ":") - 1) = "Product"
Then
'---save the modifications to the new record
rs.Update
End If
End If
Loop

'--Close the text file, we're done with it
Close #1
'--close the recordset and clean up since we don't need it
anymore.
rs.Close
Set rs = Nothing
End Sub

'---END CODE

Controls on my form:
a textbox, txtFileToImport
a button, cmdGetFile

and that's pretty much it. The code depends on the field name/field
value pairs to do the importing correctly. So the order of the fields
in the table do not matter, but the field names must match the names
in the text file for the matching to work.

To get this to work, create a form with the textbox, txtFileToImport,
and the button, cmdGetFile, on it. Copy the code into your form
module, and you should be good to go. But make sure you have a
reference to DAO 3.6 otherwise, the stuff won't run. (Open a code
module, select Tools, Options and make sure it's checked).

 

[brokerluke]

i'm using access 2007 if that helps..

i'm having trouble figuring out where all the code goes and referencing dao
3.6
can you maybe explain to me step by step?

thank you!

 

[brokerluke]

ok looks like i have everything where needed

now how do i link the cmdGetFile to the button i'm having problems calling
the programming from the button

 

[brokerluke]

when i put it on my command button it sais "cannot find object cmdGetFile"

 

[pietlinden]

ok looks like i have everything where needed

now how do i link the cmdGetFile to the button i'm having problems calling
the programming from the button

create a button called "cmdGetFile" and then put the code into it.

 

[brokerluke]

this happened when i linked the control button to the code.

A problem occurred while Microsoft Office Access was communicating with the
OLE server or ActiveX Control.


"This error occurs when an event has failed to run because Microsoft Office
Access cannot evaluate the location of the logic for the event. For example,
if the OnOpen property of a form is set to =[Field], this error occurs
because Access expects a macro or event name to run when the event is fired."

 

[brokerluke]

Private Sub Command2_Click()
ReadFile Me.Text0

what does this refer to? is this where my file links in?

 

[pietlinden]

when i put it on my command button it sais "cannot find object cmdGetFile"

you need to add a button to your form, name it "cmdGetFile" and then
copy the code for the cmdGetFile_Click() to it.

 

[brokerluke]

making lots of progress think its almost done...

the script is halting up right here.. whats this mean?

rs.Fields(varValuePair(0)) = Trim(varValuePair(1))

thank you so much

 

[brokerluke]

i took the other line out to attempt to complete the code

If Left$(strLine, InStr(1, strLine, ":") - 1) = "Product" Then

this one said invalid procedure call or argument

 

[brokerluke]

ok it successfully processed 1 record.. i had to step through it in debug..
it seems to be locking up on
rs.Fields(varValuePair(0)) = Trim(varValuePair(1))

when it starts a new record it stops at "customer information"

 

[pietlinden]

ok it successfully processed 1 record.. i had to step through it in debug...
it seems to be locking up on
rs.Fields(varValuePair(0)) = Trim(varValuePair(1))

when it starts a new record it stops at "customer information"

The simplifying assumption I made to make this work was that the field
names in your table were exactly the same as the ones in your text
file. This appears to be incorrect. I would change the field names
in the table you import into and then everything should work. If
that's not an option for you, create a new table where the fields
match exactly and then append all the data in the work table into your
final table.

Also, if you have to process lots of these files and they're in the
same directory, you could just loop the directory, and process each
one with maybe 5 more lines of code... (but let's get this problem
solved first.)

Pieter

 

[brokerluke]

Alright... FINALLY!! got everything working properly thank you so much for
the help Pieter, was a great learning experience and all the code works very
well.!

thanks again!

 

[pietlinden]

Alright... FINALLY!! got everything working properly thank you so much for
the help Pieter, was a great learning experience and all the code works very
well.!

thanks again!

You're welcome.

Now the fun part. If you have to process more than one file at a
time, you can use DIR() to loop over the contents of a directory and
process each file. Just use the BrowseFolder API and you're good to
go.