Hi,
I'm trying to import a CSV file into a PostgreSQL DB.
Customer requires all data from appian must rely on that DB which is connected as datasource.
I'm using the plug in Excel tool and the Smart Service Import CSV to Database.
I got the following error from stdOut log:
ERROR ImportCsvToDatabase8 Error executing SQL org.postgresql.util.PSQLException: ERROR: syntax error at or near "," Position: 17
From database, i turned statements log on and i found out the following statement:
2024-03-29 17:32:08 UTC:10.245.48.249(51148):appian@postgres:[3319]:ERROR: syntax error at or near "," at character 17 2024-03-29 17:32:08 UTC:10.245.48.249(51148):appian@postgres:[3319]:STATEMENT: select `field1`, `field2` from `table` where 1=0 2024-03-29 17:32:08 UTC:10.245.48.249(51148):appian@postgres:[3319]:LOG: execute S_1: ROLLBACK
I'm running the same statement on my client in Postgres and i got the same error of course.
What I found out is that backtick is causing problems as it as a different use on o Postgres than MariaDb.
Do you know any workaround to bypass this problem?
Something to skip/bypass the backtick and use something different?
Thanks a lot
Alessandro
Discussion posts and replies are publicly visible
Do you need to do this frequently? If not, did you consider to just import that file directly using a DB tool?
yes, very frequently. It's a core process.
CSV files can be tricky, because there is not clearly defined format.
Are you sure that the DB error message relates to the import? It's a select statement.
Do you have any influence on how that file is created?
Is there any option to send that data to Appian avoiding that CSV file?
Can you post a row of that CSV file?
Did you try the various separator options in the smart service?
yeah, I Know.
Although the same file, with the same configuration in the smart service except for the data source, with the same tables configurations (everything lowercase), works fine with Appian MariaDb.
Separator is ; but as it works fine on MariaDb i don't think it's the cause of the issue.
Unfortunately no, I have no governance on those files and they cannot be avoided as part of the process.
I can't post those data, but the format is clearly string;string;... Even other reader ov CSV files like the one in DBeaver can read fine the file and query it as it was a table.
Reading on internet, postgres does not use backtiks to force column names to keep their case type, instead ". Backticks is for MariaDB and mySql.
I'm afraid it's a bug of the plugin
Why dont't you use Read Excel Paged function, instead of import csv to database, and then call an stored procedure to insert the data you just read.
IN that way , you'll avoid the bug od the smart service.
Unfortunately, it works only with excel spreadsheet as it needs to read a valis Office Open XML. My files are CSV (basically text with a separator)
I just try it.
IN that case, you could try converting your csv to json, or xml with this plugin..
community.appian.com/.../csv-conversion
Hi Alessandro M. even we have tried some scenarios importing csv to PostgreSQL DB. We got to know that those plugins works for only mysql databases
Yeah, that's the issue with plugins. They only gain new features if enough people ask for it. Or you pick up the code and add that feature yourself.
Just to add my vote: we have tried to use this smart service to import CSV data to an AuroraDB with Postgres engine, and we receive almost the same error; e.g.:
ERROR ImportCsvToDatabase8 Error executing SQL org.postgresql.util.PSQLException: ERROR: syntax error at or near "`" Position: 15 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) at com.appiancorp.ps.exceltools.util.DatabaseUtils.getColumnNames(DatabaseUtils.java:115)
We can't see the SQL that the service is sending to the DB, but the "getColumnNames" method name suggests that the service is trying to get normalized column names by issuing the same SELECT reported in this post, and adding backticks around the column names.
If we don't find some workaround we won't be able to use the Smart Service, and we will have to manually implement reading the file... it is a pity; for if the Service would just have the option to not quote the columns, it would work with more databases.
Thanks and best regards
A last note about this: it has turned out that in plugin version 2.6.3, released on April 27 2024, support for Postgres was added. And it works, or at least it does in our case.