HI All,
I have a requirement to process a CSV file to my database. i have used a smart service(Export CSV to Database) from Excel Tools plugin. But some of the special characters are not read properly.For example:
The data i have received in CSV has : ~@ö<é;ó¿üäÖÇ*àÄ=É!"ѧæñ á½èçòíê°
after consuming the file i have got this in my database : ~@�<�;�����*��=�!"ѧ��������
Can anyone suggest if we have any alternative options for this please.
Thanks,Sai Kiran Pannala.
Discussion posts and replies are publicly visible
So, you export data from Appian into a CSV and import it, where? Appian uses UTF-8 to export the data. Is the other DB set up to store unicode? Does the import read the data in unicode?
HI Stefan,
Apologies if my statements were unclear.I am not exporting data from APPIAN.Basically i am getting data in a CSV file through SFTP integration from an external source. We need to consume that file into pur APPIAN database.While consuming the file i am using a smart service(Export CSV to Database) from Excel Tools plugin. And this smart service or plugin is unable to read few special characters as mentioned above.
I think the plugin expects the data to be encoded in UTF-8. Please check that.
It worked when i converted the CSV file with UTF-8 encoding and then run it. Thanks alot for the quick help.
Hi Stefan,
Good Day!!
After converting the file to UTF-8 the issue i mentioned got fixed but there is one another issue i am seeing now.If i use "" in any of the column then the node errors out and throws below error.java.lang.IllegalStateException: IOException reading next record: java.io.IOException: (line 3) invalid char between encapsulated token and delimiter
Could you please suggest me on this.
Hi sai,I'm facing similar issue. how did you resolve this or any suggestions to work on Stefan Helzle
Similar issue can mean a lot. Let's take the guesswork out. Can you share some details?
I'm uploading a CSV using import csv to database smart service, but the thing is some rows of the data containing double quotes in the values. for example: employee1;"1";"name ""appian"""; so, I'm getting an error like java.lang.IllegalStateException: IOException reading next record: java.io.IOException: (line 175) invalid char between encapsulated token and delimiter. Is there any way to fix this or some workarounds?
That is a very common problem with CSV files. There is no defined standard.
If your file is on the smaller side, you could try to use the "text file reader" plugin to read and parse that file manually.
If you have a larger file, you could try to use another plugin to first convert that CSV into JSON and back to CSV. This way it could be more uniform.
Thank you Stefan
a!localVariables( local!fileData: readtextfromfile( txtFile: ri!csvDocument, preserveLineBreaks: true ), local!fileLines: split(local!fileData, char(10)), a!forEach( items: local!fileLines, expression: if( fv!isFirst, {}, a!localVariables( local!splitItem: split(fv!item, ","), /*Create Records or CDT */ ) ) ) )