data boostrap on external database

Hi, I want to know if its possible to bootstrap data from excel files to an external database. Let say i create a connection and wrap an external db via integration studio as an extension.if import the extension into service studio, what is the possibility of bootstrap multiple files in excel to that external database . I just want to do data file transfer from excels files many excel files to external db hooked to outsystems in a semi automated or automated fashion.


Hi Tmlewin

I never did that, but don't know why it wouldn't be possible.
From the point of view of the Bootstrap Scaffolding, the external entities definition is no different from OutSystems entity definition. In fact, that's the idea: transparency.

You just need to be sure that the external entities can be written to, so the connection must use a user that has write access.

Other than that, it should work.

Are you having problems bootstrapping external entities?

Cheers

Eduardo Jauch wrote:

Hi Tmlewin

I never did that, but don't know why it wouldn't be possible.
From the point of view of the Bootstrap Scaffolding, the external entities definition is no different from OutSystems entity definition. In fact, that's the idea: transparency.

You just need to be sure that the external entities can be written to, so the connection must use a user that has write access.

Other than that, it should work.

Are you having problems bootstrapping external entities?

Cheers


Thanks Eduardo, i certainly understand that. The thing is i am doing some form of ETL, i have over 200 csv files in a directory some of the files ranges from 200kb to 250mb per file.Now i am thinking of how to use outsystems in importing those csv along with their data as tables in my external database. There is a caveat to it, the CSV files that was given to us is  in a very untidy format . for example some columns have values like piped quotes string e.g |Helium , "Neon"|, |Propane|    

So as you can see during manual imports once the sql tool detects  a comma it creates an additional column and leads to data truncation and failed import. I want to build a small import app that can help automate this use case in outsystems. I would appreciate if you have any tips that can put me in the right direction.Thanks

Hi

Not sure I can be of much help.
To make things easier, I would first "fix" the CSVs.
And I am not seeing any "easy way" of doing it. You probably will want to replace the comma separateto avoid conflict with the strings, like for example, replace the , by ;

The problem is that you need to create a routine to do that.
For example, you will need to count when you start the pipe and when it closes. This way you can decide if you are "inside" (and keep the comma) or "outside" and replace the comma by a semi colon.

You may find a tool that is able to do that, depending on your case. You may use a text editor or another tool to replace every |, by |; and see if this solves your issue when importing the data.

Cheers.

Hi,

You can use regex split to only split by the comma's outside the pipes. Since your files can be so big I wouldn't use the outsystems actions to create records in tables since that would take too much time. I would create on Outsystems database replicas from the tables in external database and use this component to do bulk inserts on outsystems database and then use a sql script to copy from um table to the another. Or you can change the component to be able to bulk insert into external databases (this would be the better option).

Regards,

Marcelo 

Marcelo has a good point here, because of the size of files. Being an external database, if you have direct access to it, probably you would be better using an external tool to import data. 

Not sure Regex will be able to solve the comma thing if this is irregular inside the file, but this can be an option. 

The problem with doing this with OutSystems, if I get the idea of Marcelo, is that you will need to upload those files to the OutSystems server, and use a more complex way of processing them doe their size (timers with restart, control of processed lines, etc). 

Cheers