Does Import CSV to Database v6 work for csv file with null values?

I have a csv file with delimiter '|'. When I try to run the smart service by passing a csv file where few fields are null, it is returning the error message 'Error executing SQL'. But when I replace the null with random text values it is writing the data to DB.

Is this an expected behavior of this smart service?

Is there any way to import or read a csv file with null values?

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Lead Developer

    The best approach is definitely to verify the database schema, as Srinivas mentioned. If the columns are defined as NOT NULL, then inserting null or empty values will cause SQL errors — which seems to be exactly what's happening in your case.

    If modifying the database to allow nulls is not an option, there is a workaround:

    You can use the readcsvlog() function to read the CSV file into a CDT. Once the data is loaded, you can loop through the CDT in Appian, check for null or empty values, and replace them with default values (e.g., "N/A" for text, 0 for numbers). After that, you can insert the cleaned data into the database using Write to Data Store Entity.

    However, this workaround is significantly more complex and less efficient than simply updating the database schema to allow nulls in the appropriate columns. If possible, adjusting the DB structure is the cleaner and more maintainable solution

  • readcsvlog() function only allows to read from log files. We won't be able to read from csv document.

  • 0
    Certified Lead Developer
    in reply to Nandhavi Nandakumar

    You're absolutely right — I responded too quickly without thinking it through.

    The readcsvlog() function is specifically designed to read log files, not general CSV documents. It only works with the application server log files, not with user-uploaded CSV files stored as Appian documents.

    Thanks for pointing that out! I appreciate the clarification.

  • 0
    Certified Lead Developer
    in reply to Nandhavi Nandakumar

    As an alternative, you could try using the previewCSVForImport() function from the Excel Tools plugin. It allows you to read CSV documents uploaded to Appian. Example:

    previewCSVForImport(
      document: todocument(ri!csvFile),
      delimiter: ";",
      charset: "UTF-8",
      numberOfRows: 1000
    )
Reply Children
No Data