Importing and organizing my data into a database

[mgeerts]

I hate to make my first post an "I can't do this, help me do this" post, but here I am; apologies in advance for being a newb, I'll do my best to repay karma with interest afterward.

I have a data set from a series of tests that I've performed and I'm trying to analyze my results. I have been agonizing over this in Excel and I think I'd be better off trying to use a database application and Access seemed to be the place to start. Allow me to try to describe the data, which is located in CSV files.

Each CSV file is a Test (a configuration of the apparatus)
Each Test has 1000 iterations.
An Iteration is stored as one line with some summary data (5 comma separated summary values), followed by 100 lines of test results (3 comma separated values per line).

My goal is to have:
1) a table of test samples: SampleIndex, Iteration, valueA, valueB, valueC
2) a table of iterations: IterationIndex, Test, summaryValue1, summaryValue2, summaryValue3, summaryValue4, summaryValue5
3) a table of tests: TestIndex, "Test Parameters" (manually entered after import)

So in case I've said anything ambiguous, here's a rundown of the data structure said another way:
Each Test is referenced by 1000 Iterations and has a string attributed to it
Each Iteration is referenced by 100 Samples and has 5 summaryValues attributed to it
Each Sample has 3 values attributed to it

Besides the .csv's where each file is a Test I do have this broken up in an excel workbook where each Sheet is a test, but then the Iteration and Sample data is organized as 1 row describing the iteration summary and 100 rows describing the sampled results, repeated 1000 times. I can switch around the presentation of data however is best to get it into the database proposed above. My end-game is to be able to plot graphs of (for example) valueA vs valueB in Test1 for all iterations where summaryValue2 is "string", or some such manipulation.

Also, if I'm asking this question poorly, I apologize and I'm happy to clarify according to any criticism I receive.

Thank you!

 

[HiTechCoach]

I hate to make my first post an "I can't do this, help me do this" post, but here I am; apologies in advance for being a newb, I'll do my best to repay karma with interest afterward.

I have a data set from a series of tests that I've performed and I'm trying to analyze my results. I have been agonizing over this in Excel and I think I'd be better off trying to use a database application and Access seemed to be the place to start. Allow me to try to describe the data, which is located in CSV files.

Each CSV file is a Test (a configuration of the apparatus)
Each Test has 1000 iterations.
An Iteration is stored as one line with some summary data (5 comma separated summary values), followed by 100 lines of test results (3 comma separated values per line).

My goal is to have:
1) a table of test samples: SampleIndex, Iteration, valueA, valueB, valueC
2) a table of iterations: IterationIndex, Test, summaryValue1, summaryValue2, summaryValue3, summaryValue4, summaryValue5
3) a table of tests: TestIndex, "Test Parameters" (manually entered after import)

So in case I've said anything ambiguous, here's a rundown of the data structure said another way:
Each Test is referenced by 1000 Iterations and has a string attributed to it
Each Iteration is referenced by 100 Samples and has 5 summaryValues attributed to it
Each Sample has 3 values attributed to it

Besides the .csv's where each file is a Test I do have this broken up in an excel workbook where each Sheet is a test, but then the Iteration and Sample data is organized as 1 row describing the iteration summary and 100 rows describing the sampled results, repeated 1000 times. I can switch around the presentation of data however is best to get it into the database proposed above. My end-game is to be able to plot graphs of (for example) valueA vs valueB in Test1 for all iterations where summaryValue2 is "string", or some such manipulation.

Also, if I'm asking this question poorly, I apologize and I'm happy to clarify according to any criticism I receive.

Thank you!

Check out this example: At Your Survey - Roger's Access Library

At Your Survey (AYS) is a full featured application that allows users to create their own surveys by designing the questions and providing a lookup of possible responses. AYS uses a fairly normalized table structure so the same tables, forms, code, queries, and reports can be used for any number of surveys. There is a brief manual to help you get started as well as a sample survey with data.
At Your Survey is provided for educational purposes only. Steal ideas and methods but not the whole of the application. You may not distribute this application except upon agreement from Business Results. This runs in Access 2000.

 

[mgeerts]

Check out this example: At Your Survey - Roger's Access Library

Does this have anything at all to do with my question? I suspected not, but I gave you the benefit of the doubt and downloaded it anyway. Sure enough it appears to be completely unrelated to importing csv or excel data into a database.

-Matt

 

[HiTechCoach]

Does this have anything at all to do with my question? I suspected not, but I gave you the benefit of the doubt and downloaded it anyway. Sure enough it appears to be completely unrelated to importing csv or excel data into a database.

-Matt

Matt,

I had to guess at what you were after since you never ask a single question. You only stated goals.

You stated you goal is to have a database to hold hte question data. You never stated that you have one. That is why I posted the link. The first step will be to get a database properly normalized. This should look nothing like the CVS data unless that are table dumps from an existing database.

Until we know exact what he data looks like, it will be impossible to give you any specific help.

Do you have a specific question?

 

[mgeerts]

I appreciate your efforts. I managed to create a monster excel sheet of samples from all of the tests combined (my cpu was not happy with me) and import it into Access. Perhaps my post clarifying my goals was all that I needed to see the data structure correctly.

 

[HiTechCoach]

I appreciate your efforts. I managed to create a monster excel sheet of samples from all of the tests combined (my cpu was not happy with me) and import it into Access. Perhaps my post clarifying my goals was all that I needed to see the data structure correctly.

Thanks for the update.

Good luck with your project.