Send csv to to influxdb

Telegraf
1

Hello I am using Python Script to transfer my csv file to influxdb.

This is short discription of my file

Time [2:0] [2:1] [2:2] [2:3] [2:4] [2:5] [2:6] [2:7] [2:9] [2:10] [coil_id]
time Ziehkraft_IST Ziehkraft_IST_INT Ziehkraft_IST_LIN Drehzahl-Istwert vom Regler Strom-Istwert vom Regler ISTWERT ZIEHGESCHW.GEGLAETET ISTWERT ZIEHGESCHW.GEGLAETET Ziehkraft_IST_V D_Roh D_Fertig coil_id
sec
14.07.2017 19:03:15.000000 303.818 0 0.760291 0.72338 144.676 0.723379 144.676 0.0303818 345 310 nan
14.07.2017 19:03:15.010000 303.818 0 0.760291 0.72338 144.676 0.723379 144.676 0.0303818 345 310 0

I used this python script to export my csv to python :slight_smile:

import pandas as pd
from influxdb import DataFrameClient
import numpy as np

def convertTime(s):
try:
dt = pd.datetime.strptime(s, ‘%d.%m.%Y %H:%M:%S.%f’)
return dt
except:
return None

df = pd.read_csv(‘one.CSV’, delimiter=’;’, skiprows=[0,2,3,4])
print(df.head())
df[“time”] = df[“time”].map(convertTime)
df = df.set_index(“time”)
print(df.head())

client = DataFrameClient(host=‘localhost’, port=8086 )
client.switch_database(‘MF’)
#client.write_points(df, ‘iba’)
nRows, nCols = df.shape
K = 1000
n0 = 0
while n0 < nRows:
n1 = min(n0 + K, nRows)
print(n0, n1)
client.write_points(df[n0:n1], ‘three’)
n0 = n1

with this script my csv was transfered to influxdb but
1 I want to transfer the data with tag = coil_id , please let me know what to write/change in the script

2 I want to add 3 other key field into the database:time_productive_id, time_id, coil_id_last

I tried with INTO clause:

  1. SELECT last(b)/60 as “time_productive_id” INTO “MF”.“autogen”.“ziehmachine” FROM(SELECT cumulative_sum(a) as b FROM(SELECT last(“Ziehkraft_IST”)*0+1 as a FROM “MF”.“autogen”.“ziehmachine” WHERE “Lichtschranke am Hauptgetriebe (RSZ)” = true GROUP BY time(1s), *) GROUP BY *) GROUP BY *

  2. SELECT last(b)/60 as “time_id” INTO “MF”.“autogen”.“Ziehmachine” FROM(SELECT cumulative_sum(a) as b FROM(SELECT last(“Ziehkraft_IST”)*0+1 as a FROM “MF”.“autogen”.“Ziehmachine” GROUP BY time(1s), *) GROUP BY “coil_id”) GROUP BY “coil_id”

  3. SELECT last(“coil_id”) as “coil_id_last” INTO “MF”.“autogen”.“Ziehmachine” FROM “MF”.“autogen”.“Ziehmachine” GROUP BY *

  4. SELECT last(b)/60 as “time_productive_id” FROM(SELECT cumulative_sum(a) as b FROM(SELECT last(“Ziehkraft_IST”)*0+1 as a FROM “MF”.“autogen”.“ziehmachine” GROUP BY time(1s), *) GROUP BY *) GROUP BY *

influx
influx1350×444 17 KB

But these key fields are not showing in the database.

Please let me know the solution .

2

Hello @Nitesh,
Before we dive in, may I ask what version of influx are you using? Would you be open to using the the csv write command to write directly to your influxdb instance?

It looks like you’re using the old client that isn’t supported. I highly recommend using this client instead which allows you to write dataframes directly to influxdb. You can include another column in your dataframe with the tag you want.

"""
Write Pandas DataFrame
"""
_now = pd.Timestamp().now('UTC')
_data_frame = pd.DataFrame(data=[["coyote_creek", 1.0], ["coyote_creek", 2.0]],
                           index=[now, now + timedelta(hours=1)],
                           columns=["location", "water_level"])

_write_client.write(bucket.name, record=data_frame, data_frame_measurement_name='h2o_feet',
                    data_frame_tag_columns=['location'])
1 Like
3

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.