Forum Discussion

AnnaTamaoka's avatar
AnnaTamaoka
Qrew Member
12 years ago

Automatic export from QuickBase into Excel

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.
APIs and custom code
Data import and export
reports and charts
  • DavidHawe's avatar
    DavidHawe
    Qrew Trainee
    12 years ago
    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.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    12 years ago
    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.
    • AdminEntry's avatar
      AdminEntry
      Qrew Cadet
      8 years ago
      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
  • AnnaTamaoka's avatar
    AnnaTamaoka
    Qrew Member
    12 years ago
    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!
      • RobinGold's avatar
        RobinGold
        Qrew Member
        6 years 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
        ------------------------------
  • AnnaTamaoka's avatar
    AnnaTamaoka
    Qrew Member
    12 years ago
    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!
  • MM's avatar
    MM
    Qrew Member
    12 years ago
    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?
  • AnnaTamaoka's avatar
    AnnaTamaoka
    Qrew Member
    12 years ago
    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!