How to add table relationship to csv file

General
1

I have 4 large csv files that ultimately need to be imported I want to import my info.csv file and to create a 1to1 relationship between it and the other three files (which will be tables when imported)

all the tables have One field in common (ID) so I need to add a relationship to all the csv files so when I import them the relationships will be set up. These are large files so importing and then adding the relationships manually is not really an option.

I have looked at

and

but have not been able to figure out what I need to do to my csv files to set up the relationships i need.

I created two test tables in backendless, set up the 1to1 relation (both ways ) and thought I’d export them as csv to see how it was done, but it seems the springboard plan does not allow me to export tables.

can you point me to some documentation as to how I do this?

2

Here’s a cookbook article that describes the structure of the CSVs with relationships:

3

Thanks for the response. I did look at that but it was not clear to me how to do it. for example looking at the first two lines of the customer.csv file (in the cookbook)

name,phoneNumber,"objectId({""type"":""STRING_ID""})","address({""type"":""RELATION"",""relatedTable"":""Address""})"
"Joe","555-1212","765EF32B-A715-4C54-84D6-EB1E8B622121","F1202A07-3775-4AEE-AC72-9EA0FC1BC54C"

there are 4 fields here name, phone, object id, and address which is a relation.
so i can add the relation as a new field on my csv, but where does the 4th column entry come from
in the case above that’s “F1202A07-3775-4AEE-AC72-9EA0FC1BC54C” on the second line.

in my case I have one field “ID” which is the same in all 4 tables. so I need to add a relationship and then match the rows with the same ID field in each table ( that is they need to be related )
so I can add the relations but how to I make it match the ID field in each table (IE the rows in each table must be bound to the rows with the matching ID ) I hope this makes sense. I can’t build the relations manually I’ve got about 98000 records in each table.

HH

4

Hello @hharrington

I recommend that you create the schema you expect with simple data in Backendless. Then export it and see how everything is arranged there.

Regards

5

Thanks for the response. My first thought was to do exactly that but as noted in my post: “it seems the springboard plan does not allow me to export tables.”

image

6

@hharrington

there are 4 fields here name, phone, object id, and address which is a relation.
so i can add the relation as a new field on my csv, but where does the 4th column entry come from
in the case above that’s “F1202A07-3775-4AEE-AC72-9EA0FC1BC54C” on the second line.

Column “address” has relation to table “Address”
“F1202A07-3775-4AEE-AC72-9EA0FC1BC54C” is objectId of records from table “Address”
Look at the “Address” table. Miami has objected “F1202A07-3775-4AEE-AC72-9EA0FC1BC54C”
So Joe’s address is “Miami”

in my case I have one field “ID” which is the same in all 4 tables. so I need to add a relationship and then match the rows with the same ID field in each table ( that is they need to be related )…

It’s not entirely clear to me here.
Can you create the schema you expect with simple data? Then, provide us please with your appId and we will provide you with a schema of your data in the CSV format.

7

awesome!

my app id : 9359F741-6D5D-AEF2-FF22-4834A2050400

the tables in question:
test_samp_info
test_Proc_Ult
test_trace
test_oxides

there are other tables, but they are from the missions. If you look at this schema and see something suboptimal please let me know. the end result with the full data will be 270 fields across 4 tables with like fields grouped by table. There one field Sample_ID, that appears in all tables and I have used to create the relationships. I expect to end up with maybe 200k records.

if you could export these to csv that would be really helpful.

thanks
H

8

Hello @hharrington!

I’ve set up tables similar to your structure and exported them to a CSV file.
This should help you better understand how it all works.

test_samp_info:

"County({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Latitude({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Longitude({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Sample_ID({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","State({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","objectId({""constraints"":[],""type"":""STRING_ID"",""dataSize"":36})","ownerId({""constraints"":[],""type"":""STRING"",""dataSize"":36})","created({""constraints"":[],""type"":""DATETIME""})","updated({""constraints"":[],""type"":""DATETIME""})","oxides({""constraints"":[],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""test_oxides"",""relationIdentificationColumn"":""Sample_ID"",""dynamicProps"":{""localData"":{}}})","proc_ult_anal({""constraints"":[],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""test_Proc_Ult"",""relationIdentificationColumn"":""Sample_ID"",""dynamicProps"":{""localData"":{}}})","trace({""constraints"":[],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""test_trace"",""relationIdentificationColumn"":""Sample_ID"",""dynamicProps"":{""localData"":{}}})"
"JOHNSON","37.8881","82.8131","D171579","Kentucky","2891C2BE-0704-4590-9674-6450432E1E63",null,"2023-05-19T12:01:11.072Z","2023-05-19T12:45:08.841Z","8FD76EAA-6D0A-40CE-8B3B-59C2EDB96994","11B29D6B-4014-4008-A77C-1DB44E6DAA15","2903BE46-57A7-44F5-8A5A-6681EB48B4F7"
"JOHNSON","37.9006","82.8519","D171582","Kentucky","59D571C8-52E7-4884-B935-A1B4E2A2ECF8",null,"2023-05-19T12:01:06.873Z","2023-05-19T12:45:09.202Z","EA24D157-10B5-4796-A307-9AB72A33E4D7","F803A5CE-8F81-4268-B861-6472C64192D8","AC9080B6-274B-43BF-A351-9173D6DC459F"
"LAWRENCE","38.0272","82.805","D168263","Kentucky","B6CB0C3F-C775-4E65-8BCF-9CAABBF28160",null,"2023-05-19T11:50:35.625Z","2023-05-19T12:45:04.280Z","A10F4DD0-E7BC-43CA-8F18-B3F748D384D7","8C910110-090E-49FC-ABB6-6C9726F1B9AE","61B8A1EB-CA80-4393-88CB-4D994C2A1C10"

test_Proc_Ult.csv:

"Fixed_Carbon({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Fixed_Carbon_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Moisture({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Moisture_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Sample_ID({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Volatile_Matter({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Volatile_Matter_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","objectId({""constraints"":[],""type"":""STRING_ID"",""dataSize"":36})","ownerId({""constraints"":[],""type"":""STRING"",""dataSize"":36})","created({""constraints"":[],""type"":""DATETIME""})","updated({""constraints"":[],""type"":""DATETIME""})"
null,"B",null,"B","D171579",null,"B","11B29D6B-4014-4008-A77C-1DB44E6DAA15",null,"2023-05-19T11:54:10.161Z","2023-05-19T12:41:57.322Z"
null,"B",null,"B","D168263",null,"B","8C910110-090E-49FC-ABB6-6C9726F1B9AE",null,"2023-05-19T11:58:21.920Z","2023-05-19T12:42:14.241Z"
null,"B",null,"B","D171582",null,"B","F803A5CE-8F81-4268-B861-6472C64192D8",null,"2023-05-19T11:59:00.769Z","2023-05-19T12:42:32.805Z"

test_trace:

"Al({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Al_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","GSAsh_Dry({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","GSAsh_Dry_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Sample_ID({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Si({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Si_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","objectId({""constraints"":[],""type"":""STRING_ID"",""dataSize"":36})","ownerId({""constraints"":[],""type"":""STRING"",""dataSize"":36})","created({""constraints"":[],""type"":""DATETIME""})","updated({""constraints"":[],""type"":""DATETIME""})"
"31000","d","18.3",null,"D171579","50500","d","2903BE46-57A7-44F5-8A5A-6681EB48B4F7",null,"2023-05-19T11:54:52.914Z","2023-05-19T12:42:06.598Z"
"3500","d","8.71",null,"D168263","5290","d","61B8A1EB-CA80-4393-88CB-4D994C2A1C10",null,"2023-05-19T12:00:23.369Z","2023-05-19T12:42:18.998Z"
"2540","d","7.29",null,"D171582","6450","d","AC9080B6-274B-43BF-A351-9173D6DC459F",null,"2023-05-19T11:59:41.383Z","2023-05-19T12:42:38.652Z"

test_oxides:

"GSAsh({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","GSAsh_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Remnant_Moisture({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Remnant_Moisture_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Sample_ID({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","SiO2({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","objectId({""constraints"":[],""type"":""STRING_ID"",""dataSize"":36})","ownerId({""constraints"":[],""type"":""STRING"",""dataSize"":36})","created({""constraints"":[],""type"":""DATETIME""})","updated({""constraints"":[],""type"":""DATETIME""})"
"2.5",null,"4","e","D171579","46","8FD76EAA-6D0A-40CE-8B3B-59C2EDB96994",null,"2023-05-19T11:57:26.513Z","2023-05-19T11:58:05.726Z"
"8.86",null,"4.78","r","D168263","36","A10F4DD0-E7BC-43CA-8F18-B3F748D384D7",null,"2023-05-19T11:51:15.361Z","2023-05-19T11:51:49.612Z"
"6.5",null,"4","e","D171582","46","EA24D157-10B5-4796-A307-9AB72A33E4D7",null,"2023-05-19T11:56:53.264Z","2023-05-19T11:57:17.566Z"

Regards,
Alexander

9

thanks for the response. I downloaded these and they do import! progress. However my full data dump will not have the object id fields which appears to be how the relations are mapped (per row) do I just generate my own and fill them in on the objectid field in each table and the reference to that table ? if so, what characters and what length should I be using to make my ids?

EDIT: i just tried using my own object ids but when it imported them, they were all blank
EDIT2 : i also tried using the value of the sample_id field for the object id’s but that did not work either.

I really need to be able to import the tables with the references populated as manually assigning them is not feasible.

10

Hi @hharrington

Object relationships are established based on the objectId field, so this field is mandatory.
I have replaced our identifiers with the ones you use, specifically sample_id .
The import process was successful and all the data has been imported with the relationships properly established.
Here are the data I used for the import:

test_samp_info.csv:

"County({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Latitude({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Longitude({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Sample_ID({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","State({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","objectId({""constraints"":[],""type"":""STRING_ID"",""dataSize"":36})","ownerId({""constraints"":[],""type"":""STRING"",""dataSize"":36})","created({""constraints"":[],""type"":""DATETIME""})","updated({""constraints"":[],""type"":""DATETIME""})","oxides({""constraints"":[],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""test_oxides"",""relationIdentificationColumn"":""Sample_ID"",""dynamicProps"":{""localData"":{}}})","proc_ult_anal({""constraints"":[],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""test_Proc_Ult"",""relationIdentificationColumn"":""Sample_ID"",""dynamicProps"":{""localData"":{}}})","trace({""constraints"":[],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""test_trace"",""relationIdentificationColumn"":""Sample_ID"",""dynamicProps"":{""localData"":{}}})"
"JOHNSON","37.8881","82.8131","D171579","Kentucky","D171579",null,"2023-05-19T12:01:11.072Z","2023-05-19T12:45:08.841Z","D171579","D171579","D171579"
"JOHNSON","37.9006","82.8519","D171582","Kentucky","D171582",null,"2023-05-19T12:01:06.873Z","2023-05-19T12:45:09.202Z","D171582","D171582","D171582"
"LAWRENCE","38.0272","82.805","D168263","Kentucky","D168263",null,"2023-05-19T11:50:35.625Z","2023-05-19T12:45:04.280Z","D168263","D168263","D168263"

test_Proc_Ult.csv:

"Fixed_Carbon({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Fixed_Carbon_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Moisture({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Moisture_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Sample_ID({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Volatile_Matter({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Volatile_Matter_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","objectId({""constraints"":[],""type"":""STRING_ID"",""dataSize"":36})","ownerId({""constraints"":[],""type"":""STRING"",""dataSize"":36})","created({""constraints"":[],""type"":""DATETIME""})","updated({""constraints"":[],""type"":""DATETIME""})"
null,"B",null,"B","D171579",null,"B","D171579",null,"2023-05-19T11:54:10.161Z","2023-05-19T12:41:57.322Z"
null,"B",null,"B","D168263",null,"B","D168263",null,"2023-05-19T11:58:21.920Z","2023-05-19T12:42:14.241Z"
null,"B",null,"B","D171582",null,"B","D171582",null,"2023-05-19T11:59:00.769Z","2023-05-19T12:42:32.805Z"

test_trace.csv:

"Al({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Al_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","GSAsh_Dry({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","GSAsh_Dry_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Sample_ID({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Si({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Si_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","objectId({""constraints"":[],""type"":""STRING_ID"",""dataSize"":36})","ownerId({""constraints"":[],""type"":""STRING"",""dataSize"":36})","created({""constraints"":[],""type"":""DATETIME""})","updated({""constraints"":[],""type"":""DATETIME""})"
"31000","d","18.3",null,"D171579","50500","d","D171579",null,"2023-05-19T11:54:52.914Z","2023-05-19T12:42:06.598Z"
"3500","d","8.71",null,"D168263","5290","d","D168263",null,"2023-05-19T12:00:23.369Z","2023-05-19T12:42:18.998Z"
"2540","d","7.29",null,"D171582","6450","d","D171582",null,"2023-05-19T11:59:41.383Z","2023-05-19T12:42:38.652Z"

test_oxides.csv:

"GSAsh({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","GSAsh_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Remnant_Moisture({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Remnant_Moisture_Q({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Sample_ID({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","SiO2({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","objectId({""constraints"":[],""type"":""STRING_ID"",""dataSize"":36})","ownerId({""constraints"":[],""type"":""STRING"",""dataSize"":36})","created({""constraints"":[],""type"":""DATETIME""})","updated({""constraints"":[],""type"":""DATETIME""})"
"2.5",null,"4","e","D171579","46","D171579",null,"2023-05-19T11:57:26.513Z","2023-05-19T11:58:05.726Z"
"8.86",null,"4.78","r","D168263","36","D168263",null,"2023-05-19T11:51:15.361Z","2023-05-19T11:51:49.612Z"
"6.5",null,"4","e","D171582","46","D171582",null,"2023-05-19T11:56:53.264Z","2023-05-19T11:57:17.566Z"

Regards,
Viktor

11

ok I"m not sure what I am doing wrong. to get system generated object id’s I removed the objectid column. I then imported my tables and schema visual modeler shows manual_test_import_samp_info does have a relationship with the 3 other tables manual_test_import_oxides, manual_test_import_Proc_Ult, and manual_test_import_trace

however the columns for the relationships in manual_test_import_samp_info are blank.
Here is the file i uploaded. you can see the columns for the relationships are populated.

but on import they are blank in the table.

"County({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Latitude({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Longitude({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","Sample_ID({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})","State({""constraints"":[],""type"":""STRING"",""dataSize"":250,""dynamicProps"":{""localData"":{}}})",""dataSize"":36})","ownerId({""constraints"":[],""type"":""STRING"",""dataSize"":36})","created({""constraints"":[],""type"":""DATETIME""})","updated({""constraints"":[],""type"":""DATETIME""})","oxides({""constraints"":[],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""manual_test_import_oxides"",""relationIdentificationColumn"":""Sample_ID"",""dynamicProps"":{""localData"":{}}})","proc_ult_anal({""constraints"":[],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""manual_test_import_Proc_Ult"",""relationIdentificationColumn"":""Sample_ID"",""dynamicProps"":{""localData"":{}}})","trace({""constraints"":[],""type"":""RELATION"",""autoLoad"":false,""relatedTable"":""manual_test_import_trace"",""relationIdentificationColumn"":""Sample_ID"",""dynamicProps"":{""localData"":{}}})"
JOHNSON,37.8881,82.8131,D171579,Kentucky,null,2023-05-19T12:01:11.072Z,2023-05-19T12:45:08.841Z,D171579,D171579,D171579
JOHNSON,37.9006,82.8519,D171582,Kentucky,null,2023-05-19T12:01:06.873Z,2023-05-19T12:45:09.202Z,D171582,D171582,D171582
LAWRENCE,38.0272,82.805,D168263,Kentucky,null,2023-05-19T11:50:35.625Z,2023-05-19T12:45:04.280Z,D168263,D168263,D168263
12

Hi @hharrington

You should not delete the objectId column under any circumstances. Please restore it wherever you have removed it.
Use the same values for the objectId column as you have in Sample_ID.
Essentially, you will have two duplicated columns, objectId and Sample_ID, but I don’t think it will be a problem at this stage.
At least, this way you can easily prepare large tables for import with pre-established relationships.
Later, if new records are added to the tables within the application, the objectId for new records will be generated by the server in the usual format and will differ from the values in your Sample_ID column. However, you will establish the relationships within the application.
I hope you manage to prepare everything and the import process goes smoothly.
Once again, carefully review the working examples I posted in my previous message.

Regards.
Viktor

13

Thanks for your response.
I didn’t want to use the sample_id as the objectId because new objects (as opposed to imported ones) will get an objecid generated by backendless and I want them all to be the same format.

I think I have solved this issue this way: I uploaded the table without objectId or relationship, manually added the relationship via schema table editor.

then wrote code(less) to iterate through all rows in the table and set the relationship. This seems to have worked. let me know if there is a better way to achieve this result.

image

image
image818×590 23 KB