Excel cutting off Column Data?

  • Thread starter Thread starter John Rugo
  • Start date Start date
J

John Rugo

Hi All,
I'm not sure what forum to present this question in, sorry if it is wrong.

I am pulling data into a Vb.NET app using ADO.NET.
I am then pushing this data out to an Excel Spreadsheet via OLEDB.

My problem here is that I have a comments field in SQL Server 2000 that is
of type VarChar(4000). If my record's column data is longer then 256
characters it will be truncated. I'm trying to figure out a way around
this. I am setting the output of the column for Excel to (Memo) if that
helps.

Thanks for any help,
John.
 
¤ Hi All,
¤ I'm not sure what forum to present this question in, sorry if it is wrong.
¤
¤ I am pulling data into a Vb.NET app using ADO.NET.
¤ I am then pushing this data out to an Excel Spreadsheet via OLEDB.
¤
¤ My problem here is that I have a comments field in SQL Server 2000 that is
¤ of type VarChar(4000). If my record's column data is longer then 256
¤ characters it will be truncated. I'm trying to figure out a way around
¤ this. I am setting the output of the column for Excel to (Memo) if that
¤ helps.

Not sure how you are putting the data into an Excel Worksheet but if your using SQL the driver makes
a guess as to the destination data type depending upon the contents of the source field. It
typically scans the first eight rows to determine the field types. If the length of any of the first
eight rows is greater than 255 the column is a Memo field, and if not then it's just a Text field.

See if the following helps:

PRB: Transfer of Data from Jet 4.0LEDB Source Fails with Buffer Overflow Error
http://support.microsoft.com/default.aspx?scid=kb;en-us;281517


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Is there anyway around this problem?


¤ Hi All,
¤ I'm not sure what forum to present this question in, sorry if it is wrong.
¤
¤ I am pulling data into a Vb.NET app using ADO.NET.
¤ I am then pushing this data out to an Excel Spreadsheet via OLEDB.
¤
¤ My problem here is that I have a comments field in SQL Server 2000 that is
¤ of type VarChar(4000). If my record's column data is longer then 256
¤ characters it will be truncated. I'm trying to figure out a way around
¤ this. I am setting the output of the column for Excel to (Memo) if that
¤ helps.

Not sure how you are putting the data into an Excel Worksheet but if your
using SQL the driver makes
a guess as to the destination data type depending upon the contents of the
source field. It
typically scans the first eight rows to determine the field types. If the
length of any of the first
eight rows is greater than 255 the column is a Memo field, and if not then
it's just a Text field.

See if the following helps:

PRB: Transfer of Data from Jet 4.0LEDB Source Fails with Buffer Overflow
Error
http://support.microsoft.com/default.aspx?scid=kb;en-us;281517


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
¤ Is there anyway around this problem?

Other than ensuring that the column in one of the first eight rows has more than 255 characters, the
answer would be no. That is simply how the driver works with respect to the Registry entry value.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Could the registry entry be changed to fix this?


¤ Is there anyway around this problem?

Other than ensuring that the column in one of the first eight rows has more
than 255 characters, the
answer would be no. That is simply how the driver works with respect to the
Registry entry value.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
...
¤ Is there anyway around this problem?

Other than ensuring that the column in one of the first eight rows has more
than 255 characters, the
answer would be no. That is simply how the driver works with respect to the
Registry entry value.
Click to expand...

Using the MS OLEDB provider for Jet, you can create an Excel table
with a 'Memo' Jet date type column, the only way I know of getting
256+ characters into a cell.

To demonstrate (assumes column LongCol in table Blah is 256+
characters):

CREATE TABLE
[Excel 8.0;database=C:\Tempo\db.xls;].[Sheet1$]
(Col1 VARCHAR(35))
;
INSERT INTO
[Excel 8.0;database=C:\Tempo\db.xls;].[Sheet1$]
(Col1)
SELECT LongCol AS Col1 FROM Blah
;

This fails with the error, 'The field is too small to accept the
amount of data you attempted to add.'

CREATE TABLE
[Excel 8.0;database=C:\Tempo\db.xls;].[Sheet1$]
(Col1 MEMO)
;
INSERT INTO
[Excel 8.0;database=C:\Tempo\db.xls;].[Sheet1$]
(Col1)
SELECT LongCol AS Col1 FROM Blah
;

No error and data is inserted without being truncated.

--
 
Back
Top