Strings longer than 255 characters are not copied by SqlBulkCopy.

excel sqlbulkcopy

Question

I am trying to copy a large Excel spreadsheet into the SQL Server database. I am opening an OldDbConnection to the Excel spreadsheet and reading everything from the [Sheet1$]. Then, I am using the OleDbCommand to get a IDataReader with the spreadsheet data.

There are several cells in the Excel sheet with text contents of more than 256 characters. But, once this gets uploaded to the database table using SqlBulkCopy, I only see first 255 characters from these excel cells in the database. The database table fields are 5000 characters long.

Does SqlBulkCopy limit the field size? Thanks!

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

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

I am not aware of SqlBulkCopy and OldDbConnection.
however Excel might be giving out first 255 chars.
In Excel if you access a cells value with .value property you get first 255 chars, and if you acccess it with .text property you get full text. Hope this will give you some idea.

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

Related Questions

Example of SqlBulkCopy WriteToServer. What am I doing incorrectly?

By default SqlBulkCopy will turn your foreign keys into nocheck foreign keys. You need to add the option ...SqlBulkCopyOptions.CheckConstraints... in order to keep your foreign keys checking all existing and all new data.
c# sql sqlbulkcopy sql-server
asked by jhilden

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

Can I use sql bulk copy to copy data within the same server ?
sql sqlbulkcopy sql-server
asked by Relativity

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

I am trying to copy a record from one SQL Server table to another. Both tables have the same structure, one of the columns is of type xml. Source table has a large xml content in of the rows, about 2.5Mb....I saved the content of the xml column into a file, see map.zip attached or download it from ...https://docs.google.com/leaf?id=0Bz4PXXEQL5TpM2...
sql sqlbulkcopy sql-server xml
asked by stask

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

Im currently uploading data to MS SQL server via SQLBulkCopy and Transactions. I would like to be able to raise an event after each batch has been uploaded (I have already tried SQLRowsCopied event and it doesnt work, see quote below)...MSDN quote:...No action, such as transaction activity, is supported in the connection during the execution of the...
c# sqlbulkcopy sql-server
asked by Hans Rudel

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

Pretty straight-forward import using SqlBulkCopy:...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 Ole...
excel oledb sqlbulkcopy
asked by Alex Polkhovsky

how to use ascii character for quote in COPY in cqlsh

I am uploading data from a a big .csv file into Cassandra using copy in cqlsh. I am using cassandra 1.2 and CQL 3.0. However since " is part of my data I have to use some other character for uploading my data, I need to use any extended ASCII characters. I tried various approaches but fails....The following works, but need to use an extended ascii...
cassandra cql3 cqlsh csv sqlbulkcopy
asked by eldho

Troubleshooting SqlBulkCopy not doing minimal logging

Based on the idea presented ...here..., I've created a ...QueuedDataReader... which wraps a ...ConcurrentQueue... in an ...IDataReader... so I can pass it to ...SqlBulkCopy... and 'stream' data into the database....It works ok, and pretty fast, but the log file grows quite dramatically, even though I believe I've implemented everything suggested ..
import performance sqlbulkcopy sql-server sql-server-2008-r2
asked by Benjol

SqlBulkCopy does not store accented characters properly

I am designing this table, ...the collation for every column... is set to ...French_CI_AS..., French culture, accent sensitive. ...Every sql string type considered.... ...I am building a ...typed dataset... for this table (not the purpose of this question)....Sql ...bulk... copy:... var ds = new FrenchCharacters.FrenchDataSet(); using (var desti...
datatable sqlbulkcopy sql-server-2008
asked by andrei.ciprian

SqlBulkCopy is not copying all the data in a column over

As a hacky workaround I simply put a character string into each column in the top 8 rows of my csv file. It fools sqlbulkcopy into treating all fields as strings. Then in sql delete the records that contain the character string.
.net sql sqlbulkcopy vb.net visual-studio-2012
asked by dudesickle

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

I am having an issue with SqlBulkCopy (in c#, framework 4.5.1) where a valid XML string is causing the error System.Data.SqlClient.SqlException: {"XML parsing: line 1, character 29, A string literal was expected"}"...The table is... ...CREATE TABLE [dbo].[JobManager_BigTextTest]( [PKID] [int] IDENTITY(1,1) NOT NULL, [BigXML] [xml] NOT NULL, PK_JobM...
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