Use a CSV as a Data Share Data source in v6.0

Owned by Kim Stimpson, created
Last updated: 19 Oct, 2023
4 min read

Overview

In iShare 5.8 (32 bit) we used the Jet Provider when using a CSV as a Data Share Data source but this is not available in 64-bit. For v6.0 you need to:

  1. Create an ODBC Data Source Name (DSN)

  2. Create a Schema.ini

  3. Create a Data Share connection

Step-by-step guide

Step 1: Create an ODBC Data Source Name

  • Open the ODBC Data Sources in the Control Panel > System and Security > Administrative Tools directory.

  • Select the System DSN tab.

  • Click on the Add... button.

  • Select 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 .

  • Uncheck the Use Current Directory and using the Select Directory... button, choose the directory where your CSV file is stored.

  • Now click OK and the new entry should appear in the System DSN list.

Step 2: Create a Schema.ini

When the Text driver is used, the format of the text file is determined by using a schema information file. The schema information file is always named Schema.ini and always kept in the same directory as the text data source. The schema information file provides information about the general format of the file, the column name and data type information, and several other data characteristics.

Here is an example for our CSV file:

So the Schema.ini would look like this:

[report_streetBins.csv] ColNameHeader=True Format=Delimited(,) Col1=X Text Col2=Y Text Col3=ogc_fid Text Col4=id Text Col5=descriptio Text Col6=uniqueid Text Col7=assetid Text Col8=case_url Text

  • The first entry in Schema.ini is always the name of the text source file enclosed in square brackets.

  • Then you need to specify if your file has column header i.e. True if your file has column headers or False if it does not.

  • And the format of your CSV file i.e. comma delimited

  • The following lines are entries for each column in your CSV file in the correct order specifying the field name and data type i.e. for the first column our field name is X and it is a Text field. Here is a list of the data types:

    • Bit

    • Byte

    • Short

    • Long

    • Currency

    • Single

    • Double

    • DateTime

    • Text

    • Memo

    • ODBC data types Char (same as Text)

    • Float (same as Double)

    • Integer (same as Short)

    • LongChar (same as Memo)

    • Date date format

Step 3: Create a Data Share connection

In iShare Studio you need to add a Data Share Connection for your data source. 

  • With the Data Share Connections node selected right click and select Add DataSource from the context menu.

  • Select to add a Standard Data source

  • Click the Configure button.

  • On the Provider tab, choose the Microsoft office 12.0 Access Database Engine OLE DB Provider
    option from the list.

  • On the Connection tab enter the following:
    Data Source: Enter the path to your CSV file
    User Name: Admin
    Tick the Blank password checkbox

  • On the Advanced tab tick Read and ReadWrite access e.g.

  • Now select the All tab and select the Extended Properties entry from the list.

  • Click the Edit Value button.

  • In the dialog that appears, enter the Property Value as

text;HDR=No;FMT=Delimited

  • Click OK.

  • To ensure that the connection is set up correctly go back to the Connection tab and click the
    Test Connection button.

  • Click OK and OK again and you are taken back to the Create a new Data Source dialog.

  • Click the Construct SQL... button.

  • This displays a list of the available fields in the CSV file. Note: If there is more than one CSV
    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 and you will now have a new entry in Data Share where the filename is the name of the .csv file.

  • Click on the Fields node to display the fields and data e.g.

  • Now we need to test the Data source Synchronisation. Select the Data source for your CSV right click and select Synchronise from the context menu.

  • Now use pgAdmin to check the table in your Data Share database.

  • You can automate the synchronisation via a scheduled Workflow Job - please see Data Share Synchronisation for details.