Importing a bulk of files from different folders

[jangobango]

Hello All,

I'm a newby here so not sure if this is the right forum page or if it needs to go in another.

I'm more of an excel guy than access, so fairly new to this stuff - have done some basic database work and queries in the past but usually stuff that can be taught to a monkey.

I have been given a folder - of which I'm pretty sure was designed for use with Access, I'm just not sure how to put it together.

In the folder (let's call it the Home Folder) I have a master file - in this master file is a list of districts & a unique ID. For each unique ID there is a folder (in the Home Folder) with the same name, and in each district folder, are 3 CSV files; services, financials & key people - they are all in the same table format & all names uniqueID_services or financials or uniqueID_key_people. So the layout is pretty handy in which it is fairly easy to connect them all - problem is there are 2000 folders & it will take way too long to place each individual unique ID table into one consolidated table in Acess.



The layout is pretty much;

HOME FOLDER
> UNIQUEID1
> UNIQUEID1_SERVICES.CSV
> UNIQUEID1_FINANCIALS.CSV
> UNIQUEID1_KEYPEOPLE.CSV
> UNIQUEID2
> UNIQUEID2_SERVICES.CSV
> UNIQUEID2_FINANCIALS.CSV
> UNIQUEID2_KEYPEOPLE.CSV
> UNIQUEID3
> UNIQUEID3_SERVICES.CSV
> UNIQUEID3_FINANCIALS.CSV
> UNIQUEID3_KEYPEOPLE.CSV
MASTER_FILE.CSV
[in master file]
UNIQUEID, DISTRICT
UNIQUEUD 1, DISTRICT 1
UNIQUEUD 2, DISTRICT 2
UNIQUEUD 3, DISTRICT 3



Now, as all the tables are in the exact same format - It's pretty simple to go into an access page and import the master file, and then create a blank table for services, financials and keypeople so that all the files should follow the same format. I just want to know if there is a way I can upload the whole library (even if code is the way forward I'm happy to go down that route) into Access in one go - perhaps using the master file as a reference point.

Hope this makes sense, I feel like this is a fairly simple problem but I just have no idea where to begin - any guidance would be much appreciated.

Many Thanks
JOBO

[ranman256]

I would make an import folder somewhere. Then at your top folder above, do a search on *.*.
copy all the files to the import folder. Then import all files from this folder to the tables, using the filename to post to the table.
i will post more when my PC (with the vb code) reboots.

[ranman256]

code to scan the import folder and import every file.
it overwrites every file to ImportFile.csv which is linked in the db as a table.

Public Sub ScanAllFilesInDir(ByVal pvDir)
Dim vFil, vTargT
Dim i As Integer
Dim fso
Dim oFolder, oFile
Dim vSrc
const kTARG = "\\server\FOLDER\ImportFile.csv"
dim vCode, vType
On Error GoTo errImp
If Right(pvDir, 1) <> "" Then pvDir = pvDir & ""
sTbl = "xlFile"
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(pvDir)
For Each oFile In oFolder.Files
    vFil = pvDir & oFile.Name
    If InStr(vFil, ".csv") > 0 Then      
           vSrc = pvDir & oFile.Name 
           filecopy vSrc, kTARG 
           i= instr(oFile.name,"_")
           vCode = left(ofile.name,i-1)  'code on front of file
    vType = mid(ofile.name,i+1)   'services, financials, or keypeople
          select case vType
              case "Services_csv"
                docmd.RunQuery "qaImportServices"
              case "financials_csv"
                docmd.RunQuery "qaImportFinancial"
              case "keypeople_csv"
                docmd.RunQuery "qaImportPeople"
          end select
    End If
Next
Set fso = Nothing
Set oFile = Nothing
Set oFolder = Nothing
MsgBox "Done"
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
Exit Sub
Resume
End Sub

[jangobango]

Hi,

Thankyou Ranman, this is a very good code - just wondering if there is a more effective way. You see this folder I have recieved is (as part of a data trial) something that I will receive into my server on a daily basis - there are currently 20,000 files in the 2000 folders, and copying 330MB of data every morning just seems a bit inefficient, especially as I would like to utilize the fact that every folder can be mapped to the master file.

How I pictured the database to work is something like this;

A master table containing all the all the file names and a code that says something along the lines of;

For each UNIQUE ID in LIST (from 1st to Last)

Import UNIQUE ID n / SERVICES.CSV to SERVICES TABLE
Import UNIQUE ID n / FINANCIALS.CSV to SERVICES TABLE
Import UNIQUE ID n / KEY_PEOPLE.CSV to SERVICES TABLE

Next Unique ID

Is this possible ?

[ranman256]

You get 2000 files everyday??!! That's fine for archive, but you really need to get 1 file, with all the data in all those files.
then you only import 1 file a day.

[ssanfu]

Welcome to the forum, JOBO

I'm not really sure what you are trying to do. If it is import 20,000 CSV files, probably need to use code.

Not quite sure how many tables you want. One table for each unique service, financial and keypeople CSV file?
Or 3 tables, one each for service, financial and keypeople for all unique IDs?
And not sure where "MASTER_FILE.CSV" comes into play.


Can you explain what your process would be if you did it manually?


Would you post a couple of lines from "MASTER_FILE.CSV", and a couple of lines for each "UNIQUEID1_SERVICES.CSV", "UNIQUEID1_FINANCIALS.CSV" and "UNIQUEID1_KEYPEOPLE.CSV"?


This is confusing.

For each UNIQUE ID in LIST (from 1st to Last)

Import UNIQUE ID n / SERVICES.CSV to SERVICES TABLE
Import UNIQUE ID n / FINANCIALS.CSV to SERVICES TABLE
Import UNIQUE ID n / KEY_PEOPLE.CSV to SERVICES TABLE

Next Unique ID

If, as you say, the structure of the CSV files are the same, you can set up an import specification. It takes one line to import the CSV file

DoCmd.TransferText acImportDelim, "EmpImportSpecification", "tblAddsEdits", strFileName, True

but..... there is a lot of supporting code.

[jangobango]

2000 files a day is a pain - but I dont think they are able to change those requirements.

Apologies, that was my fault for the confusion (bad copy+pasting), I am looking for a seperate table for each file, so should look like;

Import UNIQUE ID n / SERVICES.CSV to SERVICES TABLE
Import UNIQUE ID n / FINANCIALS.CSV to FINANCIALS TABLE
Import UNIQUE ID n / KEY_PEOPLE.CSV to KEY_PEOPLE TABLE

We are currently using an excel spreadsheet to hold all the data, I am exploring access as an alternative as Excel is not a database tool and does not hold the data efficiently or effectively, but I am much more familiar with excel than access.

Currently we have 1 spreadsheet which is basically just a copy of the master file - we can import the master file in every day and we will be notified if there are any new districts.

We then have on our seperate worksheets a copy of the tables for Financials, Key People & Services. Each morning we run a macro that takes the list from column A (which is the Unique ID) and the code says something along the lines of ;

from range ( A1 to A(end) ) open file A1 / financials.csv and place data from *row 2, column 2* in cell B1, data in *row 2 column 3* in B2 ect....

Next dataset in range (A2)

This loops until the end, and eventually dumps all data from the 3 tables into the right place, just wondering if it is possible to do something similar on Acess

[ssanfu]

Just to be clear,

you get the data in Excel every day and export it to CSV files that you want to import into Access
or
you get the CSV files and import them into Excel (but want to use Access)?

This can be done with VBA and queries.


Have you created a dB yet?

Would you post samples:
a couple of lines from "MASTER_FILE.CSV",
a couple of lines from "UNIQUEID1_SERVICES.CSV",
a couple of lines from"UNIQUEID1_FINANCIALS.CSV"
and a couple of lines from "UNIQUEID1_KEYPEOPLE.CSV"?

[jangobango]

I get data sent the CSV files every day, and then import all the information to excel through VBA. But I'd like to test if it would be more efficient to use ACESS as VBA often cannot handle so much data.

Most of the information rarely changes, services & key people should never change so it just overwrites the information (but if something does change it will overwrite the previous data and create a seperate audit log of the changes made on that specific dataset) - financials on the otherhand change once a month (so I'm also generating a history of all monthly changes & that gives me a good timeseries)

This is an example of how the data looks.

MASTER_FILE.CSV

UNQUEID, REGION
IT00001, Italy
GB00001, Great Britain
US00001, United States
IT00002, Italy

IT00001/FINANCIALS.CSV
UNQUEID, REGION, SERVICE, HEADCOUNT, %TOTAL
IT00001, Italy, Service1, 10, 0.1
IT00001, Italy, Service2, 150, 0.7
IT00001, Italy, Service3, 35, 0.2

IT00001/SERVICES.CSV
UNQUEID, REGION, SERVICE, BUSINESS, PROFIT, EXPENSES, CURRENCY
IT00001, Italy, Service1, Business1, 100,000, 90,000, EUR
IT00001, Italy, Service1, Business2, 150,000, 50,000, EUR
IT00001, Italy, Service2, Business1, 50,000, 60,000, GBP
IT00001, Italy, Service2, Business2, 70,000, 70,000, GBP
IT00001, Italy, Service3, Business1, 225,000, 12,000, EUR
IT00001, Italy, Service3, Business2, 17,000, 150,000 GBP

[jangobango]

Just to be clear,

you get the data in Excel every day and export it to CSV files that you want to import into Access
or
you get the CSV files and import them into Excel (but want to use Access)?

Would Acess even be a good alternative to excel for these tasks ? I would like to continue using excel as the platform for running analyses, but is it worth migrating over to Acess if it is going to have just as hard a time handling so much data as Excel ? I'm just not that familiar with database systems.

[ssanfu]

The maximum size of an Access database is 2GB. If you need more that that, SQL Server Express maximum size it 10 GB.
I have an Access table that has over 2.5 million records. With all of the other tables, the dB size is still only 350 megabytes....

Access can handle magnitudes more data than Excel. Access can even perform the analyses. The down side is that it takes programming to do the analyses, where, using Excel, the analysis procedure can easily changed.

Importing the data into Access is as easy as importing into Excel.