Import comma-delimted text, add modifiers and append in VBA

  • Thread starter Thread starter Drew
  • Start date Start date
D

Drew

Hi All,

I will soon have a bunch of text files, each containing one record in
a comma-delimited file (they are the results of a detailed online
survey). My plan was to have command button that would automatically
import one file and append the record it contained to the main table.
This part is easily done with transfertext.

However, I would like to add the date when the record was imported
and, more importantly, the textfile name (to avoid importing the same
one twice). I have a set of functions that I use regularly that would
allow one to browse for the file and parse out the file name (thanks
to kind newsgroup member) which would be stored in a variable.

My question is, how do I get the filename from a variable in my
procedure added to the record before/while it gets appended to the
table? Can I 1)use transfertext to get the record into a recordset,
2)then add the new fields and values to the recordset, and 3)append
the recordset to the table? If so, how do I import to a recordset
from a delimited file and how do I add a new field to a recordset
(hey, I have avoided recordsets entirely until recently).

The other idea I had was to use transfertext to create a temporary
table, then have an append query ready to add the record from the
temporary table to the main table along with the date and file name.
The temporary table would have to get deleted every time.

Any ideas?

Thanks, and sorry this is so long. Drew.
 
Two ways:

(1) Import the text file into a temporary table, then run an append query
(to copy data to the target table) via VBA code that copies the imported
data and that uses the variables' values as the source of the two fields
(filename and date imported).

(2) Open a recordset to the target table (permanent table), open the text
file via VBA code, read each line of the text file and write that line's
data and the two variables' values into a new record for the recordset.
 
Ken Snell said:
Two ways:

(1) Import the text file into a temporary table, then run an append
query (to copy data to the target table) via VBA code that copies the
imported data and that uses the variables' values as the source of
the two fields (filename and date imported).

(2) Open a recordset to the target table (permanent table), open the
text file via VBA code, read each line of the text file and write
that line's data and the two variables' values into a new record for
the recordset.
Click to expand...

Or a third way, which is essentially the same as the first one above,
except that you would just link to the text file rather than importing
it, then run the append query, then delete the linked table.

Or a fourth way, which is essentially the same as the second one above,
except that you build and execute an inline SQL statement to insert the
values parsed from the text file, along with the variable values, into
the target table.
 
Or a fourth way, which is essentially the same as the second one above,
except that you build and execute an inline SQL statement to insert the
values parsed from the text file, along with the variable values, into
the target table.
Click to expand...

Or a fifth way, with a VBScript or Perl script to concatenate all the
one-record files into a single text file, adding the filenames and dates
as it goes. Then import the resulting file.
 
John Nurick said:
Or a fifth way, with a VBScript or Perl script to concatenate all the
one-record files into a single text file, adding the filenames and dates
as it goes. Then import the resulting file.
Click to expand...

I've even written an EXCEL macro in VBA that does combination of text files
into a new text file. Runs very quickly and lets user select the files to be
combined!
 
John Nurick said:
Or a fifth way, with a VBScript or Perl script to concatenate all the
one-record files into a single text file, adding the filenames and dates
as it goes. Then import the resulting file.
Click to expand...


Thanks everyone for your helpful (and fast) ideas. Now I will try to
make it work. Cheers, Drew
 
John Nurick said:
Or a fifth way, with a VBScript or Perl script to concatenate all the
one-record files into a single text file, adding the filenames and
dates as it goes. Then import the resulting file.
Click to expand...

Good one. You don't need VBScript or Perl to do that, really, since we
have VBA at our disposal. I'll grant it's probably simpler in Perl.
 
Back
Top