#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    20
    Rep Power
    0

    Exporting data with cfx_csvExport


    Hello,

    I'm running into an issue trying to export double byte data into .csv format using Excel. The database currently stores Chinese characters for a name. When I run a report and try to export that data in .csv format using the custom tag cfx_csvExport the data shows as ?????

    I understand that Excel doesn't handle double byte characters very well.

    This seems to be an issue on the side of Excel right? Is there a way I can export double byte characters using the custom tag successfully?
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,286
    Rep Power
    968
    I've never used that custom tag so I have no idea. I tried a Google Search for the tag but nothing comes up, so my guess is that it's really old. Newer versions of CF have built-in Excel generation features, using (I believe) Apache POI.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    20
    Rep Power
    0
    Unfortunately I'm on ColdFusion Mx7 and there won't be plans to upgrade.

    I pulled this code and I'm trying to get it to work to generate an excel file.

    Code:
    <cfif url.format eq "excel">      
    <cfheader name="Content-Disposition" value="attachment; filename=test.xls">      
    <cfcontent type="application/vnd.ms-excel; charset=utf-8" reset="yes">     
     <html> 	 	
    <head> 		
    <title>Course Dump</title> 	
    </head> 	 	
    <body> 		
    <table> 			
    <tr> 				<th>VendorCourseID</th> 				<th>CourseName</th> 				<th>IsLive</th> 			
    </tr> 			
    <cfoutput query="courses"> 				<tr> 					<td>#vendorCourseID#</td> 					<td>#coursename#</td> 					<td>#islive#</td> 				
    </tr> 			
    </cfoutput> 		
    </table> 	
    </body> 
    </html>     
        
     <cfabort /> </cfif>
    This loads the data in Excel, however when Excel load up the following message appears before opening the spreadsheet.

    'The file you are trying to open, 'test-1.xls' is in a different format then specified by the file extension. Verify the file is not corrupted ans is from a trusted source before opening the file.'

    I go ahead and open the file and the double byte characters are still messed up.

    Showing as:
    デル オンライントレーニングにようこそ(Japan test)
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,286
    Rep Power
    968
    If you can view the content without the Excel conversion and you can see the UTF-8 characters, then the problem has to be within the CFX tag that's generating the file.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    This loads the data in Excel, however when Excel load up the following message appears before opening the spreadsheet.
    That is due to Excel's Extension Hardening security feature . Whenever file content does not match the extension, the user is warned. In your case it is because you are actually returning html, but cfcontent claims it is an .xls file. You cannot disable the warning from the server. The only way to avoid it is make the file extension match the content.

    (Faux-Excel exports used to be very handy, but the new security restrictions make them a less than ideal option these days)

    I go ahead and open the file and the double byte characters are still messed up.
    If you are using HTML, you must also include a <meta> tag that specifies charset=UTF-8. See this thread for details .
    Last edited by cfSearching; April 13th, 2013 at 08:04 PM.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    Unfortunately I'm on ColdFusion Mx7 and there won't be plans to upgrade.
    If you need generate a true binary Excel file, take a look at the POIUtility.cfc. I believe the early versions supported MX7.

    That said, you should not need any extra tags or components to generate a CSV file. It is essentially just a text file, you can generate yourself - on any version of CF. Take a look at cflib.org. It has several functions for converting queries to CSV.

IMN logo majestic logo threadwatch logo seochat tools logo