Append Date to Imported CSV

ryancgarrett

Board Regular
Joined
Jun 18, 2011
Messages
122
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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,212
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jun 18, 2011
Messages
122
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,212
Office Version
365
Platform
Windows
You are welcome!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,444
Messages
5,414,529
Members
403,532
Latest member
mikexcel12

This Week's Hot Topics

Top