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.