CSV file returns only column names

SpaceRodento · July 17, 2023, 5:25am

Hi all

I feel like I’m getting somewhere but hitting a brick wall on last meters.

Trying to import CSV to influxdb with Telegraf File plugin. Idea is to manually place .csv into a folder which is monitored by telegraf.
Tested the connection to database with hard drive-plugin and it showed ok in Data Explorer
Only column names are showing up in Data Explorer, no data is being transferred and I cant find anything wrong with config, date format or anything else.
CSV in question should be ok. Excel opened it up without problems and MSSQL could also import it. Using V1 shell, no data can be seen in bucket
If there’s better way to achieve this or a way to test it in command line, I’m happy to change to whatever works.

Debug mode on:

2023-07-17T04:18:51Z I! Loading config: /etc/telegraf/telegraf.conf
2023-07-17T04:18:51Z I! Starting Telegraf 1.27.2
2023-07-17T04:18:51Z I! Available plugins: 237 inputs, 9 aggregators, 28 processors, 23 parsers, 59 outputs, 4 secret-stores
2023-07-17T04:18:51Z I! Loaded inputs: file
2023-07-17T04:18:51Z I! Loaded aggregators:
2023-07-17T04:18:51Z I! Loaded processors:
2023-07-17T04:18:51Z I! Loaded secretstores:
2023-07-17T04:18:51Z I! Loaded outputs: influxdb_v2
2023-07-17T04:18:51Z I! Tags enabled: dc=finland host=tupu
2023-07-17T04:18:51Z I! [agent] Config: Interval:10s, Quiet:false, Hostname:"tupu", Flush Interval:10s
2023-07-17T04:18:51Z D! [agent] Initializing plugins
2023-07-17T04:18:51Z D! [agent] Connecting outputs
2023-07-17T04:18:51Z D! [agent] Attempting connection to [outputs.influxdb_v2]
2023-07-17T04:18:51Z D! [agent] Successfully connected to outputs.influxdb_v2
2023-07-17T04:18:51Z D! [agent] Starting service inputs
2023-07-17T04:19:01Z D! [outputs.influxdb_v2] Buffer fullness: 0 / 10000 metrics

“Buffer fullness” keeps repeating.

Config, inputs.file -part

[[inputs.file]]
  files = ["/home/pi/database/Log_2023.csv"]
  data_format = "csv"
  csv_header_row_count = 1
  csv_skip_rows = 0
  csv_column_names = ["Row", "Sensor", "Date time", "Cumulative chip Count", "1h count Frequency", "24h Count", "Moisture", "Temperature T", "P2_Rough", "P2_Calibrated", "F0", "Fdif", "Tdif", "DeltaF", "P", "A", "B", "C", "D", "E", "F", "N", "T1", "T2", "T3", "DevResults", "CustomerID1", "CustomerID2", "CustomerID3", "Misc1", "Misc2", "Misc3"]
# trying ; and , 
csv_delimiter = ";" 


  csv_trim_space = true  # Set this to true if you want to trim leading and trailing spaces in the fields
  csv_tag_overwrite = false
  csv_measurement_column = ""  # Leave this empty if you want to use the default measurement name ("file")
  csv_timestamp_column = "Date time"  # Specify the column containing the timestamp
  csv_timestamp_format = "2006-1-2 15:04:05"  # Format of the timestamp column
  csv_timezone = "Europe/Helsinki"
  csv_skip_errors = false

Few rows of the .csv in question.

Row;Sensor;Date time;Cumulative chip Count;1h	count Frequency;24h Count;Moisture;Temperature T;P2_Rough;P2_Calibrated;F0;Fdif;Tdif;DeltaF;P;A;B;C;D;E;F;N;T1;T2;T3;DevResults;CustomerID1;CustomerID2;CustomerID3;Misc1;Misc2;Misc3;
1;Engine 1;2023-3-13 13:13:36;3;0;0;0.0;1372.0;0.0;0.0;0.0;0.0;0.0;0.0;0.1;-0.007;-0.01;0.00042;0.0125;0.0003;8.58;1.0;50.0;55.0;60.0;FALSE;OM-Finland;Autotalli 1;Testipenkki 1;;12333;;
2;Engine 2;2023-3-13 13:13:36;0;0;0;0.0;19.9;0.0;0.0;0.0;0.0;0.0;0.0;0.1;-0.007;-0.01;0.00042;0.0125;0.0003;8.58;1.0;50.0;55.0;60.0;FALSE;OM-Finland;Pieksämäki 1;Turbiini 1;;;;
3;Engine 3;2023-3-13 13:13:36;0;0;0;0.0;;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;FALSE;OM-Finland;Jääkiekkohalli 5;Mopo 3;45;;;
4;Engine 1;2023-3-13 13:16:35;3;0;0;0.0;1372.0;0.0;0.0;0.0;0.0;0.0;0.0;0.1;-0.007;-0.01;0.00042;0.0125;0.0003;8.58;1.0;50.0;55.0;60.0;FALSE;OM-Finland;Autotalli 1;Testipenkki 1;;12333;;
5;Engine 2;2023-3-13 13:16:35;0;0;0;0.0;20.0;0.0;0.0;0.0;0.0;0.0;0.0;0.1;-0.007;-0.01;0.00042;0.0125;0.0003;8.58;1.0;50.0;55.0;60.0;FALSE;OM-Finland;Pieksämäki 1;Turbiini 1;;;;
6;Engine 3;2023-3-13 13:16:35;0;0;0;0.0;;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;0.0;FALSE;OM-Finland;Jääkiekkohalli 5;Mopo 3;45;;;

syslog file excerpt, does this open up anything?

Jul 16 22:47:39 tupu systemd[1]: Started Session 13 of user pi.
Jul 16 22:47:46 tupu influxd-systemd-start.sh[532]: ts=2023-07-16T19:47:46.316115Z lvl=error msg="Unable to write gathered points" log_id=0j3i5a>Jul 16 22:47:56 tupu influxd-systemd-start.sh[532]: ts=2023-07-16T19:47:56.303885Z lvl=error msg="Unable to write gathered points" log_id=0j3i5a>Jul 16 22:47:59 tupu systemd[1]: session-13.scope: Succeeded.
Jul 16 22:47:59 tupu systemd[1]: session-13.scope: Consumed 1.640s CPU time.

jpowers · July 17, 2023, 1:02pm

Idea is to manually place .csv into a folder which is monitored by telegraf.

Consider using the directory monitor plugin instead of file. The monitor plugin can check for new files and update based on new files or updates to them.

Only column names are showing up in Data Explorer, no data is being transferred and I cant find anything wrong with config, date format or anything else.

Your timestamps are from 3+ months ago. Ensure that you have the correct zoom in the Data Explorer. Columns do not show up without data as well

When I ran your config with the example CSV I got metrics:

file,host=ryzen A=-0.007,D=0.0125,Tdif=0,P2_Calibrated=0,B=-0.01,E=0.0003,CustomerID1="OM-Finland",1h\ count\ Frequency=0i,P2_Rough=0,Fdif=0,P=0.1,T2=55,CustomerID3="Testipenkki 1",Misc2=12333i,Sensor="Engine 1",N=1,T3=60,C=0.00042,T1=50,Misc1="",Cumulative\ chip\ Count=3i,Moisture=0,Temperature\ T=1372,F0=0,DeltaF=0,24h\ Count=0i,CustomerID2="Autotalli 1",Misc3="",DevResults=false,F=8.58,Row=1i 1678706016000000000
file,host=ryzen Fdif=0,F=8.58,N=1,Moisture=0,Cumulative\ chip\ Count=0i,A=-0.007,E=0.0003,T3=60,CustomerID3="Turbiini 1",Row=2i,C=0.00042,D=0.0125,DeltaF=0,T1=50,Misc1="",P2_Rough=0,Temperature\ T=19.9,B=-0.01,DevResults=false,24h\ Count=0i,Tdif=0,P=0.1,CustomerID1="OM-Finland",Misc3="",Sensor="Engine 2",F0=0,CustomerID2="Pieksämäki 1",P2_Calibrated=0,T2=55,Misc2="",1h\ count\ Frequency=0i 1678706016000000000
file,host=ryzen F=0,N=0,Misc2="",Fdif=0,Temperature\ T="",P=0,Row=3i,D=0,T3=0,CustomerID1="OM-Finland",A=0,C=0,T1=0,T2=0,CustomerID3="Mopo 3",Misc1=45i,Misc3="",Moisture=0,P2_Rough=0,B=0,Sensor="Engine 3",Cumulative\ chip\ Count=0i,24h\ Count=0i,Tdif=0,E=0,CustomerID2="Jääkiekkohalli 5",1h\ count\ Frequency=0i,P2_Calibrated=0,F0=0,DeltaF=0,DevResults=false 1678706016000000000
file,host=ryzen F=8.58,CustomerID3="Testipenkki 1",Tdif=0,P2_Calibrated=0,D=0.0125,E=0.0003,T1=50,DevResults=false,Misc2=12333i,Misc3="",Cumulative\ chip\ Count=3i,Sensor="Engine 1",P=0.1,A=-0.007,T2=55,Row=4i,Fdif=0,T3=60,CustomerID1="OM-Finland",F0=0,B=-0.01,C=0.00042,1h\ count\ Frequency=0i,Moisture=0,DeltaF=0,24h\ Count=0i,Temperature\ T=1372,N=1,CustomerID2="Autotalli 1",Misc1="",P2_Rough=0 1678706195000000000
file,host=ryzen CustomerID1="OM-Finland",P2_Calibrated=0,F0=0,B=-0.01,E=0.0003,T2=55,T1=50,Moisture=0,P2_Rough=0,P=0.1,A=-0.007,N=1,Sensor="Engine 2",Cumulative\ chip\ Count=0i,1h\ count\ Frequency=0i,CustomerID2="Pieksämäki 1",Misc3="",D=0.0125,T3=60,Misc1="",Misc2="",Row=5i,Fdif=0,DevResults=false,Temperature\ T=20,Tdif=0,F=8.58,CustomerID3="Turbiini 1",24h\ Count=0i,DeltaF=0,C=0.00042 1678706195000000000
file,host=ryzen A=0,DevResults=false,Sensor="Engine 3",P=0,Misc2="",N=0,T2=0,Moisture=0,P2_Calibrated=0,C=0,E=0,T3=0,CustomerID2="Jääkiekkohalli 5",Cumulative\ chip\ Count=0i,Tdif=0,F=0,CustomerID1="OM-Finland",Row=6i,D=0,B=0,T1=0,Misc1=45i,P2_Rough=0,Fdif=0,24h\ Count=0i,Temperature\ T="",F0=0,DeltaF=0,CustomerID3="Mopo 3",Misc3="",1h\ count\ Frequency=0i 1678706195000000000

You will note that the data all has the same measurement name, tag set, and timestmap. This means that the data is essentially all the same and the last row read by influxdb will win.

Please check out the docs on line protocol, specifically how tags help index data. For example, you probably want the customer IDs and sensors to be tags.

SpaceRodento · July 20, 2023, 8:46pm

Thank you!
I havent yet solved it yet, but this gives a lot of new tools to work on.
I’m still guessing if I got the right tool. “running with the example CSV” is a lot harder than I thought it would be.
When logging into InfluxDB, I am greeted with big friendly icons to import my CSV, and granted with only unfriendly errors when I want to do just that. Its frustrating trying to find just one of the myriad choices how am I supposed to test my config and database.

srebhan · July 26, 2023, 9:40am

@SpaceRodento just one more tool: If you manually run Telegraf with the --test flag it will print out the gathered metrics on the console but will not send it to your output. This way you can easily debug the generated metrics eliminating potential query issues…

Topic		Replies	Views
CSV file data into InfluxDB via Telegraf Telegraf influxdb , telegraf	9	6959	August 5, 2019
Help needed getting a CSV file into Influxdb using Telegraf Telegraf csv	1	484	March 7, 2022
CSV file data into InfluxDB via Telegraf No time on CSV file Telegraf telegraf	4	1194	February 1, 2022
Not able to view csv data in influxdb Dashboards influxdb , telegraf , grafana	1	1163	January 23, 2019
Where to find telegraf tutorial to move data from .csv file to influxdb? Telegraf telegraf	7	6394	February 3, 2020

CSV file returns only column names

Related Topics