Multiple external data files

Markhad

New Member
Joined
Jan 22, 2012
Messages
6
I am after some advice as the best way to tackle my requirements.

I have around 100 (and growing) data files which are currently all CSVs and each contain a couple of thousand records. I do not mind if these data files are changed or imported into Excel and kept that way instead. I want to be able to access the files to process them and to update them. I also want to run analysis on all of them - ie a page showing totals for each, comparing them etc. Finally, I want multiple users to be able to access different files at the same time. I am happy for each individual data file to be locked but other users should be able to access to other 99. I am reasonable competent with VB so can automate processes.

I can think of 2 main ways of doing this.

1. Create one large Excel doc with a sheet per data set which is where the data would then be kept.
My concern with this is that the Excel doc with so many sheets would become unwieldly and quite heavy. It would also make it tricky for multiple users to work on the document as I’ve never really got my head around the sharing feature in Excel where the same doc can be open on multiple computers

2. Create some sort of skeleton read-only Excel file containing no data that sets up queries to each of the CSV files. Each user can the open the Excel doc, import a CSV file to work on and update and then export when done. I am not sure how to prevent multiple users accidentally working on the same CSV file and their updates wiping out the others? It also means, presumably, that I lose any ability for the skeleton file to hold any updated information as it is read only. Again, is having 100+ queries unwieldly?

Is there a third way I haven’t thought of? I don’t really want to learn a whole different system, eg MS Access even though it may be better suited to handling records this way.

Thanks in advance.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Markhad

New Member
Joined
Jan 22, 2012
Messages
6
Can't seem to edit my post so adding on....

I had thought of a third way of basically creating an Excel file per data set - ie 100 Excel files each containing the data in a sheet. My concern with this is I am not sure of an efficient way of comparing all the data - I would need an analysis Excel doc that reads the data from all 100 other excel docs. It also means if I want to add functionality, I have to update all 100 docs.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,882
Messages
5,627,453
Members
416,248
Latest member
inese_green

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top