SqlBulkCopy genera System.FormatException durante l'esecuzione di WriteToServer (DataTable)

c# csv formatexception sql sqlbulkcopy

Domanda

Attualmente sto scrivendo un metodo per leggere i dati da un file CSV e importarli in una tabella SQL.

        DataTable dt = new DataTable();
        String line = null;
        int i = 0;

        while ((line = reader.ReadLine()) != null)
        {
            String[] data = line.Split(',');
            if (data.Length > 0)
            {
                if (i == 0)
                {
                    foreach (object item in data)
                    {
                        DataColumn c = new DataColumn(Convert.ToString(item));
                        if (Convert.ToString(item).Contains("DATE"))
                        {
                            c.DataType = System.Type.GetType("System.DateTime");
                        }
                        else { c.DataType = System.Type.GetType("System.String"); }
                        dt.Columns.Add(c);
                    }
                    i++;
                }
                else
                {
                    DataRow row = dt.NewRow();
                    for (int j = 0; j < data.Length; j++)
                    {
                        if (dt.Columns[j].DataType == System.Type.GetType("System.DateTime"))
                        {
                            row[j] = Convert.ToDateTime(data[j]);
                        }
                        else
                        {
                            row[j] = data[j];
                        }
                    }
                    dt.Rows.Add(row);
                }
            }
        }
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[Constant.CONNECTION_STRING_NAME].ConnectionString);
        SqlBulkCopy s = new SqlBulkCopy(con);
        s.DestinationTableName = "abc";
        con.Open();
        s.WriteToServer(dt);

Il problema durante l'esecuzione di questo metodo, un'eccezione viene sempre generata su s.WriteToServer (dt); detto

System.FormatException: la stringa non è stata riconosciuta come DateTime valido. C'è una parola sconosciuta che inizia dall'indice 0.

Ho eseguito il debug e ho visto tutti i dati caricati correttamente in DataTable. Ecco un esempio di una riga di dati nel mio file CSV

DATA_VENDOR_ID,DATA_VENDOR_SUB_ID,DATA_VENDOR_CLIENT_ID,DATA_VENDOR_ACTIVITY_CODE,ACTIVITY_NAME,EFFECTIVE_DATE,ACTIVITY_LEVEL1,ACTIVITY_LEVEL2,ACTIVITY_LEVEL3,ACTIVITY_LEVEL4,ACTIVITY_LEVEL5,PARTICIPANT_ID,DATA_VENDOR_ALT_ID,FILE_CREATION_DATE,INC_VALUE    
V01,,22097,ABCD01,Physical Activity,10/01/2010,Entertain Kiosk,ABCD - EFG 54,30,,AB01,W1234567891,,08/07/2006,100

e il mio schema di tabella SQL:

RowID   int Unique/AutoIncrement
DataVendorId    varchar(32) 
DataVendorSubId varchar(32) 
DataVendorClientId  varchar(32) 
DataVendorActivityCode  varchar(32) 
ActivityName    varchar(64) 
EffectiveDate   datetime    
ActivityLevel1  varchar(253)    
ActivityLevel2  varchar(253)    
ActivityLevel3  varchar(253)    
ActivityLevel4  varchar(253)    
ActivityLevel5  varchar(253)    
ParticipantID   varchar(32) 
DataVendorAltId varchar(32) 
FileCreationDate    datetime    
IncValue    varchar(5)  
CreatedDate datetime    optional/allow null
ModifiedDate    datetime    optional/allow null

Risposta accettata

Il primo problema che vedo è che avrai problemi con la colonna RowID ; Mi aspetto che stia cercando di compensare i dati con una colonna al momento - non sa che lo stai omettendo. È possibile eseguire il pasticcio con i mapping o (nella tabella di dati) aggiungere una colonna RowID (all'indice 0), ma tenere presente che SQL Server ignorerà i valori a meno che non si abiliti l'inserimento di identità.

Forse prova una conversione datetime più esplicita:

row[j] = DateTime.ParseExact(data[j], "dd/MM/yyyy", CultureInfo.InvariantCulture);

Nota che non posso sapere dai dati se è dd / MM o MM / dd, quindi potrebbe essere necessario modificarlo.


Risposta popolare

Ho avuto un problema simile in cui le colonne erano spente e una volta definita la mappatura, nessun problema:

using (SqlBulkCopy sbc = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["SQLDatabase"].ConnectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                sbc.DestinationTableName = "DestinationTable";
                sbc.ColumnMappings.Add("foo", "bar");
                sbc.ColumnMappings.Add("hello", "world");
                sbc.ColumnMappings.Add("col1", "col2");
                sbc.WriteToServer(data);
            }

Inoltre, ho un'estensione convertitore List to DataTable che utilizzo per convertire la mia lista.

public static DataTable ToDataTable<T>(this IEnumerable<T> data)
        {
            PropertyDescriptorCollection properties =
                TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = GetDataValue(prop.GetValue(item));
                table.Rows.Add(row);
            }
            return table;
        }

Il metodo GetDataValue () pulisce i miei dati per le date MinValue, ecc:

private static object GetDataValue(object value)
        {
            if (value == null || (value.GetType() == typeof(DateTime) && Convert.ToDateTime(value) == DateTime.MinValue) || (value.GetType() == typeof(DateTime) && Convert.ToDateTime(value) < Convert.ToDateTime("01/01/1753")))
            {
                return DBNull.Value;
            }

            return value;
        }


Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow