Import permits from excel sheets.

Owned by Edward Barrier (Unlicensed)
Last updated: Dec 11, 2023
3 min read

4 Main Steps

  1. Excel Cleanup

  2. Import to datagrip

  3. import from table to main tblPermits table

  4. Create Roles

Excel Cleanup

First thing to do is update the headers to match the following:

  • Subsidiary

  • Acronym

  • Facility

  • Program

  • Description

  • Activity

  • Frequency

  • StartDate

  • EndDate

  • DueDate

  • Instructions

  • DocsRequired

  • CurrentPermitNumber

Secondly, make sure there are no null Subsidiary, Acronym, Facility, Program, Activity, Frequency, StartDate, EndDate, DueDate, DocsRequired cells.

  • Acronym can be filled with “N/A.”

  • Subsidiary be the same 100% through, so just copy and paste.

  • The above line applies for Acronym as well.

  • I would send an email if Facility or Program is null.

  • DocsRequired can be set to 0 if it’s null.

  • StartDate = 1900-01-01 if null

  • EndDate = DueDate if null.

  • DueDate = EndDate if null.

  • If both EndDate and DueDate are null set to 2125-01-01.

  • You can get a rough idea of what the frequency should be based on the start date and due date if its null. The list of frequencies is in the ActionItem db as tblFrequency.

Import into datagrip

  • Save the excel sheet as a CSV file.

  • Import into datagrip.

  • Select the file.

  • Not sure if it matters but my settings are below.

Import into tblPermits

  • In the test environment, you can easily delete all permit data by running the following stored procedure. I do this every time I import a new excel sheet.

EXEC sp_RemovePermitData

  • After this, I run the following stored procedure. @TableName will be the new table you imported.

EXEC sp_ImportTable @TableName

Create Roles

Now you can create the new roles for the new subsidiary. You can find those here under email reminders.

Note: The acronym for the role should match the acronym in the tblPermits table linked to each task/permit.

 

Rolling the dates forward

Joseph will most likely ask for the dates to be rolled forward. All this means is he wants them to be renewed so that there are no expired tasks to start. What I do for this is start with the query below.

SELECT * FROM tblPermits WHERE PermitId IN ( SELECT PermitId FROM vw_ExpiredPermits )

  • Monthly: Just make it whatever the current month is. Sometimes the duedate will be in the next month at some point.

    • Ex: original’s EndDate is Oct. 31 and DueDate is Nov. 14th. Change to current month with duedate next month.

  • Yearly: Just change the year value.

  • Quarterly: Just change them to whatever the current quarter is. If it’s a week or so out from a new quarter or month I just change it to the next one.

    • Q1: Jan 1 - Mar 31

    • Q2: Apr 1 - Jun 30

    • Q3: Jul 1 - Sep 30

    • Q4: Oct 1 - Dec 31

  • I would renew the rest through plant focus.