ODBC Driver Creation and CSV Import

ODBC Drivers which provides a universal data access interface. With ODBC, application developers can allow an application to concurrently access, view, and modify data from multiple, diverse databases.

There are many ODBC drivers in MS Acess, they will come up with new drivers for others shortly. I will explain basic (*.txt,*csv) Drivers.

We need schema.ini file which tells the column names, character set and Header reqired.

schema.ini

It usually contains

 CharacterSet=OEM  
 Col1=Group Char Width 30  
 Col2=ID Integer 

More info you can get in : https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx

To set Up ODBC DSN:

Control Panel->Administrative Tools->Data Source

Click On 'Finish' give name for ODBC drive and select directory where you have 'schema.ini'.

To Set Up ODBC DSN in CF

Data & Services-> Data Sources->Add New Data Source

Give name for DSN and select ODBC socket from Drop down.

After adding DSN.

You will see the ODBC name which is set in the MS Acess before. Submit and DSN is ready to use.

To Read CSV from ODBC Driver

 <cfscript>  
      queryService = new Query();  
                queryService.setDataSource('csv');  
                queryService.setName("GetData");  
                queryService.setSql("SELECT * FROM sample.csv");  
                GetData = queryService.execute().getResult();  
 </cfscript>  
 <cfdump var="#GetData#" >  

You can get contents of CSV file into a Query object by using this.

File Header in column name and values under it as contents.

You can read text files also First Line will be column name. Subsequent lines will be values under it as contents.

In CF file processing is taken care by ColdFusion10/cfusion/CustomTags/com/adobe/coldfusion/base.cfc

We can easily read CSV files and make it into Query object.

Reading DOC file and writing in PDF using HWPF,LOWAGIE objects

Hi,

I came across writing into PDF using HWPF,LOWAGIE  objects in CF as well as in JAVA. Here I am reading a DOC file and copying contents to a PDF. It can be used alternate to CFDOCUMENT for CF8 below versions.

Required JARS are

 <cfscript>  
      // Creating JAVA fileinput stream object  
      fileinput = CreateObject("java", "java.io.FileInputStream");  
      // Calling InputStream constructor  
      fileinput.init("C:\Users\admin\Adobe ColdFusion Builder workspace\Practise\word.doc");  
      //Creating HWPF object  
      document = CreateObject("java", "org.apache.poi.hwpf.HWPFDocument");  
      // Calling HWPF constructor  
      document.init(fileinput);  
      // Creating a WordExtractor Object  
      reader = CreateObject("java", "org.apache.poi.hwpf.extractor.WordExtractor");  
      // Calling WordExtractor constructor  
      reader.init(document);  
      //Each line is captured in docfile as an array  
      docfile = reader.getParagraphText();  
      //Dumpingto see Docfile in array format  
      writeDump(docfile);  
      docfilefull = '';  
      //convertingarray to string  
      for(a in docfile)  
      {  
           docfilefull = docfilefull & a & chr(13) & chr(10);  
      }  
      //Creating lowagie object  
      document = CreateObject("java", "com.lowagie.text.Document");  
      // Calling lowagie constructor  
      document.init();  
      // Creating FileOutputStream object  
      fileIO = CreateObject("java", "java.io.FileOutputStream");  
      // Calling FileOutputStream constructor with path/name of file to written  
      fileIO.init("C:\Users\admin\Desktop\word19.pdf");  
      // Creating PdfWriter object  
      writer = CreateObject("java", "com.lowagie.text.pdf.PdfWriter");  
      //Calling getInstance method  
      writer.getInstance(document, fileIO);  
      //Calling open method of lowgie object  
      document.open();  
      // Creating Paragraph object  
      paragraph = CreateObject("java", "com.lowagie.text.Paragraph");  
      //Calling paragraph constructor passing the string which have the doc file content  
      paragraph.init(docfilefull);  
      //Writing the PDF document with DOC contents  
      document.add(paragraph);  
      //Closing document  
      document.close();  
 </cfscript>