JDBC File Loader (via MID Server)

[enojardi]

How many times have you thought? I wish I could use JDBC to connect and extract data from a flat file instead of a database.

Well… This application (first release / beta version) does exactly that… and more:

* Connect using JDBC to a file via the MID Server

* Support for any flat file (CSV, TXT, etc)

* Support for zipped (.zip) files (containing a flat file)

* Choose any column separator you like

* Choose header row number or no header row at all

* Support for wildcard (*) and dynamic (scripted) file names

* Support for processing multiple files at once (using * wildcard)

* Load all rows from the file or execute your own "SELECT" SQL statement

* Select Import Set mode: Load only or Load & Transform (if a map is available)

* Bonus (experimental): onComplete file action: Copy, Move, Rename or Delete the file!

Link to App in Share: ServiceNow Share

[erikbos]

Hi John,

Thanks for your sharing your integration work ! I was actually just looking into adding non-standard JDBC drivers as specified on Adding JDBC Drivers for Unsupported Data Sources - ServiceNow Wiki and was toying with the idea of adding the csvJdbc driver this way. (Next to CSV I have got two additional internal sources that come with their own JDBC drivers. So likely I will have to do anyway..)

Do you mind sharing what the differences are between adding just the JAR, as specified by SN, or to say use your file loader app?

[enojardi]

Excellent question! I've obviously tried that before but unfortunately couldn't make it work. Maybe because of the driver I chose, missing some steps, config, I don't know...

The difference from my app is that it allows you to do a lot more than just loading data (as per the bullets points). But just remember that this is a "custom" application (i.e. not supported by ServiceNow).

I'm curious to hear if you make it work. Let me know

[erikbos]

I initially did not get it to work as my connection url was mangled, I still had a file attached to the Data source.. After removing that and manually setting the connection URL the mid server started to use the CsvJDBC driver. But by defaul thet CsvJDBC driver unfortunately halts due to an exception.

My steps to get it to work:

Adjust CsvJDBC driver

The midserver appears to always invoke the setQueryTimeout() method of the JDBC driver (even when the timeout is not set in the Data source definition). Probably to always limit the time the database should spent on a query. The CsvJDBC implementation of this method is just throwing an exception. Perhaps a bit too strict: as CSV query time is not relevant for me I removed the exception throwing.

Code and build instructions:

1. Following the instructions on http://csvjdbc.sourceforge.net/develop.html for getting the source code and building
2. Edit build/build.xml and pom.xml and add a suffix to the version string, to clarify that you are building a derived version
3. Edit csvjdbc/src/main/java/org/relique/jdbc/csv/CsvStatement.java
4. Comment out the throw exception line in setQueryTimeout()
5. Build new .jar

(I will check with the developer of CsvJDBC if he wants to keep setQueryTimeout() throwing an exception, as it feels a bit strict)

Add data source

Follow the instructions on http://wiki.servicenow.com/index.php?title=Adding_JDBC_Drivers_for_Unsupported_Data_Sources and use your newly built .jar. The page does describe everything that you need to configure on your instance for a new JDBC source.

Some notes:

- Most important is that you add "jdbc:relique:csv:/tmp" as connection URL in the data source.

- The directory that holds the CSVs on the mid server is specified as part of the connection URL

- The directory it needs to be readable by the JVM process

- The CSV driver will use the table name as filename for each CSV in the directory

- I think the file format features of CsvJDBC can be set via the connection string, but I have not tried it.

Let me know if you have any questions getting it to work.

Erik

[enojardi]

Thanks for sharing, Erik. I'll keep this in mind next time I need to build a simple data source.

Are you able to share the revised jar file? Cheers, JJ