Append Date to Imported CSV

[ryancgarrett]

I have a pretty simple database that I use for basic reporting. Every day I download a csv report from a website that contains sales metrics from the previous day. There are 4 columns of data (A-D in Excel).


The Access table I import into has 6 fields (the first being an auto number record ID). The second field is ReportDate. The remaining fields match the csv fields.

Before importing the file I open in Excel, insert a new column to the left called ReportDate, and fill in the date that the data is from (i.e. today I input yesterdays date in Column A for all records). Then I can use the import wizard in Access to import into my database.


Is there a way to build my own import wizard in access that prompts the user to enter the report date, and then can append that to all records imported, instead of manually doing it in Excel beforehand?

 

[Joe4]

Sort of. Here is how I would do it.

First, do the following:Create a Macro that does the following:
1. Create an Import Specification
2. Create an Update Query that prompts the user to enter the date, and populates all records missing this field with that date

Then create a Macro that:
1. Imports the file (as per the Import Specification)
2. Runs the Update Query to update the records with the entered date

So then you would just run the Macro each time. If you need to make it more dynamic, you can use the "Convert Macros to Visual Basic" to convert those steps to VBA code, which you can then edit in VBA code. Then just call/run the VBA code (various ways to do that - via Macro, via button, etc).

 

[ryancgarrett]

Sort of. Here is how I would do it.

First, do the following:Create a Macro that does the following:
1. Create an Import Specification
2. Create an Update Query that prompts the user to enter the date, and populates all records missing this field with that date

Then create a Macro that:
1. Imports the file (as per the Import Specification)
2. Runs the Update Query to update the records with the entered date

So then you would just run the Macro each time. If you need to make it more dynamic, you can use the "Convert Macros to Visual Basic" to convert those steps to VBA code, which you can then edit in VBA code. Then just call/run the VBA code (various ways to do that - via Macro, via button, etc).

Great workaround. I'll start to build that out. Thanks!

 

[Joe4]

You are welcome!