Writing entries gives 'invalid field format' error but only if 2 tags are added together. Separately works fine

s3n · November 2, 2021, 10:06am

Hello Community ,

I’m new to influx and trying to import existing data from a CSV file to influx (OSS) but currently facing an error which I can’t understand and doesn’t make sense to me, other than some limitations I don’t know of and can’t find in the docs.

If necessary: my code

The CSV (with the line, that gives me the following error message):

sep=;
Date;Entry Type;Value;Currencs;Category;Person;Account;Counter Account;Group;Note;Recurring;
"17.06.2019";"Expense";"-26,56";"EUR";"LS &gt; Groceries";"";"Sparkasse Giro";"";"";"Penny vom 14.06. , falsches konto";"no";

Error:

Traceback (most recent call last):
  File "/Users/simon/Repos/primoco-toolbox/primoco_toolbox.py", line 60, in <module>
    main(sys.argv[1:])
  File "/Users/simon/Repos/primoco-toolbox/primoco_toolbox.py", line 53, in main
    influxImport()
  File "/Users/simon/Repos/primoco-toolbox/primoco_toolbox.py", line 25, in influxImport
    importer.startImport()
  File "/Users/simon/Repos/primoco-toolbox/influx_importer.py", line 130, in startImport
    self.writeDataFrameToInflux(dataFrame)
  File "/Users/simon/Repos/primoco-toolbox/influx_importer.py", line 96, in writeDataFrameToInflux
    write_client.write(
  File "/usr/local/lib/python3.9/site-packages/influxdb_client/client/write_api.py", line 371, in write
    results = list(map(write_payload, payloads.items()))
  File "/usr/local/lib/python3.9/site-packages/influxdb_client/client/write_api.py", line 369, in write_payload
    return self._post_write(_async_req, bucket, org, final_string, payload[0])
  File "/usr/local/lib/python3.9/site-packages/influxdb_client/client/write_api.py", line 517, in _post_write
    return self._write_service.post_write(org=org, bucket=bucket, body=body, precision=precision,
  File "/usr/local/lib/python3.9/site-packages/influxdb_client/service/write_service.py", line 62, in post_write
    (data) = self.post_write_with_http_info(org, bucket, body, **kwargs)  # noqa: E501
  File "/usr/local/lib/python3.9/site-packages/influxdb_client/service/write_service.py", line 166, in post_write_with_http_info
    return self.api_client.call_api(
  File "/usr/local/lib/python3.9/site-packages/influxdb_client/api_client.py", line 341, in call_api
    return self.__call_api(resource_path, method,
  File "/usr/local/lib/python3.9/site-packages/influxdb_client/api_client.py", line 171, in __call_api
    response_data = self.request(
  File "/usr/local/lib/python3.9/site-packages/influxdb_client/api_client.py", line 386, in request
    return self.rest_client.POST(url,
  File "/usr/local/lib/python3.9/site-packages/influxdb_client/rest.py", line 304, in POST
    return self.request("POST", url,
  File "/usr/local/lib/python3.9/site-packages/influxdb_client/rest.py", line 254, in request
    raise ApiException(http_resp=r)
influxdb_client.rest.ApiException: (400)
Reason: Bad Request
HTTP response headers: HTTPHeaderDict({'Content-Type': 'application/json; charset=utf-8', 'X-Influxdb-Build': 'OSS', 'X-Influxdb-Version': '2.0.9', 'X-Platform-Error-Code': 'invalid', 'Date': 'Mon, 01 Nov 2021 12:34:52 GMT', 'Content-Length': '274'})
HTTP response body: {"code":"invalid","message":"unable to parse 'bookings,Account=Sparkasse\\\\ Giro,Category=LS\\\\ \u003e\\\\ Groceries,Entry\\ Type=Expense ,Currencs=\"EUR\",Note=\"Penny vom 14.06.  falsches konto\",Recurring=\"no\",Value=-26.56 1560729600000864000': invalid field format"}

What I want is, that the columns “Entry Type”, “Person”, “Category”, “Account” are tags. The code is working and writing the data to influx without any problems if:

I write the data with “Entry Type”, “Person”, “Category” as tags
I write the data with “Entry Type”, “Person”, “Account” as tags

But as soon as I write the data with “Entry Type”, “Person”, “Category” AND “Account” as tags I get the mentioned error.
This tells me that my overall code is working fine, but it seems like there are some limitations I don’t know of.

I got the same error invalid field format before I didn’t escape spaces, commas and equal signs in the tag values like explained in the docs. But I fixed that (prepareTagColumns function in my code).

Any help welcome!

bednar · November 3, 2021, 9:32am

Hi @s3n,

thanks for using our client.

I’ve tried your code with your one-line CSV and everything works correctly:

Uniquify DateTime Column.
Unescape HTML.
Prepare tag columns.
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 1 entries, 2019-06-17 to 2019-06-17
Data columns (total 11 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   Entry Type       1 non-null      object 
 1   Value            1 non-null      float64
 2   Currencs         1 non-null      object 
 3   Category         1 non-null      object 
 4   Person           0 non-null      float64
 5   Account          1 non-null      object 
 6   Counter Account  0 non-null      float64
 7   Group            0 non-null      float64
 8   Note             1 non-null      object 
 9   Recurring        1 non-null      object 
 10  Unnamed: 11      0 non-null      float64
dtypes: float64(5), object(6)
memory usage: 96.0+ bytes
start writing 1 entries:
write 0-1
send: b'POST /api/v2/write?org=my-org&bucket=my-bucket&precision=ns HTTP/1.1\r\nHost: localhost:8086\r\nAccept-Encoding: identity\r\nContent-Length: 185\r\nContent-Encoding: identity\r\nContent-Type: text/plain\r\nAccept: application/json\r\nAuthorization: Token my-token\r\nUser-Agent: influxdb-client-python/1.24.0dev0\r\n\r\n'
send: b'bookings,Category=LS\\\\ >\\\\ Groceries,Entry\\ Type=Expense Account="Sparkasse Giro",Currencs="EUR",Note="Penny vom 14.06. , falsches konto",Recurring="no",Value=-26.56 1560729600000000000'
reply: 'HTTP/1.1 204 No Content\r\n'
header: X-Influxdb-Build: OSS
header: X-Influxdb-Version: 2.0.9
header: Date: Wed, 03 Nov 2021 09:24:53 GMT

Can you share your CSV file?

Also if you would like to try a serialization from DataFrame to LineProtocol you can use something like:

from influxdb_client.client.write.dataframe_serializer import data_frame_to_list_of_points
from influxdb_client.client.write_api import PointSettings
points = data_frame_to_list_of_points(data_frame=dataFrame,
                                      data_frame_measurement_name=self.measurement,
                                      data_frame_tag_columns=self.tagColumns,
                                      point_settings=PointSettings())

print(points)

Regards

s3n · November 6, 2021, 1:27pm

Hi @bednar thanks a lot, for having a look!

Sorry, in my linked code there is the working code. So it fails if at line 29 instead of only having
self.tagColumns = ['Entry Type', 'Category', 'Person']
adding the ‘Account’ Column as well:
self.tagColumns = ['Entry Type', 'Category', 'Person', 'Account']
Which is what I want.

So either
self.tagColumns = ['Entry Type', 'Category', 'Person']
and
self.tagColumns = ['Entry Type', 'Account', 'Person']
are working but not together
self.tagColumns = ['Entry Type', 'Category', 'Person', 'Account']

Fails also with the single line CSV.

But I just found out a few things:

the issue is solved when I remove the comma in the “Note” column. So instead of having "Penny vom 14.06. , falsches konto" writing "Penny vom 14.06. falsches konto" works.
Same goes for just removing the space before the comma so "Penny vom 14.06., falsches konto" works also

Here’s another example CSV which fails:

sep=;
Date;Entry Type;Value;Currencs;Category;Person;Account;Counter Account;Group;Note;Recurring;
"01.10.2018";"Expense";"-1,00";"EUR";"Testcategory";"";"Testaccount";"";"";"This , works not";"no";
"01.10.2018";"Expense";"-1,00";"EUR";"Testcategory";"";"Testaccount";"";"";"This, works";"no";

For my code I can easily write a fix for that, but maybe there’s an issue with the influx python client. which causes that.

Running your code regarding the LineProtocol serialization with the example CSV gives that result:

['bookings,Account=Testaccount,Category=Testcategory,Entry\\ Type=Expense ,Currencs="EUR",Note="This  works not",Recurring="no",Value=-1.0 1538352000000000000', 'bookings,Account=Testaccount,Category=Testcategory,Entry\\ Type=Expense Currencs="EUR",Note="This, works",Recurring="no",Value=-1.0 1538352000000001000']

edit:
running the code again but with 'Account' not added as a tag:

['bookings,Category=Testcategory,Entry\\ Type=Expense Account="Testaccount",Currencs="EUR",Note="This , works not",Recurring="no",Value=-1.0 1538352000000000000', 'bookings,Category=Testcategory,Entry\\ Type=Expense Account="Testaccount",Currencs="EUR",Note="This, works",Recurring="no",Value=-1.0 1538352000000001000']

So it says "This , works not" how it should be instead of "This works not"

Having a value "This ,works not" is also failing. So it only has to do with the leading space before a comma.

bednar · November 10, 2021, 10:24am

@s3n thanks for detail explanation.

I’ve prepared PR #359 with fixed version of client. If you would like to use dev version of client then install client via:

pip install git+https://github.com/influxdata/influxdb-client-python.git@fix/dataframe-lp-empty

Regards

Topic		Replies	Views
Newb -influx write to CSV question InfluxDB 2 influxdb , csv	4	804	June 9, 2022
Struggling to write data with multiple fields via python InfluxDB 2 python	1	435	July 8, 2024
Getting error when trying to write to Influxdb InfluxDB 2 grafana , flux	12	627	December 6, 2023
Curl add data to influxdb says {"error":"unable to parse invalid field format"} InfluxDB 2	0	537	March 3, 2023
Strange error unable to parse just for some rows of my data in InfluxDB 1.8 influxdb	0	494	December 5, 2021

Writing entries gives 'invalid field format' error but only if 2 tags are added together. Separately works fine

Related topics