Discussions

Expand all | Collapse all

Automatic export from QuickBase into Excel

  • 1.  Automatic export from QuickBase into Excel

    Posted 10-16-2013 13:34
    Hello!

    There are a few parts to this project I'm trying to complete. First, I have specific filtered table reports I've created in QuickBase in multiple different tables. I'd like to automatically export the filtered reports into specific Excel sheets in a workbook.

    For example, I have a table called "Clients" where I've created the report, "Active Clients". I also have an Excel Workbook called "Fact Sheets" with multiple different tabs (sheets). One of the worksheets is called "Clients". I want to export my Active Clients report into the Clients worksheet.

    Is this possible? I've been trying to read up on the API guide (https://www.quickbase.com/api-guide/index.html) but it doesn't sound exactly like what I'm looking for.


  • 2.  RE: Automatic export from QuickBase into Excel

    Posted 10-17-2013 13:40
    I would suggest finding an Excel expert. I am not sure but I believe you can use the "From Web" wizard or possibly use the embedded VBA in Excel to employee the QuickBase API to import QuickBase data to a sheet in Excel.


  • 3.  RE: Automatic export from QuickBase into Excel

    Posted 10-17-2013 14:18
    Thank you! I'll try both suggestions and see how it goes. :)


  • 4.  RE: Automatic export from QuickBase into Excel

    Posted 10-18-2013 07:34
    You can do a "Web Query" from Excel. Under the Data tab select "From Web" (displayed on the far left) and browse to your QuickBase table and you are off to the races.


  • 5.  RE: Automatic export from QuickBase into Excel

    Posted 04-26-2017 20:18
    Hi Dan, I've implemented this approach successfully and I'm able to obtain the data I need. 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


  • 6.  RE: Automatic export from QuickBase into Excel

    Posted 10-18-2013 12:57
    Thank you for your response! I tried to do this, and it was almost exactly what I wanted. Unfortunately, the output was a bit more complicated than I have the knowledge for. So the short answer is that this would be able to work for anyone trying to do this, but I don't think it would work for me.

    The long answer (if anyone is interested), is that I need the data of the table that I pull to appear in column/row A1. When I try to pull over the report from QuickBase, it seems to carry over much more information than is visible, including all 6 different types of reports and descriptions (i.e. "Table; Show your data in a spreadsheet-style report with rows and columns.") as well as the filters of the report and the summary of how many records are available. Another thing that doesn't work for me is that I have some multi-line text fields that show up in one cell per line; I need all lines to appear in one cell.

    Thank you for your help!


  • 7.  RE: Automatic export from QuickBase into Excel

    Posted 10-18-2013 13:30
    Read this article:

    http://www.kimgentes.com/worshiptech-web-tools-page/2010/8/19/web-connecting-csv-files-as-external-data-to-excel-spreadshe.html

    The URL you would use would be a CSV report using either a report with this setting:

    Options | Format = Comma-Separated Values

    Or a URL that calls API_GenResultsTable

    https://YOURSUBDOMAIN.quickbase.com/db/YOURDBID?act=API_GenResultsTable&qid=1&options=csv


  • 8.  RE: Automatic export from QuickBase into Excel



  • 9.  RE: Automatic export from QuickBase into Excel

    Posted 24 days ago
    I realize this is an older post, but I am also trying to figure out how to automate the downloading of my tables into local Excel files, and running into a few issues with all of the suggestions I have been able to find. I can't get the "From Text" option to work, as it doesn't use the stored credentials, and redirects to a login page -- wheres the credentials do work when I get data From Web.

    With the From Web method, there are two problems. The main problem is that it only downloads the first 100 rows, as the remaining rows are on additional pages and I can't find a way to make QuickBase display all table rows on one page even in plain text format. Second, it is less of a problem, but above and to the left of the data table the downloaded data shows the following text that appears to be some hidden menu artifact coming through from the html: 

    Table
    Show your data in a spreadsheet-style report with rows and columns.
    Kanban
    Work with your data as cards in columns. Unavailable for this table because it has no valid multiple-choice or user fields.
    Grid edit
    Create a report to enter or edit many records at once.
    Summary
    Group and total your data to answer questions about large data sets.
    Chart
    Visualize your data in charts including pie, bar, line and others.
    Map
    Display records with an address as pins on a map.
    Calendar
    Show your data as events on a calendar to help with scheduling and timing.
    Timeline
    Create a Gantt chart for records that have start and end dates.

    Full Report Grid Edit Email More 1-100 of 498 Buses


    Any help appreciated, as I am very eager to automate the process of downloading my tables to Excel on a regular basis for backup and version control purposes.

    ------------------------------
    Robin Gold
    ------------------------------



  • 10.  RE: Automatic export from QuickBase into Excel

    Posted 10-18-2013 13:52
    This is really helpful! I'll play around with this and see if it fits my needs. If not the project I'm working on, definitely another one I'm involved in. Thank you!


  • 11.  RE: Automatic export from QuickBase into Excel

     
    Posted 12-03-2013 19:26
    Anna,
    were you able to figure out how to get [Field 1] to pull into Cell A1 in Excel, and [Field 2] to pull into Cell B36 (or whatever other cell you wanted it to go into?


  • 12.  RE: Automatic export from QuickBase into Excel

    Posted 12-06-2013 14:23
    Hello!
    Thank you for following up. I don't think I was able to do exactly what I wanted, but it turned out that the group I was working with decided to go a different direction with the project that would benefit them more in the long run. We'll see...I may need to open this topic back up in the future, but for now I think everything is all set.

    Thank you!


  • 13.  RE: Automatic export from QuickBase into Excel

    Posted 03-13-2014 14:08
    https://YOURSUBDOMAIN.quickbase.com/db/YOURDBID?act=API_GenResultsTable&qid=1&options=csv


    This looks like something I could use....What would be the values I need to replace in the above line...?  "YOURSUBDOMAIN" I would replace with my sub domain...
    What about "YOURBID?" what would replace this?
    Where would I put the table name?

    Sorry...new to trying this, but this would make the whole export table go much better.