Troubleshooting formatting errors in Winshuttle Studio scripts

By Tammy Lake posted 06-07-2021 11:50

  

A common type of error returned when data is being uploaded to SAP from Winshuttle is a data format error.  For example, the error returned may be something like “Enter a numeric value”.  Typically, the resolution is to change the format of the Excel column where the data is being uploaded from.

How do you know which column is causing the error if the error is simply “Enter a number value” with no additional details?  Best practice tips are below for avoiding and troubleshooting formatting errors when using Winshuttle Studio scripts:

 

Developers - Avoiding formatting errors

  • Format Excel to match SAP during script mapping process - When you create an Excel template to be used with a Transaction script, check to make sure the format of the data in the Excel column (or cell for cell-based mapping) matches the format in the Winshuttle Studio script.
    • In the example below, the format expected for the Amount in Document Currency field is BCD (binary-coded decimal or number with decimals) and the field is mapped to column S. Therefore, column S in the Excel template should be formatted to match: “Number”.   “General” will also work if data entered manually or returned from an Excel formula is numeric.


      • Format mapping examples are below (Note: Any time a formula is used to calculate the data, the formula must return the data in the format required by SAP).

    Runners - Troubleshooting formatting errors

    • If the error is displayed on an existing Excel template, start by looking at the error and then look at the data to see which column(s) could be the issue. For example, if the error is “Enter a numeric value”, look at the data in the columns to narrow down which column could be the culprit.
      • Identify the columns where the data being entered is all numeric.
      • Check the format of each of those columns.
      • If the format is something other than Number (or General in the case of formulas), change the format to either Number or General as required.
      • Save the file and try to upload the data again.
      • NOTE: If the Excel template was accessed from the Winshuttle server (Studio Manager, Foundation, or Evolve), report the issue to the developer responsible for script modifications so the Excel template on the server can be corrected moving forward.

     

    Exceptions

    There may be cases when SAP is expecting data in a specific format that other than the default.  Examples:

    • Padding – Sometimes SAP requires specific padding (No Padding, Pre-Pad with spaces, Pre-Pad with Zeros, Post-Pad with Spaces). Click here to view a support article relating to Padding.
    • Use Transform formulas when SAP is very specific - In a few exceptional cases, SAP requires data to be uploaded in a very specific format. For example, if you are uploading journal entry data using COPA fields that includes Customer ID or Ship to ID, SAP requires the data for those fields to be a fixed value of 10 if the data is numeric.  However, it will accept text values “as is”. 
      • In these exceptional cases, use Transform formulas in the Winshuttle script to format the data to meet SAP’s requirements to include preceding 0s
        • Example Formula: If column AL is not blank and is a number pre-pad with 0s and make the length 10, or if it is text accept the data as is: =IF(NOT(ISBLANK(AL13)),IF(ISNUMBER(VALUE(AL13)),RIGHT("0000"&AL13,10),IF(NOT(ISNUMBER(val(AL13))),AL13)),"")

     

    Sometimes SAP can be picky.  It’s like figuring out a puzzle.  However, once you have all of the pieces put together, your upload can be a masterpiece users can enjoy for years to come. 

    For more support, please create a support request here.

    0 comments
    28 views

    Permalink