3

I'm trying to export a CSV of my members using Solspace User.

It works fine, except that when I open the CSV in Excel, it actually shows the quotes around the values. I know that obviously my template has quotes around each value, but some addresses have commas in them - without the quotes, it throws off the CSV.

My template looks like this: (I've added whitespace for readability).

{exp:ifelse parse="inward"}
  {if group_id == 1 OR group_id == 6}

    {exp:http_header content_type="text/csv"}

    email, screen_name, address,
    {exp:user:users limit="100000"}
      "{email}", "{screen_name}", "{address}",
    {/exp:user:users}

  {if:else}
    {redirect="404"}
  {/if}
{/exp:ifelse}

Has anyone tried to do this sort of thing? Am I missing something obvious?

When opening in Excel, it looks like:

email             | screen_name  | address
------------------+--------------+------------------------
name@domain.com   | "John Smith" | "123 Street, Somewhere"

When using Finder's Quick Look, it looks just fine (no quotes).

I'm not sure if this is a general CSV issue, an issue with EE or an Excel thing.
Any help would be appreciated.

Edit
Actually, the first column doesn't show the quotes, but the rest do. Which I really don't understand.

  • 1
    I'm not sure, but I can tell you I've found that the easiest way to get this going is to format your data as an html table and open that in Excel (where you can then save it as a CSV if desired). See this answer. – Alex Kendrick Apr 11 '13 at 2:11
2

Alex pointed me in the direction of using an HTML table to output the data, then serve it as an xls file instead.
While this technically doesn't answer the question of how to fix the CSV issue, it did help me solve the real problem I was having, which was the data being incorrect.

When using a lone table tag, everything shows up in Excel in Times New Roman with no borders. To get it to show up with default Excel styling, use the following markup:

{exp:ifelse parse="inward"}
{if group_id == 1 OR group_id == 6}

{exp:http_header content_type="application/vnd.ms-excel"}

<html>
<head>
  <meta http-equiv=Content-Type content="text/html; charset=macintosh">
  <meta name=ProgId content=Excel.Sheet>
</head>
<body>

  <table>
    <tr>
      <th>email</th>
      <th>etc...</th>
    </tr>
    {exp:user:users limit="100000"}
    <tr>
      <td>{email}</td>
      <td>etc...</td>
    </tr>
    {/exp:user:users}
  </table>

</body>
</html>

{if:else}
  {redirect="404"}
{/if}
{/exp:ifelse}
| improve this answer | |
1

This is an Excel issue. If you open the file by double-clicking or dropping directly on Excel, you'll see delimiters. If you import the file, Excel will ask you to ID the delimiters and remove them.

| improve this answer | |
  • I was excited to try it out. It asked me for the delimiters and the text qualifier... Thought it was going to be successful. But, nope. Still added the quotes. But like I added to the question... the first column doesn't display quotes. Weird. – Jason Varga Apr 11 '13 at 12:50
  • That's odd. What does the file look like just in a text editor? Are you getting the new line/ return marks? Assuming the .csv is properly formatted, the import option always works for us. Maybe display 2 or 3 lines of the .csv file, with headers and some sample data (fake of course). – nonprofit_tech Apr 11 '13 at 15:27
1

I did something recently that may be of use in your cirumstance: LowReplace

Its use is fairly straight forward, I used it to output Freeform data to JSON but should work for you.

{exp:user:users limit="100000"}
{exp:low_replace find="QUOTE|NEWLINE" replace="\QUOTE|SPACE" multiple="yes"}
"{email}", "{screen_name}", "{address}",
{/exp:low_replace}
{/exp:user:users}

This isnt tested, you'll likely need to wrap each field in a Low Replace but might help !!

| improve this answer | |
  • Probably will end up with way too much overhead. There are 3 fields in my example but in reality there are a lot more. Running a plugin on each field, for thousands of rows, it'll probably crap itself. :) – Jason Varga Apr 11 '13 at 15:11
  • I used this to output about 2000 entries with 20 custom fields each, and it worked well. I had to modify it a bit to get it to open properly in Excel, which apparently takes "" instead of \": {exp:low_replace find="QUOTE|NEWLINE" replace="QUOTEQUOTE|SPACE" multiple="yes"}{resource_predefined_license}{/exp:low_replace} – Oh, and the newlines don’t need to be removed, but I left it for prettiness of plain text file – Charlie Gorichanaz Nov 8 '13 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.