Import CSV to HSQLDB skip duplicated (commad "NOT IN" not working)

jcd420 · March 7, 2023, 1:56pm

Im trying to import a CSV file it all works if I do not include

WHERE ""TBCSV"".""BLINGSO"" NOT IN ""xdata"".""BLINGSO""

i want if xdata.BLINGSO exits do not import that record from TBCSV database, what did I do wrong? Thank so much for your time.

oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
sCopy = "INSERT INTO ""xdata"" (SELECT ""TBCSV"".""DATE"", ""TBCSV"".""DESCR"", ""TBCSV"".""SKU"", replace(""TBCSV"".""QTY"",',','.'), ""TBCSV"".""VALORTT"", ""TBCSV"".""MRK"", ""TBCSV"".""MRKSO"", ""TBCSV"".""BLINGSO"", ""TBCSV"".""FRETE"", ""TBCSV"".""NF"", ""TBCSV"".""NAME"",False,null FROM ""TBCSV"", ""xdata"" WHERE ""TBCSV"".""BLINGSO"" NOT IN ""xdata"".""BLINGSO"")"
oStatement.executeUpdate( sCopy )

RobertG · March 7, 2023, 2:34pm

Set to:

WHERE ""TBCSV"".""BLINGSO"" NOT IN (SELECT ""BLINGSO"" FROM ""xdata"")

So the full code for the macro will be:

sCopy = "INSERT INTO ""xdata"" (SELECT ""DATE"", ""DESCR"", ""SKU"", replace(""QTY"",',','.'), ""VALORTT"", ""MRK"", ""MRKSO"", ""BLINGSO"", ""FRETE"", ""NF"", ""NAME"",False,null FROM ""TBCSV"" WHERE ""BLINGSO"" NOT IN (SELECT ""BLINGSO"" FROM ""xdata""))

jcd420 · March 7, 2023, 2:57pm

thank you for the quick reply but I still get an error:
“BASIC runtime error.
type: com.sun.star.sdbc.SQLExeption
Message: missing) instatement [SELECT ““DATE””, ““DESCR””, ““SKU””, replace(”“QTY”",’,’,’.’), ““VALORTT””, ““MRK””, ““MRKSO””, ““BLINGSO””, ““FRETE””, ““NF””, ““NAME””,False,null FROM ““TBCSV”” WHERE ““BLINGSO”” NOT IN (SELECT ““BLINGSO”” FROM ““xdata””)]
object.ccx:173

I also tryed

sCopy = "INSERT INTO ""xdata"" (SELECT ""TBCSV"".""DATE"", ""TBCSV"".""DESCR"", ""TBCSV"".""SKU"", replace(""TBCSV"".""QTY"",',00',''), ""TBCSV"".""VALORTT"", ""TBCSV"".""MRK"", ""TBCSV"".""MRKSO"", ""TBCSV"".""BLINGSO"", ""TBCSV"".""FRETE"", ""TBCSV"".""NF"", ""TBCSV"".""NAME"",False,null FROM ""TBCSV""  WHERE ""BLINGSO"" NOT IN (SELECT ""BLINGSO"" FROM ""xdata"")"

RobertG · March 7, 2023, 3:40pm

There is missing a close bracket: ). Have corrected in my first post.