Automatic download of QuickBase data to Excel spreadsheet

  • 0
  • 2
  • Question
  • Updated 4 weeks ago
  • Answered
I'm using a data connection ("From the web") in Excel to automatically download the data that I need from QuickBase.

The only issue I'm facing now is that from time to time my data connection seems to "forget" my QuickBase credentials and the data download fails, until I go into Connection Properties and click on "Edit Query".  When I do this, Excel attempts to open the QuickBase report in a browser window within the Edit Query dialog box and asks me to enter my QuickBase credentials. Once I re-enter my credentials, the data download executes successfully.

I've attached a screenshot of the Edit Query dialog box.



I don't want to have to do this every time. Is there a way to store the credentials within the connection string?

Thanks in advance.

Juan
Photo of Juan

Juan

  • 466 Points 250 badge 2x thumb
  • annoyed

Posted 2 years ago

  • 0
  • 2
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,784 Points 20k badge 2x thumb
Excel is acting like a newly opened second browser in a sense so that the ticket / credentials established with your current browser (Chrome, FireFox, Edge etc) session has nothing to do with the ticket / credentials established for the newly opened Excel session. In other words you have to log in QuickBase from Excel to establish a independent QuickBase session even if you have a session active QuickBase session in your browser.

I don't have Excel on my current machine but I did test the following procedure using Open Office Calc which has a similar but different interface to Excel's Web Query feature (Microsoft may have renamed this feature in more recent versions). In Calc you don't have those yellow boxed arrows as Excel has so you have to use this navigation:

Insert | Link to External Data | Paste URL | Select Table Identifier

The URL I pasted into Calc was as follows:

https://haversineconsulting.quickbase.com/db/main?a=SignIn&loginid=HaversineConsulting&passw...

Unrolled the URL looked like this:

https://haversineconsulting.quickbase.com/db/main?a=SignIn
&loginid=HaversineConsulting
&password=MYPASSWORD
&nexturl=https://haversineconsulting.quickbase.com/db/bgcwm2m4g

I then selected a Table Indentifier (a cruder selection mechanism to Excel's boxed yellow arrows) and that table populated. What is going on is that Calc is logging in to QuickBase and following the redirect to the URL specified through the nexturl parameter. I would try a similar URL from your application but initially specify a nexturl parameter without any ampersands in them to test if Excel will operate similarly. If it does you can URLEncode any ampersands in the nexturl parameter.

Let us know if this procedure works in Excel (I think it will)
(Edited)
and, what about when the login string, combined with the nexturl string, and the API to get the particular query I want... altogether is just too many characters? is there a way to hop from one page to the next programmatically?
Photo of Joe Smith

Joe Smith

  • 150 Points 100 badge 2x thumb
Hi Jonathan, I installed the Selenium reference into my Excel.  The VBA commands allow me to navigate around QuickBase and collect data. 
Photo of Alisher Nizamov

Alisher Nizamov

  • 100 Points 100 badge 2x thumb
Hello,

I am looking into creating an excel query for a quickbase table. I used the Data - New Query - Web Query, in the field I insert the web site where the table sits and hit Enter. Excel returns a Navigator window where the table view shows a two-row four-column table that is not even close to what the actual table has.

My goal: I pull tables from quickbase on a regular bases and wanted to automate the operation by creating a web query and refreshing it every time I am ready to export tables from QB into excel. 

I have read the posts regarding my matter but the posts are old and the approaches are not working in my case.

Thank you in advance,
Alec.
Photo of Juan

Juan

  • 466 Points 250 badge 2x thumb
Hello Alec,

Here's how I made this work for me.  

1. Make sure the report you want to export/download is in "Normal" format.

2. Set the Dynamic Filters to "None".

3. Uncheck the "Allow users to search using the Quick Search field".

4. In Excel, make the connection to the report in the way you specified. Make sure that when you get to the step where you select the dataset you want to download, you click the little arrow that selects the entire table.

5. The report will download and it will look wonky. Don't worry about that. Now go back to Quick Base and change the format of that same report to "Tab-Separated Values" and save it.

6. Go back to Excel and refresh your query.  You should now see the report in the format that you need.

Hope that helps.

Juan
Photo of Alisher Nizamov

Alisher Nizamov

  • 100 Points 100 badge 2x thumb
Juan, thanks for the response.

Here is what I am doing: in Excel I go to Data - New Query - From another Source - From Web - in the dialogue in the URL field I paste the link of the quickbase where the table sits - hit OK and below is what it displays. The headers in the display table are not what I have in the table in QB.
 
Photo of Debbie Taylor

Debbie Taylor

  • 664 Points 500 badge 2x thumb
Ping Eric at Data Collaborative    WIth their DC Merge tool, you can do it easily.
Photo of Alisher Nizamov

Alisher Nizamov

  • 100 Points 100 badge 2x thumb
Thanks for the response. I sure will contact him.
Photo of Alisher Nizamov

Alisher Nizamov

  • 100 Points 100 badge 2x thumb

I left a message via 'Contact Us' at Data Collaboration and so far no response. Thanks.
Photo of Keith

Keith, Champion

  • 1,020 Points 1k badge 2x thumb
Take a look at our XL Docs for Quick Base add-on

You can demo it here:  https://juicedtech.quickbase.com/db/bj446yrpq

Keith
info@juicedtech.com
Photo of Alisher Nizamov

Alisher Nizamov

  • 100 Points 100 badge 2x thumb
Hello,

I did not see any other responses for my thread. I thought if we get this resolve, it would benefit many quickbase users. Below is my whole idea of what I want:

My process: I pull several tables from quickbase every week and it is manual.
What I want (possible solution): An excel template with live connections / queries to the quickbase tables where I refresh them every time I need the tables and use the tabs. This will eliminate manual pulls that are time consuming and so repetitive at times.

I was able to get to the point where it directs me to save or open the file. I am looking for more intelligent ways to manage the repetitive processes.

Thanks and look forward to hearing solutions.
Hope many of us will benefit from this.

Alec.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,736 Points 50k badge 2x thumb
Did you check out Doccs XL from Juiced? I have implemented it for several clients. It works well.

This conversation is no longer open for comments or replies.