CSVHelper错误：由于空柱面无法执行转换

Question

我试图将CSV文件的数据带入C＃（。Net版本4.5.2），将CSOM导出到SharePoint Online列表中。我的问题是我遇到此错误：

exception丢弃：'csvhelper.typeconversion.typeconverterexception'in csvhelper.dll funktionäre.exe错误：0：失败：无法执行转换。 文本： '' 会员类型：System.Single typeconverter：“ csvhelper.typeconversion.singleconverter' iReader国家： 圆柱：0 CurrentIndex：11 Headercord： [“ id”，“ anrede”，“ vorname”，“ nachname”，“ firma”，“ strasse”，“ ort”，“ Postleitzahl”，“ Land_Region”，“ Fax”，“ FAX”，“ Telefon”，“ Mitgli” edsnummer”，“ benutzer2”，“ ortsgruppencode”，“ ortsgruppenname”，“电子邮件”，“ initialen”，“ pripat”，“ webseite”，“ webseite”，“ clialatur”] IPARSER状态： Bytecount：0 Charcount：315 行：2 Rawrow：2 计数：20 RAWRECORD： 1 ;;präsidendund max ;; nidum; class =“ __ cf_email__” data-cfemail =“ B5DCDBD3DAF5DBDCD1C0D898DDDAC1D0D99BD6DAD8”> [email＆nbsp; procearted] ; P.; P.; aus ;; 2016 'funktionäre.exe'（CLR V4.0.30319：funktionäre.exe）：已加载'c：\ windows \ Microsoft.net \ assembly \ gac_msil \ mscorlib.resources \ v4.0_4.0.0.0.0.de_b77a5c561934e089 \ mscorlib.resources.dll'。模块是没有符号的。 funktionäre.exe错误：0：堆栈跟踪：bei csvhelper.typeconversion.defaulttypeconverter.convertfromstring（字符串文本，ireaderrow row row，membermapdata membermapdata） bei csvhelper.typeconversion.singleconverter.convertfromstring（字符串文本，ireaderrow Row，MemberMapdata MemberMapData） bei lambda_method（关闭） bei csvhelper.expressions.recordcreator.createt bei csvhelper.expressions.recordmanager.createt bei csvhelper.csvreader.d__87 1.Movenex（）bei system.collections.generic.generic.list 1..ctor（iEnumerable 1 collection） iEnumerable 1来源） C：\ Users \ users \ dkienzl \ source \ repos \funktionäre\funktionäre\ program.cs.cs：zeile 164中的bei ementlistfromcsv.program.getRogram.getRecorcordSv（） C：\ Users \ dkienzl \ source \ repos \funktionäre\funktionäre\ program.cs.cs中的bei ementlistfromcsv.program.main（） 程序'[26296]funktionäre.exe'已以代码0（0x0）。

这是我的程序

using System;
using System.IO;
using System.Configuration;
using System.Collections.Generic;
using System.Reflection;
using System.Diagnostics;
using System.Linq;
using System.Text;
using CsvHelper;
using Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client.Utilities;
using Microsoft.SharePoint.Client.Taxonomy;

namespace ImportListFromCSV
{
class Program
{
    const string csvPath = @"C:\Users\DKienzl\Documents\FDB\Faktuell_normalisiert.csv";
    static void Main()
    {
        try
        {
            //Get site URL and credentials values from     config
            Uri siteUri = new Uri(ConfigurationManager.AppSettings["SiteUrl"].ToString());
            var accountName = ConfigurationManager.AppSettings["AccountName"];
            char[] pwdChars = ConfigurationManager.AppSettings["AccountPwd"].ToCharArray();

            //Convert password to secure string
            System.Security.SecureString accountPwd = new System.Security.SecureString();
            for (int i = 0; i < pwdChars.Length; i++)
            {
                accountPwd.AppendChar(pwdChars[i]);
            }

            //Connect to SharePoint Online
            using (var clientContext = new ClientContext(siteUri.ToString())
            {
                Credentials = new SharePointOnlineCredentials(accountName, accountPwd)
            })
            {
                if (clientContext != null)
                {
                    //Map records from CSV file to C# list
                    List<CsvRecord> records =     
GetRecordsFromCsv();
                    //Get config-specified list
                    List spList = clientContext.Web.Lists.GetByTitle(ConfigurationManager.AppSettings["ListName"]);

                   foreach (CsvRecord record in records)
                    {
                        //Check for existing record based 
on title (assumes Title should be unique per record)
                          CamlQuery query = new 
CamlQuery();
                         query.ViewXml =     
String.Format("@<View><Query><Where><Eq><FieldRef Name=\"ID\" />" +
                             "<Value Type=\"Text\">{0}    
</Value></Eq></Where></Query></View>", record.ID);
                         var existingMappings = spList.GetItems(query);
                         clientContext.Load(existingMappings);
                         clientContext.ExecuteQuery();

                         switch (existingMappings.Count)
                         {
                             case 0: 
                        //No records found, needs to be added
                        AddNewListItem(record, spList, clientContext);
                               break;
                            default:
                                //An existing record was found - continue with next item
                                continue; 
                        }
                    }

                }
            }
        }
        catch (Exception ex)
        {
            Trace.TraceError("Failed: " + ex.Message);
            Trace.TraceError("Stack Trace: " + ex.StackTrace);
        }
    }

    private static void AddNewListItem(CsvRecord record, List spList, ClientContext clientContext)
    {
        //Instantiate dictionary to temporarily store field values
        Dictionary<string, object> itemFieldValues = new Dictionary<string, object>();
        //Use reflection to iterate through the record's properties
        PropertyInfo[] properties = typeof(CsvRecord).GetProperties();
        foreach (PropertyInfo property in properties)
        {
            //Get property value
            object propValue = property.GetValue(record, null);
            //Only set field if the property has a value
            if (!String.IsNullOrEmpty(propValue.ToString()))
            {
                //Get site column that matches the property name
                //ASSUMPTION: Your property names match the internal names of the corresponding site columns
                Field matchingField = spList.Fields.GetByInternalNameOrTitle(property.Name);
                clientContext.Load(matchingField);
                clientContext.ExecuteQuery();

                //Switch on the field type
                switch (matchingField.FieldTypeKind)
                {
                    case FieldType.User:
                        FieldUserValue userFieldValue = GetUserFieldValue(propValue.ToString(), clientContext);
                        if (userFieldValue != null)
                            itemFieldValues.Add(matchingField.InternalName, userFieldValue);
                        else
                            throw new Exception("User field value could not be added: " + propValue.ToString());
                        break;
                    case FieldType.Lookup:
                        FieldLookupValue lookupFieldValue = GetLookupFieldValue(propValue.ToString(),
                            ConfigurationManager.AppSettings["LookupListName"].ToString(),
                            clientContext);
                        if (lookupFieldValue != null)
                            itemFieldValues.Add(matchingField.InternalName, lookupFieldValue);
                        else
                            throw new Exception("Lookup field value could not be added: " + propValue.ToString());
                        break;
                    case FieldType.Invalid:
                        switch (matchingField.TypeAsString)
                        {
                            case "TaxonomyFieldType":
                                TaxonomyFieldValue taxFieldValue = GetTaxonomyFieldValue(propValue.ToString(), matchingField, clientContext);
                                if (taxFieldValue != null)
                                    itemFieldValues.Add(matchingField.InternalName, taxFieldValue);
                                else
                                    throw new Exception("Taxonomy field value could not be added: " + propValue.ToString());
                                break;
                            default:
                                //Code for publishing site columns not implemented
                                continue;
                        }
                        break;
                    default:
                        itemFieldValues.Add(matchingField.InternalName, propValue);
                        break;
                }
            }
        }

        //Add new item to list
        ListItemCreationInformation creationInfo = new ListItemCreationInformation();
        ListItem oListItem = spList.AddItem(creationInfo);

        foreach (KeyValuePair<string, object> itemFieldValue in itemFieldValues)
        {
            if (itemFieldValues != null)
            {
                //Set each field value
                oListItem[itemFieldValue.Key] = itemFieldValue.Value;
            }
            
        }
        //Persist changes
        oListItem.Update();
        clientContext.ExecuteQuery();
    }

    private static List<CsvRecord> GetRecordsFromCsv()
    {
        List<CsvRecord> records = new List<CsvRecord>();
        using (var sr = new StreamReader(csvPath))
        {
            var reader = new CsvReader(sr, System.Globalization.CultureInfo.CurrentCulture);
            records = reader.GetRecords<CsvRecord>().ToList();
        }

        return records;
    }

    private static FieldUserValue GetUserFieldValue(string userName, ClientContext clientContext)
    {
        //Returns first principal match based on user identifier (display name, email, etc.)
        ClientResult<PrincipalInfo> principalInfo = Utility.ResolvePrincipal(
            clientContext, //context
            clientContext.Web, //web
            userName, //input
            PrincipalType.User, //scopes
            PrincipalSource.All, //sources
            null, //usersContainer
            false); //inputIsEmailOnly
        clientContext.ExecuteQuery();
        PrincipalInfo person = principalInfo.Value;

        if (person != null)
        {
            //Get User field from login name
            User validatedUser = clientContext.Web.EnsureUser(person.LoginName);
            clientContext.Load(validatedUser);
            clientContext.ExecuteQuery();

            if (validatedUser != null && validatedUser.Id > 0)
            {
                //Sets lookup ID for user field to the appropriate user ID
                FieldUserValue userFieldValue = new FieldUserValue();
                userFieldValue.LookupId = validatedUser.Id;
                return userFieldValue;
            }
        }
        return null;
    }

    public static FieldLookupValue GetLookupFieldValue(string lookupName, string lookupListName, ClientContext clientContext)
    {
        //Ref: Karine Bosch - https://karinebosch.wordpress.com/2015/05/11/setting-the-value-of-a-lookup-field-using-csom/
        var lookupList = clientContext.Web.Lists.GetByTitle(lookupListName);
        CamlQuery query = new CamlQuery();
        string lookupFieldName = ConfigurationManager.AppSettings["LookupFieldName"].ToString();
        string lookupFieldType = ConfigurationManager.AppSettings["LookupFieldType"].ToString();

        query.ViewXml = string.Format(@"<View><Query><Where><Eq><FieldRef Name='{0}'/><Value Type='{1}'>{2}</Value></Eq>" +
                                        "</Where></Query></View>", lookupFieldName, lookupFieldType, lookupName);

        ListItemCollection listItems = lookupList.GetItems(query);
        clientContext.Load(listItems, items => items.Include
                                            (listItem => listItem["ID"],
                                            listItem => listItem[lookupFieldName]));
        clientContext.ExecuteQuery();

        if (listItems != null)
        {
            ListItem item = listItems[0];
            FieldLookupValue lookupValue = new FieldLookupValue();
            lookupValue.LookupId = int.Parse(item["ID"].ToString());
            return lookupValue;
        }

        return null;
    }

    public static TaxonomyFieldValue GetTaxonomyFieldValue(string termName, Field mmField, ClientContext clientContext)
    {
        //Ref: Steve Curran - http://sharepointfieldnotes.blogspot.com/2013_06_01_archive.html
        //Cast field to TaxonomyField to get its TermSetId
        TaxonomyField taxField = clientContext.CastTo<TaxonomyField>(mmField);
        //Get term ID from name and term set ID
        string termId = GetTermIdForTerm(termName, taxField.TermSetId, clientContext);
        if (!string.IsNullOrEmpty(termId))
        {
            //Set TaxonomyFieldValue
            TaxonomyFieldValue termValue = new TaxonomyFieldValue();
            termValue.Label = termName;
            termValue.TermGuid = termId;
            termValue.WssId = -1;
            return termValue;
        }
        return null;
    }

    public static string GetTermIdForTerm(string term, Guid termSetId, ClientContext clientContext)
    {
        //Ref: Steve Curran - http://sharepointfieldnotes.blogspot.com/2013_06_01_archive.html
        string termId = string.Empty;

        //Get term set from ID
        TaxonomySession tSession = TaxonomySession.GetTaxonomySession(clientContext);
        TermStore ts = tSession.GetDefaultSiteCollectionTermStore();
        TermSet tset = ts.GetTermSet(termSetId);

        LabelMatchInformation lmi = new LabelMatchInformation(clientContext);

        lmi.Lcid = 1033;
        lmi.TrimUnavailable = true;
        lmi.TermLabel = term;

        //Search for matching terms in the term set based on label
        TermCollection termMatches = tset.GetTerms(lmi);
        clientContext.Load(tSession);
        clientContext.Load(ts);
        clientContext.Load(tset);
        clientContext.Load(termMatches);

        clientContext.ExecuteQuery();

        //Set term ID to first match
        if (termMatches != null && termMatches.Count() > 0)
            termId = termMatches.First().Id.ToString();

        return termId;
    }
}
}

代码。 /strong>

using System;

namespace ImportListFromCSV
{
public class CsvRecord
{
    public float ID { get; set; }
    public string Anrede { get; set; }
    public string Vorname { get; set; }
    public string Nachname { get; set; }
    public string Firma { get; set; }
    public string Strasse { get; set; }
    public string Ort { get; set; }
    public string Postleitzahl { get; set; }
    public string Land_Region { get; set; }
    public string Fax { get; set; }
    public string Telefon { get; set; }
    public float Mitgliedsnummer { get; set; }
    public float Benutzer2 { get; set; }
    public float Ortsgruppencode { get; set; }
    public string Ortsgruppenname { get; set; }
    public string Email { get; set; }
    public string Initialen { get; set; }
    public bool Privat { get; set; }
    public string Webseite { get; set; }
    public float Legislatur { get; set; }
}
}

，这是我的app.config（但当然不是我的真实数据）：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
    <supportedRuntime version="v4.0"         
sku=".NETFramework,Version=v4.5" />
</startup>
<appSettings>
    <add key="SiteUrl" value="http://myWebsite/" />
    <add key="AccountName" value="[email protected]"     
/>
    <add key="AccountPwd" value="Password123" />
    <add key="ListName" value="Funktionaere" />
    <add key="LookupListName" value="Lookup List" />
    <add key="LookupFieldName" value="Title" />
    <add key="LookupFieldType" value="Text" />
</appSettings>
</configuration>

我从中得到的大多数代码（我必须进行一些更改，因为它有点更改年龄较大。）：

这是我一直在使用的CSV文件： https://drive.google.com/file/d/1gvqur3g--pxleff0qmo3wqmdbemevdeh/viewh/view？usp = sharing

我希望有人可以帮助我

最好的问候

Daniel Daniel Daniel Daniel Daniel

Answer 1

错误消息让您知道问题是什么。它试图将空文本<代码>文本转换为' memberType：system.single。您可以使属性mitgliedsnummer，benutzer2和ortsgruppencode nullable nullable

public class CsvRecord
{
    public float ID { get; set; }
    public string Anrede { get; set; }
    public string Vorname { get; set; }
    public string Nachname { get; set; }
    public string Firma { get; set; }
    public string Strasse { get; set; }
    public string Ort { get; set; }
    public string Postleitzahl { get; set; }
    public string Land_Region { get; set; }
    public string Fax { get; set; }
    public string Telefon { get; set; }
    public float? Mitgliedsnummer { get; set; }
    public float? Benutzer2 { get; set; }
    public float? Ortsgruppencode { get; set; }
    public string Ortsgruppenname { get; set; }
    public string Email { get; set; }
    public string Initialen { get; set; }
    public bool Privat { get; set; }
    public string Webseite { get; set; }
    public float Legislatur { get; set; }
}

，或者您可以在映射中给它们一个默认值。

public class CsvRecordMap : ClassMap<CsvRecord>
{
    public CsvRecordMap()
    {
        AutoMap(CultureInfo.InvariantCulture);
        Map(x => x.Mitgliedsnummer).Default(0);
        Map(x => x.Benutzer2).Default(0);
        Map(x => x.Ortsgruppencode).Default(0);
    }
}

我还注意到您将遇到属性privat的问题。它是bool，无法转换值“ AUS”。

Answer 2

例外的数据记录似乎包括一些无效的数据：

1 ;;präsidentund max ;; hotel nidum; am wiesenhang 1a;mösern/seefeld; 6100; 6100;Österreich;; 4,35e+13;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;》 [email＆nbsp; procearted] ; p.; aus; aus;; 2016

尤其是值4,35E+135e+13。对我来说，看起来像是典型的Excel/CSV转换:)