ejemplo - sqlbulkcopy c# example excel
SqlBulkCopy-El valor dado de tipo String del origen de datos no se puede convertir para escribir dinero de la columna de destino especificada (7)
Estoy obteniendo esta excepción cuando intento hacer una copia de SqlBulk desde una tabla de datos.
Error Message: The given value of type String from the data source cannot be converted to type money of the specified target column.
Target Site: System.Object ConvertValue(System.Object, System.Data.SqlClient._SqlMetaData, Boolean, Boolean ByRef, Boolean ByRef)
Entiendo lo que dice el error, pero ¿cómo puedo obtener más información, como la fila / campo en el que está sucediendo? La base de datos se completa con un tercero y puede contener hasta 200 columnas y hasta 10k filas. Las columnas que se devuelven dependen de la solicitud enviada a la tercera parte. Todas las columnas de datos son de tipo cadena. Las columnas de mi base de datos no son todas de tipo varchar, por lo tanto, antes de ejecutar la inserción, formateo los valores de datos utilizando el siguiente código (código no importante eliminado):
//--- create lists to hold the special data type columns
List<DataColumn> IntColumns = new List<DataColumn>();
List<DataColumn> DecimalColumns = new List<DataColumn>();
List<DataColumn> BoolColumns = new List<DataColumn>();
List<DataColumn> DateColumns = new List<DataColumn>();
foreach (DataColumn Column in dtData.Columns)
{
//--- find the field map that tells the system where to put this piece of data from the 3rd party
FieldMap ColumnMap = AllFieldMaps.Find(a => a.SourceFieldID.ToLower() == Column.ColumnName.ToLower());
//--- get the datatype for this field in our system
Type FieldDataType = Nullable.GetUnderlyingType(DestinationType.Property(ColumnMap.DestinationFieldName).PropertyType);
//--- find the field data type and add to respective list
switch (Type.GetTypeCode(FieldDataType))
{
case TypeCode.Int16:
case TypeCode.Int32:
case TypeCode.Int64: { IntColumns.Add(Column); break; }
case TypeCode.Boolean: { BoolColumns.Add(Column); break; }
case TypeCode.Double:
case TypeCode.Decimal: { DecimalColumns.Add(Column); break; }
case TypeCode.DateTime: { DateColumns.Add(Column); break; }
}
//--- add the mapping for the column on the BulkCopy object
BulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(Column.ColumnName, ColumnMap.DestinationFieldName));
}
//--- loop through all rows and convert the values to data types that match our database''s data type for that field
foreach (DataRow dr in dtData.Rows)
{
//--- convert int values
foreach (DataColumn IntCol in IntColumns)
dr[IntCol] = Helpers.CleanNum(dr[IntCol].ToString());
//--- convert decimal values
foreach (DataColumn DecCol in DecimalColumns)
dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());
//--- convert bool values
foreach (DataColumn BoolCol in BoolColumns)
dr[BoolCol] = Helpers.ConvertStringToBool(dr[BoolCol].ToString());
//--- convert date values
foreach (DataColumn DateCol in DateColumns)
dr[DateCol] = dr[DateCol].ToString().Replace("T", " ");
}
try
{
//--- do bulk insert
BulkCopy.WriteToServer(dtData);
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
//--- handles error
//--- this is where I need to find the row & column having an issue
}
Este código debe formatear todos los valores para sus campos de destino. En el caso de este error, el decimal, la función que lo limpia eliminará cualquier carácter que no sea 0-9 o. (punto decimal). Este campo que está arrojando el error sería anulable en la base de datos.
La excepción de nivel 2 tiene este error:
Error Message: Failed to convert parameter value from a String to a Decimal.
Target Site: System.Object CoerceValue(System.Object, System.Data.SqlClient.MetaType, Boolean ByRef, Boolean ByRef, Boolean)
y la excepción de nivel 3 tiene este error:
Error Message: Input string was not in a correct format
Target Site: Void StringToNumber(System.String, System.Globalization.NumberStyles, NumberBuffer ByRef, System.Globalization.NumberFormatInfo, Boolean)
¿Alguien tiene alguna idea para arreglar? ¿O alguna idea para obtener más información?
@Corey - Simplemente elimina todos los caracteres no válidos. Sin embargo, tu comentario me hizo pensar en la respuesta.
El problema fue que muchos de los campos de mi base de datos son anulables. Cuando se usa SqlBulkCopy, una cadena vacía no se inserta como un valor nulo. Entonces, en el caso de mis campos que no son varchar (bit, int, decimal, datetime, etc.), intentaba insertar una cadena vacía, lo que obviamente no es válido para ese tipo de datos.
La solución fue modificar mi bucle donde valido los valores para esto (repetido para cada tipo de datos que no sea una cadena)
//--- convert decimal values
foreach (DataColumn DecCol in DecimalColumns)
{
if(string.IsNullOrEmpty(dr[DecCol].ToString()))
dr[DecCol] = null; //--- this had to be set to null, not empty
else
dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());
}
Después de hacer los ajustes anteriores, todo se inserta sin problemas.
Asegúrese de que los valores de columna u agregados en la clase de entidad tengan propiedades de conjunto de obtención también en el mismo orden que está presente en la tabla objetivo.
Compruebe los datos que está escribiendo en el servidor. Puede ser que los datos tengan delimitador que no se utiliza.
me gusta
045|2272575|0.000|0.000|2013-10-07
045|2272585|0.000|0.000;2013-10-07
tu delimitador es ''|'' pero los datos tienen un delimitador '';'' . Así que para esto estás recibiendo el error.
Para las personas que se encuentran con esta pregunta y reciben un mensaje de error similar con respecto a un nvarchar en lugar de dinero:
El valor dado de tipo Cadena del origen de datos no se puede convertir al tipo nvarchar de la columna de destino especificada.
Esto podría ser causado por una columna demasiado corta.
Por ejemplo, si su columna se define como nvarchar(20) y tiene una cadena de 40 caracteres, puede aparecer este error.
Utilice SqlBulkCopyColumnMapping.
Ejemplo:
private void SaveFileToDatabase(string filePath)
{
string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["MHMRA_TexMedEvsConnectionString"].ConnectionString.ToString();
String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=/"Excel 12.0/"", filePath);
//Create Connection to Excel work book
using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
{
//Create OleDbCommand to fetch data from Excel
using (OleDbCommand cmd = new OleDbCommand("Select * from [Crosswalk$]", excelConnection))
{
excelConnection.Open();
using (OleDbDataReader dReader = cmd.ExecuteReader())
{
using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
{
//Give your Destination table name
sqlBulk.DestinationTableName = "PaySrcCrosswalk";
SqlBulkCopyColumnMapping AdmissionPaySrcID=new SqlBulkCopyColumnMapping("AdmissionPaySrcID","AdmissionPaySrcID");
sqlBulk.ColumnMappings.Add(AdmissionPaySrcID);
SqlBulkCopyColumnMapping TMHP_Detail = new SqlBulkCopyColumnMapping("TMHP_Detail", "TMHP_Detail");
sqlBulk.ColumnMappings.Add(TMHP_Detail);
SqlBulkCopyColumnMapping PaySrcType = new SqlBulkCopyColumnMapping("PaySrcType", "PaySrcType");
sqlBulk.ColumnMappings.Add(PaySrcType);
SqlBulkCopyColumnMapping AgencyID = new SqlBulkCopyColumnMapping("AgencyID", "AgencyID");
sqlBulk.ColumnMappings.Add(AgencyID);
SqlBulkCopyColumnMapping CountyCode = new SqlBulkCopyColumnMapping("CountyCode", "CountyCode");
sqlBulk.ColumnMappings.Add(CountyCode);
SqlBulkCopyColumnMapping EntityID = new SqlBulkCopyColumnMapping("EntityID", "EntityID");
sqlBulk.ColumnMappings.Add(EntityID);
sqlBulk.WriteToServer(dReader);
}
}
}
}
}
Ya que no creo que "Please use..." plus some random code that is unrelated to the question es una buena respuesta, pero creo que el espíritu era correcto, decidí responder esto correctamente.
Cuando está utilizando Sql Bulk Copy, intenta alinear sus datos de entrada directamente con los datos del servidor. Por lo tanto, toma la tabla de servidores y realiza una declaración SQL similar a esto:
INSERT INTO [schema].[table] (col1, col2, col3) VALUES
Por lo tanto, si le asigna las Columnas 1, 3 y 2, AUNQUE PENSO, sus nombres pueden coincidir (por ejemplo, col1, col3, col2). Se insertará así:
INSERT INTO [schema].[table] (col1, col2, col3) VALUES
(''col1'', ''col3'', ''col2'')
Sería un trabajo extra y una sobrecarga para la inserción masiva de SQL para determinar un mapeo de columnas. Entonces, en lugar de eso, le permite elegir ... Asegurarse de que su Código y sus columnas de la Tabla SQL estén en el mismo orden, o establecer explícitamente que se alineen por Nombre de columna.
Por lo tanto, si su problema es una mala alineación de las columnas, que es probablemente la mayor parte de la causa de este error, esta respuesta es para usted.
TLDR
using System.Data;
//...
myDataTable.Columns.Cast<DataColumn>().ToList().ForEach(x =>
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(x.ColumnName, x.ColumnName)));
Esto tomará su DataTable existente, que está intentando insertar en su objeto BulkCopy creado, y simplemente asignará explícitamente el nombre al nombre. Por supuesto, si, por alguna razón, decidió nombrar sus Columnas de DataTable de manera diferente a sus Columnas de SQL Server ... eso es lo que tiene usted.
hay otro problema que debe resolver al intentar la columna de asignación es la longitud de la cadena, por ejemplo, TK_NO nvarchar (50) tiene que asignarla a la misma longitud en el campo de destino