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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,746
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
53,746
Office Version
365
Platform
Windows
You are welcome!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,907
Messages
5,489,659
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top