import csv 1

[autex]

Ok, I have a csv in a standard format, it always has the same fields. We always have to do the same routine of complicated sorts and deletes in excel to get certain counts. I want to automate this process. I was thinking access would be the best place to do this.

I can import the csv into a table in access. I realize I can have it go into a new table or into one I've already created.

My problems are these:
1. I want to automate the import process. I don't want the user to have to choose where the csv gets imported to or the details of the importing process. Can I automate this?

2. Once the sorting is done and the user get's their magic "counts" I want to delete the table that was just created, if the new table always had the same name I think this would be simple, but if two users were using this at once I imagine I would have two different table names.

Any advice?

 

[HitechUser]

autex,

Use the TransferText method.

Here is a sample line..

DoCmd.TransferText acImportDelim, , "Accesstest", "c:/temp/AccessTest.csv", True

Also you can delete your table object (see help text)

I hope this helps...

 

[Dirtbike]

I do this all the time. First I FTP my files from the UNIX system to a DIR in Windows. (you could Xcopy of something)The FTP overwrites the previous file. Then in Access I link to that .CSF file once. Then I query the link file and make a table. Now I've got "fresh" data in a format that worked fast in Access. When i'm through slicing and dicing the data I have my users press a button to output to excel. Works great. \
I have code to "wait for the DOS shell to complete" if you want...otherwise Access won't wait for the DOS stuff to be done before it starts with it's chores.

Scott in Charlotte, NC

 

[autex]

thanks, first I've heard of acimportdelim. Dirtbike you are way above my ability.

 

[autex]

I'm getting a runtime error 3709
the search key was not found in any record?\

 

[autex]

Oh, i'm sorry my bad this doesn't create a table it has to already exist.