Insert csv. file to SQLite

General
19

ok .. so you know what the problem is. You setup your db table in such a way that the primary key (that must be unique) repeats itself and that is not allowed. Setting the Date as primary key will not work since the date repeats itself several times every day for your inserted rows. And in the case of the Time as primary key .. the time will repeat itself the next day.

One solution is to set a field in the db called (lets say) Datetime a combination of Date and Time fields from your CSV file .. This surely will be unique and can be set as Primary key. Once you make those changes to your table columns, a variation of the function could be ..

var payload = msg.payload;

for(i=0; i < payload.length; i++) {
    
    var Data = payload[i]["Data"];
    var Time = payload[i]["Time"];
    var Temperature = payload[i]["Temperature"];
    node.warn( [Data, Time, Temperature] ); //check debug sidebar
   
    node.send({"topic": `INSERT INTO STAR ('Datatime', 'Temperature') VALUES ('${Data} ${Time}', ${Temperature})`})
}

ps. As far as the function code goes .. you dont need to save node.send() into a variable and then returning it outside the loop. That is not how i shared the code. We used node.send() in this case instead of a return because we wanted to send many msgs in the loop.

1 Like