Issue importing a Managed MetaData Field

Jan 18 at 7:15 AM
Hi all,

I have used this tool a lot for importing and it works great. Now I come across an issue I cannot solve.
First of all: I am using Office 365.

I have a single-valued Managed Meta Data column named "Tijdvak" ((translated to English it means: Timeperiod) that contains a hierarchical structure of years, quarters and months:

Image

I am trying to import this using an importmapping:
    <ImportMapping xsi:type="ImportMapping_ManagedMetaDataCSOM">
      <DestinationField>Tijdvak</DestinationField>
      <SourceColumn>Tijdvak</SourceColumn>
    </ImportMapping>
But well ... the files are imported succesfully, but the MMD field is not imported. No further error message is generated.

Link to Excel-importfile

Link to Configuration file (anonymized)

Can you see whats going wrong?

Thanks in advance.
Theo
Coordinator
Jan 18 at 8:45 AM
Guessing that maybe 2017-q1 is not available for tagging, maybe try 2017-q1-Jan or change that tagging

If not that then enable logging
Jan 18 at 9:17 AM
Thanks for replying MadAboutImport.

I just did some more testing ... Everything works fine when I have a plain list without content types. In that case 2017 en 2017-Q1 are set properly.

But when I switch to using content types the fields are not set anymore. I have tried also another MMD field with same results.

Perhaps this gives a clue?
Jan 18 at 9:18 AM
O, and logging is not possible on Office 365.
Coordinator
Jan 18 at 9:52 AM
Make sure you are using the latest difs version

Turn on logging in difs (see manual)

You can then see the error message reported by difs

You can also try making fields non mandatory

Sometimes the problem is that the same cannot be performed because you are not setting a mandatory field and so even a good field setting fails
Jan 18 at 12:36 PM
I must be doing wrong. I followed your instructions above (I am/was using version 2.7). I do not have mandatory fields.

I set the autoflush option to true but no logs are created. I ran DIFS as administrator, started it from the command line as administrator, etc, etc. The log is stored in D:\Temp\SPImportHelper.log, which is a location that I can write to. But no logs appear.
Coordinator
Jan 18 at 7:25 PM
Normally this means that there are no errors being logged (I know it's confusing and will be changed).

My DIFS.exe.config looks like this and logs just fine (if there is an error applying meta data) but produces no log if there are no errors.
<?xml version="1.0"?>
<configuration>
    <configSections>
    </configSections>
<startup>
  <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>
  <system.diagnostics>
    <!-- By setting autoflush false the listener will not be written to; this can get BIG.  Set to true for troubleshooting -->
    <trace autoflush="true"></trace>
    <sources>
      <source name="SPImportHelper">
        <listeners>
          <remove name="Default"/>
          <!-- The log file specified below will contain full details the of import.  The user running the import must have write permission and autoflush must be set to true.  If left in the default program files directory you must Run as Administrator -->
          <add name="eventlog"
              type="System.Diagnostics.TextWriterTraceListener"
              initializeData="c:\temp\SPImportHelper.log">
            <!--
            <filter type="System.Diagnostics.EventTypeFilter" initializeData="Information"/>
            -->
          </add>
        </listeners>
      </source>
    </sources>
  </system.diagnostics>
</configuration>
Jan 23 at 2:26 PM
I have been able to get the logging working (by testing it with some real bad metadatavalues) and it started logging. Unfortunately the scenario I describe below does not result in any logging.


After tons of tries and investigation I finally found a pattern in the results.


For test I am using an existing Excel-file I have used for ages. Its date is old (2014): Northwind June Expense Categories.xlsx

I dit the following test in importing this and another XLSX file, but all with some metadata-field for the Tijdvak-field.
  1. Import Northwind June Expense Categories.xlsx and keep its destination name the same.

    This will result in no metadata added in SharePoint Online.
  2. Import Northwind June Expense Categories.xlsx and change its destination name to a1 (without extension).

    This resulted in the metadata being added in SharePoint Online.
    This gave me the idea to start with an new Excel-document instead of the Northwind one.
  3. Import Test.xlsx (brand new almost empty) and keep its destination name.

    This file is imported correctly with metadata.
    So it is something in the Northwind file.
    There was a content type id in it (but no property Tijdvak). So lets get rid of the id first by removing this property.
  4. Import Northwind June Expense Categories.xlsx but now without the contenttype id in it.

    Still no metadata.
    Ok, then the big cleansing: remove all personal info and xml data in the file.
  5. Imported the clean Northwind June Expense Categories.xlsx.

    Tada! The file is now imported and the metadata is set correctly.
So, ....
a. There is something in my Excel file that causes problems, but I have no idea what.
b. When imported SharePoint Online interprets this Excel file somehow and refuses to add metadata.
c. when imported with no Excel extension for the destination filename SharePoint will accept the metadata

Well at least I have the cause localized. I am going to import now a lot of data and we will see if the metadata gets added. I hope these files are ok. I'll keep you informed, if you want.
Coordinator
Jan 23 at 3:22 PM
Edited Jan 23 at 3:23 PM
Sounds like the problem is with property promotion.

Click here

Always GREAT! to hear about your experiences
Feb 28 at 6:45 PM
I'm running into the same problem. At one point the Managed Metadata worked fine and now it does not even with the default Document Content Type. I tried to turn on additional logging and had the same problem as above. The Console however is throwing an exception "Exception importing : C:.....docx : You must fill out all required properties before checking in this document."
but a trace log is not generating. I like to trial the 2.7 version but I cannot since I have 64 bit OS running 32 bit Office. I need to rebuild it and the source is not available. Any chance of having the source code available?
Coordinator
Mar 1 at 6:29 AM
Just download the zip of difs release and run setup32.exe
Mar 1 at 12:43 PM
It installs fine but I appear to have this error - is it related to my 32-bit office?

About to connect to source :
Source connected :
Exception during import : The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. : System.Data :
Mar 1 at 12:48 PM
In the logging tab....

SPImportHelper Information: 1000 : Progress : About to connect to source :
SPImportHelper Information: 1023 : DataSetSource : Connection string Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Difs\DifsTest\StaffDocuments-DifsTest-Terms.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
SPImportHelper Information: 1000 : Progress : Source connected :
SPImportHelper Information: 1004 : DataSetSource : Fill select * from [files$]
SPImportHelper Information: 1000 : Progress : Exception during import : The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. : System.Data :


I had this problem in the last version. I had to load the source and rebuild it using the Platform target: x86 option using the 32-bit SPHelperImport.dll and it worked!
Coordinator
Mar 1 at 3:28 PM
Which setup exe did you run and was difs install to program files or profile files 32
Mar 1 at 4:45 PM
I installed the 32 bit and then uninstalled and trialed the 64. Both yield the same result.
Coordinator
Mar 1 at 5:24 PM
Mar 3 at 2:43 PM
Got past my problem after installing the latest and greatest Office version and using 64 bit Dif tool and Office. The logging is much better in this version! Still getting an error and I believe it's the term? It's definitely the termset. It appears to find the guid but can't set it? (7******.docx is not the real filename). File uploads fine but remains checked out since the Metadata field can't be set.

SPImportHelper Information: 1014 : Add_Document : Adding C:\Difs\DifsTest\DifsFilesToTest\7******.docx to SharePoint as /App360KM/DifsTest/7******.docx
SPImportHelper Information: 1002 : SetMetaData : Setting destination field ContentTypeId
SPImportHelper Information: 1001 : SetMetaData : Using source value 0x0101002D6A83321ED28349B7C8CE8CA91B9447
SPImportHelper Information: 1002 : SetMetaData : Setting destination field Title
SPImportHelper Information: 1001 : SetMetaData : Using source value 7******.docx
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_String
SPImportHelper Information: 1002 : SetMetaData : Setting destination field MyColumn
SPImportHelper Information: 1001 : SetMetaData : Using source value Overview
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_String
SPImportHelper Information: 1002 : SetMetaData : Setting destination field NonsenseTermset
SPImportHelper Information: 1001 : SetMetaData : Using source value Foo
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_ManagedMetaDataCSOM
SPImportHelper Information: 1010 : Get_Term : TaxonomyFieldType
SPImportHelper Information: 1011 : Got_Term : c9760af8-f92e-4545-9e60-8200f56bfa3b
SPImportHelper Information: 1012 : Set_Term : c9760af8-f92e-4545-9e60-8200f56bfa3b
SPImportHelper Information: 1000 : Progress : Exception importing : C:\Difs\DifsTest\DifsFilesToTest\7******.docx : You must fill out all required properties before checking in this document.
Coordinator
Mar 3 at 8:03 PM
What is the configuration of the destination field

Mandatory?

Allow multi values ?

Show full term path?

Allow fill in value?

Etc
Mar 3 at 8:34 PM
The destination field is mandatory which is why it says it must be filled out before the document can be completely checked in.

It does not allow multi values but does allow fill in. In my case however I'm supplying it the term "Foo" and that is an existing term. The Display Value is Display term label in field.
Coordinator
Mar 4 at 7:31 AM
Very surprised that you are having problems in this configuration. This has certainly tested out ok. And as you say the term id is retrieved.

Is this SharePoint online or on-premises?

What is the build number of sharepoint ? This shows in the log when the connection is first made.

What is the authentication type ? E.g. current or office365? It might be easier to post your entire XML config file - remove any sensitive info

Are you passing Foo or Foo; as source value. The former is correct for a single value field.

Is it definitely the managed field which is the problem. i.e. if you make this optional does the record save ok?

Once you set the missing managed column on the imported document does it check in ok?

Is the term c9760af8-f92e-4545-9e60-8200f56bfa3b the id for foo? I think you can see this in the url bar when you look at the foo term in the term store manager

Does the problem persist if you upload a test.txt file that just contains a single word - I ask this because sometimes word documents contain a properties field of the same name as your destination column and property promotion then overrides your supplied value with that in the word document - which might be blank or a term which does not exist.
Mar 6 at 1:14 PM
Edited Mar 6 at 1:15 PM
Q:Is this SharePoint online or on-premises?
A:It's Sharepoint online that I am migrating to.

Q: What is the build number of sharepoint ? This shows in the log when the connection is first made.
A: SharePoint Version 16.0.6223.1204 (as shown in the logs below)

Logs:
SPImportHelper Information: 1000 : Progress : About to connect to source :
SPImportHelper Information: 1023 : DataSetSource : Connection string Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Difs\DifsTest\StaffDocuments-DifsTest-Terms.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=0";
SPImportHelper Information: 1000 : Progress : Source connected :
SPImportHelper Information: 1004 : DataSetSource : Fill select * from [files$]
SPImportHelper Information: 1000 : Progress : Source filled : 3 rows loaded
SPImportHelper Information: 1000 : Progress : About to connect to destination :
SPImportHelper Information: 1019 : Authenticating: Authenticating to url https://******.sharepoint.com/App360KM
SPImportHelper Information: 1020 : Authenticating: Authenticating with username vtang@******.onmicrosoft.com SPImportHelper Information: 1021 : Authenticating: Authenticating using ClientAuthenticationMode Default
SPImportHelper Information: 1022 : Authenticated: Authenticated to SharePoint Version 16.0.6223.1204
SPImportHelper Information: 1019 : Authenticating: Authenticating to url https://******.sharepoint.com/App360KM
SPImportHelper Information: 1020 : Authenticating: Authenticating with username vtang@******.onmicrosoft.com SPImportHelper Information: 1021 : Authenticating: Authenticating using ClientAuthenticationMode Default

Q: Are you passing Foo or Foo; as source value. The former is correct for a single value field.
A: I trialed both methods - currently it's just "Foo" without semi-colon


Q: Is it definitely the managed field which is the problem. i.e. if you make this optional does the record save ok?
A: Yes, if the term is optional the document uploads fine w/o the checkout required message. However the term does not populate in the metadata. This is the only metadata field left to populate.


Q: Once you set the missing managed column on the imported document does it check in ok?
A: Yes, when I manually update it from Sharepoint Online and then check in after populating that one field it works.

Q:Is the term c9760af8-f92e-4545-9e60-8200f56bfa3b the id for foo? I think you can see this in the url bar when you look at the foo term in the term store manager
A: I see the guid on the termstoremanager.aspx page at the bottom you see the Unique Identifier on the General tab.

Unique Identifier for "Foo"
c9760af8-f92e-4545-9e60-8200f56bfa3b


Q:Does the problem persist if you upload a test.txt file that just contains a single word - I ask this because sometimes word documents contain a properties field of the same name as your destination column and property promotion then overrides your supplied value with that in the word document - which might be blank or a term which does not exist.
A: I trialed this and same issue. I have a mix of word and excel docs.



As I was putting these Q/A's together, I noticed something. From an administrative perspective of my tenant - if I goto the Admin section of O365->Sharepoint->Term Sets and do a search on "Foo" the results do not find it. However if you go to the Site where the document library is found and List Settings-> and the Metadata properties of the term and then click on "Edit Using Term Set Manager" link - the link is somewhat different. I'm not sure if that is the culprit?

So Admin rights to O365 Termset:
https://******-admin.sharepoint.com/_layouts/15/termstoremanager.aspx

vs.

Access via List Settings->(Metadata field of column) -> "Edit Using Term Set Manager" link - I noticed the url link is:
https://******.sharepoint.com/App360KM/_layouts/15/termstoremanager.aspx?SiteUrlAndTermSetId=e5f6aa03-6751-4cb5-a0b0-a29fec548247|8f721b4c-db1f-4f0c-9206-d42ab0d0a43b|ab878bff-a7bc-4c3b-92b9-c35efacabdfc

From the latter link if I do a Search on "Foo" the results do appear but at the admin link it does not.
Mar 6 at 1:22 PM
As I think about it - I have a feeling that's not the culprit since it does find the GUID.
Coordinator
Mar 6 at 3:17 PM
I suggest;

Go to
https://******-admin.sharepoint.com/_layouts/15/termstoremanager.aspx

Create a brand new TermSet from here.

Test with this new term set.

If this works then that is your problem - at least we will know and I can have a look why this might happen.

Unfortunately if you create a term set from site settings in a site it creates a term set that is local to the site collection and these never work that well - plus you cannot tell easily that this has been done.

Always create new term sets from

https://******-admin.sharepoint.com/_layouts/15/termstoremanager.aspx
Mar 16 at 8:59 PM
Finally have time to go back.

So I created the termset as you suggested on the termstoremanager.aspx on the Admin page. Same result.

Not sure why this is happening. Here's what I see in the Log tab:

SPImportHelper Information: 1001 : SetMetaData : Using source value 7366_App360_System_Details.docx
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_String
SPImportHelper Information: 1002 : SetMetaData : Setting destination field MyColumn
SPImportHelper Information: 1001 : SetMetaData : Using source value Details
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_String
SPImportHelper Information: 1002 : SetMetaData : Setting destination field Location
SPImportHelper Information: 1001 : SetMetaData : Using source value Raleigh
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_ManagedMetaDataCSOM
SPImportHelper Information: 1010 : Get_Term : TaxonomyFieldType
SPImportHelper Information: 1011 : Got_Term : 2adb7261-74e4-47e8-9169-dcae676a2b1a
SPImportHelper Information: 1012 : Set_Term : 2adb7261-74e4-47e8-9169-dcae676a2b1a
SPImportHelper Information: 1000 : Progress : Exception importing : C:\Difs\DifsTest\DifsFilesToTest\7366_App360_System_Details.docx : You must fill out all required properties before checking in this document.
Coordinator
Mar 17 at 10:52 AM
Ok so I double checked and for me it works fine with a mandatory field.

SPImportHelper Information: 1002 : SetMetaData : Setting destination field Employee Name
SPImportHelper Information: 1001 : SetMetaData : Using source value Burton, Sarah
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_ManagedMetaDataCSOM
SPImportHelper Information: 1010 : Get_Term : TaxonomyFieldType
SPImportHelper Information: 1011 : Got_Term : 9542db18-9b19-4644-a107-ccde0abcc6d7
SPImportHelper Information: 1012 : Set_Term : 9542db18-9b19-4644-a107-ccde0abcc6d7
SPImportHelper Information: 1000 : Progress : Imported : \vmware-host\Shared Folders\TestData\Staff\B\Burton, Sarah - 1004\Promotion.txt

The only times I have seen this be a problem is;

Property Promotion (So you would not see the issue if you upload text.txt contain just the word test)
Location based meta data set on the folder that you are importing to (or at least I saw a thread from a user claim this to be a problem).
Value "Raleigh" appears twice in the term set that the field is connected to
User error (Sorry I have to put that in)

Let me know if you get this sorted.
Mar 21 at 8:48 PM
I tried a text file and that did work!

Creating an excel file from scratch did not work - gave the same error. I placed the file in the same local folder as the other office docs that failed. C:\Difs\DifsTest\DifsFilesToTest. I thought creating an excel doc from scratch would work? With Property Promotion if I create an excel doc from scratch the properties for the metadata do not exist and should be taking it from the .xml and .xls spreadsheet that we specify for the DIFs tool, no?
Coordinator
Mar 21 at 9:51 PM
This is nothing to do with difs but SharePoint property promotion

Call your field something different than location at least while you are importing and preferably forever
Mar 23 at 6:28 PM
Is location a reserved word? I trialed other field names called "MyLocation" and that does not work either?

Powershell can disable property promotion but I believe that requires admin rights for O365. I am only a site owner without any higher privileges.

I'm confused why just creating an excel file without any custom document properties on the office doc itself would not work?

So I created on the termstoremanager.aspx a new Term Set "Flavors" as a required field and terms: Strawberry, Blueberry, Vanilla, Cherry and mapped the in the .xml file and added the proper columns and still comes up with the same error. It seems to find the Term in the logs but somehow can't set it - guid's look correct. Am I the only one having this issue?


Logs below:
...
SPImportHelper Information: 1000 : Progress : Destination connected :
SPImportHelper Information: 1008 : Get_ContentType : Document
SPImportHelper Information: 1009 : Got_ContentType : 0x0101002D6A83321ED28349B7C8CE8CA91B9447
SPImportHelper Information: 1014 : Add_Document : Adding C:\Difs\DifsTest\DifsFilesToTest\Test.txt to SharePoint as /App360KM/DifsTest/Test.txt
SPImportHelper Information: 1002 : SetMetaData : Setting destination field ContentTypeId
SPImportHelper Information: 1001 : SetMetaData : Using source value 0x0101002D6A83321ED28349B7C8CE8CA91B9447
SPImportHelper Information: 1002 : SetMetaData : Setting destination field Title
SPImportHelper Information: 1001 : SetMetaData : Using source value Test.txt
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_String
SPImportHelper Information: 1002 : SetMetaData : Setting destination field MyColumn
SPImportHelper Information: 1001 : SetMetaData : Using source value Overview
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_String
SPImportHelper Information: 1002 : SetMetaData : Setting destination field Flavors
SPImportHelper Information: 1001 : SetMetaData : Using source value Blueberry
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_ManagedMetaDataCSOM
SPImportHelper Information: 1010 : Get_Term : TaxonomyFieldType
SPImportHelper Information: 1011 : Got_Term : 8902282a-b401-4e7a-bfc6-9e3397aeb065
SPImportHelper Information: 1012 : Set_Term : 8902282a-b401-4e7a-bfc6-9e3397aeb065
SPImportHelper Information: 1000 : Progress : Imported : C:\Difs\DifsTest\DifsFilesToTest\Test.txt
SPImportHelper Information: 1014 : Add_Document : Adding C:\Difs\DifsTest\DifsFilesToTest\MyExcelDoc.xlsx to SharePoint as /App360KM/DifsTest/MyExcelDoc.xlsx
SPImportHelper Information: 1002 : SetMetaData : Setting destination field ContentTypeId
SPImportHelper Information: 1001 : SetMetaData : Using source value 0x0101002D6A83321ED28349B7C8CE8CA91B9447
SPImportHelper Information: 1002 : SetMetaData : Setting destination field Title
SPImportHelper Information: 1001 : SetMetaData : Using source value MyExcelDoc.xlsx
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_String
SPImportHelper Information: 1002 : SetMetaData : Setting destination field MyColumn
SPImportHelper Information: 1001 : SetMetaData : Using source value Support
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_String
SPImportHelper Information: 1002 : SetMetaData : Setting destination field Flavors
SPImportHelper Information: 1001 : SetMetaData : Using source value Strawberry
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_ManagedMetaDataCSOM
SPImportHelper Information: 1010 : Get_Term : TaxonomyFieldType
SPImportHelper Information: 1011 : Got_Term : bfb17580-fb85-47fc-8380-6a889e410f01
SPImportHelper Information: 1012 : Set_Term : bfb17580-fb85-47fc-8380-6a889e410f01
SPImportHelper Information: 1000 : Progress : Exception importing : C:\Difs\DifsTest\DifsFilesToTest\MyExcelDoc.xlsx : You must fill out all required properties before checking in this document.
SPImportHelper Information: 1014 : Add_Document : Adding C:\Difs\DifsTest\DifsFilesToTest\7366_App360_System_Details.docx to SharePoint as /App360KM/DifsTest/7366_App360_System_Details.docx
SPImportHelper Information: 1002 : SetMetaData : Setting destination field ContentTypeId
SPImportHelper Information: 1001 : SetMetaData : Using source value 0x0101002D6A83321ED28349B7C8CE8CA91B9447
SPImportHelper Information: 1002 : SetMetaData : Setting destination field Title
SPImportHelper Information: 1001 : SetMetaData : Using source value 7366_App360_System_Details.docx
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_String
SPImportHelper Information: 1002 : SetMetaData : Setting destination field MyColumn
SPImportHelper Information: 1001 : SetMetaData : Using source value Details
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_String
SPImportHelper Information: 1002 : SetMetaData : Setting destination field Flavors
SPImportHelper Information: 1001 : SetMetaData : Using source value Vanilla
SPImportHelper Information: 1003 : SetMetaData : Using a mapping of type SPImportHelper.Settings.ImportMapping_ManagedMetaDataCSOM
SPImportHelper Information: 1010 : Get_Term : TaxonomyFieldType
SPImportHelper Information: 1011 : Got_Term : b5b0d6c9-f717-43ff-b79d-5db8f06877c9
SPImportHelper Information: 1012 : Set_Term : b5b0d6c9-f717-43ff-b79d-5db8f06877c9
SPImportHelper Information: 1000 : Progress : Exception importing : C:\Difs\DifsTest\DifsFilesToTest\7366_App360_System_Details.docx : You must fill out all required properties before checking in this document.