SPSS Statistics

Expand all | Collapse all

OUTPUT EXPORT macro - !SUBSTR function returns inconsistent errors

  • 1.  OUTPUT EXPORT macro - !SUBSTR function returns inconsistent errors

    Posted Sat July 31, 2021 10:35 AM
    I mostly export output windows to Excel files. However Excel has a limit up to 31 characters for a sheet name. If the name is longer than 31 SPSS throws an error and the output doesn't export elements. So I created a macro to avoid this situation.
    Code:
    define ExportToExcel (!positional !charend('/')
    /!positional !default("test.xlsx") !cmdend)

    /*CLEANING
    output modify
    /select all except (charts tables models texts pagetitles)
    /deleteobject delete = yes.

    /*CUTTING SHEET NAME TO 31 CHARS
    !let !strSheetName = !unquote(!1)
    !if (!length(!strSheetName) > 31) !then
    !let !strSheetName = !substr(!strSheetName,1,31)
    !ifend
    !let !strSheetName = !quote(!strSheetName)

    /*ADDING .XLSX TO FILE NAME ENDING IF NOT PRESENT
    !let !strFileName = !unquote(!2)
    !if (!index(!strFileName,".xlsx") = 0) !then
    !let !strFileName = !concat(!strFileName,".xlsx")
    !ifend
    !let !strFileName = !quote(!strFileName)

    /*EXPORT
    output export
    /contents export = visible layers = all modelviews = printsetting
    /xlsx documentfile = !strFileName
    operation = createsheet
    sheet = !strSheetName
    location = lastcolumn notescaptions = yes.

    !enddefine.

    For testing purposes I ran this macro twice (with different sheet names of course):
    * WORKS FINE.
    ExportToExcel "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA".

    * ERROR 293:
    The position and length given in a macro SUBSTR function are inconsistent with the string argument.
    The null string has been used for the result.

    ExportToExcel "Abcdefgh".

    I totally don't understand why second instance returns an error. Do you have any idea?

    ------------------------------
    Konrad Gałuszko
    ------------------------------


  • 2.  RE: OUTPUT EXPORT macro - !SUBSTR function returns inconsistent errors

    Posted Sat July 31, 2021 12:59 PM
    I have two thoughts.

    First, run SET MPRINT ON.
    so you can see what syntax the macro is generating.

    Second, and harder to fix, when you truncate the string with !substr, you are truncating on a byte, not character boundary.  That means that you might truncate in the middle of a multibyte character such as might occur in nonwestern languages.  If the names are all plain ascii, you are okay.

    There is a function truncatestring in the spssaux Python module included with Statistics, but it would be awkward to  use it in the middle of a macro.  The char.substr function works on character boundaries, but it applies to case data.  Ideally, this whole macro would be turned into a small Python function.

    --





  • 3.  RE: OUTPUT EXPORT macro - !SUBSTR function returns inconsistent errors

    Posted Mon August 02, 2021 06:21 AM
    1. This is generated syntax visible in an output window after activating SET MPRINT ON:
    435 0 M>* works fine.
    436 0 M> * works fine.
    ExportToExcel "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA".
    437 0 M>
    438 0 M> .
    439 0 M>
    440 0 M>
    441 0 M> output export /contents export = visible layers = all modelviews = printsetting /xlsx documentfile = 'test.xlsx' operati
    on = createsheet sheet = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' location = lastcolumn notescaptions = yes
    443 0 M>
    * error 293:
    444 0 M> * error 293:
    The position and length given in a macro SUBSTR function are inconsistent with the string argument
    445 0 M> The position and length given in a macro SUBSTR function are inconsistent with the string argument
    The null string has been used for the result.
    446 0 M> The null string has been used for the result.
    ExportToExcel "Abcdefgh".

    Warning # 235
    The position and length given in a macro SUBSTR function are inconsistent with
    the string argument. The null string has been used for the result.
    447 0 M>
    448 0 M> .
    449 0 M>
    450 0 M>
    451 0 M> output export /contents export = visible layers = all modelviews = printsetting /xlsx documentfile = 'test.xlsx' operati
    on = createsheet sheet = '' location = lastcolumn notescaptions = yes
    453 0 M>
    * works fine.
    454 0 M> * works fine.
    ExportToExcel "AAAAAAA aaaaaaaa AAAAAA yyyyyyyyyyyyyy".
    455 0 M>
    456 0 M> .
    457 0 M>
    458 0 M>
    459 0 M> output export /contents export = visible layers = all modelviews = printsetting /xlsx documentfile = 'test.xlsx' operati
    on = createsheet sheet = 'AAAAAAA aaaaaaaa AAAAAA yyyyyyy' location = lastcolumn notescaptions = yes

    2. I'm confused. I always use Unicode - even when a macro argument contains locale characters. Morover there's no non-ASCII characters in "Abcdefgh" string (passing a string without quotes also doesn't work).

    3. Yeah, I'm aware that probably the only solution is to write a Python script...

    ------------------------------
    Konrad Gałuszko
    ------------------------------



  • 4.  RE: OUTPUT EXPORT macro - !SUBSTR function returns inconsistent errors

    Posted Mon August 02, 2021 09:29 AM
    The problem is that you are referring to a location beyond the end of the string.  use !if and !length to check whether truncation is needed.

    Otherwise, the code is okay with plain roman characters, but it might fail with other characters  when truncating.

    --