Search code examples
mysqlsqlcsvtime-seriesheidisql

Importing csv file into single column of a MySQL table

I have googled this a lot, and I have not found anything matching my problem.

I have a lot of Time Series, containing different sensors readings. Each Time Series is stored into a .csv file, so each file contains a single column.

I have to populate this MySQL table:

CREATE TABLE scheme.sensor_readings (
    id int unsigned not null auto_increment,
    sensor_id int unsigned not null,
    date_created datetime,
    reading_value double,
    PRIMARY KEY(id),
    FOREIGN KEY (sensor_id) REFERENCES scheme.sensors (id) ON DELETE CASCADE
) ENGINE = InnoDB;

while the sensors table is:

CREATE TABLE scheme.sensors ( 
    id int unsigned not null auto_increment,
    sensor_title varchar(255) not null,
    description varchar(255) not null,
    date_created datetime,
    PRIMARY KEY(id)
) ENGINE = InnoDB;

Now, I should fill the reading_value field with values contained in the above descripted .csv files. An example of this kind of file:

START INFO  
Recording Time  *timestamp*
Oil Pressure    dt: 1,000000 sec
STOP INFO   
0,445328    
0,429459    
0,4245  
0,445099    
0,432434    
0,433426    
...
EOF

What I need is to design an SQL query in which I populate this table while reading values from a .csv file. I cannot figure out how to proceed: should I use some sort of temporary table as a buffer?

I use HeidiSQL as Client.

Solution

  • The kind of tool you looking for is called an ETL (Extract, transform, Load).

    You can extract data form csv files (among other), transfrom them by adding the info from the sensor db-table (among other), and load it into the sensor_reading db-table.

    There are plenty of ETL on the market. Although, I should be agnostic, a free, easy to learn and covering all your future needs, you may start evaluating PDI (Pentaho Data Integrator, nicknamed Kettle). Go there, download the latest Data Integrator, unzip and press the spoon.bat / spoon.sh. A nice getting started is there. And the StackOverFlow flag Pentaho Data Integration, respond usually quite quickly.

    Alternatively you may try Talend or plenty others.