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

[stevemac]

Ok - the script was pretty easy to find.   Unfortunately I cannot copy / paste from work environment to here - so have retyped it.   Highly recommend you review and test before implementation into your production environment

function checkDay() {

  var gdt = new GlideDateTime().getDisplayValueInternal();

  var day = gdt.getDayOfWeekLocalTime();

  var result = false;

  if (day !=6 && day != 7) {

      result = true;

  }

  return result;

}

checkDay();

[Community Alums]

Hi Jonatan,

Thanks for this tool. I have deployed it in Istanbul and it works fine for reading the file in the local directory of the Mid server machine. But I have an issue when I try to read the file in the mapped drive. I have mapped drive [X:] of the mid server machine to another server and I am trying to read the file and I get an [error,java.sql.SQLException: Directory not found: X:\]

Any idea how I can address this issue? Thanks.

Prakash

[Community Alums]

Jonatan, one more question. When I give "New File Path" to Move/Rename File, it works fine. If I give the same path as Dynamic, its deleting the file but not renaming it.

[Richard Hine]

Hi Prakash,

Long time no speak! I thought I had posted an answer to this but it is not showning. The MID server will be running under a service account, you need to either ensure that the drive mapping exists for that service account or use a UNC path which is my suggested approach. You should also ensure that the service account you are using has credentials on the remote path.

Regarding the rename, you might find the login you are using might have delete permissions but not modify, suggest you try logging into the remote share with the login and see if you can do it manually as I have had no issues with rename.

I hope this helps, you can ping me on LinkedIn if required.

Thanks,

Rich

[Boobalan]

Hi Jonatan,

Good day!!

We are using this utility in our customer instance and facing some issues while execution.

It accepts only 20 records and when I select load and transform all records. The import set is not getting loaded with the data.

But in history tab, I can see this message " *.csv: 30035 row(s) extracted ".

Also for the similar kind of data source it is accepting close to 30000 records.

Kindly provide your insights on the same.

Regards

Boo