CSV Files

Owned by Kim Stimpson
Last updated: 19 Feb, 2015
3 min read

CSV is the encompassing name for all delimited text; they need not adhere strictly to the ‘comma separation’ rule, but simply be delimited by a character into a tabular form of data.  Unlike the DBF, they hold no information about their structure, and require a separate file to provide this information.

This is done by the use of a file called schema.ini which is created in the directory where the CSV file resides.

Define the Data Source

From the Administrative Tools area, open the Data Sources (ODBC).  The Administrative Tools can usually be found in the Control Panel.

 

  • Select the System DSN tab

  • Click on the Add… button.

  • Choose the Microsoft Text Driver (*.txt;*.csv) driver entry from the list and click Finish.

  • Complete the ODBC Text Setup by entering the Data Source Name using something that will uniquely identify the source data, e.g. Council Tax Properties.
  • Enter a brief Description for the Data Source is you wish.
  • Uncheck the Use Current Directory and using the Select Directory… button and choose the directory where the file is stored.
  • Now click OK and the new entry should appear in the System DSN list.

Schema.ini

This file is a standard INI file with a section for each file in the directory.  For this example we will be dealing with a file called properties.csv.  It is delimitered by the Pipe character ‘|’, and has 13 columns.  The simplest definition for this file would be as follows:-

Note that you can change the column names to something more appropriate; in the above example we have simply chosen FieldNamen.  We have also chosen to make all the fields of type Char with Width 100.  This gives flexibility if there are problems with the data.

For further information regarding the Schema.ini file please contact your DB Administrator or an Astun Technology consultant.

Working with the Datasource in Data Share

Please refer to the topic Adding a new Data Source for the general details on how to add a new Data Source and use the following options.

  • In this case we need to create a Standard Data Source.
  • On the Provider tab, and choose the Microsoft Jet 4.0OLE DB Provider option from the list.
  • On the Connection tab enter the directory where the CSV file is stored.  You do not need to fill in any log on information.
  • Now select the All tab.
  • Select the Extended Properties entry from the list, and click the Edit Value button.

  • In the dialog that appears, enter the Property Value text;HDR=No;FMT=Delimited and press OK.
  • To ensure that the connection is setup correctly, you can go back to the Connection tab and click the Test Connection button.  Once correctly setup, click OK.
  • You are now taken back to the Create New Data Source dialog.
  • Click the Construct SQL… button.
  • This should bring up a list of the available fields in the CSV file.  If there is more than one DBF file in the directory, you may have to use the drop down to select the one you require. Select the fields you wish to import, and click OK.
  • Click OK on the Create New Data Source dialog, and you will now have a new entry in Data Share called filename#csv where filename is the name of the .csv file.  Although the name appears weird, it’s best to preserve it.