Export of forms data to Access?

[Jeff Seaman]

Hello,

I am trying to find a way to automatically export forms data to an Access database once it is sent to the server (maybe using external commands?). But the extent of my knowledge doesn't include any programming language or command line execution. Has anyone ever done this, or are there any suggestions/preexisting discussions?

Automatic export may be somewhat pre-emptive on the other hand. I can't quite figure out how to export forms data as entries in single MS Access database....

Any suggestions?

-Jeff

[Morgan Belkadi]

Hello Jeff,

if you're not techy at all, I'd recommend you to stick with the default database type (h2).
What would you like to do exactly? Do you know you can export your results via the Forms tab, once received?

Best,
Morgan

[Jeff Seaman]

Hi Morgan,

Thanks for the reply. I am already quite familiar with exporting forms results manually. So I have updated the discussion topic to what I need to do right now (automatic forms export to an Access database, if at all possible). Any thoughts?

-Jeff

[Morgan Belkadi]

Hello Jeff,

I guess the best way to do this may be to set up a MySQL database, and run a Script which would access it and do what you want to do.

Hope that helps...

Morgan

[Jeff Seaman]

I kind of had a feeling I might have to do something with SQL and maybe ODBC. But I may be jumping the gun a bit as I can't even manually export the data into an existing Access database; which is what I am trying to work towards.I've played around with exporting the FLSMS forms data to an excel file, and then importing to Access. But the problem is that it seems impossible to export the FLSMS forms csv file into an existing database (without creating a new file).

If I can't figure out the automatic export of forms data, thats okay. I just need to get forms data into an existing Access database (whenever I try, I just get an error message). That is my top priority. And its all new territory for me. Any ideas?

[Morgan Belkadi]

Hi Jeff,

in order to have your existing data exported somewhere else, you'll have to write a quick script which would read:

- either the exported CSV file
- or the current database, which must be in a file if you use the default h2 database system

The script would then parse and save the data where you want to have it.

Hope that helps!

Morgan

[Jay]

I'd like to do something similar to Jeff, without necessarily having to export data into a CSV. It would be ideal if Forms data could appear automatically in an Excel spreadsheet, in real time.

Like Jeff, I'm not much of a coder, but my thought was to do something from Excel with ODBC/JDBC. I was thinking it could access the h2 database directly and pull data into the Excel sheet.

I'm running into technical dead ends, however (and may even be barking up a totally wrong tree!) -- is there any possibility with this method/outcome?

Thanks!

[kiwanja]

Hi jay

 

We did talk about the potential of allowing incoming Form data to trigger an action in "External Commands", which could have written the data to an external database, CSV, or whatever. We're not planning on doing much further development with Forms right now, but Alex might have some ideas about how you might be able to knock something together here. I'll ask him to comment.

 

Cheers.

 

Ken

[Alex Anderson]

Hi Jay,

If you use a MySQL database with FrontlineSMS, I expect you could poll this periodically from Excel using VBA.  I've no experience with VBA though, so I can't suggest much more than that.

 

Alex

[Jay]

Thanks, Alex --

I was able to install and configure a MySQL database on my machine (key step was actually creating the database from the MySQL client, then FL took it from there!). I have further succeeded in pulling in table data into excel using the MySQL ODBC driver. It even pulls in new Forms data automatically! Last step is getting the data to be displayed correctly, in a normalized list like it displays within Frontline itself. Am exploring creating a custom table, but am so new at this that I am rather slow! Thanks for the suggestions and advice!