Unable to export csv to influxDB

[shnabin]

Hi, I am new to python and I'm trying to export a csv file into influxdb, i am getting the following error, please help me -----------------This is what I wrote------------------ C:\Users\AppData\Local\Programs\Python\Python37-32\Scripts>export_csv_to_influx.exe -c C:\Users\AppData\Local\Programs\Python\Python37-32\Scripts\test1.csv -d ; -db exa1 -m demo -t time -tc value1 -fc value2,value3,value4,value5 -u admin -p -fi true -s localhost:8086

-----------------This is the error--------------------- Info: Database exa1 already exists Traceback (most recent call last): File "C:\Users\AppData\Local\Programs\Python\Python37-32\Scripts\export_csv_to_influx-script.py", line 11, in load_entry_point('ExportCsvToInflux==0.1.16', 'console_scripts', 'export_csv_to_influx')() File "c:\users\appdata\local\programs\python\python37-32\lib\site-packages\ExportCsvToInflux\exporter_object.py", line 497, in export_csv_to_influx force_insert_even_csv_no_update=args.force_insert_even_csv_no_update) File "c:\users\appdata\local\programs\python\python37-32\lib\site-packages\ExportCsvToInflux\exporter_object.py", line 287, in export_csv_to_influx for row, int_type, float_type in convert_csv_data_to_int_float: File "c:\users\appdata\local\programs\python\python37-32\lib\site-packages\ExportCsvToInflux\csv_object.py", line 211, in convert_csv_data_to_int_float int_status = int_type[key] KeyError: 'time;value1;value2;value3;value4;value5;md5' -----------------This is how my csv file looks like----------------

test1.csv

time;value1;value2;value3;value4;value5 01/01/90 00:00;0;0;0;10.9;-167.06 01/01/90 01:00;0;0;0;10.7;-167.06 01/01/90 02:00;0;0;0;10.4;-167.06 01/01/90 03:00;0;0;0;10.2;-167.06 01/01/90 04:00;0;0;0;10;-167.06 01/01/90 05:00;0;0;0;9.0997;-167.06 01/01/90 06:00;0;0;0;7.6999;-167.06 01/01/90 07:00;31.983;8.7807;32.008;8.0002;3230.9 01/01/90 08:00;88.324;12.105;90.002;10;10711 01/01/90 09:00;114.16;15.716;118.01;13;14033 01/01/90 10:00;152.04;19.021;155;15.4;18964 01/01/90 11:00;294.84;24.542;277;17.5;37532 01/01/90 12:00;384.71;28.33;335.01;19;49312 01/01/90 13:00;632.51;35.63;510.99;20;79933 01/01/90 14:00;522.94;33.246;418;20.4;66567 01/01/90 15:00;155.93;24.37;140.01;20.6;19289

A separate csv is created after I run my command line as follows, i'm not sure what this is for ----------automatically created csv file-------------------- test1_influx.csv time;value1;value2;value3;value4;value5;md5 01/01/90 00:00;0;0;0;10.9;-167.06;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 01:00;0;0;0;10.7;-167.06;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 02:00;0;0;0;10.4;-167.06;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 03:00;0;0;0;10.2;-167.06;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 04:00;0;0;0;10;-167.06;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 05:00;0;0;0;9.0997;-167.06;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 06:00;0;0;0;7.6999;-167.06;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 07:00;31.983;8.7807;32.008;8.0002;3230.9;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 08:00;88.324;12.105;90.002;10;10711;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 09:00;114.16;15.716;118.01;13;14033;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 10:00;152.04;19.021;155;15.4;18964;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 11:00;294.84;24.542;277;17.5;37532;e0a1485ccf44c8c4859ff6d445a6fa3b 01/01/90 12:00;384.71;28.33;335.01;19;49312;e0a1485ccf44c8c4859ff6d445a6fa3b

Please help me.

2. I need some help for using the same as a library, can you please elaborate on how to do that with a small example as the below mentioned is not clear for me. Sorry for the silly questions

from ExportCsvToInflux import ExporterObject

exporter = ExporterObject() exporter.export_csv_to_influx(...)

Thank you!!

[Bugazelle]

Hello @krishnacy ,

1. It is the bug. Please update the version to 0.1.17. If you don't want to update, use the "," as separate.

And, your time column is: 01/01/90 00:00, not match the default one: %Y-%m-%d %H:%M:%S. You need to use: -tf "%m/%d/%y %H:%M" Here is the command to export your data:

export_csv_to_influx \
    -c test1.csv \
    -d ";" \
    -db exa1 \
    -m demo \
    -t time \
    -tc value1 \
    -fc value2,value3,value4,value5 \
    -u admin \
    -p \
    -fi true \
    -s localhost:8086 \
    -tf "%m/%d/%y %H:%M"

And you could query the data from influx as below.

> select * from demo
name: demo
time               value1 value2 value3 value4 value5
----               ------ ------ ------ ------ ------
631152000000000000 0.0    0      0      10.9   -167.06
631155600000000000 0.0    0      0      10.7   -167.06
631159200000000000 0.0    0      0      10.4   -167.06
631162800000000000 0.0    0      0      10.2   -167.06
631166400000000000 0.0    0      0      10     -167.06
631170000000000000 0.0    0      0      9.0997 -167.06
631173600000000000 0.0    0      0      7.6999 -167.06
631177200000000000 31.983 8.7807 32.008 8.0002 3230.9
631180800000000000 88.324 12.105 90.002 10     10711
631184400000000000 114.16 15.716 118.01 13     14033
631188000000000000 152.04 19.021 155    15.4   18964
631191600000000000 294.84 24.542 277    17.5   37532
631195200000000000 384.71 28.33  335.01 19     49312
631198800000000000 632.51 35.63  510.99 20     79933
631202400000000000 522.94 33.246 418    20.4   66567
631206000000000000 155.93 24.37  140.01 20.6   19289

2. To use the library as the code. Here is the sample based on your case.

from ExportCsvToInflux import ExporterObject

exporter = ExporterObject()
print(exporter.export_csv_to_influx.__doc__)
exporter.export_csv_to_influx(csv_file='test1.csv',
                              delimiter=';',
                              db_name='exa1',
                              db_measurement='demo',
                              time_column='time',
                              tag_columns='value1',
                              field_columns='value2,value3,value4,value5',
                              db_user='admin',
                              db_password='',
                              force_insert_even_csv_no_update=True,
                              db_server_name='localhost:8086',
                              time_format='%m/%d/%y %H:%M')

[shnabin]

Hi @Bugazelle ,

Thanks a lot for your help. I updated the version and I'm facing the following error with the time_format

exporter.export_csv_to_influx (csv_file ='G:\Nam\myname1.csv', db_name= 'exa3', db_measurement= 'demo', time_column= 'time', tag_columns= 'value1', field_columns= 'value2,value3,value4,value5', db_user= 'admin', db_password= '', force_insert_even_csv_no_update= True, db_server_name= 'localhost:8086', time_format='%d/%m/%y %H:%M')

The error is as follows

Info: Database exa3 already exists Traceback (most recent call last): File "C:\Users\AppData\Local\Programs\Python\Python37-32\pyin.py", line 15, in time_format='%d/%m/%y %H:%M') File "C:\Users\AppData\Local\Programs\Python\Python37-32\lib\site-packages\ExportCsvToInflux\exporter_object.py", line 315, in export_csv_to_influx datetime_naive = datetime.datetime.strptime(row[time_column], time_format) File "C:\Users\AppData\Local\Programs\Python\Python37-32\lib_strptime.py", line 577, in _strptime_datetime tt, fraction, gmtoff_fraction = _strptime(data_string, format) File "C:\Users\AppData\Local\Programs\Python\Python37-32\lib_strptime.py", line 359, in _strptime (data_string, format)) ValueError: time data '01-01-1990 00:00' does not match format '%d/%m/%y %H:%M'

The following is in cmd prompt :

C:\Users\AppData\Local\Programs\Python\Python37-32\Scripts>export_csv_to_influx -c G:\Nam\myname1.csv -db exa1 -m demo -t time -tc value1 -fc 'value2,value3,value4,value5' -u admin -p -fi true -s localhost:8086 -tf "%y/%m/%d %H:%M:%S" Info: Database exa1 already exists Traceback (most recent call last): File "C:\Users\AppData\Local\Programs\Python\Python37-32\Scripts\export_csv_to_influx-script.py", line 11, in load_entry_point('ExportCsvToInflux==0.1.17', 'console_scripts', 'export_csv_to_influx')() File "c:\users\appdata\local\programs\python\python37-32\lib\site-packages\ExportCsvToInflux\exporter_object.py", line 497, in export_csv_to_influx force_insert_even_csv_no_update=args.force_insert_even_csv_no_update) File "c:\users\appdata\local\programs\python\python37-32\lib\site-packages\ExportCsvToInflux\exporter_object.py", line 315, in export_csv_to_influx datetime_naive = datetime.datetime.strptime(row[time_column], time_format) File "c:\users\appdata\local\programs\python\python37-32\lib_strptime.py", line 577, in _strptime_datetime tt, fraction, gmtoff_fraction = _strptime(data_string, format) File "c:\users\appdata\local\programs\python\python37-32\lib_strptime.py", line 359, in _strptime (data_string, format)) ValueError: time data '1990-01-01 00:00:00' does not match format '%y/%m/%d %H:%M:%S'

I changed the CSV time format to y-m-d hh:mm:ss & d-m-y hh:mm:ss, in both the cases, i am having the same issue

the csv looks like this: time value1 value2 value3 value4 value5 01-01-1990 00:00 0 0 0 10.9 -167.06 01-01-1990 01:00 0 0 0 10.7 -167.06 01-01-1990 02:00 0 0 0 10.4 -167.06 01-01-1990 03:00 0 0 0 10.2 -167.06 01-01-1990 04:00 0 0 0 10 -167.06 01-01-1990 05:00 0 0 0 9.0997 -167.06 01-01-1990 06:00 0 0 0 7.6999 -167.06 01-01-1990 07:00 31.983 8.7807 32.008 8.0002 3230.9 01-01-1990 08:00 88.324 12.105 90.002 10 10711 01-01-1990 09:00 114.16 15.716 118.01 13 14033 01-01-1990 10:00 152.04 19.021 155 15.4 18964 01-01-1990 11:00 294.84 24.542 277 17.5 37532 01-01-1990 12:00 384.71 28.33 335.01 19 49312 01-01-1990 13:00 632.51 35.63 510.99 20 79933 01-01-1990 14:00 522.94 33.246 418 20.4 66567 01-01-1990 15:00 155.93 24.37 140.01 20.6 19289 01-01-1990 16:00 27.591 20.081 30.006 19.4 2541.1 01-01-1990 17:00 2.5214 0 3.0022 17 -166.71

Thank you!!

[Bugazelle]

Hello @krishnacy Sorry for the delay. These days are too busy.

The error ValueError: time data '1990-01-01 00:00:00' does not match format '%y/%m/%d %H:%M:%S' means your time value does not match your time format.

The default time format is: %Y-%m-%d %H:%M:%S, that means your time value should be something like: 1970-01-01 00:00:00

So:

if you provided time value is: 1990-01-01 00:00:00, no need to change format, because the default one fits

If you provided time value is: 01-01-1990 00:00, you need to customize your time formate: -tf "%m-%d-%Y %H:%M"

if you provided time value is: 01/01/90 00:00, you need to customize your time formate: -tf "%m/%d/%y %H:%M"

For more info about time format, check here: https://www.tutorialspoint.com/python/time_strftime

[shnabin]

Hello @Bugazelle ,

Thank you very much for your support, Now its working!!! My mistake was, I used "%m/%d/%y %H:%M" instead of "%m-%d-%Y %H:%M".

Thank you!!