CODEWITHSUNDEEP
pythoncsvinfluxdb
rob
rob

Reputation: 153

importing csv file data into influxdb

is it possible to push csv file data into influxdb. e.g I had a sensor device which update data every minute to csv file. I need to do some operations on it. I need to push csv file data to influxdb and for that purpose i am writing a python script. I tried to find some examples but couldn't successful to find any. below my python script only had reading csv file code which is simple but no code how to pass this csv to influx db. my csv file and code which i am trying looks like this.

DATE,value
2017-03-23 11:50:38,16
2017-03-23 11:50:44,83
2017-03-23 11:50:50,16
2017-03-23 11:50:56,70
2017-03-23 11:51:02,96

and the code is 

import datetime
import random
import time
import os
import csv
from csv import reader
import argparse
from influxdb import client as influxdb


db = influxdb.InfluxDBClient(host, 8086, rob, xxxx, sensors_data)


def read_data(filename):
    with open(filename) as f:
        reader = f.readlines()[1:]
        for line in reader:
            print line


if __name__ == '__main__':
    filename = '/home/rob/myfile.csv'
    a = read_data(filename)

I find this link but its different and does not according to my requirements. http://coendegroot.com/grafana-influxdb-and-python-simple-sample/ I can read this csv file and now I need to send csv file data to influx db. but I don't know how to do with python script. I tried to find examples but no success. if someone guide me, I will very grateful. thanx a lot

I change the code according to Pigueiras suggestion and it looks like this 

import datetime
import random
import time
import os
import csv
from csv import reader
import argparse
from influxdb import client as influxdb


db = influxdb.InfluxDBClient(host, 8086, rob, xxxx, sensors_data)
def read_data():
    with open('file.csv') as f:
        return [x.split(',') for x in f.readlines()[1:]]

a = read_data()

for metric in a:
    influx_metric = [{
        'measurement': 'your_measurement',
        'time': a[0],
        'fields': {
            'value': a[1]
        }
    }]
    db.write_points(influx_metric)

I tried this code and it give me following error

 'NameError: name 'your_measurement' is not defined

and if i use like like 'measurement': 'your_measurement'. then i got this error. .......

    File "all_flux.py", line 37, in <module>
db.write_points(influx_metric)
  File "/usr/local/lib/python2.7/dist-packages/influxdb/client.py", line 411, in write_points
tags=tags, protocol=protocol)
  File "/usr/local/lib/python2.7/dist-packages/influxdb/client.py", line 461, in _write_points
protocol=protocol
  File "/usr/local/lib/python2.7/dist-packages/influxdb/client.py", line 282, in write
data = make_lines(data, precision).encode('utf-8')
  File "/usr/local/lib/python2.7/dist-packages/influxdb/line_protocol.py", line 154, in make_lines
_convert_timestamp(point['time'], precision)
  File "/usr/local/lib/python2.7/dist-packages/influxdb/line_protocol.py", line 40, in _convert_timestamp
raise ValueError(timestamp)
ValueError: ['2017-03-23 11:50:38', '16\r\n']

the DATE field or columns looks like this '2017-03-23 11:50:38' so I use return [x.split(',').... and also I ignore first line. but i got this value error

Upvotes: 4

Views: 21037

Answers (2)

Ken
Ken

Reputation: 395

I found an solution: export csv to influx. https://github.com/Bugazelle/export-csv-to-influx

Install by: pip install ExportCsvToInflux

demo.csv

timestamp,url,response_time
2019-07-11 02:04:05,https://jmeter.apache.org/,1.434
2019-07-11 02:04:06,https://jmeter.apache.org/,2.434
2019-07-11 02:04:07,https://jmeter.apache.org/,1.200
2019-07-11 02:04:08,https://jmeter.apache.org/,1.675
2019-07-11 02:04:09,https://jmeter.apache.org/,2.265
2019-07-11 02:04:10,https://sample-demo.org/,1.430
2019-07-12 08:54:13,https://sample-show.org/,1.300
2019-07-12 14:06:00,https://sample-7.org/,1.289
2019-07-12 18:45:34,https://sample-8.org/,2.876

export_csv_to_influx \
--csv demo.csv \
--dbname demo \
--measurement demo \
--tag_columns url \
--field_columns response_time \
--user admin \
--password admin \
--force_insert_even_csv_no_update True \
--server 127.0.0.1:8086

Upvotes: 2

Pigueiras
Pigueiras

Reputation: 19356

You just need to build a JSON body to insert into Influx with the data you read from the CSV. Be careful with the read_data function which it is not returning anything.

Something like this should work fine (of course, it can be optimized to insert multiple points at once):

def read_data():
    with open('file.csv') as f:
        return [x.split(',') for x in f.readlines()[1:]]

a = read_data()

for metric in a:
    influx_metric = [{
        'measurement': 'your_measurement',
        'time': metric[0],
        'fields': {
             'value': metric[1]
        }
    }]
    db.write_points(influx_metric)

Upvotes: 4

Related Questions