CODEWITHSUNDEEP
c#excelsql-server-2012sqlbulkcopy
eyalb
eyalb

Reputation: 3022

string filed is truncated on SqlBulkCopy insert

I use SqlBulkCopy to insert excel data to table.

One of the fields is ntext on the destination table but when the data is inserted the data is truncated after 255 chars.

using (OleDbConnection conExcel = new OleDbConnection(connstring))
                {
                    DL_ID = library.Save();

                    conExcel.Open();

                    string sheet1 = conExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                    DataTable dtExcelData = new DataTable();

                    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT " + Convert.ToString(DL_ID) + " as DL_ID,* FROM [" + sheet1 + "]", conExcel))
                    {
                        oda.Fill(dtExcelData.DefaultView.Table);

                    }
                    conExcel.Close();

                    string consString = ConfigurationManager.ConnectionStrings["mycon"].ConnectionString;
                    using (SqlConnection con = new SqlConnection(consString))
                    {

                        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                            sqlBulkCopy.DestinationTableName = "dbo.IMPORTS_LIBRARY_DATA";

                            sqlBulkCopy.ColumnMappings.Add(0,"DL_ID");
                            sqlBulkCopy.ColumnMappings.Add(1,"ILD_OLD_ID");
                            sqlBulkCopy.ColumnMappings.Add(2,"ILD_Desc");
                            sqlBulkCopy.ColumnMappings.Add(3, "ILD_Unit");
                            sqlBulkCopy.ColumnMappings.Add( 4,"ILD_Empty");
                            sqlBulkCopy.ColumnMappings.Add( 5,"ILD_Price1");
                            sqlBulkCopy.ColumnMappings.Add( 6,"ILD_Price2");

                            con.Open();
                            sqlBulkCopy.WriteToServer(dtExcelData);
                            con.Close();
                        }


                    }

                }

the filed that is truncated is ILD_Desc

i tried to change it to nvarchar(max) and it's still truncated. i didnt get an error, just see the data truncated on destination table to 255 chars.

UPDATE:

CREATE TABLE [dbo].[IMPORTS_LIBRARY_DATA](
[ILD_ID] [int] IDENTITY(1,1) NOT NULL,
[DL_ID] [int] NULL,
[ILD_OLD_ID] [varchar](50) NULL,
[ILD_Desc] [ntext] NULL,
[ILD_Unit] [nvarchar](50) NULL,
[ILD_Empty] [varchar](50) NULL,
[ILD_Price1] [varchar](50) NULL,
[ILD_Price2] [varchar](50) NULL,
[ILD_Folder1] [varchar](50) NULL,
[ILD_Folder2] [varchar](50) NULL,
[ILD_Folder3] [varchar](50) NULL,
[ILD_Date] [varchar](50) NULL,
 CONSTRAINT [PK_IMPORTS_LIBRARY_DATA] PRIMARY KEY CLUSTERED 
(
[ILD_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Upvotes: 0

Views: 1770

Answers (1)

Cristina Alboni
Cristina Alboni

Reputation: 1022

I think the issues is with the OLEDB provider for excel.

When you use OLEDB providers then the datatype is determined automatically by the provider based on the first 8 rows. If you have lengthy cells in the first 8 rows then data type will be set as text and otherwise it will be memo type which can hold 255 characters. To overcome this issue Either change the registry setting as mentioned in below KB article: http://support.microsoft.com/kb/281517 or use Microsoft.Jet.OLEDB provider to read the data.

OBS: You can change the registry setting called TypeGuessRows. Values range is from 0 to 16 (default is 8), but if you set 0, the number of rows scanned is 16384.

http://forums.asp.net/t/1913118.aspx?Microsoft+oledb+data+acces+truncates+the+data+length+to+255+characters

Upvotes: 1

Related Questions