can you conditionally import data into Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mikedibens - 29 Oct 2007 15:49 GMT

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.

Reply to this Message

'69 Camaro - 29 Oct 2007 20:52 GMT

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.

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.

Reply to this Message

mikedibens - 30 Oct 2007 13:37 GMT

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

> Hi, Mike.
>
[quoted text clipped - 24 lines]
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
> info.

Reply to this Message

'69 Camaro - 30 Oct 2007 20:39 GMT

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).

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.

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.

Reply to this Message