Data Load Editor: QVD to TEXT file generates "unexpected error" token

[jrn_001]

Hello,

I inherited code that reads QVD data into a text string separated by a "|" symbol within a text file on a cloud sever. Then, an $(INCLUDE= .txt) file is written, after which SQL syntax is used to INSERT OVERWRITE the data into a DataBricks table store. This is the code (variable names etc. redacted):

//First the fields are read in from the QVD

[TABLE]:
LOAD
A,
B
C,
D,
E,
TABLE.Edited_By,
TABLE.Edited_At
FROM [lib://location/file.qvd]
(qvd);

//Then the QVD is transmuted into a text string, and then SQL-inserted into a DataBricks table store.

unqualify*;
NoConcatenate
table_qvd:
LOAD *
RESIDENT TABLE;

unqualify*;
NoConcatenate
table_qvd_str:
LOAD
CONCAT(
"A" & '|' &
"B" & '|' &
"C" & '|' &
"D" & '|' &
"E" & '|' &
TABLE.Edited_By" & '|' &
TABLE.Edited_At" &
'%') AS [SET DATA =] RESIDENT table_qvd;

STORE [SET DATA =] FROM table_qvd_str INTO "lib://location/table_qvd.txt" (TXT);

$(INCLUDE=[lib://location/table_qvd.txt]);

SQL INSERT OVERWRITE workspace.databricks_table VALUES ('$(DATA)')!EXECUTE_NON_SELECT_QUERY;

DROP TABLES table_qvd, table_qvd_str;

...generating this error at the point at which "Edited_At" and "Edited_By" are separated

..and I have isolated the line of code that generates this error as the bold $(INCLUDE=) line above:

The following error occurred:

Unexpected token: '|1', expected one of: 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', 'and', ...

The error occurred here:

UserId=0101010102>>>>>>|1<<<<<</9/2024 5:12:21 PM%

I don't understand why this happens for some of the text files, but not for all of them, as I repetitively do this for a number of VizLib WriteBack table-generated QVDs. There doesn't seem to be any hidden symbols or strange-formatted hidden text in the field where the unexpected token arises. I change the token from the pipe symbol to an asterix, to a back-slash, to a comma- nothing works: the "unexpected token" error keeps getting thrown.

Any help would be greatly appreciated. Thank you!

[jrn_001]

Thank you for this information.

[jrn_001 · Accepted solution]

Thank you again for all your help.  A REPLACE (FIELD, ';' , ',') as FIELD has solved this problem by replacing the problematic semicolon in the automatic audit field with a comma. All good  now 🙂