Excel Export Problem

[jlkane]

One of my Form Tools installations collects grant applications from teachers. The form asks them to specify the grade levels served, so a natural response is in the form of "3-4" or "9-12". Responses like these, when exported to Excel are being automagically treated as date fields, and so are seen in Excel as "Mar-3" or "Sep-12".

I thought, "Oh, that's just Excel being too smart its own good. I'll just reformat that column." But no, reformatting the column to Text or General produces the appropriate date code instead of the data expected, which leads me to believe that the conversion is actually happening in the export code.

Can anyone see a solution that I can implement as an administrator here? ...or does Ben need to provide a code tweak in the Export module?

Jim

P.S.
I wondered if the problem might be specific to Excel on the Mac, so I tried to open the Submissions file in Google Doc's spreadsheet ...and it simply wouldn't open at all. Have others seen this?

P.P.S.
This and another export-to-Excel problem both lead me to believe that exporting to a tab-delimited text file might be a better solution in some ways than the current (very clever) export to .xls format. Reactions?

[Ben]

Hey Jim,

I may have to look at your other Excel problem tomorrow.

But on this one, it's definitely not Form Tools or the Export Manager module that's doing the conversion. I've encountered this before and I don't think it can be resolved from within Excel. Excel, once it's decide the column is a date column, there's no way to convince it otherwise.

Instead, you can tackle it via the Export Manager export type - you'll need to edit that Smarty markup to either reformat the field value right there, or maybe add a space at the end (or start) of the <td> cell. The latter is easier to do, so maybe give that a go first. That may be enough to convince Excel that it's just a plain string field.

Let me know how that goes. If still no luck, let me know.

- Ben

[jlkane]

Excel, once it's decided the column is a date column, there's no way to convince it otherwise.

Yes, Excel is sometimes too smart for its own good!

Instead, you can tackle it via the Export Manager export type - you'll need to edit that Smarty markup to either reformat the field value right there, or maybe add a space at the end (or start) of the <td> cell. The latter is easier to do, so maybe give that a go first.

Can you offer me a little more explicit instruction on this point, please, Ben? I'm a web designer/builder in HTML/CSS, but Smarty is new to me.

First, please help me make sure I'm in the right place...
The Smarty markup you're looking at is reached through the FT admin interface for the Export Module, right? Attached to {$LANG.export_manager.phrase_table_format}? There I see markup that clearly builds the HTML tables, though it's less obvious that the same template is used for the Excel export.

If that's the right place, then I see that the template already includes a bunch of spaces at both the start and end of the cell, inserted for readability of the Smarty code. But those spaces don't actually factor into the export, do they? I probably need to ask Smarty to include a space in some explicit way, perhaps at this point in the code:
{elseif $field_type == "textarea"}
{$value}

Sorry to need this hand-holding,

Jim

[jlkane]

In Excel, a leading apostrophe is the official way to dictate "treat the string that follows as text" ...so if you can help me prepend an apostrophe to all text strings, that should fix this problem for good.

Thanks in advance,

Jim

[Ben]

Huh! I didn't know that about the apostrophe. Another weird Excel-ism! And yes, you're absolutely right about the whitespace that was added for readability. So let's try something like this:

Code:

<h1>{$form_name}</h1>

<table cellpadding="2" cellspacing="0" width="100%" class="print_table">
{* display the column headings *}
<tr>
  {foreach from=$display_fields item=column name=row}
    <th>{$column.field_title}</th>
  {/foreach}
</tr>

{* loop through all submissions in this current result set, and display each item in a manner
   appropriate to the field type *}
{foreach from=$submissions item=submission name=row}
{strip}
  <tr>
    {foreach from=$display_fields item=field name=col_row}
      {assign var=field_id value=$field.field_id}
      {assign var=field_type value=$field.field_info.field_type}
      {assign var=col_name value=$field.col_name}
      {assign var=value value=$submission.$col_name}

    <td>'
      {if $field_type == "select" || $field_type == "radio-buttons"}
        {smart_display_field_values field_id=$field_id selected=$value}
      {elseif $field_type == "checkboxes" || $field_type == "multi-select"}
        {smart_display_field_values field_id=$field_id selected=$value multiple=true}
      {elseif $field_type == "system"}
        {if $col_name == "submission_id"}
          {$submission.submission_id}
        {elseif $col_name == "submission_date"}
          {$submission.submission_date|custom_format_date:$timezone_offset:$date_format}
        {elseif $col_name == "last_modified_date"}
          {$submission.last_modified_date|custom_format_date:$timezone_offset:$date_format}
        {elseif $col_name == "ip_address"}
          {$submission.ip_address}
        {/if}
      {elseif $field_type == "textarea"}
        {$value|nl2br_excel}
      {else}
        {$value}
      {/if}
    </td>

    {/foreach}
  </tr>
{/strip}
{/foreach}

</table>

I confess I haven't tested it, but the new {strip} content will strip out all whitespace and the apostrophe added right after the opening <td> cell should (fingers crossed) tell Excel to render the contents as string.

Let me know how it goes! And sorry for the patchy support...

- Ben

[jlkane]

I think that probably qualifies as "good enough," Ben. It acts a little weird -- you actually see the leading apostrophes in Excel ...UNTIL you put your cursor in the formula bar for each field and hit "Enter". Then it's business as usual for Excel: the apostrophe is still stored in the cell, but it's no longer displayed.

I think this may be as close to a fix as we need. Thanks so much for your help, Ben!

Jim

[Ben]

Weird... Excel has so many oddities like this.

Well, I hope it's at least sufficient for your purposes. Email me back if it turns out that it's a real nuisance and you need to pursue other solutions.

All the best!

- Ben

[jlkane]

Weird... Excel has so many oddities like this.

Well, I hope it's at least sufficient for your purposes. Email me back if it turns out that it's a real nuisance and you need to pursue other solutions.

I've had my fussiest user test this solution and it's been pronounced "perfect."

She and I both thank you for your time and attention, Ben!

Jim