Import Automation and...

[Chris via AccessMonster.com]

I have deployed an app in the app is a form that imports all files in a directory. Stores the files as tables with the tablename as the filename. Works great! I would like to take this one step further in that certain files contain different variations of a larger group for example a group is EB33. My function now would import the file and I would have a table name EB33.

In that file can contain two different variables of that call them EB33A and EB33b. Right now I would have to eventually create a seperate table for each. I would like to make two tables from that one import EB33A and "B". Meanwhile I still have mutliple files to import. Here is the code I am currently using for importing all files.

Private Sub Command0_Click()

'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String
Dim tblName As String

ChDir ("c:\Email_Promo\Lists")
strfile = Dir("*.csv")
Do While Len(strfile) > 0

'table name same as file name without ext
tblName = Left(strfile, (InStr(1, strfile, ".") - 1))

DoCmd.TransferText acImportDelim, "ImportFiles", tblName, strfile, True

strfile = Dir
Loop

End Sub

 

[John Nurick]

Hi Chris,

It sounds as if you've got data something like this

Field1,Field2,Field3,Field4
A,1298,Something,Something else
A,4355,Text,Other value
B,4358,Value,Blah blah
A,9776,Stuff,Nonsense
B,6554,Football,Goldfish

and you want to import all the records with "A" into one table and all
the records with "B" into another. In normal database practice this is
always undesirable and almost never necessary. Instead, import it all
into one table and create two queries, one that returns only the As and
one that returns only the Bs.

I have deployed an app in the app is a form that imports all

files in a directory. Stores the files as tables with the tablename as
the filename. Works great! I would like to take this one step further in
that certain files contain different variations of a larger group for
example a group is EB33. My function now would import the file and I
would have a table name EB33.

In that file can contain two different variables of that call them

EB33A and EB33b. Right now I would have to eventually create a seperate
table for each. I would like to make two tables from that one import
EB33A and "B". Meanwhile I still have mutliple files to import. Here is
the code I am currently using for importing all files.

 

[Chris via AccessMonster.com]

It gets a little more complicated than that. The A and B lists always have
variable information in front of the letter in your example...

Field1,Field2,Field3,Field4
A,1298,Something,Something else
A,4355,Text,Other value
B,4358,Value,Blah blah
A,9776,Stuff,Nonsense
B,6554,Football,Goldfish

Field1 can have variable control information infront of A and B for example

Field1
EB33A
EB34A
EB33B
EB39B... etc.

So on any given day I have 50 variabled grouped records and about 400,000
records or more. If there is a way to make the variable the criteria field
in a make table query I am all ears, as well as using that variable to make
the table name and still be an automated one button process.

Thanks

 

[John Nurick]

Chris, I'm suggesting that it's not necessary to import the "EB??A" and
"EB??B" records into separate tables. If you do so, you are in effect
storing data in the names of the tables, which is never a good idea with
relational databases.

Instead, import the data into a single table. Any time you need to
retrieve a subset of the data, use a query. For instance,
SELECT * FROM MyTable WHERE Field1 Like "*B";
will return EB33B, EB39B etc. but none of the As.

 

[Chris via AccessMonster.com]

Maybe I need to explain what I am trying to accomplish better. At my
company we send out email promotions the promotions are assigned a source
code this source code (i.e. EB33A, EB33B) is a group of emails or a list
based on a market people fall under.

These lists are then stored in a database and are pulled into our email
client and sent out. This whole process is done via the web application my
company has and marketing sets this up. All my department is responsible
for is pulling these lists together and putting them in a SQL database with
the names of the tables same as source code. There really is now relational
or heavy database processing going on. In the list files (.csv files) come
in a few big files then we have to seperate them out according to source
code.

Here are the challenges...
-In the list files (.csv files) come in a few big files then we have to
seperate them out according to source code.
-They don't get grouped by "a" or "b" it goes into the whole source code
"EB33A" or "EB33B" or "EB39A" all need to be in their respective tables.
-I can't set up static queries because we never use the same source code
twice. So that needs to be a variable.

Right now we are doing this manually and we have anywhere from 30 to 40
promos a day and takes about 45 minutes to an hour to do.

This is a smaller piece to a larger process that I am trying to get down
from 4 hours a day to 30 minutes and this is the remaining piece.

 

[John Nurick]

Things are getting a little clearer. Going back to your example, you
have a csv file called (say) EB33.txt, whose first field contains values
such as "EB33A", "EB33B" and so on. You want to end up with all the
"EB33A" records in a table called "EB33A", and all the "EB33B" records
in a table called "EB33B". Of all the files you import, some are like
this (though each will have different values in place of "EB33A" etc.);
but others contain only a single "group" of records.

A few more questions:

1) Do you know in advance, or can your code tell from the filename,
which files contain only one group of records, and which, like EB33.txt,
contain two or more and therefore need to be "split" into more than one
table? If not, it means your code will have to parse the file to
discover how to treat it.

2) In the case of files that need to be "split", do you know in advance
what the groups are (e.g. EB33A and EB33B) or is it necessary for your
code to read the file and identify all the distinct values in the first
field in order to process it?

3) Do the text files have a header line containing the field names? If
so, are the fields and their names always the same, or does the
structure vary from one file to another?

One general approach would be to have your code construct and execute a
series of SQL make-table queries to import selected records directly
from the text files, using this syntax:

SELECT * INTO EB33A
FROM [Text;HDR=Yes;Database=C:\Folder\;].EB33#txt
WHERE Field1='EB33A'
;

You'd construct this in a string variable, concatenating the fixed bits
such as "SELECT * INTO " with the variable ones such as strFileName
(="EB33" in this case), and then use the DAO or ADO .Execute method to
run the query.

If you have to read the first field to get the table names, you could do
something like this pseudocode:

For each file in folder
Open Recordset "SELECT DISTINCT Field1 FROM textfile"
Do While Not Recordset.eof
Construct and execute make-table query using
value of Recordset.Field1 (e.g. EB33A)
Recordset.Movenext
Loop
Recordset.Close
Next file

 

[Chris via AccessMonster.com]

Ok, I definetly think we are closer to the same page... the answers to your
questions...

1) Do you know in advance, or can your code tell from the filename,
which files contain only one group of records, and which, like EB33.txt,
contain two or more and therefore need to be "split" into more than one
table? If not, it means your code will have to parse the file to
discover how to treat it.
-I do not know in advance what will be in there. The file will have a
general name like "Market.csv". In other words I would have to grab all
files in one directory.

2) In the case of files that need to be "split", do you know in advance
what the groups are (e.g. EB33A and EB33B) or is it necessary for your
code to read the file and identify all the distinct values in the first
field in order to process it?
-The code should read the file to find the values

3) Do the text files have a header line containing the field names? If
so, are the fields and their names always the same, or does the
structure vary from one file to another?
-The text files don't have field names however I can change that. The field
names would be static like Field1..etc. as there are only 4 fields. The
structure is always the same that I can control.

I am going to try one of the sample codes you had below as I think it's the
one that applies.

For each file in folder
Open Recordset "SELECT DISTINCT Field1 FROM textfile"
Do While Not Recordset.eof
Construct and execute make-table query using
value of Recordset.Field1 (e.g. EB33A)
Recordset.Movenext
Loop
Recordset.Close
Next file

If you can provide anymore info I would appreciate it. I am experienced
with Access but not as much with VB.

 

[Chris via AccessMonster.com]

John I just wanted to let you know I went to try it but variables always
give me a hard time. Like in the code for text file how do I call that
variable as well as tblName I am still not quite there. I also have never
used the For Each... statement as well.

I really appreciate you sticking this one out with me. Thanks

 

[John Nurick]

Well, it would be something like this, which is air code (i.e. one stage
better than pseudocode):

Private Sub cmdImport_Click()
Const EXT = "csv"
Dim strFolder As String
Dim strFileName As String
Dim strSQLGetGroups As String
Dim strSQLMakeTable As String
Dim rstR As DAO.Recordset

strFolder = "C:\My Folder\"

strFileName = Dir(strFolder & "*." & EXT)

'Outer loop, once per file
Do While Len(strFileName) > 0

'trim extension off filename
strFileName = Left(strFileName, _
Len(strFileName) - Len(EXT) - 1)

'Construct query to get distinct values from first field
strSQLGetGroups = "SELECT DISTINCT F1 FROM " _
& "[Text;HDR=No;Database=" & strFolder & ";]." _
& strFileName & "#" & EXT & ";"
Set rstR = dbEngine(0)(0).OpenRecordset( _
strSQLGroups, dbOpenSnapshot)

'Inner loop, once per group within file
Do Until rstR.EOF
'table name is in first field,
'construct make-table query
strSQLMakeTable = "SELECT * INTO " _
& rstR.Fields(0).Value _
& " FROM [Text;HDR=No;Database=" & strFolder & ";]." _
& strFileName & "#" & EXT & " WHERE F1 = '" _
& rstR.Fields(0).Value & "';"

'*** You may want to include code here to handle the
'situation where there is already a table of this
'name

dbEngine(0)(0).Execute strSQLMakeTable, dbFailOnError
rstR.Movenext 'next group
Loop
rstR.Close
Set rstR = Nothing

'Rename or move processed file so it's not
'done a second time
Name strFolder & strFileName & "." & EXT _
As strFolder & strFileName & ".DONE"

strFileName = Dir() 'get next filename
Loop

 

[Chris via AccessMonster.com]

Thank You so much for your help, I can't express this enough. This works
just like I had wanted it too.

I was just wondering...

You left a hole in the code where you said
'*** You may want to include code here to handle the
'situation where there is already a table of this
'name

I would actually like to have it append to an existing table if the name
already exists the only constraint is that a primary key is defined on the
first table as I can't have duplicate email addresses. (We don't want to
send the same promo twice to one person).

I have found away to assign primary keys but I have not been able to fit it
in the code for it to work.

 

[John Nurick]

In that case I'd do it slightly differently inside that inner loop:

1) If there's no existing table, construct and execute a create-table
query (this is different from a make-table query, it creates an empty
table and lets you specify the primary key and stuff, which a make-table
query doesn't).

To find out whether the table already exists, you can use the function
at http://www.mvps.org/access/tables/tbl0001.htm.

The query will look something like this. Look in Access Help for
"Microsoft Jet SQL Reference", and in that for "Data Definition
Language".

CREATE TABLE TableName (
F1 CHAR (8),
F2 CHAR (255) CONSTRAINT PrKey PRIMARY KEY,
F3 CHAR (255),
F4 LONG
);

2) Now, wherever you started, there's a table to append to - so you
construct and execute an append query instead of a make-table query. It
will look something like this (both this and the CREATE TABLE sample
above assume that the second field is the one you want to be the primary
key:

INSERT INTO TableName
SELECT * FROM [textfile specification]
WHERE F2 NOT IN (SELECT F2 FROM TableName);