Looking to import daily files with variable names and append to a table...

[AccessN00bAlex]

I have a bunch of .csv files that dropped into the same folder everyday. The filenames are variable but the convention is always filename-mmddyyyy.csv.

I want to automate the process of importing these files and appending them to an existing table (while checking for duplicates.)

Here's the workflow. I'll open up the database and there'll be a form with a date picker. Once the date is selected, I want the import to run once I click a button, or a message saying the files have already been imported. As a complete n00b (see username) I have no idea how to even start. Any help would be appreciated.

Running Windows 7 64-bit and Access 2010 (also 64-bit)

[andy49]

How noob is noob?

Creating the following tables would start you off.

TblData to receive appended data

TblFiles to record file names dates etc


For example

TblData
Id autonumber PK
FIELDA
FieldB
Etc to match the csv layout

TblFiles

DateID AUTONUMBER PK
UploadID integer FK (linked to ID in table1)
Filename string
Dateupload Date/time




Sent from my iPhone using Tapatalk

[Micron]

Do you really want to append the files to a table or just their paths? Or just the file names? Storing files in Access tables is not a good idea.

Since the path is the same each day, decide on whether or not it would be better to store the folder path in a table or hard code it. The former would allow easy modification should the path change, the latter requires re-writing code. Then the process is basically, for each file name ending in .xlsx (or whatever) in "C:\SomeFolder" append (INSERT INTO) your table. The path field would be set to unique. Turning off warnings at the start of the operation would suppress any messages resulting from trying to add the file path a second time. If you don't care to know it already exists and just want to have it as a unique record, then why bother figuring out if it's already there? Just make sure you have a properly constructed error trap that ensures DoCmd.SetWarnings False is not left in that state.

There's lots of info regarding looping through the files in a folder. Here's one (not necessarily the best for you, but seems simple enough)
http://www.codevba.com/office/loop_f...m#.WNcknLi1vio

[AccessN00bAlex]

Thanks for the help. I want the user to specify the date to be imported by picking a date from a calendar on the front end. As I said, the csv's all have a date in their naming convention.

Once the hit the "run" button, access then grabs all the daily files and appends them to the appropriate table.

I know access isn't ideal but just a few of us will be using it.

[AccessN00bAlex]

If I'm not mistaken, what you're suggesting is having access through each file in a given folder, import it, then append unique records to a larger folder.

The problem is, there are probably 15-20 CSV files added daily. I suspect this would end up taking a long time to run in 6 months time. Apologies if I misunderstood.

[AccessN00bAlex]

This is what I think I need, step by step.

1. User selects a date from a calendar and hits a "Run" button.
2. There is a table to generate the filenames to be imported. Field 1 is the report name "Apples" and Field 2 is the date selected in step 1, formatted as "mmddyyyy"

Field 3 is a concatenated Field 1 & 2, eg "path\apples04062017.csv"

3. Access will then import each filename generated in Field 3 and append each one to its own YTD table. Eg, apples04062017.csv will get appended to tbl_applesYTD.

4. At this point I'll have queries and macros to build the daily reports I'll need.

Does this make sense?

[andy49]

Makes a lot of sense. Problems?

Although why even have field3. If it's just a value you could have in a variable


filename = filepath & "/" & [field1] & cstr([field2]) &".csv"


Sent from my iPhone using Tapatalk

[AccessN00bAlex]

lol--the problem is I don't know how to do it.

[andy49]

Ok do you have the table already.

Everything after that is "easy" and we'll help all the way.

https://support.office.com/en-gb/art...a-c08f65928f84

Start here.


Sent from my iPhone using Tapatalk

[AccessN00bAlex]

>>Ok do you have the table already.

>>Everything after that is "easy" and we'll help all the way.


I'm familiar enough with Access, just not VBA. I have the tables, a YTD table "tblExampleYTD" that data gets appended to, and a table that has all the reports I need imported "tblTempReportList". **This last table will be a list of the csv files to be imported, and includes fields for the report prefix, the path, a date "mmddyyyy" and a concatenated field of all of these values.

I have a form created with a text box where a user selects a date. I want it so when the user hits a button, the date value in the text box gets passed to the ImportDate field in the "tblTempReportList".

My next step will be to create a loop that looks at the concatenated field, and imports and appends each csv to the appropriate YTD tables...but one step at a time

[andy49]

Ok design a query which only returns the values you need based on the text box.


Sent from my iPhone using Tapatalk

[AccessN00bAlex]

Correct...I'm just not sure how to pass the value the user selects in the text box so it populate a field in a table. For instance:

Fields	Record
ReportPrefix	Apples
Extension	.csv
DatePickerValue	04042017
Path	c:\reports\
ConcatenatedValue	c:\reports\Apples04042017.csv

The table has 15-20 records...I want the value from the datepicker passed to the "DatePickerValue" field in the above table...I'm not sure how to do this. I've tried the following code to see if I can assign the text box value to a variable but it won't work.

Public Sub PassTempImportDate() 'According to my vba for dummies book, I have to name the procedure ;>
Dim ImportDate As Date 'This is the variable name I chose
ImportDate = TempImportDate 'TempImportDate is the name of the text box
MessageBox.Show (ImportDate) 'I just put this in there so I could see if it worked when I pasted it in the immediate window. It didn't.
End Sub

[andy49]

Private Sub Calendar0_Click()
Me.Text1.Value = Calendar0.Value
End Sub

I've used calendar0 as my calendar and text1 as my textbox