Input Tool, special characters replaced for CLOB column in DB
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I've a Clob column in a database table which has special characters e.g "(double quotes) and '(single quotes)
When I input them in Alteryx via Input tool or IN-DB tool these get replace by blanks or?(question mark character)
----------Sample text----------------
Input in DB - Hell’o “How”are you’
Input in Alteryx - Hell?o How?are you?
---------------------------------------
I've already tried checking "Force sql w char support", but it results in an error m_OCILobRead Error: ORA-24806: LOB form mismatch.
I've also tried changing the encoding from Localization to Unicode UTF-8, but this also doesn't solve the error.
Is there any way I can retain special characters as it is ?
Solved! Go to Solution.
- Labels:
- AMP Engine
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
what database and driver are you using? my suggestion would be to check the driver config in odbc 64.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
If this is the only issue you're facing, you can try using the Multi-Row or Multi-Field Formula tool in Alteryx to replace the '?' with the correct characters, such as single quotes (' " ') or double quotes (" " ").
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
More information on the driver/db can help. This is most likely a conflict between something in the driver and your database. I see this with certain drivers where the driver is set for one encoding and the DB has another.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Driver : Oracle Insta Client, connecting to db via oci, version 12.1.
DB : Oracle Database 19c
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
replacing is not possible, as there are multiple characters getting replaced by some different characters with a possibility of new characters coming in future.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I've also tried using Python tool to connect to db for fetching data :
from ayx import Alteryx
import cx_Oracle
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine('oracle+cx_oracle://username:password@hostname:portnumber/servicename')
con = engine.connect()
outpt = con.execute("SELECT 1 FROM DUAL")
df = pd.DataFrame(outpt.fetchall())
df.columns = outpt.keys()
print(df.head())
con.close()
But this results into "ORA-12505: TNS:listener does not currently know of SID given in connect descriptor"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Problem indeed was with the drivers.
OCI couldn't understand special chars, but when I shifted the connection to ODBC and checked Force sql w char support, Input tool understands the characters now.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you mark my suggestion to check the driver as a solution?
