Skip to main content
Inspiring
November 11, 2021
Question

JDBC Driver for text files - CF2021

  • November 11, 2021
  • 2 replies
  • 704 views

We are upgradging to CF2021 and have a few text files that we are accessing via a JDBC to text driver. Foermely, we used the fostex driver with CF9.  

 

We found StelsCSV but are having issues getting it to work correctly.  We are getting an error "[StelsCSV JDBC driver] Can't load the file 'txt.txt' to H2 database. Error was: File 'D:\ProcessData_DEV\txt.txt' doesn't exist"  I'm not sure where it's getting txt.txt from .  We don't have that configured anywhere.

 

Are there alternate drivers out there that are easy to get set up?

 

Thank you,

 

-=Rob=-

    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    Community Expert
    November 14, 2021

    Look at the ColdFusion logs. Share with us all the errors - in full - pertaining to the issue. They will contain a hint as to the cause of the error.

    Charlie Arehart
    Community Expert
    Community Expert
    November 11, 2021

    While I'd never heard of stelscsv, there are other options for processing CSV files with CFML. One thing not clear from your request: is it that this stelcsv driver allowed you to write SQL against the csv, from within CFML? Or might it be enough if the CSV was read into CFML as if it's a query, and then you could use a query of queries aginst that? The latter is possible with built-in or easily added features of CFML. More in a moment.

     

    First, as for the stelscsv link you offered, it's not a URL. Perhaps that was just a typo. But what URLs I could find for the product all end up redirecting to a password-protected page on the vendor's site, so I think we can assume the product is defunct.

     

    Next, as for reading CSV files in CFML, a primary option that may interest some folks (and most don't seem to know it exists) is that CFHTTP has a nifty feature that if it's pointed to a URL that is a CSV, that result will be pulled in as if it's a query. (Of course, that's a challenge if the file to be read is not readily accessible as a URL from within CFML.)

     

    Then, too, one can certainly read ANY file into CF using tags or functions like cffile, fileread, etc. They just don't have special support for CSVs, so then you can end up having to "turn it into something useful". Then again there are functions at cflib.org that can convert a variable containing CSV data into a query, or to an array. The latter, which is "more recent", shows doing the cffile and then the function to process it. Give those a shot.

     

    And FWIW, there has long been ODBC support for reading CSV files, and CF still supports defining an ODBC DSN that leverages the odbc-jdbc bridge (though JDBC is far preferred, especially for querying real databases).

     

    Beyond all these, someone may well chime in with a JDBC driver designed for reading in CSVs. I see one here, for instance. 

     

    Let us know if any of these get you going. (If you wanted help with that one driver, it seems a challenge unless you can point us to where to get it. I will say I avoid pulling files down from generic "download sites", as you don't know for sure what you may be getting.)

    /Charlie (troubleshooter, carehart. org)