Import External File and change 1 field before Insert

[lorirobn]

Hi,

I have been manually importing a text file on a weekly basis to an
Access table. The data on one field is about to change (happens to be
the first field). It was numeric, and is defined as numeric on the
Access table. Now it will have a prefix of "K", for example: K00075.

I would like to create a procedure in my Access database that strips
the "K" off the field, and inserts the rest of the data into the table.
I know I can do it manually in Word or an application like that before
I import it, but I'd prefer to streamline the process now.

How can I can read the input file, strip the K off, and write an
output file without the K. Then import this 2nd file into my database?
I have never written anything within Access to read/write external
files before (only in COBOL, only a tad bit different!). Is this done
in a module, a macro, or what? Is that the best way to do this?

I would just like to sort-of replicate my manual process, which was:
Get External Data, Import file with delimiters, Load into Existing
table AFTER STRIPPING THE K. Can I automate something like that?

Thanks a bunch in advance,
Lorirobn
ps - I cannot get the file without the K.

 

[Dirk Goldgar]

Hi,

I have been manually importing a text file on a weekly basis to an
Access table. The data on one field is about to change (happens to be
the first field). It was numeric, and is defined as numeric on the
Access table. Now it will have a prefix of "K", for example: K00075.

I would like to create a procedure in my Access database that strips
the "K" off the field, and inserts the rest of the data into the
table. I know I can do it manually in Word or an application like
that before I import it, but I'd prefer to streamline the process now.

How can I can read the input file, strip the K off, and write an
output file without the K. Then import this 2nd file into my
database? I have never written anything within Access to read/write
external files before (only in COBOL, only a tad bit different!). Is
this done in a module, a macro, or what? Is that the best way to do
this?

I would just like to sort-of replicate my manual process, which was:
Get External Data, Import file with delimiters, Load into Existing
table AFTER STRIPPING THE K. Can I automate something like that?

Thanks a bunch in advance,
Lorirobn
ps - I cannot get the file without the K.

It sounds to me like the easiest thing to do would be to *link* to the
file, rather than importing it, and then run an append query that
selects the data from the linked file and inserts it in the target
table, stripping the "K" off that field in the process. You'd use a
calculated field in the query, with an expression along these lines:

NoK: IIf([OriginalField] Like "K*",
Mid([OriginalField], 2),
[OriginalField])

That expression would all be on one line in the query definition; I
just broke it onto multiple lines for clarity in this post.

 

[lorirobn]

Great suggestions - thank you, Ken and Dirk.

Dirk, can I link to a text file? How do I go about doing so? (I have
only linked via Linked Table Manager... I'm guessing you mean something
entirely different?).

I would like to automate as much of the process as possible, which is
why I want to import the file using VBA code. I have so far written
VBA to read the input text file and strip the "K". Now I have to
finish the Insert Into. And then figure out how to run this (have been
doing it in the Immediate window) - can anyone tell me how to run it?
(in a query within a macro?). Not sure if this is the way to go, but
am playing around with it.

My code so far is:

Sub OpenFile()

Dim hFile As Long
Dim strFileIn As String
Dim strTeacherID As String
Dim strFirstName As String
Dim strLastName As String

hFile = FreeFile

strFileIn = "C:\TestFileExport.txt" ' I shorted this
for simplicity

Open strFileIn For Input Access Read Shared As hFile

Do Until EOF(hFile)
Input #hFile, strTeacherID, strFirstName, strLastName

'Srip the 1st character of Teacher ID, which is a "K"
strTeacher_ID = Mid$(strTeacherID, 2)

Dim strSQL As String
strSQL = "INSERT INTO TESTTBL (teacher_id, firstname, lastname" _
& "VALUES (strTeacherID, strfirstname, strlastname)"
(I am working on this part now...)
Loop

Close hFile

End Sub

THANKS!

 

[Dirk Goldgar]

Great suggestions - thank you, Ken and Dirk.

Dirk, can I link to a text file? How do I go about doing so? (I have
only linked via Linked Table Manager... I'm guessing you mean
something entirely different?).

When you use the File -> Get External Data menu, one of the menu options
is "Link Tables...". In the Link dialog that comes up, you can change
the Files of Type: dropdown to "Text Files". At that point, it's really
the same as importing a text file, except that the text file becomes a
linked table, rather than a local Access table. You can then read from
it and use it as a source for an append query, just like a normal table.

I would like to automate as much of the process as possible, which is
why I want to import the file using VBA code. I have so far written
VBA to read the input text file and strip the "K". Now I have to
finish the Insert Into. And then figure out how to run this (have
been doing it in the Immediate window) - can anyone tell me how to
run it? (in a query within a macro?). Not sure if this is the way to
go, but am playing around with it.

My code so far is:

Sub OpenFile()

Dim hFile As Long
Dim strFileIn As String
Dim strTeacherID As String
Dim strFirstName As String
Dim strLastName As String

hFile = FreeFile

strFileIn = "C:\TestFileExport.txt" ' I shorted this
for simplicity

Open strFileIn For Input Access Read Shared As hFile

Do Until EOF(hFile)
Input #hFile, strTeacherID, strFirstName, strLastName

'Srip the 1st character of Teacher ID, which is a "K"
strTeacher_ID = Mid$(strTeacherID, 2)

Dim strSQL As String
strSQL = "INSERT INTO TESTTBL (teacher_id, firstname, lastname" _
& "VALUES (strTeacherID, strfirstname, strlastname)"
(I am working on this part now...)
Loop

Close hFile

End Sub

THANKS!

Ignoring what I said about the linking approach, you could finish that
code and probably make it work by modifying the SQL statement like this:

strSQL = _
"INSERT INTO TESTTBL (teacher_id, firstname, lastname) " & _
"VALUES (" & strTeacherID & ", " & _
Chr(34) & strfirstname & Chr(34) & ", " & _
Chr(34) & strlastname & Chr(34) & ")"

That's assuming that Teacher_ID is a numeric field, and the name fields
are text. And then you'd have to actually execute the statement:

CurrentDb.Execute strSQL, dbFailOnError

That said, you could do this the import in VBA using the linking
approach I outlined, like this:

'----- start of alternative code -----

DoCmd.TransferText acImportDelim, , "tmpImport", _
"C:\TestFileExport.txt"

strSQL = _
"INSERT INTO TESTTBL (teacher_id, firstname, lastname) " & _
"SELECT IIf(Field1 Like "K*", Mid(Field1, 2), Field1), " & _
"Field2, Field3 " & _
"FROM tmpImport"

CurrentDb.Execute strSQL, dbFailOnError

DoCmd.DeleteObject acTable, "tmpImport"

'----- end of alternative code -----

 

[lorirobn]

Excellent, Dirk!
I got it working, using the TransferText to do the Import (a new
command for me), and the INSERT INTO the temp file. It works great,
and I like it better than my input file method.

What is the best way for me to execute this (weekly)? Currently, I
have it in a VBA module, as a Sub OpenFile(), but I don't think this is
correct. I have been executing it from the Immediate window. Should I
do a 'runcode' from a macro, and make this a function?

THANKS so much for your help......Lorirobn

 

[Dirk Goldgar]

Excellent, Dirk!
I got it working, using the TransferText to do the Import (a new
command for me), and the INSERT INTO the temp file. It works great,
and I like it better than my input file method.

Great! Actually, I made a small mistake, in that the code I posted will
import the file to table "tmpImport", when what I meant to do was link
the file. Try changing this line:

to this:

DoCmd.TransferText acLinkDelim, , "tmpImport", _
"C:\TestFileExport.txt"

There's no need to bring the text file fully into the database, just to
append it to the other table.

What is the best way for me to execute this (weekly)? Currently, I
have it in a VBA module, as a Sub OpenFile(), but I don't think this
is correct. I have been executing it from the Immediate window.
Should I do a 'runcode' from a macro, and make this a function?

I would call a procedure like this from the Click event of a command
button on a form. That's because most of my applications have one or
more forms that serve as menus of functions, each function triggered by
a command button. Or you might add a custom menu item to do it; or
both.

 

[lorirobn]

Dirk -
This is really great. I have been working with Access for 2 years now
and have never used macros. I am excited to learn them finally and
also about the unlimited possibilities this opens up in database use.
I did as you suggested: created a command button, put the link to the
temp file, and voila - it's working beautifully. Thank you so much!
Lorirobn