XLS Files

Importing data from a Microsoft Excel file is similar to the process used to import data from a CSV file and entails creating a Standard DataShare Connection using the Microsoft Jet 4.0 OLE DB Provider.

Some preparation of the spreadsheet maybe required in order to import the data such as ensuring the formatting of the cells is consistent and that column names only contain characters deemed valid for column names in PostgreSQL. In practice this usually means removing punctuation and non alphanumeric characters including spaces from column headings. It may also be necessary to delete empty rows/columns if selecting all data within a Worksheet to avoid blank columns and rows being imported.

If you wish to import an XLSX file into Data Share the easiest way to do this is to either export to the old XLS format and follow the steps below or to CSV file and follow the steps on importing CSV Files.

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 browse to the Excel spreadsheet you wish to import data from (you may need to choose All files from the File type dropdown as it defaults to *.mdb) and leave all other fields set to their defaults. 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 Excel 8.0;HDR=Yes;IMEX=1 and press OK. Where
- Excel 8.0 indicates that you are connecting to an Excel worksheet
- HDR indicates that specified data (worksheet, range or named range) includes column names as the first row (Yes or No)
- IMEX determines if columns with mixed formatting should be treated as strings (0 or 1)
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.
Once you have created a connection to the Excel worksheet you need to write a SQL statement to select the data that you require. The SQL must be entered manually with an Excel spreadsheet as the Construct SQL dialog is generally not able to list the available data. You can select data from a Worksheet, Range from a Worksheet, Worksheet level Named Range or a Workbook (global) Named Range. For example:

Select an entire Worksheet

SELECT * FROM [EstatesData$]

Select a Range in a Worksheet

SELECT * FROM [EstatesData$A1:D10]

Select a Named Range in a Worksheet

SELECT * FROM [EstatesData$PropertyList]

Select a Workbook level Named Range

SELECT * FROM PropertyList

Click OK on the Create New Data Source dialog, and you will now have a new entry in Data Share called filename#xls where filename is the name of the .xls file. Although the name appears weird, it’s best to preserve it.