I need to be able to import csv files in to the data base where one of the columns in the csv file is a relation column to a table already existing in the database. The relevant extract from the backendless docs is below but this is for the scenario where you are import data for both the parent & child tables at the same time. In my scenario the parent table will already be established. What format do I need to use for the column name in the child table in my example so the relations will be established with the pre-existing parent table?
"The format of the column name defining a one-to-many relation consists of column name (in the parent object), name of the file containing parent entities and a special marker designating the column as a relation. The general format of the column name is:columnName__Filename__bcklsFK__ONE_TO_MANY
When you’re importing tables which already exist in your app - there would be no overwriting. So, you can import both - parent and child tables and if the parent table already exist - relations would be established between existing parent objects and imported child objects.
Both - child and parent csv files - should be present while importing.
I can offer you the following workaround: export existing table which contains parent objects and then import them together - child.csv and exported parent.csv.
Hi Alex, I think I tried what you suggested but it did not work. Here is what I tried, I have 2 tables “categories” and “subcategories”. There is a one to many relationship where a category can have multiple subcategories. This is established by having a category column in the subcategories table which references the category record in the categories table.
All categories data is pre-existing, I need to be able to add new data to the subcategories table with relationship to the categories table using import. I have attached small sample csv files that I used to try and do the import as you suggested. The import failed, the log is below:
16:15:50 IMPORT_DATA Importing Started.
16:15:50 IMPORT_DATA Start import table: Categories
16:15:50 IMPORT_DATA Error inserting row to table Categories com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘11806B5A-D70F-288C-FFB4-3A23C1FB3400’ for key ‘PRIMARY’
16:15:50 IMPORT_DATA Error inserting row to table Categories com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘26100CF3-0F6D-EC29-FF27-FBF0704DC100’ for key ‘PRIMARY’
16:15:50 IMPORT_DATA Table ‘Categories’ was imported at 0 seconds
16:15:50 IMPORT_DATA Finish import table: Categories
16:15:50 IMPORT_DATA Start import table: Subcategories
16:15:50 IMPORT_DATA Error inserting row to table Subcategories java.lang.NumberFormatException: For input string: “26100CF3-0F6D-EC29-FF27-FBF0704DC100”
16:15:50 IMPORT_DATA Error inserting row to table Subcategories java.lang.NumberFormatException: For input string: “26100CF3-0F6D-EC29-FF27-FBF0704DC100”
16:15:50 IMPORT_DATA Error inserting row to table Subcategories java.lang.NumberFormatException: For input string: “26100CF3-0F6D-EC29-FF27-FBF0704DC100”
16:15:51 IMPORT_DATA Table ‘Subcategories’ was imported at 0 seconds
16:15:51 IMPORT_DATA Finish import table: Subcategories
16:15:51 IMPORT_DATA Importing Finished.
Exceptions while importing “Categories” table are normal - you’re able to see them if such object already exists.
Exceptions while importing “Subcategories” are caused by mistakes in .csv file. Column “subcategory_id(INT)” contains not-integer values - it causes exceptions. ObjectId’s of parent objects should be placed in column “category__Categories__bcklsFK__ONE_TO_MANY”. I’ve changed the Subcategories.csv file - it’s attached and now import should work well.
Please, inform me if this file has been created by backendless export. If so - it might be our bug and we shall be able to investigate it.
Also note that INT column should not contain empty cells - it would cause exceptions while importing.
best regards,
Alex
Hi Alex, thanks for the update. I tried the update again with my original categories file and your new subcategories file. The subcategories rows are being imported now but there is a separate problem. The relation being created between the tables is now in a new relationship column, not the existing one. See the attached screenshots to see the pre-import screen and what the subcategories table looks like after the import. In the pre-import screen shot see that the category column is being created as part of the Categories table even though in my import files the category relationship column is part of the Subcategories table. In the after the import screenshot see that there are now 2 relationship columns in the Subcatgories table. The new ones created as part of the import (the top 6 rows), the the rest of the rows are the preexisting data with the manually created relationship.
The original relation was created as follows:
Went to schema editor in subcategories table
Added a new column called category of type data object relationship one to many
Edit the category value for each row in the subcategory table to point to the correct category and saved each edit.
After doing this each subcategory row has a category row assigned to it
How can I get the import to use the existing relationship column? Am I creating the relationship column wrong in the first place? It seems natural to me that the relationship column should be created in the child table and each child record updated to point to the relevant master record. Should I instead be creating the relationship column in the master table and update each master record to point to all the child records associated with it? This seems very backwards to me but this seems to be how the import is creating the relationship.
Hi, Greg!
I understand your confusion. Let me explain the logic of relationships migration.
Type one-to-one. Considering that we have two tables: “Parent” and “Child” and “Parent” references “Child” by column named “relationToChild” with type “Data object relationship”, cardinality “one to one”. In this case exported table “Parent” would have column named “relationToChild__Child__bcklsFK__ONE_TO_ONE”. This column would contain “Child” objectId.
Type one-to-many. Same situation as above, but “relationToChild” has cardinality “one to many”. In this case logic is different. Here exported table “Child” would contain column named “relationToChild__Parent__bcklsFK__ONE_TO_MANY”. And now this column would contain “Parent” objectId, but when importing column “relationToChild” would be created in table Parent.
About your case: if single Category is referenced by many Subcategories - it’s better to create a column in table “Category” with cardinality “one to many”. The other way is creating column in table “Subcategories” (as you did) - but cardinality in this case should be one-to-one.
