Excel Export Formats FAQ

February 17th, 2011 Leave a comment Go to comments

What is the Excel Internet Query Export Option?

This option exports a file that you can open in MS Excel. Unlike other exports, it is does not contain the data (initially), but creates an internet connection to the plugin page to extract the data. The data can be refreshed from directly within Excel so there is no need to do an export every time there is new data.

What are the differences among Excel CSV (UTF8-BOM), Excel TSV (UTF16LE-BOM) and Plain CSV (UTF8) Export files?

  • For any non-Excel spreadsheet application, use ‘Plain CSV (UTF8)’.
  • For Excel, first try ‘Excel CSV (UTF8-BOM)’ and if that does not work property, try ‘Excel TSV (UTF16LE-BOM)’.
  • Excel CSV (UTF8-BOM) is generally good for most Microsoft Excel installations. But in some cases Excel will not show non-western latin characters properly from UTF8-BOM file.

    In that case, you can use Excel TSV (UTF16LE-BOM) but this format has a downside. The UTF16LE-BOM format cannot handle new lines inside of entries. Therefore the plugin converts new lines to spaces.

    In other words, if you have a form with a text area where people enter multi-line input, the returns are converted to spaces in UTF16LE-BOM but are preserved in UTF8-BOM.

  1. March 3rd, 2011 at 03:25 | #1

    Hi, I’m puzzled about where the file is exported to. What location is used to store the exports?

  2. msimpson
    March 3rd, 2011 at 06:30 | #2

    @WJ
    It is where your browser downloads files by default. For example, with IE on Windows 7 it goes under C:\Users\YOUR-LOGIN\Downloads

  3. March 3rd, 2011 at 10:37 | #3

    Brilliant! It can be ‘that’ simple as well as ‘dead’ simple. On a Mac this translates to ‘User\Documents\Downloads’. Thank you for this very usefull plugin. The job I’m working on is for a non-profit organization, i.e. low-budget, but I will recommend they make a donation. This will save them many hours. Cheers! Willem

  4. Michael
    May 30th, 2011 at 19:58 | #4

    Michael West :
    Hi using your GREAT plugin with FSCF. In the TO section of the form there is a choice of locations to send the form to (http://par-t-perfect.com/quote/) but for whatever reason the database does not store this selection, and makes it impossible to sort data based on location once it is downloaded.
    Any solutions??
    THANKS!
    Mike

    Also, if i change to CF7, will it keep the TO data?

  5. October 4th, 2011 at 15:58 | #5

    Hi Michael,

    I’d love to be able to export the data from the database to Excel. I can see that submissions are being saved but any attempt to export to Excel fails (the spreadsheet contains no data)

    Please advise – thanks!

    • Michael Simpson
      October 4th, 2011 at 16:07 | #6

      Please post support issues on the support forum.
      Assuming you have submitted data and you can see that in the Database admin page, try exporting to different formats and let me know what is and is not working for you.

  6. October 10th, 2011 at 11:37 | #7

    Michael Simpson :
    Please post support issues on the support forum.
    Assuming you have submitted data and you can see that in the Database admin page, try exporting to different formats and let me know what is and is not working for you.

    Thanks – and done!

  7. February 9th, 2012 at 09:49 | #8

    Is it possible to set up the Excel Internet Query export to:
    1. Omit/exclude the header row with column labels?
    2. Include the image files as embed object not as a link? Remark: if we have a couple of attachements and only one of them is an image, or none are images, then the value should remain as a link.

    In regards to second question, it would be great if the attachement fields could be set up separately what files to be accepted there. At the moment the settings are general affecting all attachement fields within the form.

    I noticed that if I put custom lables to the extra columns, the attachments are no longer clickable links but just texts. Could it be a bug?

    • Michael Simpson
      February 10th, 2012 at 09:03 | #9

      1. No, but I can look into putting that option in.
      2. No, since the excel exports aren’t really excel files, just text files that excel imports, there is no option to embed an object. I would have to create a new exporter that would create an actual .xls binary file.
      3. Potential bug: do you mean if you use the editor plugin to create an extra column, that makes links in a different column not clickable? Or is it if you edit the link itself? If you can give me more info, I will look into it. Thanks.

    • Michael Simpson
      March 18th, 2012 at 22:59 | #10

      1. As of version 2.3 you can now omit the header row with header=”false”. This applied to cfdb-table short code and to IQuery export. Look for the option in the admin page “Database Short Code”

  8. February 10th, 2012 at 09:46 | #11

    1. It would be great, really!
    And another great improvement would be if you put an option to specify A to Z or Z to A sorting by user specified column/s (i.e. date of submission).
    2. Is it not going to be a refreshable excell sheet then? Anyway, it would be usefull for the HTML export for sure.
    3. Yes, if I give the extra column of type “attachement” (in FS Contact form) a new label, then I get in the exported table a new column (which is bad at first place) and the text “File is attached: example.jpg” is not a link anymore.

    Furthermore, there is no way to get rid of some columns or rename them, for example: Submitted, Submitted from, Submitted login…

    I hope this information helps. But I’ll report another issue. When exporting from a site installed in a subdirectory (i.e. domain.com/site/), the query gets a wrong address and is not working. An example is:
    generated link: http://www.domain.com/wp-login.php?redirect_to=wp-admin%2Fadmin-ajax.php%3Faction%3Dcfdb-export%26form...
    real (working) link: http://www.domain.com/site/wp-login.php?redirect_to=http%3A%2F%2Fwww.domain.com%2Fsite%2Fwp-admin%2Fadmin-ajax.php%3Faction%3Dcfdb-export%26form...

  9. Sbrnds
    August 30th, 2012 at 09:28 | #12

    If I export an CSV (including data), all characters are shown properly. But if I generate an IQY-file, all non-Western characters aren’t shown correctly. Is there a way to change the UTF-type in an IQY file?

  10. November 7th, 2012 at 03:58 | #13

    I also enjoy the briliant plugin.
    Unfortunately, the excel export fail to download links to uploaded files.
    I am using TSV 16 (text fields are in Hebrew) and I get:
    http://www.acbr.bahr.co.il/wp-admin/admin-ajax.php?action=cfdb-

    File names are in English.
    Any idea what I can do?
    (link: http://www.acbr.bahr.co.il/)
    Thanks! Mulli

  11. February 13th, 2013 at 23:16 | #14

    I have the form running, contact form DB and exporting working – but all my extra fields don’t have the label names I gave them City State Zip etc – but instead are ex_field1, ex_field2 etc…) How can I get the DB to carry over the proper names of the header/fields? Thanks!!

    Joe

    • Michael Simpson
      February 14th, 2013 at 21:24 | #15

      Unfortunately, that is how the field names come thru when using FSCF. The plugin is just grabbing what is posted from the form. It doesn’t know about FSCF definitions to lookup the display name. You might bring this up on the FSCF forum. The creator of that plugin put in code to push the form data into this plugin and he would be in the best position to make this work differently.

  12. February 14th, 2013 at 21:34 | #16

    @Michael Simpson
    I see – I’ll post over there – I believe it’s got to be a simple step, I’m just missing it.

    Thanks!

  13. Unknown123
    March 24th, 2013 at 10:50 | #17

    Michael,

    Thank you for the great plugin!.

    We have successfully established connection and are able to refresh data – that’s great!. We have, however, added additional columns and are making comments and calculations adjacent to corresponding rows of imported data. When we refresh, our custom columns remain in the same position, while new data pushes everything else down! Any thoughts on how to work around this?

    • Michael Simpson
      March 24th, 2013 at 17:35 | #18

      Instead of exporting via the admin page showing the data, go to the Short Code Builder page. Pick your form, Export file type and select the columns you want in order in the “show” field. Use the Export Link that is generated.

  14. Unknown123
    March 24th, 2013 at 20:05 | #19

    How will that help eliminate the issue of cells shifting down in the worksheet while the custom column we have populated stays static?

  15. Michael Simpson
    March 25th, 2013 at 09:49 | #20

    @Unknown123
    I misunderstood. I though you meant you added columns to the form/database. I see you mean you added columns in the Excel spreadsheet. Then new rows come in from the lastest export update. I don’t know the answer. That is really an Excel question, not a CFDB question.

  16. Unknown123
    March 25th, 2013 at 21:31 | #21

    @Michael sounds fair, thank you.

  17. Espen
    May 22nd, 2013 at 09:16 | #22

    Hi there,

    I have trouble with the Excel Internet Query Export. Testing from the main site all is ok, but from a subsite in the mulitsite I got a message from Excel saying the query ended in an empty result.
    The user used is a admin user, so the only diff is the subsite/mainsite thing..

    Any known issues here?

    • Michael Simpson
      May 22nd, 2013 at 11:58 | #23

      the plugin is not yet set up to support multisite. So your sub-site doesn’t have a DB table with any data for it.

  18. Espen
    May 22nd, 2013 at 14:09 | #24

    Hi Michael,

    Yes and no, I know that the plugin not autogenerate the table. But I use a site template with functionality to create the table.. And it work just fine. Using the shortcode I can export and list all posted content. The only function not working is the Excel Internet Query..

  19. Michael Simpson
    May 22nd, 2013 at 17:23 | #25

    @Espen
    OK, but as I said, the plugin isn’t setup for multisite. I haven’t tested the functions in that configuration so I can’t know for sure on what should work and what should not and why.

  20. Espen
    May 22nd, 2013 at 17:57 | #26

    Ok, and thanks.

    This is a great plugin, and it works for me as it is – exept for this small detail. Thanks again :-)

  21. saj
    June 14th, 2013 at 08:22 | #27

    i have created a export link shortcode
    [cfdb-export-link form="Enquiry_copy" show="submit_time,name,contactno,traveldate,Submitted" filter="submit_time>$_GET(start)&&submit_time<$_GET(end)"] … but it shows empty results.. it is not getting the dates… please help

You must be logged in to post a comment.