Clever Geek Handbook

Truncating data after 255 characters when pasting into dataset from Excel, but not problem when populating DataTable

I am trying to insert data from an Excel file into a dataset using ADO.NET. Below is the accepted procedure

  • First, all excel data is loaded into dataset using

connString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = mydb.xlsx; Extended Properties = \" Excel 12.0; HDR = Yes; IMEX = 2 \ "";

  • When filling the dataset, it only inserts 255 characters. (I couldn't find where it truncates). There is no truncation code in our source code. But when the same connection is used to populate data, this problem does not arise.

Please help me to overcome this problem.

Thank you in advance

+3


source to share


3 answers


The problem is that the ACE driver infers the TEXT datatype for the column when populating the dataset. Text columns are limited to 255 characters. You need to force it to use the MEMO datatype. When you populate the database, presumably the database column is of a type that supports more than 255 characters and the driver picks that.

In addition to the methods described in this problem thread , you can force it to use the memo data type by inserting a dummy string and inserting 256 or more characters into cell for that column. (This thread is specific to Jet, but should also be applicable to ACE.)



A better alternative is perhaps using some other library to read the file and populate the DataSet without Jet / ACE.

+5


source


You are a little vague as to how it is truncated, but from the following there are several possibilities: -

  • Worksheet size 65,536 rows 256 columns
  • Column width 255 characters

Hence: -



http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

Worksheet and workbook specification

MAXIMUM LIMIT FUNCTION
Open books are limited by available memory and system resources
Worksheet size 65,536 rows 256 columns Column
width 255 characters
Row height 409 points
Pagination 1000 horizontal and vertical
Cell content length (text) 32,767 characters. Only 1024 images per cell; all 32,767 displays in the formula bar.
Sheets in a workbook Limited available memory (default 3 sheets)
Colors in a workbook 56
Cell styles in a workbook 4,000
Named views in a
workbook Limited available memory Custom number formats: 200 to 250, depending on the language version of Excel you have installed.
Names in workbook Limited available
Windows memory in workbook Limited by system resources
Panels in window 4
Linked sheets Limited available memory
Scripts are limited by available memory; the summary report shows only the first 251 scenarios Modifying cells in a scenario 32
Adjustable cells in Solver 200
Custom Functions Limited by available memory
Scale range 10 to 400 percent
Reports limited by available memory
Reference sort 3 in one view; unlimited when using sequential sorts
Undo levels 16
Fields in data form 32
Custom toolbars in workbook Limited available memory
Custom Toolbar Buttons Limited Available Memory

0


source


This is the cause of the problem - https://support.microsoft.com/en-us/kb/189897

It can be fixed by inserting a dummy line at the top (maybe a header line - I haven't tried it myself) with 255+ characters or more.

This seems like a promising solution.

0


source






More articles:


All Articles