Coen Hendrikx
Rank: #2632
152Views
14Comments
Solved
Import new entities from Excel results in "No records to show"
Question

Hi, as a newbee my first question on thisforum:

After following a course I was trying toimport data from Excel via "Import New Entities from Excel". For mostdatatabs this worked perfectly. However, importing some of the other tabs resulted in problems. 

Although I do see the appropriate columnheaders, after publishing, no records where shown ("No records toshow"). Strangely, when I left-click the entity, and select edit entitydata/example record I do see one record fully filled in.

Again: this only occurs to some tabs,while other tabs on the same file worked perfectly.

Thanks to this forum I did try somesuggestions, including:

-      Putthe data in a separate excel file

-      Remove  a column that had no data in it

-      Importing one column, try to see the data, if data was there, adding another columns, try to see the data…--> this worked, but with different results. Never managed to exclude the "problem causer". 

As this is the most basic thing to do inOutsystems, I recon I do something wrong, but I can't figure out what. I can'tfind the error-log as well (also suggested on the forum). 

 

Can anyone help me out?

qOutSystemsNoRecordsToShow.png

0
0
on 2019-11-06
Copy link to comment
Miguel Verdasca
Rank: #36
Solution

Hi Coen,

I don't know if you saw my OML.
I have the actions of exporting the tables in timers, which are scheduled for when you publish. I put a breakpoint in excel (see the image below, and put in the action of importing the sheet TblPQfcrs (BootstrapTblPQfcrs) the table I found empty. I start the debugger and then publish again, just change a widget a little up or down, whatever you need to publish later. At the end of it, these timers will start, and will give you these errors that I showed you.


As you can see from the messages, in certain fields it is waiting for one type of variable and another. To validate if what you have is well done, is in excel, leave only one line per sheet with values, and validate that the types are as expected. And so make sure you have your right logic.

In case the correct logic happens, I suggest that you make validations field by field, to prevent the user to enter incorrect types and have these problems. Basically before assigning the excel line to the structure, validate the values and if not what is expected, either convert the value or discard the line, but this is already a business decision.

If you can't, I can give you more help, but it would be helpful to have your own OML with this part of importing the excel file. But I started by doing that test I suggested, where you put all the sheets, with just one row of data.


Cheers,
Nuno Verdasac

See solution in context
0
0
on 2019-11-08
Copy link to comment
Hugo Antunes
Rank: #576

Hi Coen,


Can you please share the part of the code you´re having an issue so we can try to help you?


Kind regards,

Hugo

0
0
on 2019-11-06
Copy link to comment
Coen Hendrikx
Rank: #2632

Hugo Antunes wrote:

Hi Coen,


Can you please share the part of the code you´re having an issue so we can try to help you?


Kind regards,

Hugo

Tnx for your reply. I'm not sure if I am able to as I am working on the free-version (doing a POC to convince my projectleader to give it a try:-). Can't see where I am able to export the code, and I presume that this can only be done via a full-account. However, I will upload the excel file as suggested by Amal Raj


0
0
on 2019-11-06
Copy link to comment
Amal Raj
mvp_badge
MVP
Rank: #99

Hello Coen, 

Can share the sample excel file. Are you trying to import any foreign keys ?

I had done an excel import for the NorthWind DB, checkout the forge component here


Hope it helps !!! 

0
0
on 2019-11-06
Copy link to comment
Coen Hendrikx
Rank: #2632

Amal Raj wrote:

Hello Coen, 

Can share the sample excel file. Are you trying to import any foreign keys ?

I had done an excel import for the NorthWind DB, checkout the forge component here


Hope it helps !!! 

Hi Amal, 

I made a database in MsAccess, having tables that were related to each other. For every table in the database I made an export to Excel, resulting in the attached document (with manipulated data). 

As you can see, the (foreign) key values are included in the export, however, there is no relationship defined between the data in the different excel-tabs. I was planning to define the relations in Outsystems after importing the data. 

I also tried to create one excelfile for the content of every tab in the attached file, but with the same result: No records. 


Is the data causing the problem?


One of the "problemtabs" is tblBSPassetProd

DataToBeImported.xlsx

0
0
on 2019-11-06
Copy link to comment
Nélio Dionisio
Rank: #501

Hi :)


I think it could be related to the difference in the type of the attribute between excel and your database. See the type of the attibutes you  have in the column with no records, perhaps your attibutes are boolean, or the the size text are smaller. 

Remember you need to publish for the data to be imported into the entity. And then you can see it in the "View Data". This is because the importing of the data is bound to a timer that will run after you publish the module.

Hope it helps,


0
0
on 2019-11-06
Copy link to comment
Coen Hendrikx
Rank: #2632

Nélio Dionisio wrote:

Hi :)


I think it could be related to the difference in the type of the attribute between excel and your database. See the type of the attibutes you  have in the column with no records, perhaps your attibutes are boolean, or the the size text are smaller. 

Remember you need to publish for the data to be imported into the entity. And then you can see it in the "View Data". This is because the importing of the data is bound to a timer that will run after you publish the module.

Hope it helps,


Hi! The problem is that no data at all is imported for some tabs. And I tried to exclude some colums but still with the same.

(And concerning the publishing remark: I did this everytime, but still the same message: no records to show. But, the strange thing as mentioned in the question: when I left-click the entity, and select edit entitydata/example record I do see one record fully filled in.


0
0
on 2019-11-06
Copy link to comment
Paulo Jadaugy
Rank: #755

Hi Coen,

To have a clear picture of the error you should check the timer logs in Service center for your module.
There you can see the exception/error you're getting when doing the bootstrap.

Typically these kind of errors are due to invalid data types.


Regards,
Paulo Jadaugy

0
0
on 2019-11-06
Copy link to comment

Hello,

I grabbed your excel and created all the logic needed to import the tables and it went well. You can see OML to see if that's what you need, and if that's what you want and don't know how to do it, I'll explain it to you.

Cheers,
Nuno Verdasca

Tests.oml

0
0
on 2019-11-06
Copy link to comment



I believe that I find your problem... I've been debugging, and what happens is that you have some types that don't match what is created in the structure, I give you some examples below.

I suggest validating your file, or doing some kind of validation before trying to write the data because of the types used.

0
0
on 2019-11-06
Copy link to comment
Coen Hendrikx
Rank: #2632

Nuno Miguel Verdasca wrote:



I believe that I find your problem... I've been debugging, and what happens is that you have some types that don't match what is created in the structure, I give you some examples below.

I suggest validating your file, or doing some kind of validation before trying to write the data because of the types used.

This is what I am looking for (!), but how do I get there? How can I do the debugging myself? I do see the "Debugger" option but I can't find an option resulting in an errorlog as above. (And no training is offered on outsystems.com). 


Thanks again!

0
0
on 2019-11-06
Copy link to comment
Miguel Verdasca
Rank: #36
Solution

Hi Coen,

I don't know if you saw my OML.
I have the actions of exporting the tables in timers, which are scheduled for when you publish. I put a breakpoint in excel (see the image below, and put in the action of importing the sheet TblPQfcrs (BootstrapTblPQfcrs) the table I found empty. I start the debugger and then publish again, just change a widget a little up or down, whatever you need to publish later. At the end of it, these timers will start, and will give you these errors that I showed you.


As you can see from the messages, in certain fields it is waiting for one type of variable and another. To validate if what you have is well done, is in excel, leave only one line per sheet with values, and validate that the types are as expected. And so make sure you have your right logic.

In case the correct logic happens, I suggest that you make validations field by field, to prevent the user to enter incorrect types and have these problems. Basically before assigning the excel line to the structure, validate the values and if not what is expected, either convert the value or discard the line, but this is already a business decision.

If you can't, I can give you more help, but it would be helpful to have your own OML with this part of importing the excel file. But I started by doing that test I suggested, where you put all the sheets, with just one row of data.


Cheers,
Nuno Verdasac

0
0
on 2019-11-06
Copy link to comment
Coen Hendrikx
Rank: #2632

Hi Nuno, I hav efound the debugger, but now I am experiencing troubles with that; I entered the breakpoint and started the debugging, but the message "This module does not contain a default entry." appeared in chrome, after that I was noticed that only the client side was going to be debugged. 

For now I stop experiencing with importing data as this was just for a POC experience. You helped me a lot with valueable tips including the debugging and the data-type checkup. 

Now I will first focus on realizing a database and some screens to impress my manager. After that, I will continu trying to implement data directly from Excel, via the help you gave me above. 

tnx again, and all others, thanks as well!

(for the colleague-newbees: the debugging area is in the logic part: switch from data to logic (recht above). There you'll find the Bootstraps for every excel-worksheet you tried to implenent. From here, it should be possible to start the debugger.)

1
0
on 2019-11-08
Copy link to comment

You do not have to thank us, we are here to help each other, and only then will it be possible to grow the community. Any questions, I'm available to help you.

1
0
on 2019-11-09
Copy link to comment
Tapio Siltala
Rank: #39843

I have the same problem.

No errors when publishing, but some of the Excel text columns' data remain empty in the database sheet, even if the data in most of the other columns works ok. I have tried to change the column type in Excel from General to Text, but doesn't help.

So no errors. Any hints? 

0
0
on 5th Feb
Copy link to comment