Importing a Tab Delimited File.

[Guest]

Hi all,

I am tryint to automate a business process and part of this is taking a
report generated by another system and importing it in to Access. Normally
this is done manually and works fine. I'm trying to use the file open dialog
to select the file then import the file in to a new table in Access. I am
using the OpenAsTextStream method but when I open the database there is only
one column with all header data appended in to one column.

My Question is:
How do I import a Tab Delimited File programatically in to Access.
Also I need to add a primary key to the new table...

Any help would be greatly appreciated!!

Thanks,

Jeff

 

[Guest]

Use the TransferText method to import the file. First set up an Import spec
by doing the import manually using File-->Get External Data-->Import. On the
import wizard, look for the Advanced tab. Set up the import the way you need
it and save it with a name. You will use this name in the TransferText
method when you do it programmatically.

If you need to add a primary key, assuming this primary key will be an
autonumber fields, define a table in your database that is the final
destination table for the data. Add a column that is the primary key.

Now, create another (temporary) table just like it, but without the primary
key.
Next, create an append query that will append all the data in the temporary
table to the final destination table. You can ignore the autonumber primary
key field, it will take care of itself.

Here is what you will now do in this order:

Delete all data in the temporary table
Do the TransferText into the temporary table
Run the append query to move the data into the final destination table.

 

[Guest]

Thank you for your help with this. I just have a quick follow up question...
How do you use the named import parameters with the OpenAsTextStream??

Thanks again!!

Jeff

 

[Guest]

You don't use the openastextstream. You use the TransferText method.
Go into your VB Editor and look it up in Help

 

[Guest]

Got it!! Thank you again!

Jeff

You don't use the openastextstream. You use the TransferText method.
Go into your VB Editor and look it up in Help