Importing from Excel

Key Concepts

PlanetTogether allows users to import scheduling data from an Excel Template. This is useful for importing custom data for a demo or when doing preliminary factory modeling.

The basic concept is that the data from Excel is pulled into SQL Server and that data is then pulled into PlanetTogether. This is done automatically in the background - all you need to do is hit the refresh button and the data you have in Excel will be imported into PlanetTogether.

This article will show you how to set up PlanetTogether to import from an Excel file via SQL Server.

1. Download the Data Files

Go to this website and download the zip file provided under "Class Resources" and then extract the files.

In this folder, you will find:

  • APSInterfaceSettings.xml - This is the mappings file (a custom XML file) that will allow PlanetTogetther to map the data in the Excel template. 
  • Template_Demo.xlsx - This is the Excel Template that you will enter your scheduling data into.
  • Template_Demo.bak - This is the backup of the SQL database that will be used to connect to Excel.
  • Scenarios.dat - This is a scenario file that will be used to replace the one created by the instance. 

2. Create a New PlanetTogether Instance

In the Instance Manager, create a new instance by clicking the "New" button and filling in the information required.

                                                   

This new instance will load up with its own scenario data file and mappings file that will need to be replaced by the files we have downloaded.

It is important to stop the services before moving on to the next steps. This can be done by clicking the "Stop Services" button of the Instance Manager.

These files can be accessed by following:

Scenario file 

  • C:\ProgramData\PlanetTogether APS\<Your Instance Name and Version Number>\System\Data\Scenario
    • Note: The Instance Name and Version Number are separated by a space.
  • Delete the scenarios.dat file and replace it with the scenarios.dat file downloaded in step 1 by copy-pasting the file. 

Mappings file

  • C:\ProgramData\PlanetTogether APS\<Your Instance Name and Version Number>\IntegrationFiles
    • Note: The Instance Name and Version Number are separated by a space. 
  • Delete the APSInterfaceSettings.xml file and replace it with the one downloaded in step 1 by copy-pasting the file.

Now that you have replaced the files, you can start the services of the instance by clicking the "Start Services" button. At this point, you can log into the instance and should see some data because of the files that were loaded.

3. Set up the SQL Database

Open Microsoft SQL Server Management Studio and follow these steps:

  1. Right-click on "Databases" and select "Restore Database" from the menu.
  2. From the "General" page, select "Device" as the Source and then click on the ellipsis button:
                             
  3. Select "File" as the backup media type than click the "Add" button. Navigate to the location of the downloaded Template_Demo.bak file and select it then click "OK".
                               
  4. You should now see the restored database.

4. Get your SQL Server ready to connect to Excel

The following steps are required in order to query Excel from SQL:

  1. Install 64-but Microsoft.ACE.OLEDB.12.0 driver: http://www.microsoft.com/en-us/download/details.aspx?id=13255
  2. Configure ACE OLE Properties
    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    GO
    EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    GO
    Highlight the text above, uncomment it, execute it, and then re-comment the text.
  3. Configure DB Properties
    EXEC sp_configure 'show advanced options', 1; 
    RECONFIGURE;
    GO
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
    GO
    Highlight the text above, uncomment it, execute it, and then re-comment the text.
  4. Set SQL Server Windows Service Login as Local Account

5. Link the SQL Database to PlanetTogether

From the Instance Manager, go to the Data Source tab to set up the Import Database.

  • Import Connection type: SQL Server
  • Server Name & Database Name: Enter the name of the SQL server and the database to be connected to PlanetTogether.
  • User Name & Password: You can enter the credentials used to access the database here. 

The database contains some stored procedures that will get the data from Excel and build tables. You can tell PlanetTogether to run those stored procedures before importing by:

  1. Import Options: Run SQL before import
  2. SQL to run before import: EXEC pt_import_procedures @FilePath = '<Enter the file path to the location of the Excel file Template_Demo.xlsx>'
    1. This will execute the Stored Procedure called "pt_import_procedures" and will point to the location of the Excel file. 

6. Steps inside PlanetTogether

Sign in to the instance where you have made the changes.

Click the Refresh Planning Data button found in the ribbon. The data that was mapped into the Excel template will populate in PlanetTogether. 

                                 

Watch: Setting Up Excel Integration Files

This video will walk you through the steps to set up PlanetTogether to import from an Excel file via SQL.