Subscribe Bookmark RSS Feed

Export to Excel Truncates at 255 characters

pmroz

Super User

Joined:

Jun 23, 2011

When I save a dataset as an Excel file (.xls or .xlsb), any cells containing more than 255 characters get truncated. This appears to be a "feature" of JMP and how it uses Excel ODBC.

I can save the file as a tab-delimited text file and then import to Excel, but this is not a usable option for our users - too many steps.

Has anyone figured out how to overcome this limitation?

Here's a little code snippet that creates a JMP dataset with long character strings:



Regards,
Peter
7 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

When I check the log after saving a JMP dataset as an Excel file, I see the following information:



Is there a way to make these calls myself? Sort of a reverse analog to Open Database. That way I could overwrite the default CHAR(255) with a text length of my choosing.

I finally chanced on SAVE DATABASE, but can't figure out the correct syntax.

Thanks for any help out there!
thechadd

Community Trekker

Joined:

Jun 23, 2011

As I recall, I have had issues copying and pasting sheets (or cells?) in Excel that had cells with more than 255 characters, and I would get an error in Excel indicating as such. I don't know if this was fixed in Office 07 or 10, but the issue you are experiencing may be an Excel limitation.
pmroz

Super User

Joined:

Jun 23, 2011

That's probably from an older version of Excel.

We use Excel 97-2003 and Excel 2007 formats (.xls and .xlsx). In Excel 97-2003 the cell size limit is 32,000 characters, but only 1024 will be displayed. In Excel 2007 the cell size is also 32,000 characters, which can all be displayed.

So Excel can handle it but for some reason JMP assumes we're back in the stone age :).
thechadd

Community Trekker

Joined:

Jun 23, 2011

I think you're right. The spreadsheet I was using where I had the 255 char problem was on Office 2k as I recall. It was quite a few years ago.
thechadd

Community Trekker

Joined:

Jun 23, 2011

I think you're right. The spreadsheet I was using where I had the 255 char problem was on Office 2k as I recall. It was quite a few years ago.
thechadd

Community Trekker

Joined:

Jun 23, 2011

I think you're right. The spreadsheet I was using where I had the 255 char problem was on Office 2k as I recall. It was quite a few years ago.
thechadd

Community Trekker

Joined:

Jun 23, 2011

I think you're right. The spreadsheet I was using where I had the 255 char problem was on Office 2k as I recall. It was quite a few years ago.