How do I best create a JSON from a flat csv for table creation in OEP?

[pajot]

I am trying to follow the instructions found here to import data into a local instance of the OEP from a flat CSV file.

I expect that this is routine. Do you have an established procedure that accomplishes this?

[jh-RLI]

There are several possible solutions here. We have not yet agreed on a best practice (we dont recommend the 4. option, as this is not available to the user.)

1. Use the wizard (OEP website) to upload CSV data - navigate to Database -> click Create Table on the right side -> Create the table -> switch to Upload CSV tab / If table present: navigate to table -> click upload CSV.
2. read the data with pandas convert to JSON and then upload with the API. Examples: tutorial: 11 (section 2.1.2. Upload data)
3. read the data with pandas and use sqlalchemy with oedialect: Tutorial: OEP-oedialect_upload_from_csv, Tutorial: OEP_Upload_Process_Data_and_Metadata_oem2orm using pandas to_sql() function with sqlalchemy connection.
4. It might be the fastest way to import big datasets via your local backend access using the Postgres command line and just use the SQL command to COPY csv data to table.

There are more, but this should cover most options. Hope this answers your question or guides you in some direction, at least :D

[pajot]

Using the wizard is often too unwieldy (for example, my sample dataset has 405 fields). I did something akin to method 2 (without ever having seen the tutorial) although I had to rename the fields (the original field names began with a digit) and data types.

I'm just thinking out loud here: it would be useful to have a script that validates tabular data (as CSV, say) and tells the user what needs to be done to make it importable (checking for SQL-compatible types and field names, for example).

[jh-RLI]

This is good feedback on the wizard and could be added as an issue. The wizard should ideally be able to handle all csv based data structures.

I fully agree, this is one of the current tasks. Unfortunately, our resources in the development team are currently very limited and some tasks take a long time. Also, there has been some confusion about the full range of supported data types, as we do not only support the pure SQL/PostgreSQL data types. This is because we use different ways to communicate with the database (API, oedialect, django) each of these technologies got their own type mappings (and there is a lot of technical dept on our side because of missing documentation about the implementation on the OEP). The documentation is also on our ToDo list.
In the next weeks/months there could be significant progress here as we will get some support.