Problem populating Sharepoint LIst from Excel Spreadsheet.

Aug 27, 2016 at 11:11 PM
I am trying to populate an existing Sharepoint List on Office 365 using values from an excel spreadsheet.

Here is the xlm file I am using:
<?xml version="1.0" encoding="utf-8"?>
<DataSetImportSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Source>
    <SourceDataSetType>OLEDbSelect</SourceDataSetType>
    <OleDbSourceDataSetSettings>
      <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=J:\Test.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";</ConnectionString>
    </OleDbSourceDataSetSettings>
    <OleDbTableSourceDataSetSettings />
    <OleDbSelectSourceDataSetSettings>
      <SelectStatement>select * from [SheetName$]</SelectStatement>
    </OleDbSelectSourceDataSetSettings>
  </Source>
  <Destination>
    <AuthenticationSettings>
      <AuthenticationType>Office365</AuthenticationType>
      <domain />
      <username>username@company.onmicrosoft.com</username>
      <encryptedpassed></encryptedpassed>
    </AuthenticationSettings>
    <DestinationItemSettings>
      <DestinationItemType>Item</DestinationItemType>
      <ItemExistsBehaviour>Overwrite</ItemExistsBehaviour>
      <ImportMappings>
      
        <ImportMapping xsi:type="ImportMapping_String">
          <DestinationField>Company</DestinationField>
          <SourceColumn>Company</SourceColumn>
        </ImportMapping>
         <ImportMapping xsi:type="ImportMapping_String">
          <DestinationField>Business Phone</DestinationField>
          <SourceColumn>BusinessPhone</SourceColumn>
        </ImportMapping>
         <ImportMapping xsi:type="ImportMapping_String">
          <DestinationField>Email Address</DestinationField>
          <SourceColumn>EmailAddress</SourceColumn>
        </ImportMapping>
      </ImportMappings>
    </DestinationItemSettings>
    <DestinationListSettings>
      <DestinationWebUrlRelative>/</DestinationWebUrlRelative>
      <DestinationFolderUrlRelative>/Lists</DestinationFolderUrlRelative>
      <DestinationServerUrl>https://company.sharepoint.com</DestinationServerUrl>
      <DestinationListName>Fax List</DestinationListName>
    </DestinationListSettings>

  </Destination>
</DataSetImportSettings>
Here are the contents of the excel spreadsheet

LastName Company BusinessPhone EmailAddress ContentType ImportKey ImportStatus Exception
Lastname1 Company1 123456 Sam@somewhere.com.au Item 1 Import
Lastname2 Company2 123457 Someone@somewhere.com.au Item 2 Import

The Data importer reads the values from the spreadsheet, seems to successfully connect to sharepoint but the actual update fails with the following errors:
About to connect to source :
Source connected :
Source filled : 2 rows loaded
About to connect to destination :
Destination connected :
Exception importing : Lastname1 : Invalid file name.

The file name you specified could not be used. It may be the name of an existing file or directory, or you may not have permission to access the file.
Exception importing : Lastname2 : Invalid file name.

The file name you specified could not be used. It may be the name of an existing file or directory, or you may not have permission to access the file.

``` The error seems to indicate that it is trying to upload a file however I don;t want to upload a file but merely insert some items in a list.

Any clues?
Coordinator
Aug 28, 2016 at 9:56 AM

Hi

When DIFS installs you will see some examples installed in the examples directory.

The example most closely related to what you are doing is the “Job Descriptions” example.

This example should work so look for differences between the spreadsheet and xml and what yours contains.

I have noticed the following for starters;

You have <DestinationItemType>Item</DestinationItemType> which is correct; this tells difs to import just an item, no file.

But you seem to be missing;

<SourceColumns>

<SourceFileNameAndPath>FullName</SourceFileNameAndPath>

<ContentType>ContentType</ContentType>

<DestinationSubFolder>DestinationSubDirectories</DestinationSubFolder>

<DestinationFileName>DestinationFileName</DestinationFileName>

</SourceColumns>

ContentType is the important setting; the others will be ignored since you are importing items not files.

Also I noticed in your post that the excel contains;

LastName Company BusinessPhone EmailAddress ContentType ImportKey ImportStatus Exception

Lastname1 Company1 123456 #9;Item 1 Import" rel="nofollow"> [email removed] Item 1 Import

Lastname2 Company2 123457 #9;Item 2 Import" rel="nofollow"> [email removed] Item 2 Import

I am unsure if this is just a cut and paste problem but this infers that the email address and content type are the wrong way around in the source.

The best advice I can give it to start with the example “Job Description”, just change the list it is pointing to and may the import mappings so that it just sets title. This should work; then just adjust it to set your columns.