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

[NuckNuck]

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.

1. 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.
   
   
2. 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.

[Dan_Bracuk]

Why do you think the leading zero is not there?

[NuckNuck]

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. 

[Dan_Bracuk]

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.

[NuckNuck]

Same thing happens in Notepad.  This file contains a list of registrants and their contact information (address, city, state, zip, etc...)  Basically, I have a list of workshops that members have registered for and our trainers want to have a list of these registrants ready to be viewed on the fly (in Excel).  It worked well when cffile was being used, but it became increasingly slow as the number of registrants increased.  This is why I opted for the StringBuffer approach.  The only problem is the leading zero being removed.  I guess I could just prepend a zero, but there are cases where zip codes may not begin with a zero.  When I used cffile I just wrapped the zip in single quotes and that worked (although the quotes appeared in the CSV).  I'm looking for a similar approach with what I'm using now.

Thanks

[-__cfSearching__-]

NuckNuck wrote:

It worked well when cffile was being used, but it became increasingly slow as the number of registrants increased.

Where you writing the data all at once <cffile action="write" ...> or appending data one line at a time <cffile action="append" ..>?

[-__cfSearching__-]

NuckNuck wrote:

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. 

StringBuffer just does a string concatenation. It should not modify the data. So that suggests the problem is somewhere else: 1) in the query data, 2) the code used to add it to the stringBuffer or as Dan suggested 3) the program used to view the output.  So you should perform some basic debugging:

Dump the data in the query. Does the zip code column contain leading zeros? It might not if the zip codes were stored as numbers rather than strings. Dump the buffer right after the query loop. #csvExport.toString()#  Does the string contain zeroes?

[Adam Cameron.]

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__-]

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?

[Adam Cameron.]

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

[NuckNuck]

A Cameron wrote:

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

The zip code data that is being returned does contain the leading zero. The problem is with MS Excel dropping it.  What's interesting is that if a 9-digit zip code is present (ex. 01234-5678), the leading zero is preserved.  (BTW, the data type for the zip is varchar).  I have seen other threads where they use the 'mso-number-format' style (ex. <td style="mso-number-format:\@">, however, I am not using any HTML tables in the code.  Instead, I am using a StringBuffer object and appending data to that.

I haven't used the cfsavecontent option fully - although we were getting a blank screen in Excel when we tried using it.  The StringBuffer seemed a better choice since the speed increases were noticeable.  The spreadsheets are now available instantly (pretty much) and that has been the reason why we implemented that solution instead.

Thanks for all your feedback.

[Dan_Bracuk]

If the problem is Excel dropping the leading zeros, it comes down to what the purpose of the csv files are.  If they are strictly for viewing, prepend a single quote.  If they eventually have to be uploaded somewhere else, then it gets more complicated.

[ilssac]

It is not that MSExcel is dropping the zero, it is just not displaying it because it is assuming the zip code is numeric data not text data.  If one where to manually change the cell type from number to text the zero would then be displayed.

But since I presume you would rather Excel got it right when it opens the file, suround the value in single quotes.  This is what tells Excel to treat these values as text not numbers.  The reson it does this alread for the 9 digit zip codes is that excel detects the non-digit dash character and thus treats the entire value as text.

[NuckNuck]

ianskinner wrote:

It is not that MSExcel is dropping the zero, it is just not displaying it because it is assuming the zip code is numeric data not text data.  If one where to manually change the cell type from number to text the zero would then be displayed.

But since I presume you would rather Excel got it right when it opens the file, suround the value in single quotes.  This is what tells Excel to treat these values as text not numbers.  The reson it does this alread for the 9 digit zip codes is that excel detects the non-digit dash character and thus treats the entire value as text.

I ended up doing just that, only Excel will display the single quotes around the zip code. Now if those quotes didn't appear....

Thanks everyone.

[ilssac]

I'm not sure what you are actually trying to accomplish.

But if you open excel, and have it import the csv file you can configure it to not display the quotes.

But if you double click the file to open excel, it does not do this conversion.

[BKBK]

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

[NuckNuck]

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.

[Emily31892526qzy9]

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.