Special Character in Generated XLSX Report

Hi
 
When i generated the report in Excel it gives me some junk values in EXCEL Output. but in PDF we are getting correct value .
 
Please let me know what could be the reason and solution to fix

Find more posts tagged with

Comments

jfranken

<div>The issue is that the ascii character returned from the database is interpreted by Excel as the symbol you are seeing. The Designer is simply passing the data value to Excel. Try viewing it in Notepad++ to see what the character is supposed to be. If it doesn't display correctly in Notepad++, try copying the symbol and pasting it into asciivalue.com. The way to fix it is to replace the character wherever it occurs in the data with a different ascii value that displays the correct symbol in Excel, i.e. use one of the other ascii characters for a single quote if that's the symbol that is supposed to display.</div>
<div> </div>
<div>I hope this helps,</div>
<div>Jeff </div>

vijayshankar245

<blockquote class="ipsBlockquote" data-author="jfranken" data-cid="142947" data-time="1458594202">
<div>
 
<div>The issue is that the ascii character returned from the database is interpreted by Excel as the symbol you are seeing. The Designer is simply passing the data value to Excel. Try viewing it in Notepad++ to see what the character is supposed to be. If it doesn't display correctly in Notepad++, try copying the symbol and pasting it into asciivalue.com. The way to fix it is to replace the character wherever it occurs in the data with a different ascii value that displays the correct symbol in Excel, i.e. use one of the other ascii characters for a single quote if that's the symbol that is supposed to display.</div>
<div> </div>
<div>I hope this helps,</div>
<div>Jeff </div>
 
</div>
</blockquote>
Thanks Jeff
We are pulling the values from the database so it is hard to change it to ASCII when bulk data coming from DB.
Is there any other option to avoid this issue in excel?
Please do help

Clement Wong

What database and version are you using? What JDBC driver and version are you using?
 
Do you have a sample design that replicates the problem?
 
Are you using OS BIRT, or commercial BIRT? Also, which version?

vijayshankar245

<blockquote class="ipsBlockquote" data-author="Clement Wong" data-cid="143059" data-time="1459286492">
<div>
What database and version are you using? What JDBC driver and version are you using?
 
Do you have a sample design that replicates the problem?
 
Are you using OS BIRT, or commercial BIRT? Also, which version?
</div>
</blockquote>
Hi Clement
 
We are using teradata database and the jdbc driver is com.teradata.jdbc.TeraDriver(14.10).
 
It is Commercial BIRT and the version is 4.4.0.
 
I can't send the design but the problem is the data present in table's datatype is NAME VARCHAR(100) CHARACTER SET LATIN when the same is retrieved in the report and generated the special characters present in the field become junk values .
It occurs with all output formats.
 
Attached the output generated in web viewer. Kindly help

Migrateduser

I too had a similar problem, the Varchar datatype string from database is misinterpreted by my BIRT designer.
After I created the dataset, when I previewed my results, all the special characters present in the string field shows as junk in the preview results pane in the dataset.
 
Is there any settings where I need to change the input string field from database? Please help

Clement Wong

@vijayshankar245
 
Since you are using commercial BIRT, but are unable to attach your design or provide more information for us to replicate the issue, I would suggest that you contact your local Support Center (if your company is current on maintenance). A support case can be opened and a support engineer would be assist you directly. I can foresee that a remote screen share troubleshooting session would be needed to help diagnose the issue.
 
This information would be needed for either the community, or our Support team to assist you further:
1. Sample data string from the database
2. What does the Preview from in the Edit Data Set dialog show for that row?
3. What character set is set at the database level?
4. What character set is set at the table level? You answered LATIN.
5. What character set is set at the JDBC level?
6. What character set is set at the report level?
7. Version of Teradata server?
8. Your report design
9. If possible, a design that replicates the issue that can be run in outside of your environment.

Clement Wong

@Sugantha
 
If previewing the data shows the junk characters, it might be the JDBC driver and a setting in the driver URL that's needed, but you'll need to provide more information...
 
1. Are you using commercial or OS BIRT?
2. What version?
3. What is your database? And version?
4. Is there any character set encoding set at the database level?
5. What about at the table level?
6. What database driver are you using?
7. What's your JDBC URL look like (you can take out your server name and instance for privacy)?

Migrateduser

<blockquote class="ipsBlockquote" data-author="Clement Wong" data-cid="143073" data-time="1459356069">
<div>
@Sugantha
 
If previewing the data shows the junk characters, it might be the JDBC driver and a setting in the driver URL that's needed, but you'll need to provide more information...
 
1. Are you using commercial or OS BIRT?
2. What version?
3. What is your database? And version?
4. Is there any character set encoding set at the database level?
5. What about at the table level?
6. What database driver are you using?
7. What's your JDBC URL look like (you can take out your server name and instance for privacy)?
</div>
</blockquote>
Hi Clement
1. Im using BIRT Commercial
2. 4.4.0 version
3. Teradata 14.00.0727 
4. how do i check character set encoding at database level?
5.Character set Latin set at the table level
6.com.teradata.jdbc.TeraDriver(14.10).
7.jdbc:teradata://#servername#

vijayshankar245

<blockquote class="ipsBlockquote" data-author="Clement Wong" data-cid="143072" data-time="1459355771">
<div>
@vijayshankar245
 
Since you are using commercial BIRT, but are unable to attach your design or provide more information for us to replicate the issue, I would suggest that you contact your local Support Center (if your company is current on maintenance). A support case can be opened and a support engineer would be assist you directly. I can foresee that a remote screen share troubleshooting session would be needed to help diagnose the issue.
 
This information would be needed for either the community, or our Support team to assist you further:
1. Sample data string from the database
2. What does the Preview from in the Edit Data Set dialog show for that row?
3. What character set is set at the database level?
4. What character set is set at the table level? You answered LATIN.
5. What character set is set at the JDBC level?
6. What character set is set at the report level?
7. Version of Teradata server?
8. Your report design
9. If possible, a design that replicates the issue that can be run in outside of your environment.
</div>
</blockquote>
Hi Clement
 
I tried using Charset='UTF8' in the teradata driver URL and the junk vlaues are gone now.
 
E.g. dbc:teradata://DatabaseServerName/CharSet='UTF8'
 
but in some of the rows instead of - im getting space.. Is there any other charset can i use to avoid that as well?

Clement Wong

@Sugantha @vijayshankar245
 
I have installed Teradata Express 14.0 for VMware (4 GB) and Teradata JDBC 14.10.00.42. Teradata Express ships with various sample databases. I am able to see the data in the Preview and run/view various simple reports from the Retail and Financial database in BIRT Designer Professional iHub 3.1.
 
In order for me to assist you, I would need to see the issue. I don't have your data. If you can export your table definition, and a sample row of data that would be helpful for me to replicate the issue. You can PM me with the info if you don't want to post it here.
 
Or if you can point me to one of the sample databases/tables that replicates the problem that would be even better.
 
 
@Sugantha Since you are using commercial BIRT, and if your company is current on maintenance, you might want to contact your local Support Center for better response time than from the forums.
 
@vijayshankar245 I don't know what versions you are using so please do provide the information previously requested and also any additional information that would help me replicate the issue.