Skip to main content
M
maheshg821223
December 10, 2024
Solved

Import Excel to Database -- How to to know data errors in the file

  • December 10, 2024
  • 5 replies
  • 1 view

I am currently using Smart Service "Import Excel to Database".  The process works fine if I upload an Excel file with correct column headers and data values. In the excel file I purposefully changed the data values like changing a date value to a text value XYZ, changing Text value to a date value. When I upload this modified excel file:

1. The process does not error out. Process Model is showing everything as success.

2. The incorrect data did not get inserted.

But my question is, how do I catch such data errors? I thought the Smart Service "Import Excel to Database" is going give some information on the errors. Please advise this. 

In the real world we will not have perfect data all the time. Hence trying to figure this out.

Regards,

Mahesh

Best answer by mathieud0001

The easiest solution is to import everything in a staging table containing only text columns. You can then process the rows in the staging table and to parse/validate the data as required.

5 replies

mathieud0001
mathieud0001Answer
December 11, 2024

The easiest solution is to import everything in a staging table containing only text columns. You can then process the rows in the staging table and to parse/validate the data as required.

    M
    maheshg821223Author
    December 11, 2024

    Thank you [mention:f5e81b8502fa4e77ad12697686a4fc56:e9ed411860ed4f2ba0265705b8793d05] for your suggestion. One question I have here is, are there any automatic services that Appian offers for data validations on bulk imports from Excel/CSV to database? If there are none, then we will move forward with your suggestion.

    mathieud0001
    mathieud0001
    December 11, 2024

    There is nothing that exists for data validation or cleanup AFAIK.

    Terms & ConditionsAccessibility statement