Import of CSV data into InfluxDB via UI

InfluxDB 2
1

Hi,

Have any of you been able to import CSV data via the web UI in a similar format to what I have?
The data I am trying to import is in this simple format (I have only shown the beginning of the data here):

UTC_time,Supply_line,Return_line,Tap_water,Brine_out,Brine_in,Outdoor,Indoor,Desired_Supply_line,Compressor,Auxiliary_heater1,Hot_water,Auxiliary_Heater2,Desired_indoor_temp,
2023-01-01 00:00:09,,30,,,,,,,,,,,,
2023-01-01 00:03:19,30,,,,,,,,,,,,,
2023-01-01 00:12:34,,29,,11,12,,,,,,,,,
2023-01-01 00:13:50,,,,10,,,,,,,,,,
2023-01-01 00:14:30,33,,,6,11,,,,,,,,,
2023-01-01 00:15:48,34,,,,,,,,,,,,,
2023-01-01 00:16:26,35,,,,,,,,,,,,,
2023-01-01 00:17:44,36,,,,,,,,,,,,,
2023-01-01 00:18:59,37,,,,,,,,,,,,,
2023-01-01 00:20:49,38,,48,,,,,,,,,,,
2023-01-01 00:22:02,,30,47,,,,,,,,,,,

It starts with a timestamp and is followed by 13 fields (double or integer - yes, it’s mostly temperature).
Ideally this should be imported 1:1 into InfluxDB. Using annotations always gives errors. Otherwise, it is not clear to me how #group, #datatype and #default can describe the structure of the import or define where a given field belongs.
Or am I thinking in the wrong direction? Conversion to Line protocol is too time consuming, what is then left for me to import CVS into the database?

One more thing: InfluxDB (:latest) is running in Docker on a small ARM machine.

Thanks.

2

@John970 The InfluxDB UI only supports Annotated CSV, so the annotations are required for it to work and the columns are required to be in a specific format (must have _time, _measurement, _field, and _value columns). It would take a full restructure of your CSV to get InfluxDB to accept it through the CSV uploader in the UI.

One thing you could actually do is use Flux to read the CSV in “raw” mode (which parses everything as strings), convert columns to the proper data type, and then write the data to a bucket. You’ll need to update the format of the dataTime strings, but that can be done pretty easily.

The following Flux script:

  • Takes the CSV you provided (you had an extra, empty column that I removed)
  • Uses map() to iterate over each row and:
    • Add a measurement column (replace example-meaurement with the measurement you want to write to)
    • Update the timestamp string to RFC3339 format and convert it to a time data type,
    • Uses int() to convert all other columns to integer data types (you could also use float() for float values or uint() for unsigned integers if you don’t want signed integers).
  • Uses influxdb.wideTo() to write the “wide” (fields structured as individual columns) data to an InfluxDB bucket (replace example-target-bucket with the bucket you want to write to).
import "csv"
import "influxdata/influxdb"
import "strings"

csvData = "UTC_time,Supply_line,Return_line,Tap_water,Brine_out,Brine_in,Outdoor,Indoor,Desired_Supply_line,Compressor,Auxiliary_heater1,Hot_water,Auxiliary_Heater2,Desired_indoor_temp
2023-01-01 00:00:09,,30,,,,,,,,,,,
2023-01-01 00:03:19,30,,,,,,,,,,,,
2023-01-01 00:12:34,,29,,11,12,,,,,,,,
2023-01-01 00:13:50,,,,10,,,,,,,,,
2023-01-01 00:14:30,33,,,6,11,,,,,,,,
2023-01-01 00:15:48,34,,,,,,,,,,,,
2023-01-01 00:16:26,35,,,,,,,,,,,,
2023-01-01 00:17:44,36,,,,,,,,,,,,
2023-01-01 00:18:59,37,,,,,,,,,,,,
2023-01-01 00:20:49,38,,48,,,,,,,,,,
2023-01-01 00:22:02,,30,47,,,,,,,,,,"

csv.from(csv: csvData, mode: "raw")
    |> map(fn: (r) => ({
        _measurement: "example-measurement",
        _time: time(v: "${strings.replace(i: 1, t: " ", u: "T", v: r.UTC_time)}Z"),
        Supply_line: int(v: r.Supply_line),
        Return_line: int(v: r.Return_line),
        Tap_water: int(v: r.Tap_water),
        Brine_out: int(v: r.Brine_out),
        Brine_in: int(v: r.Brine_in),
        Outdoor: int(v: r.Outdoor),
        Indoor: int(v: r.Indoor),
        Desired_Supply_line: int(v: r.Desired_Supply_line),
        Compressor: int(v: r.Compressor),
        Auxiliary_heater1: int(v: r.Auxiliary_heater1),
        Hot_water: int(v: r.Hot_water),
        Auxiliary_Heater2: int(v: r.Auxiliary_Heater2),
        Desired_indoor_temp:int(v: r.Desired_indoor_temp)
    }))
    |> influxdb.wideTo(bucket: "example-target-bucket")

Just run this code in the Data Explorer and you should be good to go.

3

Scott, thank you for your reply!
I had a feeling that it wouldn’t be that easy to import CSV via the UI. Especially when you mentioned that it has to be in a specific format.

Your example makes sense. Otherwise it gives me an error

required column "_measurement" not in group key

but I will try to fix it myself. The important thing is that I now know which direction I need to explore to make things work as they should. It will be fun!
It is also true that this is not even a little bit like SQL when I look at your example…