Access2000: Which is the best way to add data from text file?

A

Arvi Laanemets

Hi

I use a program named Angry IP Scanner to scan computers in our LAN.
Scanning results are saved as text files, where results table is a fixed
width text column table (every column 22 characters wide) with a some
non-structured info at top (program name, scan date and time, empty rows)
and bottom (who scanned, empty rows again).

Hitherto I used an Excel application to read data in from selected text
file, to modificate them (replace all "N/A" and "N/S" with empty strings,
replace "Dead" in Ping column with a numeric value 9999, replace all other
entries in Ping column with ping value in milliseconds, etc.) and add to
excel table along with scan date and time read from text file header. This
works fine and is fast enough, but getting warious reports from this table
is different matter. As the table grows, the reports take more and more time
to recalculate. So I started to think about switching to Access.

Now maybe someone here has a good advice, which is the best way in Access to
read data from text files in? I can see 2 possible solutions:
1. Data are read into predefined empty interim Access table (text columns).
Abundant rows from top and bottom of table are deleted (I think the number
of abundant rows is always same). An append query reads data from interim
table, (adding date and time columns,) makes necessary modifications, and
adds them into final table, after that all rows in interim table are
deleted.
2. Data are read row-wise from text file directly, modified, and saved then
as memory variables. After that a row is added into final table with memory
variables as values. The cycle is repeated until all data rows are inserted.
(This is a solution quite similar to one used in my Excel application.)


Thanks in advance for every advice
 
N

Nikos Yannacopoulos

Arvi,

Both ways will work. I just want to add a third one (which assumes the
text file is alwasy in teh same folder and has the same name):

Link the text file;
Use a query on the linked file to filter out redundant rows and make
changes;
Append from the query.

HTH,
Nikos
 
A

Arvi Laanemets

Hi


Nikos Yannacopoulos said:
Arvi,

Both ways will work.

But which is better/faster? (There are n*254 datarows in one scan, usually
n=1)
I just want to add a third one (which assumes the
text file is alwasy in teh same folder and has the same name):

Every time data from different text file are added. All scan results are
stored in some folder - a text file for every scan, and I prefer not to be
limited to certain folder. A selection of scan files is read into
application and analysed. User can at will delete all data, and read in a
new selection of data, or user can add new data to existing collection. Text
files with original scan data are kept until they are manually deleted, when
considered too old. At least so works at moment my Excel application.
 
N

Nikos Yannacopoulos

Arvi,

Reading the text file line by line can be conceivably slower for very
big files, but for n*254, n being a single digit integer, I don't think
you'll be able to tell the difference.
This method also offers increased flexibilty in manipulating data, vs.
doing so in a query, so I think I would go this way. I would also use an
API call to invoke the Windoes File Open dialog and select the file to
be imported at runtime.
It would still be worth considering putting the imported data in an
intermediate table first, so you have the chance to review them before
you append them in the final table.

Nikos
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top