Excel - Import CSV

Owned by Thomas Sherman (Deactivated)
Last updated: Nov 05, 2021 by Bettina Borces
3 min read

This help page teaches you how to connect an Excel workbook to Projector report data. It will also show you how to edit and maintain the data.


Create Report

You'll first need to create a report in Projector so that you have data to pull into Excel.

  1. Go to your Reports tab in Projector
  2. Create or edit a report. On the Output tab set the following - Web service code and Auth token. If you don't see the auth token option, visit Account Settings Editor.

  

  1. Run the report
  2. Right click the report in the output area and choose Advanced | Show Output URL. Click Copy CSV URL.





Create Query from Web

Don't use the From Web button found in the default toolbar, you have to go through the New Query dropdown. Yay for confusing User Interfaces!


Follow these steps to connect Excel to the Projector report. This section assumes you have your CSV URL already.

  1. Open an Excel workbook
  2. Go to Data menu | Get & Transform ribbon section | New Query dropdown
  3. Choose From Other Sources | From Web
     
  4. Paste the URL into the From Web box. Click OK.
     
  5. Your data will preview in a popup window. Click Edit.
  6. The Advanced editor will be displayed. Click the gear icon for Source under Applied Steps.

  7. Set Line Breaks to Ignore quoted line breaks and click OK

    Why this step? Projector encloses any text field in double quotes. When that quoted field contains line breaks, we need Excel to treat it as a single entry and not try to create a new row of data.

  8. Click Close and Load. The data is inserted into your spreadsheet.


Refresh

You can schedule your Projector reports to run on a schedule and the Excel spreadsheet to refresh with the new data. 

Manual

Two ways to manually update table.

  • Right click on the table and choose Refresh
  • From the Data menu | Connections ribbon section | Choose Refresh All
Automatic

You can configure the connection to refresh on open of the Excel file or at set intervals.

  • From the Data menu | Connections ribbon section | Choose Connections
  • Select a connection and click Properties
  • Set your refresh options


Add or Remove Columns

If you alter the Projector report by adding or removing columns, you should make sure you update your Excel spreadsheet too.

  1. If you are adding a column, make sure the column has already been added to your table in Excel
  2. From the Data menu | Get & Transform ribbon section | Choose Show Queries
  3. A sidebar appears with your queries
  4. Right click your query and choose Edit
  5. Right click your query and choose Advanced Editor

  6. On the second line, edit Columns and set the proper number of columns.

    Source = Csv.Document(Web.Contents("https://app.projectorpsa.com/report/code/mycode?format=csv&UserName=tom@revcorp.tcs&AccountCode=revcorp-tcs&AuthToken=abc"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),

  7. On the third line, edit to remove columns that will no longer be imported or add columns that will be imported. You may need to get the column name from the Projector report. Make sure you set the correct column type (text, datetime, Int64.Type, etc).

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month", type datetime}, {"Engagement Code", type text}, {"Project Code", type text}, {"Resource Display Name", type text}, {"Person Hours", Int64.Type}, {"Data Type", type text}})

  8. Click Done
     


  9. Click Close & Load