Get DB Data From Folder

[HarrisonBeck]

Hello,

Is there a way to do this? Power BI and excel can both get data from a folder, not just a file. I'd like to be able to get the data from the most recent file added to a folder when the DB is refreshed. Do I need to write this in SQL?

Thank you,

Harris 

[#2]

@HarrisonBeck 

What do you mean "...get data from a folder..."?

In Windows, data is in files, files are in folders.

If you can explain more clearly what it is that you have in that folder, we could attempt some suggestions.

That said, Access is very powerful in importing data from a large variety of data sources, so I would imagine what you want is highly doable, we just need a clearer picture of that data....

[#3]

Hello @George Hepworth,

Thanks for getting back to me. What I am trying to do, is connect to the folder and use Power Query Editor in order to load into Access the most recent file regularly dropped into that folder. Both PowerBI and Excel have the functionality in the picture below.

From what I was googling, you can use SQL to do this, but I'd like to avoid that if possible. I'm trying to show my co-workers they can do this instead of copying and pasting data. Currently, I have a linked table set-up (Access to Excel), and I am pulling data from a folder in Excel.

Let me know if you need additional info.

Thank you,

Harris

[#4]

@HarrisonBeck 

What FILES are in that folder to which you are referring. You are importing FILES, not FOLDERS. I imagine they are .csv or .xls or .xlsx FILES, are they not?

Or to put it more accurately, that dialog points you to a FOLDER containing FILES, and once you identify the FOLDER, the next dialog offers you a choice of the FILES in that folder.

And that is exactly the same thing Access does, albeit the dialog is slightly different. It looks like you are already doing it, in fact. 

"I have a linked table set-up (Access to Excel), and I am pulling data from a folder in Excel." To be more accurate, that data is coming from an .xls or .xlsx FILES in that folder.

[#5]

Hello,

Our IT team dumps .CSV files into a folder (or multiple different folders) regularly. I'd like to connect to the folder so that I can pull the most recent .CSV file dumped into the folder. This is exactly what I'm trying to do, but in Access: "dialog points you to a FOLDER containing FILES, and once you identify the FOLDER, the next dialog offers you a choice of the FILES in that folder."

The linked tables I am pulling in is data set as a table within a .xlsx file. This works, but I'd like to connect directly into the source folder instead of using Excel as a bridge via a linked table.

[#6]

@HarrisonBeck 

Access doesn't work that way (nor does Excel really, as I previously noted). Once you browse to the folder, you still have to pick one file, .csv or .xlsx or whatever.

Also, you have revealed another critical detail not present earlier:

"The linked tables I am pulling in is data set as a table within a .xlsx file."

You MUST use the .xslx file to get to anything within that file, including that table. So, the whole request seems a bit unrealistic in that sense. In other words, that data set does not exist outside, or independently, or separately, from its container, which is the .xlsx file. Hence, the data has to be sourced via the .xlsx file.

I suppose you could save the data set in a different file format, but that presents the same circumstance. It would not be possible to have a persisted data set independently of the file containing it. Interestingly enough, disconnected data sets can be instantiated as recordsets in memory from within a relational database engine, but that's no help here.

You have to have a persisted dataset, laid out as a table, and that table must be in another container, i.e. a file, or in this case, the .xlsx file.

Is it not possible to create a permanently linked table from Access to the table in Excel? We do that frequently. The data in the table can be updated in Excel and that new data is reflected in the Access table.

[#7]

@George Hepworth 

Thanks for all the info. It looks like I'll have to have the Excel file make the connection to the folder, then used the linked table function in access to get the day. I'm going to perform what's described in your last paragraph. 

Thanks for the info!

[#8]

@HarrisonBeck 

At the risk of flogging a dead horse, Excel is NOT connecting to the folder either. It is connecting to a file (.csv file) within that folder. The data connection dialog specifying "Folder" requires that you browse to the folder and then pick one file from within it.

What you are doing is formatting that data in a table in Excel once you select it.

And that brings up another angle, which I should have thought of earlier. Sorry.

You CAN link Access directly to any csv file as well. So because the csv files are in the "folder", you could link Access directly to them and bypass Excel that way. I didn't think of that because it sounded like you are doing some formatting and maybe consolidating with the Excel table. But if it's just the same raw data, and all you need to do is import, or link to it, that's a possibility, too. If the .csv files change, of course, you'd have to relink to the newest set as needed.

[#9]

Hello,

I think we're saying the same thing but I'm not being clear enough. I'm intending to extract data from a folder, specifically the most recent .csv file dumped into a folder. Excel and Power BI allows you to use Power Query to connect to a folder, select the most recent file, and pull the data from that file into a table. It sounds like Access doesn't have this functionality.

Regarding your last paragraph, I don't want to connect directly to a .csv file because this is not an option "If the .csv files change, of course, you'd have to relink to the newest set as needed". For my needs, pulling the most recent file in a folder needs to be automatic.

Thanks for your help. I think I'm going to keep utilizing the linked table functionality for now.

[#10]

@HarrisonBeck 

This is where we keep talking past one another, I'm afraid.

"... specifically the most recent .csv file dumped into a folder."

You are NOT getting the data from the folder itself; you are getting the data from that .csv file which is inside that folder. 

The good news is that Access certainly does have that ability. It would be the same thing as linking to the Excel file. You can link Access to a very disparate variety of data sources,

You can automate the process of navigating to that specific folder, locating the .csv file with the most recent file date, and importing or linking to that file. It then becomes available to Access as a  linked table. I may have gotten the wrong impression about the need to put it in an Excel table. If the raw data is the same, then Excel is not useful as an intermediary step. If the data is transformed in Excel, which is the impression I got mistakenly, then it would be needed.

I don't have sample code at hand, but I have done similar tasks many times, Access supports VBA functions, such as Dir() which can locate files in folders. Here is an older download which does what you need to do, for example. 

[#11]

@HarrisonBeck 

I slept on this and decided a demo would be a good way to convey a better understanding of the tasks needed to accomplish what I think you want to do. It's not simple, but it is highly doable.

The attached zip file contains an accdb which has two basic functions:

1. Loop through all of the files in a designated folder (and in any subfolders under that designated folder) and list them in a table in the database by file name, path, date created and last modified date.  The list is displayed in a form which you can then use to:
2. Select any one of the files to link as text files (if they are .csv format files) or as Excel files (if they are .xlsx or .xls format files).

It will ONLY select those three file extensions, which is what I assumed you wanted from this discussion.

When the new files are linked as tables, the tables are named for the original file name as it was found in the folder and with the date of that file formatted as "YYYYMMDDHHMMSS" so you can track of previously linked files. 

PLEASE NOTE: Prior to using this you must specify the path to the folder where you want it to work, i.e. the folder on your network where the new .csv files and any .xls or .xlsx files are stored.

Otherwise it will fail. 

e.g. "G:\YourReportFolder" where G is a mapped drive on your network.

If you have trouble in this part, keep adjusting the folder so that it correctly identifies the right location.

[#12]

@George Hepworth Thank you for putting all of this together. I hope all of this wasn't for me and you had it already made. This id a lot of code. 

I tried running it but got this error:

Do you know off-hand what I can change in order to use this? Also, do you thnk this is a code that I can integrate into my in-use Accdb? I'd like to have a few tables within one DB update when I hit refresh. 

This concept is above my level of understanding. If this is too much to explain, don't worry about this. I appreciate your help thus-far. 

[#13]

@HarrisonBeck I did not realize you were using the 64 bit version of Office. I'll take a quick look. I'm trying to update all of my sample and demo databases for 64 bit, but that one is still in the queue. Unfortunately, I may not get to it right away today. 

[#14]

@HarrisonBeck 

Hm. This works on my own 64 bit Access, so I'm not sure where the problem lies, unless I uploaded the wrong version.

Here's the relevant code that error normally would apply to. It's already been updated in my 64 bit environment, though:

#If VBA7 Then
Private Declare PtrSafe Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)
#Else
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)
#End If

That is called in
Public Sub Sleep(ByVal lngMilliSec As Long)
If lngMilliSec > 0 Then
Call sapiSleep(lngMilliSec)
End If
End Sub

I don't think it's used in this demo, so maybe you can comment it out both the API Declaration and the Sub that uses it, and see if that works. 

No problem on the work involved. This is consolidated from two or three other working databases I've produced over the years, albeit combined here. I now use it primarily to search for image files to import into a website, but it originated with a window manufacturer who had daily csv file dumps somewhat similar to your requirement. (Hence my insistence that it would work.)

I intend to offer it as a download on my website as well, once I figure out what the issue is. As noted, I am not getting an error. I am using Access 2019 Professional 64 bit, so it should behave the same as Access 365 64 bit.

[#15]

I have the same problem and the replies have been incorrect. The above screenshot showing "Get Data, From File, From Folder" permits the bulk import of multiple files (all formatted the same) into Excel. In Access "External Data, New Source Data, From File..." does not have the "From Folder" option found in Excel. Is there a way to bulk import the contents of a folder into Access? Or, get the "From Folder" option?

[#16]

No, there is not and you'd find it unwieldy if there were.

Basic rule: Excel is not like Access. When using Excel, one has certain tools and functions available. When using Access, one has other tools and functions available.
While it would theoretically be possible to implement bulk imports from multiple worksheets into Access, the reality of how Access tables work would make that difficult.
You always have the option of automating Excel, though, using VBA to import multiple external data sources, such as a CSV or worksheet.

[#17]

It would be unweidly for MS Access developers but they could add "From Folder" to the dropdown and let it append data the same way so we end up with a 150 MM row table without a thousand import operations. Excel has a row limit and Access apparently doesn't.

[#18]

@gdrahal 

"... add "From Folder" to the dropdown ..."

I don't think that's in the cards for Access, for a number of technical and practical reasons. As I previously noted, VBA is a very powerful programming environment. You could create a procedure to append the records from one or more identically structured csv or xlsx files if you can be certain that they are actually consistent. I have had to do that for clients in the past, as a matter of fact. The key was a guarantee by the client that each csv would have the same columns, with the same names, in the same order, and with every record having the same datatypes in those fields.  NO variations permitted.

Access requires structured data for tables: the same fields in the same order with the same datatypes in each field (no mix of text and dates, for example). You simply can't guarantee that consistency in some sort of bulk import of all items in a folder. Not unless you control the files going into the folder in the first place.

However, adding records from files with different columns or columns in different orders, which is 100% possible in Excel, without the intervention of some logic that ensures compatibility, is a bridge too far for a generic process that just asks for one or more files.

It's a nice idea, but not worth investing development time and resources in pursuing. 

[#19]

On further consideration, I guess it could be possible to import multiple csv or single sheet workbooks into multiple tables in Access. And that would leave you only the task of validating the data in those tables and appending them to your master table. I'm not very optimistic it's going to happen, though.

[#20]

@George Hepworth No files are perfect but our files are identically formatted. Each 2+gb monthly folder has 67 county text files and there are a decade worth of monthly folders-all with the same headings, data type, etc. . In a perfect world, I could import a folder of folders of text files all in one fell swoop.