Possible solutions for converting csv data

Possible solutions for converting csv data

Hi community member,

At this moment I'm investigating possible solutions for converting a set of date from a client. This client is a pharmacy so the data is patiënt related. I get csv files like personal information and medication information. The patiënt data contains approximately 15.000 lines and the medication approximately 2.300.000 lines.

Our application is developed in Outsystems and is on-premises.

What's wise:
  • conversion of the data by the OutSystems platform
  • conversion of the data aside from OutSystems and import the converted data to the OutSystems platform
In advance thanks for your reply.

Greetings, Bernard

It would be hard to tell you which solution is 'best' as I'm not privy to all the details of your environment and application.  I can tell you that in my work with Outsystems, because I'm using the personal edition, I do not have direct access to the data base and have to use an Outsystems solution.  I've had excellent results using the CSVUtil component in the Forge.  Importing new data is very straight forward.  I typically create a structure with a text attribute for each field and move the raw data into that structure.  Then do the necessary validation and conversion as you move it into the entity record.  Using this process allows for handling error conditions that would abort the process, even using an external tool (invalid dates, characters in a numeric field, etc.).

Updating existing records is more challenging as you need to have a unique key such as social security number, but it is still very doable in Outsystems.

If you need any help let me know,

I suggest: 

Do the conversion by Outsystems, only in a seperate espace/tables.

So the raw csv goes as is into the staging table.

then you do the conversion to your like to the real tables.

I have worked with big csv-files before with outsystems.

the best way, imho to handle it is.

- import csv via extension per XXX lines. (to start with, 50K)

- bulkimport the csv to a staging table (probably a custom extension, but you can look at the forge for an example).

repeat this until you have it all in the staging table.

then you can do whatever you want without having too much trouble with time-outs.

This can all be done via a timer ofcourse,

any more questions, let me know :)