Source Archives - Import for SharePoint

July 10, 2018July 10, 2018

Import Excel Data to SharePoint

Source

If you want to;

Import / migrate an Excel sheet to a SharePoint list
Import / migrate an Excel sheet to a SharePoint contacts list
Import / migrate an Excel sheet to a SharePoint calendar
Import / migrate documents to SharePoint with meta data in Excel
Import / migrate files to with meta data sourced from Excel

then you can specify an Excel worksheet source;

Configuration

  <Source>
    <SourceDataSetType>ExcelXLSXWorksheet</SourceDataSetType>
    <ExcelDataSetSettings>
      <ExcelFileFullPath>c:\temp\StaffFolders.xlsx</ExcelFileFullPath>
      <WorkSheetName>directories</WorkSheetName>
    </ExcelDataSetSettings>
  </Source>

<SourceDataSetType>ExcelXLSXWorksheet</SourceDataSetType>

<ExcelFileFullPath>c:\temp\StaffFolders.xlsx</ExcelFileFullPath>

<WorkSheetName>directories</WorkSheetName>

</ExcelDataSetSettings>

</Source>

July 10, 2018July 10, 2018

Import OleDb Data to SharePoint

Source

If you wish to;

Import / Migrate OleDb data to a SharePoint list
Import / Migrate documents to SharePoint where the meta data is accessible via OleDb
Bulk create items in SharePoint where the meta data is in OleDB e.g.
- Folders
- Document Sets
Import / Migrate web pages to SharePoint where the meta data is accessible via OleDb

Then you can specify an OleDb table data source.

Configuration

OleDB Table

You can define a data source as being a table using OLEDbTable. This also (optionally) allows each row to be updated after import as successful or otherwise along with any Exception data. This is useful for large imports.

Below the table that we are telling Import for SharePoint to use is a worksheet “Clients” in an excel spreadsheet.

<Source>
    <SourceDataSetType>OLEDbTable</SourceDataSetType>
    <OleDbSourceDataSetSettings>
      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\vmware-host\Shared Folders\projects\ClientFolders.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";</ConnectionString>
    </OleDbSourceDataSetSettings>
    <OleDbTableSourceDataSetSettings>
      <TableName>clients$</TableName>
    </OleDbTableSourceDataSetSettings>
  </Source>

<SourceDataSetType>OLEDbTable</SourceDataSetType>

<ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\vmware-host\Shared Folders\projects\ClientFolders.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";</ConnectionString>

</OleDbSourceDataSetSettings>

<TableName>clients$</TableName>

</OleDbTableSourceDataSetSettings>

</Source>

If you want to update the source table with status data.

The table must contain columns ImportKey, ImportStatus, Exception.

ImportKey must be unique.

In the example below only the first 10 rows will be considered for import since only they have the importstatus “Import”.

OleDB Select

You can define a data source as simply the results of a select statement using OLEDbSelect.

<Source>
    <!-- The source used is OLEDbSelect meaning that Import for SharePoint expected to run a SQL select statement against an OleDB data source.  OleDB select will ignore columns like importstatus-->
    <SourceDataSetType>OLEDbSelect</SourceDataSetType>
    <!-- The Connection string uses a microsoft provider to access an Excel spreadsheet-->
    <OleDbSourceDataSetSettings>
      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\JobDescriptions.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";</ConnectionString>
    </OleDbSourceDataSetSettings>
    <OleDbTableSourceDataSetSettings />
    <!-- The select statement will get all the jobs from the jobs worksheet -->
    <OleDbSelectSourceDataSetSettings>
      <SelectStatement>select * from [jobs$]</SelectStatement>
    </OleDbSelectSourceDataSetSettings>
  </Source>

<SourceDataSetType>OLEDbSelect</SourceDataSetType>

<ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\vmware-host\Shared Folders\Projects\ProductDevelopment\ImportForSharePoint\WorkingArea\Examples\JobDescriptions.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";</ConnectionString>

</OleDbSourceDataSetSettings>

<SelectStatement>select * from [jobs$]</SelectStatement>

</OleDbSelectSourceDataSetSettings>

</Source>

Further Thoughts

The database behind OleDB source could be;

SQL Server
Access
Excel
Oracle
Simba
Interbase
Firebird
PostgreSQL
MySQL
MariaDB
SAP ERP
MongoDB
Azure Table

July 10, 2018July 10, 2018

Import ODBC Data to SharePoint

Source

If you want to;

Import / migrate data from an ODBC data source into a SharePoint list.
Import / migrate documents / files to SharePoint where the meta data is held in an ODBC data source
Import / migrate pages to SharePoint where the web page content is stored in an ODBC data source.
Bulk create items in SharePoint from an ODBC data source

Then you can specify an ODBC data source.

Configuration

<Source>
    <!-- The source used is ODBCSelect meaning that Import for SharePoint expects to run a SQL select statement against an ODBC data source.  This will ignore columns like importstatus -->
    <SourceDataSetType>ODBCSelect</SourceDataSetType>
    <ODBCSourceDataSetSettings>
      <!-- So a system DSN has been created called WordPress, this has in fact been pointed to a MySQL database hosting WordPress -->
      <ConnectionString>DSN=WordPress;Uid=root;Pwd=password;</ConnectionString>
    </ODBCSourceDataSetSettings>
    <ODBCTableSourceDataSetSettings />
    <ODBCSelectSourceDataSetSettings>
      <!-- The select statement to run to obtain the rows for import.  This one actually gets all wordpress blog posts -->
      <SelectStatement>select *, concat(post_name,'.aspx') as DestinationFileName, replace(post_content,'\n','&lt;br/&gt;') as PageContent, 'True' as Publish from wp_posts where post_type = 'post' and post_status = 'publish'</SelectStatement>
    </ODBCSelectSourceDataSetSettings>
  </Source>

<SourceDataSetType>ODBCSelect</SourceDataSetType>

<ConnectionString>DSN=WordPress;Uid=root;Pwd=password;</ConnectionString>

</ODBCSourceDataSetSettings>

<SelectStatement>select *, concat(post_name,'.aspx') as DestinationFileName, replace(post_content,'\n','<br/>') as PageContent, 'True' as Publish from wp_posts where post_type = 'post' and post_status = 'publish'</SelectStatement>

</ODBCSelectSourceDataSetSettings>

</Source>

Further Thoughts

The database behind the ODBC source could be;

Oracle
FoxPro
Visual FoxPro
MySQL
MongoDB
Postgre
DataFlex
Ingres
Progress
Sybase
IBM DB2
Azure Table
JDBC
Microsoft Dynamics

32 / 64 bit

To use a 32bit ODBC Driver you must be running 32bit Import for SharePoint

To use a 64bit ODBC Driver you must be running 32bit Import for SharePoint