Spring Forward Web Design
Build your own Site... Control your own Future!
One of the primary reasons to create forms on your websites is to get feedback and build a relationship with your readers. But data does not do you much good if it cannot be downloaded to a spreadsheet where you can manage it and migrate it to other applications such as your E Newsletter.
Thankfully, Breezin Forms data is very easy to download and work with.
Just go to Components > Breezin Forms > Manage Records.
Click on View Status and select BOTH to bring up all the records. Then select all the records:
Then select the records to export
Then click on CSV
One option is to open directly in Excel. I think this is not so good as it should be a comma deliminated file. So let’s save the file.
Save it the way it wants to be saved. As a CSV file.
Lets open it and take a look.
I opened it in Open Office and got:
It is separated by semi colon so I checked that box.
Then clicked on OK.
Now will select all and put it into Excel.
Click on File SAVE AS and save it as an Excel 2000 file. It is now in a form where you can do quite a number of things with it.
If you do not have Open Office, there is a slightly longer path that reaches the same result.
First, open a new excel workbook, then click on DATA Import and selected the CSV file which brings up the following text import wizard:
Change to delimited because it is not a fixed width.
Changed delimiter to semi colon and then clicked on next
Now we are looking good. Click Next.
Go with General here. You can get rid of extra columns later, so click finish.
If there is space (with a new workbook) go with the existing worksheet so click OK.
We lost the top column headings along the way, but this is very workable.
Click file SAVE AS and save this file.
If you are using Excel 2003, you should use the semicolon ( ; ) as the separator. But if you are using 2007, you should use the comma ( , ) as a separator. If you go to Components > BreezingForms > Configuration, you will see that there are some variables at the bottom that affect how your CSV file is exported. You could try changing the comma separator to a semicolon (which one to use depends on which version of Excel you have).
During the export, instead of opening the file in Excel immediately, choose the option to save it as a CSV instead. Once saved, go to the file directory where you stored it and rename the file from FILENAME.csv to FILENAME.txt.
Then open the TXT file in Excel. When you try to open it, you should see a Text Import Wizard dialog box. The fields should be completed as follows:
Choose the file type: DELIMITED (not Fixed Width)
Start Import at Row 1
Delimiter should be ; or , depending on which you entered in the configuration
Text qualifier should be " unless you have changed it
You can choose format for each column if you'd like to use it, then click FINISH. Your data should now open in column form.
If Data does not line up properly in columns
The main problem with CSV Export is that the empty values of non-required form controls are not being stored in the database...
So, if you have, for example, a QuickMode form with the following fields (* - required): First Name, Last Name*, E-mail, State, Phone, you may get the following in your export:
and so on... Exporting of such records to CSV doesn't make any sense even if you bypass that delimiter issue and alter the code of facileFormsRecord->expcsv to output headers - content in your columns will be total mess (second column may contain Last Name, State, Phone or whatever else you have in your form). And if you have several forms - it would be even worse. So I think the most important thing for export is storing all the form values in the database, even empty ones.
There is a section on the Advanced page which allows the reporting of empty cells if this becomes an issue for you. Go to Manage Forms, select your form (each form has its own database so you can have some report empty cells and others not if you want). Open the form and click on ADVANCED TAB.
Then click on More Options. Under the SETTINGS TAB, there is a drop down arrow for LOG TO DATA BASE. This is where you can change it to report ALL VALUES if you want.
Adding a Data Base Management System
Another possible solution would be to get the free data base management program SLQ2Excel.
SQL 2 Excel is a Component which enables you to deliver data from your database directly to your users as downloadable Excel files. Write any valid SQL query in the Component backend. The query is run when a user clicks the link and the current information in the database is delivered to the users browser as an Excel file. Multiple worksheets (e.g. different queries) can be compiled into one single Workbook. The spreadsheets are compatible with Microsoft Excel as well as OpenOffice Calc.
You can publish your reports in three different ways:
- Download them in the Joomla Administrator backend
- Publish the SQL 2 Excel Module to one or more pages and let users download the spreadsheets through the module links
- Publish the Sections / Categories list view to a menu and let users browse your sections/categories to locate desired report(s)
Coming Up Next
The next article will review how to migrate a Breezin Forms Package from one website to another. This will save you a lot of work if you have multiple websites. It is a very but very important article.
Then in the final article we will do a Graduation exercise by putting all of the skills together to produce an On Line Petition – one of the most important social network tools available today. You will see why Breezin Forms is the best Joomla component for making an on line petition.