Importing into Access/Excel from QuickBase automatically

  • 1
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I need to populate Access with the data in all tables in my QuickBase app. I know that you can create a report as a CSV format, and that you can use API_GenResultsTable to create something similar. I also know that you can export the entire table as a CSV. I can then import all of those CSV files into Access.

The problem is that I need to be able to do it from Access with zero interaction with a browser. I have too many tables and that need to be exported too frequently for me to manually export every single table every time. I need a URL that I can plug into Access that will allow it to directly access the CSV without a user having to interact with the save dialog.

There is a very similar question here: https://quickbase-community.intuit.com/questions/782247 I tried using the instructions in the answer and it pulls up HTML instead of a CSV file, though.

I included Excel in the title because if I can get it to work in Excel then I'm pretty sure I can get it to work in Access.

Photo of Kat

Kat

  • 86 Points 75 badge 2x thumb

Posted 4 years ago

  • 1
  • 1
Photo of Rick

Rick

  • 10 Points
We use QuNect to download data into MS Access. It's an ODBC connector that could be scheduled to automatically download the data. Also included with the license is a feature called "Backup" which allows you to auto download CSVs to a directory on your computer/network. It's a great product and reasonably priced.

http://www.qunect.com/
Photo of Kat

Kat

  • 86 Points 75 badge 2x thumb
I'm not looking for somebody to sell me a product. I want an answer on how to do it. Unless you know how it's grabbing the CSVs, this isn't helpful to me.
Photo of Rick

Rick

  • 10 Points
What's your GenResults API URL?
Photo of Kat

Kat

  • 86 Points 75 badge 2x thumb
It's this: https://[domain]/db/[dbid]?a=API_GenResultsTable&apptoken=[token]&options=csv (With the actual values where the bracketed items are, of course.) It brings up a dialog box to save the CSV file, but it interpreted as HTML when I plug it into Excel or Access.

Interestingly it doesn't seem to generate a CSV if I include the qid parameter like this: https://[domain]/db/[dbid]?a=API_GenResultsTable&qid=1000030&apptoken=[token]&options=csv The page I get for that starts with this:

function qdbWrite()
{
document.write ("<table align=center cellspacing=2 cellpadding=2 bgcolor=white class=verdana >");
...

I've tried saving the report it's referencing in both CSV and normal formats and get the same results. So it would be nice to solve that as well.
Photo of Ken

Ken

  • 170 Points 100 badge 2x thumb
For VBA in Excel and Access you're going to need to wrap some code around the Quickbase API's, initialize some MSXLM2.DOMDocuments variables, post it using WinHttp and process the returned XML.  The only time I use CSV in VBA is when using the API_ImportFromCSV API since it's the fastest way to add/update records.  Try something like this and look at the XML results:

h t t p s://[domain]/db/[dbid]?act=API_DoQuery&query={3.gt.0}&username=[username]&password=[password]&apptoken=[apptoken]
Photo of Kat

Kat

  • 86 Points 75 badge 2x thumb
I tried using an XML report but Access gives a "DTD prohibited" when I try to import it as is. It did occur to me to process it before importing it, but that's a bit more work than I have the time to do right now, so I'd like to avoid it if possible.

I don't need to import into QuickBase; this is purely one way from QuickBase into Access. Is there really no way to programmatically fetch a CVS from QuickBase using VBA? Or even XML that Access will parse on its own without my processing it? I don't see any APIs that involve exporting, only ones that import.
Photo of KenFirch

KenFirch

  • 10 Points
Kat, If you're interested I can put together a basic Access db that adds/edits records from a Quickbase table.  It won't have the bells and whistles of creating matching Access tables and synchronizing them.  You'll need to create an Access table matching your Quickbase table and edit the table/field names in the VBA code to get it to work.   Send me (kenfirch@yahoo.com) your email and I'll try to get it out later today.
Photo of Kat

Kat

  • 86 Points 75 badge 2x thumb
That would be great! I don't expect anybody to write the whole thing for me, but seeing a working example of Access fetching data from QuickBase would be extremely helpful. I'll email you right now.
Photo of Rick

Rick

  • 10 Points
Photo of Kat

Kat

  • 86 Points 75 badge 2x thumb
I haven't. It looks promising except that it says it only works with 32 bit Access and you have to disable application tokens. I suspect it won't work for me for those reasons, but I bet it'll help other people with the same issue.
Photo of Kat

Kat

  • 86 Points 75 badge 2x thumb
Hey, can you post that as a new answer so I can accept it? I think it actually will work for me. Nice find.
Photo of Rick

Rick

  • 10 Points
Photo of Rick

Rick

  • 10 Points
Done - glad I help! So many different ways to achieve this type of automation.