Module To Import Text File Into Access Table

[rreinhardt1]

Im pretty new to VBA and I was hoping someone can point me in the
right direction, here is what I am trying to do.
I have a text file that follows a specific format, and I need to strip
the text file and import the info into a table in access. So field 1 =
Row 1 Spaces 1-5; field 2 = Row 1 spaces 6-10 and field 3= row 1
spaces 12-14 and so on, if that is possible then can I automate the
module to process the whole text file, say the next set of data begins
every 60 rows and thus field 1 = row 61 spaces 1-5. Any help is
greatly appreciated

-Richard

 

[Ken Snell [MVP]]

Check out TransferText action (can be run from a macro or VBA code) in Help
file. You can set up an Import Specification to control the way the data are
imported as well.

 

[rreinhardt1]

I was looking into using the import specification to take care of this
but the file that I am importing has 60 rows, its similar to an
invoice were row 1 spaces 1-25 is the name, row 2 1-25 is the address,
and row 3 1-25 is the city state and zip,and row 1 50-65 is the date
and the import function from what I can tell cant handle that so I
need to find away to specifiy each block of information and add it to
its own field and then somehow find a way to repeat the process as the
text has will contain more than one invoice to import

 

[Ken Snell [MVP]]

That structure will not work with TransferText, with or without an Import
Specification. Your original post indicated that all the data were in one
line.

Your only choice will be to open the text file in VBA code, read each line
the way it needs to be read, and write the data into a recordset that is
bound to your table. Check out Open Statement, ReadLine, and Close Statement
in VBA Help files for a starting point. Essentially, you'd open the text
file, read an entire line into a string variable, parse out the data using
Left, Mid, or Right functions, and write the data into fields in a recordset
that you open on your table.

Post back if you have questions.

 

[rreinhardt1]

How would I go about handling the field that will list the charges,
not ever invoice will have the same number of charges, would I need to
add some sort of loop to check for that or can you recommend something
Thanks
Richard

 

[Ken Snell [MVP]]

Without seeing the format of different invoice text files, it's not possible
to give a definitive answer. You can start on the answer to the question by
writing down how you, doing a manual inspection of the data, would know when
you've found a new charge and the previous charge item is "ended" in the
file. That identification then would need to be programmed into the code
that parses/reads the text file data.

 

[rreinhardt1]

here is a link to the format of the bill http://www.scribd.com/doc/6437317/CMS-1450-UB04
Boxes 42-49 can have up to 9 pages of 22lines each, the text file is
formatted so that it fills in the blanks when it is printed. Also to
parse all of the data from the text file do I have to have a variable
for each individual box 1-80 (this is how I am writing the code, I
dont know if there is a more efficient way to achieve this)
Thanks
Richard

 

[Ken Snell [MVP]]

That image does not tell us any information about the format of the text
file that you're trying to read. We need to know or see how the text file is
set up. You're reading the text file, not the invoice image.

 

[rreinhardt1]

I believe I have solved the issue with the reading the text line by
line and and using the left, mid right functions to store the info
into variables, however the code that I have is so long it no longer
fits on one line, do you how i can continue the code or maybe make it
more efficient, i will try and post some code here for you to see

Do While Not EOF(1)
Line Input #1, Textline
strFL01 = Left(Textline, 25)
strFl03a = Right(Textline, 20)
Line Input #1, Textline
strFl01a = Left(Textline, 25)
strFl03b = Mid(Textline, 54, 24)
strFl04 = Right(Textline, 4)
Line Input #1, Textline
strFl01b = Left(Textline, 25)
Line Input #1, Textline
strFl01c = Left(Textline, 25)
strFl05 = Mid(Textline, 51, 10)
strFl06a = Mid(Textline, 61, 6)
strFl06b = Mid(Textline, 68, 6)
Line Input #1, Textline
strFl08a = Left(Textline, 24)
strFl09a = Mid(Textline, 42, 40)
Line Input #1, Textline
strFl08b = Left(Textline, 29)
strFl09b = Mid(Textline, 32, 30)
strFl09c = Mid(Textline, 65, 2)
strFl09d = Mid(Textline, 69, 9)
strFl09e = Mid(Textline, 78, 3)
Line Input #1, Textline
strFl30a = Right(Textline, 12)
Line Input #1, Textline
strFl10 = Left(Textline, 8)
strFl11 = Mid(Textline, 10, 1)
strFl12 = Mid(Textline, 14, 6)
strFl13 = Mid(Textline, 21, 2)
strFl14 = Mid(Textline, 25, 2)
strFl15 = Mid(Textline, 29, 1)
strFl16 = Mid(Textline, 32, 2)
strFl17 = Mid(Textline, 37, 2)
strFl18 = Mid(Textline, 43, 2)
strFl19 = Mid(Textline, 47, 2)
strFl20 = Mid(Textline, 51, 2)
strFl21 = Mid(Textline, 55, 2)
strFl22 = Mid(Textline, 59, 2)
strFl23 = Mid(Textline, 63, 2)
strFl24 = Mid(Textline, 67, 2)
strFl25 = Mid(Textline, 71, 2)
strFl26 = Mid(Textline, 75, 2)
strFl27 = Mid(Textline, 78, 2)
strFl28 = Mid(Textline, 81, 2)
strFl29 = Mid(Textline, 84, 2)
strFl30b = Mid(Textline, 87, 13)

CurrentDb.Execute "INSERT INTO tblTestA (FL01, Fl01a, Fl01b, Fl01c,
Fl03a, Fl03b, Fl04, Fl05, Fl06a, Fl06b, Fl07, Fl07a, Fl08a, Fl08b,
Fl09a, Fl09b, Fl09c, Fl09d, Fl09e, Fl10, Fl11, Fl12 Fl13, Fl14, Fl15,
Fl16, Fl17, Fl18, Fl19, Fl20, Fl21, Fl22, Fl23, Fl24, Fl25, Fl26,
Fl27, Fl28, Fl29, Fl30a, Fl30b) VALUES (" & " '" & strFL01 & "'," &
"'" & strFl01a & "'," & "'" & strFl01b & "'," & "'" & strFl01c & "',"
& "'" & strFl03a & "'," & "'" & strFl03b & "'," & "'" & strFl04 & "',"
& "'" & strFl05 & "'," & "'" & strFl06a & "'," & "'" & strFl06b & "',"
& "'" & strFl07 & "'," & "'" & strFl07a & "'," & "'" & strFl08a & "',"
& "'" & strFl08b & "'," & "'" & strFl09a & "'," & "'" & strFl09b &
"'," & "'" & strFl09c & "'," & "'" & strFl09d & "'," & "'" & strFl09e
& "'," & "'" & strFl010 & "'," & "'" & strFl011 & "'," & "'" &
strFl012 & "'," & "'" & strFl013 & "'," & "'" & strFl014 & "'," & "'"
& strFl015 & "'," & "'" & strFl016 & "'," & "'" & strFl017 & "'," &
"'" & strFl018 & "', (this is where the line ends and needs to
continue on the next line) " & "'" & strFl019 & "'," & " '" & strFl020
& "'," & "'" & strFl021 & "'," & "'" & strFl022 & "'," & "'" &
strFl023 & "'," & "'" & strFl024 & "'," & "'" & strFl025 & "'," & "'"
& strFl026 & "'," & "'" & strFl027 & "'," & "'" & strFl028 & "'," &
"'" & strFl029 & "'," & "'" & strFl030a & "'," & "'" & strFl030b &
"');"
Loop
There are a lot more values that I need to store so this will only get
longer
Thanks
Richard

 

[Ken Snell [MVP]]

Your entire code does not need to fit on one line. What you have is fine.

If the issue to which you refer is that your CurrentDb.Execute step cannot
fit onto one line, you can split it into separate lines that are
continuations (maximum number of continued lines is 24, so you can have one
code step that takes up 25 lines); the space plus underscore character
indicate that the line is being continued on the next line:

CurrentDb.Execute "INSERT INTO tblTestA (FL01, Fl01a, Fl01b, " & _
"Fl01c, Fl03a, Fl03b, Fl04, Fl05, Fl06a, Fl06b, Fl07, Fl07a, " & _
"Fl08a, Fl08b, Fl09a, Fl09b, Fl09c, Fl09d, Fl09e, Fl10, Fl11, " & _
"Fl12 Fl13, Fl14, Fl15, Fl16, Fl17, Fl18, Fl19, Fl20, Fl21, Fl22, " & _

(etc.)


Also, you should not use CurrentDb in the above step. Instead, it's best if
you declare a variable as a DAO.Database type, set that variable to
CurrentDb, and use that variable. It avoids all kinds of "out of scope"
issues.


Dim dbs As DAO.Database
Set dbs = CurrentDb
' code here
Do While Not EOF(1)
' more code here
dbs.Execute "INSERT INTO tblTestA (FL01, Fl01a, Fl01b, " & _
"Fl01c, Fl03a, Fl03b, Fl04, Fl05, Fl06a, Fl06b, Fl07, Fl07a, " & _
"Fl08a, Fl08b, Fl09a, Fl09b, Fl09c, Fl09d, Fl09e, Fl10, Fl11, " & _
"Fl12 Fl13, Fl14, Fl15, Fl16, Fl17, Fl18, Fl19, Fl20, Fl21, Fl22, " & _
(etc.)
' more code here
Loop
' before the procedure ends, insert this step:
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[rreinhardt1]

I've got it to work like I want it to thanks, but when I was imported
the text i got a Run time error 3075 syntax error (missing operator)
in query expression, I noticed that this happens when the text I am
importing has an ' is there a way around this
Richard

 

[Ken Snell [MVP]]

Yes. In general, you use the Replace function to double up a ' character
that is in a variable's value.

For example, you do the following to all your input fields that are text:

VALUES (" & " '" & Replace(strFL01, "'", "''") & "'," &

What the above does is replace a single ' with two ' ('') in the value. Use
that type of syntax for all your input variables from the text file reading.

 

[rreinhardt1]

Is there a way to add that function to an error handling of some type,
the ' doesnt show up in the same fields every time some times its part
of a name or part of an address or am i to just find all of the
possible fields and add the replace function to them

 

[Ken Snell [MVP]]

Just add that function to all your variables in your SQL statement build
code step. No harm is done if there are no ' characters in the variable's
value.

 

[rreinhardt1]

Is there a way to obtain the creation date and file size of the file
that is to have the data extracted and enter it into a table as sort
of a history log as well as add the date that the file was imported
into that history log table, i guess the table would have [file size],
[Creation date],[process date]
Thanks Richard

 

[Ken Snell [MVP]]

Check out the FileSystemObject in VBA Help. It has properties that tell you
information about files.

Or see this page:
http://msdn.microsoft.com/en-us/library/6kxy1a51(VS.85).aspx

Or this page:
http://msdn.microsoft.com/en-us/library/aa155438(office.10).aspx


You can use the FSO in VBA code to read the desired properties, and then you
can write those values into a table via a query or recordset.

 

[rreinhardt1]

Fantastic, I have one more question, is there is a way to get a
machines unique id and enter it in a database on a remote server and
have the program start out by checking that the unique id is still in
that remote database which would then allow the rest of the code to
run otherwise it wouldnt allow the code to run, I guess it would be a
security measure of some type, or is there a better way to go about
that
Thanks
Richard

 

[Ken Snell [MVP]]

When you say "machine's unique ID", do you mean the MAC address for the
machine? Or something else?

I have not done anything similar to what you're asking, but I would imagine
that there is a Windows API that can tell you the MAC address for the
machine in terms of the connection being used, and you could run code to
connect to another database, and use info from that other database to decide
whether to allow code to run in your database.