Import CSV data and setup blank report linking to the data

[zzgvh]

A report designer needs to be able to work in the BIRT designer with a dataset that resembles the live data that's going to be used in production. The same test dataset also needs to be present on the ReportServer when testing the report for publishing.

To enable this workflow we create a script that imports a CSV file and from that sets up the following:

• A postgres table with the data on ReportServer
• A sqlite database with the data. This is a file that can be downloaded
• A BIRT report template that is blank, and only contains a datasource to the sqlite DB

[zzgvh]

Note regarding the generation of table field names

The column headers in the CSVs I have received so far have varied a lot. The import script tries to keep as much as possible of them, while turning them into sane DB field identifiers. Process looks like this:

• Replace spaces (" ") with underscores ("_")
• Remove any characters that aren't in the set A-Za-z0-9_ i.e. all ascii alphabet, numbers and underscore
• Remove leading or trailing underscores
• If the first character now is a number (0-9) prepend an underscore
• Finally for replace empty identifiers with "columnN" where N is the column count

Some column headers contain whole FLOW questions. They can be so long that they get truncated. Luckily those columns have been prepended with an integer that I guess identifies the question, thus the columns end up being unique even if the text part of them may be the same. Currently the script will abort if there are duplicates in the final DB identifiers.