I have a collection of csv files with measurements with the following 54 columns:

SITE (string)
TIME (integer as yyyymmddhh)
PROD (double)
EST.0 (double)
EST.50 (double)

I want to read this to a mysql database. I would prefer a structure that would allow me to compute average and variance of values EST.0 .. EST.50, for each row.

What would be a good database structure for this data? Can I keep the columns as they are, or should I only have one "value" column? And if the former, how can I then get the averages and variances that I want?

From reading related questions, it looked like having so many value columns is not a good idea, so I was thinking about having a "value" column for all the double values, plus an "est_ID" column for an identifier, using -1 or NULL to denote the "PROD" value - does it make sense?

And what about the date-time column - will mysql understand it, or do I need to provide some information about date-time format?

As for handling multiple files, I guess that a small bash script calling mysqlimport should do the job nicely?




1 Answer 1


You will want to have two database tables for this.

One table with id int/bigint autoincremenmt, site, time and prod. id is the primaty key.

And one table with id (same as in previous table but no autoincrement, it refers to the id in the first table) the number of the EST, and EST . The primary key of this table would span two columns, id and number of the EST (this may require some fiddling with the database table creation tool you are using).

As for date columns, no mysql doesn't understand that format, it has its own date and datetime formats for columns. At our place here we are using unix timestamps and store these in the database as integers or bigints.

  • Thanks, this sounds like a good structure. The question now is, how do I get this from the CSV files to the mysql?
    – Michal Kaut
    Jan 7, 2013 at 10:18
  • I can't really give an unbiased answer to this, since I mostly do php on a webserver and use PDO database layer there. You can probably use bash and awk to import the data. Or you could write a small C program that accesses the mysql database. I don't know what tools you are good at ..
    – Adder
    Jan 7, 2013 at 10:27
  • I do not know how to do this with bash, since I would need access to the auto-generated IDs. I am considering C++, using some CSV parser (Boost tokenizer?) and a POCO Data library to access the database - but it will be some work. An alternative could be reading the whole file into a temp. table and then use SQL commands to create the two tables - but I do not have enough SQL skill to do this, or even to estimate how difficult it is :-(
    – Michal Kaut
    Jan 7, 2013 at 11:05
  • At the end, I went the C++ route. It was not that difficult, but it turned out the Poco Data MySQL connector is quite slow, so I ended up dumping the big EST table into a csv file and importing this from a bash script - see this thread. Jan 16, 2013 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.