Strings longer than 255 characters are not copied by SqlBulkCopy.

excel sqlbulkcopy

Question

A large Excel spreadsheet has to be copied into the SQL Server database. I am reading all of the data from the [Sheet1$] by opening an OldDbConnection to the Excel spreadsheet. I then obtain an IDataReader with the spreadsheet data using the OleDbCommand.

The Excel document has a number of cells with text that is longer than 256 characters. However, when I use SqlBulkCopy to transfer this to the database table, I only see the first 255 characters from these excel columns. The fields in the database table may hold 5000 characters.

Does SqlBulkCopy have a field size restriction? Thanks!

1
0
4/20/2009 5:11:52 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

SqlBulkCopy and OldDbConnection are unfamiliar to me.
Excel may only be providing the first 255 characters, however.
When accessing a cell's value in Excel, the first 255 characters are returned if you use the.value property, and the whole text is returned if you use the.text property. I hope this helps you with your thinking.

0
4/20/2009 9:12:39 PM

Related Questions

Example of SqlBulkCopy WriteToServer. What am I doing incorrectly?

Your foreign keys will automatically become nocheck foreign keys when you use SqlBulkCopy. You must include the choice....SqlBulkCopyOptions.CheckConstraints... To maintain your foreign keys, be sure to verify both old and new data.
c# sql sqlbulkcopy sql-server
asked by jhilden

Is it possible to transfer data across servers using sql bulk copy?

Is it possible to transfer data across servers using sql bulk copy?
sql sqlbulkcopy sql-server
asked by Relativity

Attempting to duplicate a row with significant content in an XML column using SqlBulkCopy fails.

I'm attempting to move a record across two SQL Server tables. The structure of both tables is the identical, and one of the columns is of the xml type. About 2.5Mb worth of xml data is included in each row of the source table....See the attached map.zip file for the content of the xml column that I saved, or go to ...https://docs.google.com/leaf?id...
sql sqlbulkcopy sql-server xml
asked by stask

When SQLBulkCopy is used with Transaction, an event is fired each time a batch is copied.

I'm presently using SQLBulkCopy and Transactions to upload data to MS SQL server. After each batch is posted, I'd want to be able to create an event (I have already tried SQLRowsCopied event and it doesnt work, see quote below)...MSDN citation...No action, such as transaction activity, is supported in the connection during the execution of the bulk...
c# sqlbulkcopy sql-server
asked by Hans Rudel

sqlbulkcopy from Excel via ACE.OLEDB truncates text to 255 chars

Using SqlBulkCopy, import is really simple:...string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\""; using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString)) { excelConnection.Open(); OleDbCommand cmd = new OleDbComm...
excel oledb sqlbulkcopy
asked by Alex Polkhovsky

how to use ascii character for quote in COPY in cqlsh

I'm using copy in cqlsh to transfer data from a large.csv file into Cassandra. I'm using CQL 3.0 and Cassandra 1.2. I must use another character to submit my data since " is a part of it; I must use any extended ASCII letters. I attempted a number of things but failed....The following is functional, however for my needs I must use extended ASCII ch...
cassandra cql3 cqlsh csv sqlbulkcopy
asked by eldho

Troubleshooting SqlBulkCopy not doing minimal logging

I've made a concept based on what ...here... said....QueuedDataReader... it encloses a...ConcurrentQueue... in an...IDataReader... for me to pass it on...SqlBulkCopy... data into the database and "stream" it....Even though I think I've done everything recommended by ...here... and ...here..., everything runs rather well and quickly, however the log...
import performance sqlbulkcopy sql-server sql-server-2008-r2
asked by Benjol

SqlBulkCopy does not store accented characters properly

This table is being designed by me, and it is set to...French_CI_AS... accent sensitivity, French culture, ...all sql string types taken into account.......For this table, I'm creating a ...sortable dataset... (not the purpose of this question)....bulk... copy in sql:... var ds = new FrenchCharacters.FrenchDataSet(); using (var destinationConnec...
datatable sqlbulkcopy sql-server-2008
asked by andrei.ciprian

SqlBulkCopy is not copying all the data in a column over

I used my csv file's top 8 rows as a crude workaround by just inserting a character string into each column. Sqlbulkcopy is tricked into thinking that all fields are strings. The records that contain the character string should then be deleted in SQL.
.net sql sqlbulkcopy vb.net visual-studio-2012
asked by dudesickle

Error with SqlBulkCopy: invalid character but the XML string seems to be valid

A legitimate XML string is resulting in the error System while using SqlBulkCopy (in C#, framework 4.5.1). "XML parsing: line 1, character 29, A string literal was required" Data.SqlClient.SqlException...It is the table....CREATE TABLE [dbo].[JobManager_BigTextTest]( [PKID] [int] IDENTITY(1,1) NOT NULL, [BigXML] [xml] NOT NULL, PK_JobManager_BigTex...
c# escaping sql sqlbulkcopy xml
asked by Sam Jazz

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow