Importing comma delimited .log files into Access tables

[Guy Hocking]

Hi there,

I am writing a database that is based around data logged from a server side
program, as .log files.

These file are comma delimited and import nicely into MS Excel.
However, i need to run reports and data analysis on the data, and Access is
alot more powerful and suits are needs.

Is there any way to import .log file data into an access table (it seems to
do .csv and .txt ok, but not .log?) and then write a macro that could
automate this process by clicking a form button.

I could do without manually importing it into Excel then doing a linked
table into Access.....

Any ideas?

Thanks in advance

--
GH

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

 

[Allen Browne]

You can use TransferText if the file type is registered with Windows.

Either register .log as a text type file, or rename the file to .csv (Name
statement), or copy it if that's better (FileCopy).

Access used to be smart enough to figure out how to do a TransferTEXT
regardless of the file extension, but Microsoft broke it back in Access 97.

 

[Guy Hocking]

Hi there,

Thanks for the responses.

Upon trying a TransferText statement, its says that the specification does
not exist.
I am using the following code -

****
Private Sub btn_Import_All_Files_Click()
Dim strfile As String

ChDir ("C:\Documents and Settings\My Documents\Text Reports\")
strfile = Dir("Bbs*.log")

Do While Len(strfile) > 0

DoCmd.TransferText acImportDelim, "Import_Text", "tbl_BBS_Logs",
"C:\Documents and Settings\My Documents\Text Reports\" & strfile, True
****

How do i create an import spec with a text (log) file, as Access keeps
crashing when i do this.

Does the log file have to have the table column names in it anywhere? this
is a pain as the log files are raw data from the software.
How can i import these without having to manipulate them?
I cant beleive MS make this such a farse to do, i really dont want to
Regedit every machine that uses the db, and copying them is just a pain.
Any ideas?

Thanks again

--
GH
www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

 

[Allen Browne]

You do not need to use an ImportSpec.

If you wish to create one, begin a manual import: File | Get External |
Import. After selecting the file name, click the Advanced button to create
the spec.

Please bear in mind that you will need to rename (of copy) the log file
before import (unless you register the .log file).

Column names are not required, but the format will need to be similar, e.g
you need a consistent number of columns in each row.

 

[Guy Hocking]

OK, thanks

so how can i import the data into an existing table - IE

Table:
col1 col2 col3 col4 col5

Data:
01/09/04, 0, bob, edith, 1

How could i import that data into an existing table?

(sorry if i appear nieve, i am new to this)


--
GH
www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

 

[Allen Browne]

To import into a table named "Table1":
DoCmd.TransferText acImportDelim,,"Table1", "C:\Somefile.txt", False

In practice, we generally import into a temporary table that has no
validation rules and only basic field types. We then run a series of queries
against the temp table to validate the data, e.g.
- Do the foreign key values exist in the lookup tables?
- Are the dates valid?
- Has the user already imported this log?

Once everything is fine, we then use an append query statement to write the
data to the true table. Often this involves a series of append queries to
write the flat-file data into the relational tables, so we wrap them in a
transaction for an all-or-nothing result. That's going beyond what you asked
for, but that's typically what's required.

 

[Guy Hocking]

Thanks for that,

I have successfully imported the data, Access seems to want to call the
column names F1, F2, F3 etc not sure if i can change the field names.

However the import creates a table with errors in it, how can i stop that?
The errors seem to be type conversion failures, not sure why....

Thanks again for you patience

--
Guy Hocking
MCSE

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

 

[Mitch]

Your not the first to have this problem. You could try to
re-name the file as part of a macro, then rename it back
when your done importing(if needed). Something like
rename *.log *.txt.

 

[Allen Browne]

The type conversion failures indicate that Access was unable to interpret
some of the entries in the columns as the anticipated data type.

You can delete the import error tables.

 

[Guy Hocking]

Hi there,

Thanks again.

One more thing - Is there any way of auto-deleting these import error tables
or stop them from appearing?

Thanks so much for your help. What a great service you guys provide.

--
GH

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

 

[Allen Browne]

Easy enough to delete the TableDefs or DROP the tables.