meta data for this page
  •  

F_BLOB2EXCEL

function from adhoc

Entrypoint excelblob not compatible with UTF-8 - if needed please ask

Inputs/Outputs

   Input      BLOB               BLOb to convert to Excel
   Output     CSTRING(32760)     String to use with Excel

Syntax

   To convert multiline texts and texts with converted commas to Excel, it is required to transform the blob. This function will do the following:
   -    appends a double inverted comma at the beginning and the end of the blob
   -    doubles all inverted commas in the blob
   -    deletes all CHR(13) in the blob
   -    limits the input blob to 32000 characters (limitation in Excel)
       - limit of Excel97 for one field 32000 characters
       - limit of Excel2000 for one field 32767 characters
   TestSQL
   SELECT '"ein dreizeiliger TextBLOb' || F_LF() || 'mit einer zweiten Zeile' || F_LF() || 'und einer dritten Zeile"' AS ISCORRECT, F_BLOB2EXCEL(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 3;
   SELECT NULL AS ISCORRECT, F_BLOB2EXCEL(TEXTBLOB) FROM BLOBTEST WHERE BLOBTESTID = 10;
   Note:
   Actually it doesn’t make any sense to export a very large text from the database to excel cell, this function may be practicable in combinatin with F_LEFT or F_RIGHT for instance:   
   SELECT F_RIGHT(F_BLOB2EXCEL(BLObFeldTAGEBUCH), 1000) FROM ... exportiert z.B. die letzten 1000 Zeichen des Tagebuchs.