Excel Export Formats FAQ
October 18th, 2014
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.
Hi, I’m puzzled about where the file is exported to. What location is used to store the exports?
@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
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
Also, if i change to CF7, will it keep the TO data?
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!
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!
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?
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.
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”
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...
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?
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
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
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.
@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!
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?
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.
How will that help eliminate the issue of cells shifting down in the worksheet while the custom column we have populated stays static?
@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.
@Michael sounds fair, thank you.
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?
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.
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..
@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.
Ok, and thanks.
This is a great plugin, and it works for me as it is – exept for this small detail. Thanks again 🙂
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