Import CSV question

[shmoussa]

Hi. I am fairly new to macros and visual basic. I hope someone can
steer me in the right direction.

Every week a new .csv file needs to be imported into one table of
information. (One table contains weeks of data) The .csv files are all
in the same file location.

Are any or both of these options available:

1.) A command button that when pressed will bring up an open dialog
box, starting in a specified folder location. The user will then find
the .csv file and click open and the data will be imported
automatically. Afterwards, a dialog box confirming that the import
process is complete should pop up.

AND/OR

2.) The user will click an IMPORT button and it will automatically
find the most recent .csv file in a specified directory and import it.
Afterwards, a dialog box confirming that the import process is
complete should pop up.

I need to know if and HOW both of the methods are possible.

If none are possible, can you think of any other ways to do this. It
needs to be as AUTOMATIC as possible.

I would appreciate a reply as soon as possible. Thank you.

 

[shmoussa]

PS: Before any Automatic imports are done- I need a popup which says
"Information for file modified on (DATE MODIFIED OF CSV FILE GOES
HERE) will be imported. The last date that was imported was on (LAST
IMPORTED DATE) goes here. Continue?" with a YES and NO button." I am
trying to avoid accidentally importing the same information from the
same CSV file by accidentally clicking the IMPORT button more than
once. I hope you understand.


I would very much appreciate if someone could guide me on how to do
this all of this.

 

[Steve Schapel]

Shmoussa,

This goes beyond the scope of what is possible with macros. Once you
start wanting to interact with external files, you need VBA procedures
to do this.

Here's a thought... Would it be possible to modify the conventions
relating to the csv files? So that the new csv file always has the same
name. It would surely simplify your import process. You could set up a
procedure (using the TransferText method) to import the data from the
csv, without the user needing to select the file name, and without
needing to verify that it wasn't processed twice, etc. Then, after the
import, you can use the Name method in your code to rename the file to
something suitable, leaving the way then open for the next csv to be
added to the folder. Make sense?

Either way, I would nornally recommend that the import be done in two
steps. First, import the data to a separate temporary table. And then
use an Append Query to move it from here, after any validation or
maniplulation, into the main table.

 

[shmoussa]

No. The naming convention is something I can not change. The file name
is basically the date that the file was generated. I appreciate your
response. I will look into using VB to do this. If you can help,
please let me know. I would appreciate it.

 

[Steve Schapel]

Shmoussa,

There is information on how to open the Windows File dialog, as per your
suggestion #1, at http://www.mvps.org/access/api/api0001.htm

I would suggest having a simple one-field one-record table in your
database where you update the name of the most recent import, as part of
the import procedure itself.

 

[Tom Wickerath MDB]

if you want to import from a CSV then you should be using SQL Server and
'Bulk Insert'

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

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.