locked
Error while importing CSV with Empty string in Decimal value RRS feed

  • Question

  • I have a CSV with multiple fields that should be decimal in nature but have empty values "" in some rows. I have the Input (BLOB) data set defined as decimal

    {
                    "name": "Spend",
                    "type": "Decimal"
                },

    I also have the Output dataset (SQL) defined the same way. The Database table is set to NULL.  The pipeline is giving me the below  error.  Is there a way to mark the fields in the datasets as nullable? I am using the Copy Activity in my pipeline with columnMappings.

    Copy activity encountered a user error: ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'ConversionRate' contains an invalid value ''. Cannot convert '' to type 'Decimal'.,Source=Microsoft.DataTransfer.Common,''Type=System.FormatException,Message=Input string was not in a correct format.,Source=mscorlib,'.

    Friday, January 22, 2016 1:54 PM

All replies

  • Hi Jimmy,

    You could try specifying the NullValue property in TextFormat to treat the empty string as null. Refer to this doc: https://azure.microsoft.com/en-us/documentation/articles/data-factory-azure-blob-connector/#specifying-textformat. Let us know if you still have the issues, and please further provide your runId and timestamp of your failed run.

    Monday, January 25, 2016 2:47 AM
  • Added the nullValue to the Dataset definition (shown below) and still getting the same error.  The "Null" values in my CSV are empty strings.

                "format": {
                    "type": "TextFormat",
                    "nullValue": "",
                    "quoteChar": "\""

    Monday, January 25, 2016 1:04 PM
  • Could you provide your runId and timestamp of your failed run for our further investigation?
    Tuesday, January 26, 2016 3:36 AM