Seeking Advice

[dancat]

I have recently been assigned a project from my work and I am looking for some opinions on the best way to setup a new database.

I am not completely new to using access and have created several system that are used in my company on daily basis.

New project outline:

At present we receive an email every sunday evening from about 39 stores in the uk in a csv file, the information is collected from a third party software at each of the stores which counts how many visitors there are each day through each door in the store (this is recorded in 15min intervals). However not all stores have the same amount of doors, so some fields in csv differ from site to site.

The csv files are also named differently every week such as "S011 Glasgow to 01-03-2012 09.31.45" or "S013 Edinburgh to 01-03-2012 09.31.45" (you get the idea) as you can see it shows the store and the date the information sent was collected to.

Should I

A: create a different table for each store

B: create 1 table for all stores however there is no unique data in csv file i can use to match to another record in the database.

The general idea of the data base is to collate weekly figures of each store - generating a report every monday morning.

I have attached a copy of one of the csv files just incase you want to have a look.

Is it also possible to automate the import of all csv files in one go or will they need to be done individually as the csv filename changes every week

Thanks again

 

[Lightwave]

I would if at all possible import into one table... but I can see that if you can't solve the problem of multiple fields that may not be possible.

Have a look at this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=206738&highlight=multiple+csv+import

It should get you through the problems of importing multiple CSV files and create a field which names the stores.

You can then have a query that parses the first field and separates the name of the store with the date of the information. Assuming the file names follow an absolute standard naming convention.

The tricky part that I don't know exactly how you are going to tackle is the differing fields. This is a bit of an issue. My gut feeling is that it would be great if you could go back to the third party software supplier and get them to automatically produce csv which have a standard number and naming of column names irrespective of store (even if some have to be blank) with a standard store pkid included within the files. This might not be a great deal of work for someone who knows what they are doing because presumably they are calling a query in their database which exports the csv and then e-mails it. I suspect however you won't have access to those people.

If you can't get the columns standardized import into a single table won't work but you could import individual stores into their own tables and use these as intermediate tables to normalize data and then amalgamate the information into one table within access. The caveat being that for every store number of columns and naming will hopefully be fixed.

Whatever you choose the single table in the database should end up with a structure like this. This would also be the ideal spec to give to that third party.

PKID
StoreID
Date
TimePeriod
NumberInDoor1
NumberOutDoor1
NumberInDoor2
NumberOutDoor2
NumberInDoor3
NumberOutDoor3
NumberInDoor4
NumberOutDoor4
continue for same number of doors for your store which has the most doors you actually might want to put in a few extra doors in case you buy a truly massive store (future proofing).

 

[DavidAtWork]

I think into one table is the way to go as LW has suggested. If you don't need to analyse the daily counts by possible varying numbers of doors, then it's not a problem having a varying number of fields as you can just use a temp table which will have field names F1, F2 ... Fn. You'd have to set to False the HasFiledNames in the import and delete the first row of data, then you can then use this temp table to append to a master table and at the same time you identify it with the store (which will come from the filename), the date, the time period and just a totals In and Out. The problem only comes if analysis is wanted by varying entrance doors.
If you start with all csv files in the same folder, you can use the FolderSearch function to pick up all files and judging by the file names you have listed above, there does seem to be some similarity in the naming of the files, so you can extract the store id using string functions.
Using the master daily table, you could aggregate this up to a weekly/monthly table
David

 

[dancat]

Thanks guys information greatly appreciated

The fields are set during the setup of the individual store and the program that sends them cannot be adjusted, everything seems to be preset, at the very most there will be 14 columns in one of the csv files.

I have attached another file showing the most a file can have.

Not all fields are important I could get away with only using Date, time period, total in and total out.

Would this make things easier?

I could also rename the files prior to import to the name of the store only. Although renaming 39 files everyweek could turn out to be a bir tedious.

Does anyone have a link to an example database I could look at to achieve this, unfortunately as i said I have used access I have very little experience with vba code etc.

Thanks again