Hi Everyone,I am in the middle of creating a CSV file export. I'm currently using the Java StringBuffer class to append my query data and then output the data using cfcontent. Anyway, I noticed that the Zip Code field has removed the first leading zero.How can I modify my code to allow for the entire zip code to be displayed. When I originally used cffile was able to wrap a variable in quotes and that preserved it. However, I am not using cffile since it has slowed the export considerably.Also one of the address fields contained a comma that caused a blank column to be added - shifting some of my content. I replaced the comma with a blank and that removed the comma but the added column still exists. Any idea on how to resolve that?Here is a sample of the code<cfscript> csvExport = createObject("java", "java.lang.StringBuffer"); csvExport.append("First Name,Last Name,Address1,Address2,City,State,Zip Code,Local,Workshop" & Chr(13) & Chr(10)); </cfscript><cfoutput query="oExpRegistrants"> <cfscript> Replace(reg_address2, ",",""); csvExport.append(firstname & ","); csvExport.append(lastname & ","); csvExport.append(reg_address1 & ","); csvExport.append(reg_address2 & ","); csvExport.append(reg_city & ","); csvExport.append(reg_state & ","); csvExport.append(reg_zip & ","); csvExport.append(reg_local & ","); csvExport.append(thisExpWorkshop); csvExport.append(Chr(13) & Chr(10)); </cfscript></cfoutput>Thanks in advance for your help.

Participating Frequently

Question

Leading Zero in Zip Code Removed - CSV Export with Java StringBuffer

Forum|Forum|16 years ago
September 4, 2009
4 replies
8305 views

Hi Everyone,

I am in the middle of creating a CSV file export. I'm currently using the Java StringBuffer class to append my query data and then output the data using cfcontent. Anyway, I noticed that the Zip Code field has removed the first leading zero.

How can I modify my code to allow for the entire zip code to be displayed. When I originally used cffile was able to wrap a variable in quotes and that preserved it. However, I am not using cffile since it has slowed the export considerably.
Also one of the address fields contained a comma that caused a blank column to be added - shifting some of my content. I replaced the comma with a blank and that removed the comma but the added column still exists. Any idea on how to resolve that?

Here is a sample of the code

<cfscript>
csvExport = createObject("java", "java.lang.StringBuffer");
csvExport.append("First Name,Last Name,Address1,Address2,City,State,Zip Code,Local,Workshop" & Chr(13) & Chr(10));
</cfscript>

<cfoutput query="oExpRegistrants">
<cfscript>
Replace(reg_address2, ",","");

        csvExport.append(firstname & ",");
        csvExport.append(lastname & ",");
        csvExport.append(reg_address1 & ",");
        csvExport.append(reg_address2 & ",");
        csvExport.append(reg_city & ",");
        csvExport.append(reg_state & ",");
        csvExport.append(reg_zip & ",");
        csvExport.append(reg_local & ",");
        csvExport.append(thisExpWorkshop);
        csvExport.append(Chr(13) & Chr(10));
    </cfscript>
</cfoutput>

Thanks in advance for your help.

Advanced techniques

This topic has been closed for replies.

E

Emily31892526qzy9

Participant

I had this same exact issue.

In my code, I created a dynamic HTML table which is being populated within a loop in ColdFusion. The data which is being looped over is coming from a SQL query. This is what populates my Excel spreadsheet on-click of the submit button on the UI.

Right on the <td> of the zip code, I did the following:

<td style="mso-number-format:\@">#variable#</td>

The mso-number-format is a Microsoft Office specific style which forces Excel to treat the variable as Text. No other type of coersion in the code will force MSO to view this data as text. It is the only way MSO will keep the leading zero.

BKBK

Community Expert

reg_address1 = Replace(reg_address1, "," , "");
reg_address2 = Replace(reg_address2, "," , "");

N

NuckNuckAuthor

Participating Frequently

reg_address1 = Replace(reg_address1, "," , "");
reg_address2 = Replace(reg_address2, "," , "");

Tried that and it removed the comma, but the value was still shifted to the next column. What I opted to do instead was replace the delimiter from a comma to a tab (chr(9)) and that seemed to corrected it.

A

Adam Cameron.

Inspiring

1) You can use numberFormat() to apply a display mask to the zip code.

2) If a data value has a comma embedded in it (or new line characters), then you should surround the value in double quotes. Also look out for values that have double-quotes in them; they'll need to be escaped too.

3) I'd consider using <cfsavecontent> to build this string, rather than all the StringBuffer carry on. It looks quite (unnecessarily) cluttered to me.

--

Adam

-

-__cfSearching__-

Inspiring

A Cameron wrote:
1) You can use numberFormat() to apply a display mask to the zip code.

The OP suggested the data already contained leading zeroes. So that should not be necessary. But agreed, it would work.

A Cameron wrote:

3) I'd consider using <cfsavecontent> to build this string, rather than all the StringBuffer carry on. It looks quite (unnecessarily) cluttered to me.

What has your experience been with cfsavecontent and creating large strings?

A

Adam Cameron.

Inspiring

A Cameron wrote:
1) You can use numberFormat() to apply a display mask to the zip code.
The OP suggested the data already contained leading zeroes. So that should not be necessary. But agreed, it would work.

As CF is typless, there could be some intermediary operation in which the value is mistaken for a numeric, and - of course - leading zeroes are not meaningful with numerics, so will be dropped.

Forcing the fomatting @ the last moment CF is using the value should ensure the formatting is preserved.

A Cameron wrote:

3) I'd consider using <cfsavecontent> to build this string, rather than all the StringBuffer carry on. It looks quite (unnecessarily) cluttered to me.
What has your experience been with cfsavecontent and creating large strings?

Depends on what you mean by large. The reason to use a StringBuffer is because of a slight performance & memory hit when doing string concatenation, because StringBuffers are optimised for this sort of operation (Strings are not).

However if one has a <cfsavecontent> around the whole operation, it's just the one string, so the issue is moot.

Either way, there's going to be a large string (~buffer) in memory, so there's a slight concern there.

If the string was going to be enormous, I'd be appending to a file, rather than building it in memory, but apparently that won't work here (which puzzles me). I've never specifically had problems creating strings of any size (be it with string concatenation, or <cfsavecontent>, so I've never needed to investigate the ins and outs of this.

To be honest, if the data is coming from the DB, and it's a lot of data, I'd probably try to perform this transformation on the DB, rather than getting CF to do it.

--

Adam

D

Dan_Bracuk

Inspiring

Why do you think the leading zero is not there?

N

NuckNuckAuthor

Participating Frequently

When I view the column of zip codes in the exported CSV file, the leading zero for each is removed. So 01111, shows up as 1111.

D

Dan_Bracuk

Inspiring

If you view it with notepad instead of excel, are the leading zeros there? Make sure you re-create the file and don't open it with excel, just notepad.

By the way, what is the intended use of the resulting file? The answer to this affects the best way to deal with the leading zero situation.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded