Can't manage to guess correct syntax for CSV import with one-to-many relation

Data Service
1

Hi @mark-piller and team,

Here’s a call for help, after hours of searching, reading the documentation and every forum item even remotely related to the subject, I’m giving up on this one and would appreciate any help.

I am trying to import some data into my database so that I can continue developing. I will need to import more production data later on anyways, so I need to get this working.

In that table, there is a one-to-many relation. I think of it as a list of items which are tagged with one or more categories. When importing, I can’t bypass those tags, they need to be in there.

1. Header row

I have tried both the simple and more elaborate header row syntaxes to no avail:
tags__Tags__bcklsFK__ONE_TO_MANY
or
"tags({""constraints"":[],""type"":""RELATION_LIST"",""autoLoad"":false,""relatedTable"":""Tags""})"

2. Data rows

And I am not sure about the syntax for the data rows. Should it be some CSV escaped JSON ? Such as:
"[""155CDE7A-BC3D-45B8-B1F0-90199A40924E"",""E4DFAFC4-11E0-4BC0-9232-13012D393965""]"
Or a simple comma separated list ? Such as:
"155CDE7A-BC3D-45B8-B1F0-90199A40924E,E4DFAFC4-11E0-4BC0-9232-13012D393965"
Or something different ?

Anyhow, the column is never recognized by the Data service and when I go ahead, it never shows up in the data table. Strangely, that column is never mentioned in the import logs, although it really is in the file.

Could you help in telling me what I’m doing wrong ? Thanks.

2

Here’s a tip to avoid spending hours:

  1. Create your schema (sounds like you have already done that)
  2. Enter some sample data in all related tables
  3. Switch to Manage > Export and run the export for your tables.
  4. The export file is located in the Files section, see the “export” directory.

Take a look at the file as it will contain precisely the formatting of the header and your sample data.

Regards,
Mark

3

Hi @mark-piller and thanks for the quick reply.

I have seen that advice from you in other posts as well. I would have gladly done that. However, exporting is currently not available in my plan… And I suspect others might have also failed to do so for the same reason.

Is there another way to get this information ?

Best regards,

Nicolas

4

The header row element looks correct, however, without seeing the rest of the files, I cannot say conclusively. As for the data rows, it should be a comma-delimited list of values:

155CDE7A-BC3D-45B8-B1F0-90199A40924E,E4DFAFC4-11E0-4BC0-9232-13012D393965

You’re welcome to attach your file and we will gladly inspect it for errors for you.

5

Hi again @mark-piller ,

Thank you for confirming the format of the data rows. Before replying I just tried again twice with this new info, and with the headers either “simple” or “elaborate”. But still no go, and even before I finalize the import the column doesn’t show at all when the file is analyzed.

Here’s a simplified version so that you may correct my errors.

Cheers.
Items.csv (371 Bytes)

6

This is an incomplete set of files. Since you’re using relations, I’d also need to see Countries.csv and Tags.csv. Could you attach them as well?

7

Well the data is already in the database in the appropriate tables, so I don’t have the files themselves, but here’s what they probably would look like if exported.

Tags.csv (241 Bytes)
Countries.csv (70 Bytes)

8

Please take a look at the attached file below. This is the proper way to structure the relations in the import files. I will make sure the Import documentation is updated as soon as possible.
export_2022_03_29_23_23_53.zip (1.2 KB)

9

Thank you @mark-piller for this reply with the examples. Honestly, I would never have guessed that the relation being in the Items table, the relations had in fact to be imported in the Tags file. Updated documentation would surely be great for other puzzled users.

[ As a side note, this solution seems rather unpractical because as I understand it, this requires creating two files instead of one. Plus, when adding more Items, their ids are not yet known, so this requires importing the Items, extracting their Ids and populating the second Tags file with these ids and then importing this second file. It’s quite a complication. ]

Anyhow, I attempted the imports again, and I’m sorry to say, still no success. I tried both ticking and unticking the “Objects in the CSV file must overwrite existing objects in the database”. When unticked, there is an error in the logs, probably because since the items already exist they can’t be “updated” somehow, although it would just be referencing them in a relation. And when ticked, the import goes straight through, but nothing happens. The logs just say “Saved 0 records.” Really strange.

I fear I’m going to have to program my own import service to cope with this situation. Backendless is really great, and it’s the best I found to launch my app in a few months. But I’m sorry to say that inserting new external data into Backendless seems to be a bit of a weak spot. Any improvement would be welcome.