Re: Excel cutting off Column Data?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: onedaywhen (onedaywhen_at_fmail.co.uk)
Date: 05/12/04 

Date: 12 May 2004 07:53:32 -0700

"John Rugo" wrote ...

> ¤ 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.

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. 

--
Quantcast