can you conditionally import data into Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a large number of text files (one per week). They are huge and I
only want to import records where a field is equal to a set value. If I
imported all the data from a couple of weeks it would break Access.
 
Hi, Mike.
I've got a large number of text files (one per week). They are huge and I
only want to import records where a field is equal to a set value.
Click to expand...

Use an append query to import only selected data into the table. For
example:

INSERT INTO tblData
SELECT *
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].Data.txt
WHERE (Dept = "Accounting");

You will also likely need a Schema.ini file to organize the data during
import. Please see the following Web pages:

http://msdn2.microsoft.com/en-us/library/ms709353.aspx

http://support.microsoft.com/kb/149090

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
69 Camaro,

I managed to use this technique without using the Schema.ini as you
discussed. I think this is more useful if you need to ensure the format of
fields is kept (eg, dates).

Now I need to work out how to run this against X (random number) of text
files in a directory. The current method would require me to rename the file
name reference in the query.

Many thanks!

Mike

'69 Camaro said:
Hi, Mike.
I've got a large number of text files (one per week). They are huge and I
only want to import records where a field is equal to a set value.
Click to expand...

Use an append query to import only selected data into the table. For
example:

INSERT INTO tblData
SELECT *
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].Data.txt
WHERE (Dept = "Accounting");

You will also likely need a Schema.ini file to organize the data during
import. Please see the following Web pages:

http://msdn2.microsoft.com/en-us/library/ms709353.aspx

http://support.microsoft.com/kb/149090

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
Click to expand...
 
Hi, Mike.
I managed to use this technique without using the Schema.ini as you
discussed. I think this is more useful if you need to ensure the format
of
fields is kept (eg, dates).
Click to expand...

Yes. It's for ensuring data types and columns with illegal characters in
the names are imported correctly .
Now I need to work out how to run this against X (random number) of text
files in a directory. The current method would require me to rename the
file
name reference in the query.
Click to expand...

If you put the SQL into a string within a VBA procedure, you can alter the
path and file name programmatically, then concatenate the strings together
to form the full SQL statement to execute.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
Back
Top