How to resolve if the Special characters are changed when loading the data from excel file to SQL table

[Former Member]

Hi Experts,

I have a scenario as below.

My source is excel file and which is having customer master data. In that Name1 filed is having some special characters (symbols which are not part of our keyboard, like company registered symbol, R in the circle, pound symbols) .

The above data when I am extracting from the excel file and loading into my Target SQL server table, the special characters (symbols) are getting changed.

Only the special charaters are getting changed and remaining characters are displaying as expected.

I tried to resolve this by saving the excel file encoding as UTF-8 and creating the Target datastore code page and server codepage using UTF-8, but no luck.

Please suggest me how to resolve this issue.

Regards,

Vijay Bhaskar

[akhileshkiran · Accepted solution]

Hi

First make your source Code-page to UTF-16.

and in target table double click and scroll down to bottom

in miscellaneous select Yes to use VARCHAR as NVARCHAR in supported Database.

Output:

Regards,

Akhileshkiran.

[Former Member · Accepted solution]

Hi Akhileshkiran,

Great, It's working fine as you suggested.

Thanks for your quick help on this.

Regards,

Vijay

[akhileshkiran · Accepted solution]

Hi ,

Please close the Thread if you feel helpful.

Regards,

Akhileshkiran.

[Former Member · Accepted solution]

Hi Akhileshkiran,

I have got another problem similar to that as below. Please suggest the solution.

I have both source and target tables in the same database and using teh same datastore.

When I am trying to load the source table data into target table (both are SQL server) using a Query transform with some conditions on a column then the Special characters (symbols) are getting changed.

If we remove the conditions in the Query transform then the Special symbols are loading as expected.

Why these special symbols are getting changed when we are using conditions  in Query transform?

Please suggest the solution. Many thanks in advance.

Regards,

Vijay

[akhileshkiran · Accepted solution]

Hi

Can you share me a screenshot or please let me know what condition you are using in the query.

Please mark the above thread as answered so that it can be helpful to every one. Who are facing with the same problem .

Regards,

Akhileshkiran.

[former_member200473 · Accepted solution]

Hi Vijay,

Kindly try by changing code page for SQL server also as UTF-16

Shiva Sahu

[Former Member · Accepted solution]

Target database should have Default UTF 16  as the code page. Please try this. Extended ASCII characters need to be supported by the database to store the results. Otherwise the conditions or any function you are using in the database does it contains any Ascii checking rules. Please Verify that also.

Thanks and Regards

Joy

[Former Member · Accepted solution]

Hi Joy,

My target datastore is having UTF-8 codepage and the server code page is also UTF-8. I am not able to find the UTF-16 code page in Datastore settings.

If I am using any built-in functions like substring, matchpattern in the Query transform then the special characters are getting chnaged otherwise its working fine as expected.

I am surprising why these special symbols are getting changed only when we use the built-in functions in Query transform?

Please suggest...

Regards,

Vijay

[Former Member · Accepted solution]

Hi Shiva Sahu,

Please suggest me how to change the code page for SQL server  also as UTF-16 ?

Regards,

Vijay

[akhileshkiran · Accepted solution]

Hi

Go to Datastore Tab. Right click on your Datastore. Select edit click on Advance Button.

Scroll Down you can see Code Page.

Try using code page as Unicode it may work i think so. If possible i will try and let you know

Regards,

Akhileshkiran.

[Former Member · Accepted solution]

Hi Akhileshkiran,

I have changed the datastore codepage to Unicode and UTF-8 and tried to load the data, but the problem still persist.

If I am NOT using any substr or matchpattern or ltrim functions in Query transform then the special symbols are loading into the Target table as expected and no issues.

The problem is coming only when i am using the built-in functions in Query transform.

Don't know why the problem is coming only with built-in functions in BODS.

Regards,

Vijay

[akhileshkiran · Accepted solution]

Can you share me a  Screeshort   what is the problem you are facing.

Regards,

Akhileshkiran.