fabio-miranda / csv-to-influxdb Goto Github PK

Hi,

I am new to python. I'm trying to write a csv file to influxdb using cmd prompt in windows 10 and i am receiving the following error, please help me.

csv-to-influxdb.py: error: unrecognized arguments: %H:%M:%S'

C:\Users\admin\AppData\Local\Programs\Python\Python37\Scripts>python csv-to-influxdb.py --dbname test --input D:\mydata\FL_insurance.csv --tagcolumns policyID --fieldcolumns tiv_2011 -tc timestamp -tf '%d-%m-%y %H:%M:%S'
usage: csv-to-influxdb.py [-h] -i [INPUT] [-d [DELIMITER]] [-s [SERVER]]
[-u [USER]] [-p [PASSWORD]] --dbname [DBNAME]
[--create] [-m [METRICNAME]] [-tc [TIMECOLUMN]]
[-tf [TIMEFORMAT]] [-tz TIMEZONE]
[--fieldcolumns [FIELDCOLUMNS]]
[--tagcolumns [TAGCOLUMNS]] [-g] [-b BATCHSIZE]
csv-to-influxdb.py: error: unrecognized arguments: %H:%M:%S'

my csv time format is d-m-y

It looks like the script is not able, or I was not able, to save a variable with a null value.
I tried with "","null", null and nothing at all, but none of them gave the desired null value.
Can you please tell me what I'm missing?

thanks,
Loris

Hi Miranda,

I am not able to add unix epoch time in to influx, getting following error.

ValueError: time data '1616491233000000000' does not match format '%Y-%m-%d %H:%M:%S'

My CSV format

name,tags,time,command,crit,crit-fill,host,max,min,performanceLabel,service,unit,value,warn,warn-fill
metrics,,1616491233000000000,check_metrics,75,none,hostA,,0,pl,hostcheck,%,0,60,none
metrics,,1616491233000000000,check_metrics,5000,none,hostA,,0,rta,hostcheck,ms,0.092,3000,none

Hello,
Thank you for your contribution about the import of csv files into InfluxDB.
I'm trying the code since several days and I do not succeed in resolving the error mentionned in the subject : InfluxDBClientError: 401: {"code":"unauthorized","message":"Unauthorized"}
I putted the login and password in the line code as indicated in the help of csv-to-influxdb and this is what I obtain :
Read 792 lines
Inserting 792 datapoints...
Problem inserting points for current batch
Traceback (most recent call last):
File "csv-to-influxdb.py", line 211, in
loadCsv(args.input, args.server, args.user, args.password, args.dbname,
File "csv-to-influxdb.py", line 151, in loadCsv
raise e
File "csv-to-influxdb.py", line 147, in loadCsv
response = client.write_points(datapoints)
File "C:\Users\Ecologie\anaconda3\lib\site-packages\influxdb\client.py", line 603, in write_points
return self._write_points(points=points,
File "C:\Users\Ecologie\anaconda3\lib\site-packages\influxdb\client.py", line 681, in _write_points
self.write(
File "C:\Users\Ecologie\anaconda3\lib\site-packages\influxdb\client.py", line 413, in write
self.request(
File "C:\Users\Ecologie\anaconda3\lib\site-packages\influxdb\client.py", line 378, in request
raise InfluxDBClientError(err_msg, response.status_code)
influxdb.exceptions.InfluxDBClientError: 401: {"code":"unauthorized","message":"Unauthorized"}

Buon giorno, mi scuso se ho usato questo modo per contattarti.
Vorrei usare lo script per migrare dati che ho presenti in files .csv formattati in questo modo ,
"timestamp, Nome variabile" ... ecco sotto le prime 5 righe di un file CSV d'esempio per la variabile Pfv

timestamp,Pfv
1460041860,592
1460041920,573
1460041980,609
1460042040,660
1460042100,660

Il timestamp è in formato Unix standard ( precisione di 1 sec ) e c'e' una nuova riga ogni circa 60 secondi.

Non sono riuscito a capire come e se è possibile usare il tuo sript per fare questa operazione.

( lo scopo è quello di migrare da un formato dati proprietari ( phpfina usato in Emoncms ) a InfluxDb passando per csv come formato di interscambio ).

Grazie per l'eventuale risposta,
Fabrizio

Hi,

I used your solution quite well, but as I moved to influxDB 1.8.6 it seems that there is an issue with the import of data.
A typical .csv-File-DataLine would like

2021-05-28T08:00:47.004,49.954104,8.308486,106.100000,39212,3542.5,42127,5000.0,34283,1.842210159999997,161,70.0,2,3, 34283,32.89798135999996,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,,,,,

As there might be data (floats) between the comma within an earlier line I get a type mismatch error (400) from influx client, as it is now a 'string' (empty one).
Scratching my head the solution was quite simple.

Within the "#open csv"-area there is the following loop:

 for f in fieldcolumns:
     v = 0.0
     if f in row:
         if (isfloat(row[f])):
             v = float(row[f])

I added an 'elif' to check whether the column is an empty string, if it is, then I skip that field. No it looks like

  for f in fieldcolumns:
      v = 0.0
      if f in row:
          if (isfloat(row[f])):
              v = float(row[f])
          elif (isbool(row[f])):
              v = str2bool(row[f])
          else:
              v = row[f] # may insert an empty string
      if v:
          fields[f] = v

This also should speed up the import, if you have a lot of empty fields (not all messages are scanned at the same time), like in my .csv-files.

Thank you for the great tool

Title says it all

Hi - can you help me with you script?
Example csv:

timestamp,Ladezustand,Batterie,Batterie (Entladen),Netzeinspeisung,Netzbezug
01.05.2021 00:00,59,0,122,1,1
01.05.2021 00:00,40,0,140,5,2

my dbname is "test" - how to set "measurement" in influxdb correct with your script when trying to access
the data via Grafana? from my perspective measuremtn should contain all names in the header row like Ladezustand etc.
the values below should be visible as values

For example a Grafana query looks like this:

SELECT last("Leistung") FROM "Batterie" WHERE $timeFilter

where "Leistung" should be a value from csv above eg: 122, "Batterie is the column header name and one measurement

I'm using te follwoing command:

./csv-to-influxdb.py --input write_values6.csv --dbname nodered --metricname stromzaehler_neu -tc time -d ';' --fieldcolumns Aktuelle_Leistung,Total_Verbrauch

and I get the following error message

Inserting 671 datapoints...
Problem inserting points for current batch
Traceback (most recent call last):
File "./csv-to-influxdb.py", line 207, in
args.timezone, args.ssl, args.force)
File "./csv-to-influxdb.py", line 144, in loadCsv
raise e
influxdb.exceptions.InfluxDBClientError: 400: {"error":"partial write: field type conflict: input field "Total_Verbrauch" on measurement "stromzaehler_neu" is type string, already exists as type float dropped=1"}

However the csv file looks fine

stromzaehler_neu;Aktuelle_Leistung;Total_Verbrauch;time
stromzaehler_neu;500.51;5122.7096;2022-08-27 05:00:00
stromzaehler_neu;500.51;5123.2192;2022-08-27 06:00:00
stromzaehler_neu;500.51;5123.7288;2022-08-27 07:00:00

Any idea about this ?

Hi, Does this work with InfluxDB2? I am new to this domain and understood that database concept does not exist anymore in InfluxDB2, but bucket concepts. Can it be modified to work with buckets? Thanks

Does this work with Python 2.7 version? I tried to install dependencies like influxdb but could not find matching version. Or is this Python 3 only utility?

first, this is a great script to import data to influxdb. while, when I try to import my csv file to influx and get error " nfluxdb.exceptions.InfluxDBClientError: 400: {"error":"partial write: unable to parse 'value,host=0 1540686240000000000': invalid field format\n".
then I dig out that row has NaN value, I import successfully after I dealing all missing data/

so, could you add this fill/delete missing data as an optional parameter?

My timestamps are already in epoch format, yet I cannot figure out how to write that format for the relevant arg.

You missed something in your README.
with no words you mention that the database given is getting dropped.
Deleting database test
Creating database test

This missing information can and will lead to problems for the User.

File should start with
#!/usr/bin/env python

python3 csv-to-influxdb.py -i server1.csv -s server5:8086 --create --dbname NewTest --tagcolumns host --fieldcolumns CPU,GPU --metricname name --timecolumn time --timeformat unix
Deleting database NewTest
Creating database NewTest
Traceback (most recent call last):
File "csv-to-influxdb.py", line 194, in
args.timezone, args.ssl)
File "csv-to-influxdb.py", line 77, in loadCsv
datetime_naive = datetime.datetime.strptime(row[timecolumn],timeformat)
File "/usr/lib/python3.7/_strptime.py", line 577, in _strptime_datetime
tt, fraction, gmtoff_fraction = _strptime(data_string, format)
File "/usr/lib/python3.7/_strptime.py", line 359, in _strptime
(data_string, format))
ValueError: time data '1590335330162702714' does not match format 'unix'

CSV looks like this

name,time,CPU,GPU,host
temperature,1590335330162702714,53.5,53,server1