Importing a Spreadsheet with Id's in it already.

Hi,

I tried to import a Spreadsheet with worksheet tab called "Capabilities"with the following into my Table "Capability":

  • Id - Long integer
  • Capability - text
  • ParentCapId - Long integer 
  • Description  - text.

I tried the following three alternatives:

  1. I tried importing the above as is: It does not give an error but ends up with no values being imported. I can add and edit records I create in my table, but it never reads in any form the spreadsheet.
  2. Next I tried importing with switching the Id value to Auto Increment = No, but it still does not bring the records in then switch back to auto increment = yes. I saw this as a solution in another post. Did not work for me.
  3. Finally I tried deleting the Id column in the spreadsheet and importing just the last three columns records, hoping that it would auto create the Id record and auto increment, but not. Strangely the record example from my spreadsheet shows in the example data on a form table, but it never adds it into my actual table once published.

How should I get my data into the DB? There are about 1000 records - I don't want to capture them manually! Thanks

Hi Charles,

Did you create an action to import your records from the Excel file? What exactly do you see in debug when you try inserting the values - what goes wrong? Do you see anything in the list returned from the ExcelToRecordList action?

Thanks Afonso. I'm still a newbie, only just figured out how to use the debugging after this question! I'll investigate, 

I just used the automatic XLS import where it sets everything up based on the right menu option under Data called "Import new Entities from Excel..." I'd expect it to just work as advertised on the tin!

Hi Charles,

Your second solution should work. Attached a simple example.

Regards,

Marcelo

Charles Edwards wrote:


Please mentioned error you get it while import a spreadsheet.


Please check below mentioned points before importing spreadsheet into DB:

1. Please check the column name in your excel file and Entity attribute name both values are same (Also check it is case sensitive).

2.if all are correct, Right-click the Entity, select Advanced , and then the option Create Action
to Bootstrap Data from Excel.

3.Browse to the Resources folder and select the .xlsx Excel file.

4.In the “Create Action to Bootstrap Data from Excel” window, ensure that the
column headers names from the excel file ( Excel Columns ) match
the names of the Entity attributes ( ‘Attributes’ ). 

If so, click on the Proceed button. Otherwise, click on the Cancel button and fix the names, or data
types, of the mismatched Movie Entity attributes. Then, redo the third step again.
Your Entity attributes must match in name and type what is in the Excel file.

Thanks.


Hi,

I tried to import a Spreadsheet with worksheet tab called "Capabilities"with the following into my Table "Capability":

  • Id - Long integer
  • Capability - text
  • ParentCapId - Long integer 
  • Description  - text.

I tried the following three alternatives:

  1. I tried importing the above as is: It does not give an error but ends up with no values being imported. I can add and edit records I create in my table, but it never reads in any form the spreadsheet.
  2. Next I tried importing with switching the Id value to Auto Increment = No, but it still does not bring the records in then switch back to auto increment = yes. I saw this as a solution in another post. Did not work for me.
  3. Finally I tried deleting the Id column in the spreadsheet and importing just the last three columns records, hoping that it would auto create the Id record and auto increment, but not. Strangely the record example from my spreadsheet shows in the example data on a form table, but it never adds it into my actual table once published.

How should I get my data into the DB? There are about 1000 records - I don't want to capture them manually! Thanks



Thanks will again in a few days when I have the time. Much appreciated for the answers! :-)