Manage a large amount of workbooks with CSV imported data

Hi everyone, I hope I have posted this in the correct place.

I just finished doing my bachelor thesis user tests on testing 2 different Heads Up Displays. I have all these wonderful CSV files chock full of great data, things like seconds inbetween keystrokes, key most used, total time etc.... I ran a test 2 times for every user for 16 users who completed 4 tasks each. So the API I was using output a CSV file for ever test. Each CSV file looks like this:

Please Login or Register  to view this content.

I then created a new workbook for every single CSV based on a template I made. I don't know if it was the most efficient way of doing it but I created this template:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

Time in Between Each Key Stroke (ms)

Total Time in Seconds

Total Time in Minutes

Average Time In Between Each Key Stroke (s)

Char or Command

Times Pressed

Key Most Used

Number of Mistakes

-

#N/A

#N/A

#DIV/0!

a

0

a

0

b

0

Then I just insert the CSV file using data from text. And all of the fields automatically update. Then I save the file, and re open the template, import the data again, and repeat...

The new workbook file then looks like:

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

Time (ms)

Hand Rotation

Left Thumb

Right Thumb

Tapped finger

Code

Mapped Output

Time in Between Each Key Stroke (ms)

Total Time in Seconds

Total Time in Minutes

Average Time In Between Each Key Stroke (s)

Char or Command

Times Pressed

Key Most Used

Number of Mistakes

14

C

1

0

0

C1000000

-

797,853

13,29

4,52

a

8

Backspace

64

150

C

0

0

0

C0000000

-

b

5

So now for each user I have 8 files:

U001_Task1_A1.xlsx
U001_Task1_A2.xlsx // Where U001 is the user code Task1 is the task # and A1 or A2 is for attempt 1 or 2.
U001_Task2_A1.xlsx
U001_Task2_A2.xlsx
E001_Task1_A1.xlsx
E001_Task1_A2.xlsx

I have as mentioned 16 users so I now have 128 workbook files. I still have all of the original CSV files as well just in case. I now want to start getting say an average time for all users from task 1 for example and its here that I feel hopeless. I tried importing all the Task1 A1 tasks from all users that start with U0 into one workbook. Then trying to make a template where I can get all averages... but its painstakingly slow. Is there some better way of linking my files? I have fairly limited experience with Excel and am desperate for suggestions tips or any help!!

This is actually an Access solution. Instead of dumping data into various multilple data packs...it should have been putting it in 1 single database. Then analysis would be simple. Is this possible?

Hmm, I suppose I could dump it into 1 database. Im not sure how I would structure it though. I thought it would be easier to maintain in separate files up until now. Each file has anywhere from 100 rows to 700 rows of data. I'll start trying to see what I can come up with. Any further tips or suggestions are most appreciated!

You can also dump into a single excel file (up to 1 million rows & change)
Analyze 1 file not several.

The problem would still be me having to go through and add all the files together, one after another. As far as I know anyways. I settled on coding a quick C# app in Visual Studio that parsed the CSV into a temp database and then just interpreted it from there.

In the future? I think I would still stick with a simple app. My tests created a new csv file for every task and there seems to be no easy way to merge 100's of csv files, that I know of anyway in excel. Thanks anyways for the help!