(Adapted from an article in TimePilot's monthly newsletter. View our Newsletter archive.)
Your TimePilot Central software has the ability to create spreadsheets and other types of files containing exactly the TimePilot data you want—and nothing else. You set up the format you want only once; from that point onward, all you have to do is tell the software to prepare that data in that format, and the job is done.
The feature is called “User-Defined Exports.” Here’s how to set it up and use it.
- In TimePilot Central, click the “File” menu, then run your cursor over “User-Defined Export” and click “Create/Edit User-Defined Exports” in the menu that appears.
- The User-Defined Export screen will appear. (see below)
When the window opens, you’ll be in the “File Setup” section. Here you’ll tell the software whether you want to create an entirely new Export or to modify one you’ve already created, as well as the kind of file you want to create and where you want to put it.
Let’s assume you’re creating your first Export, and you plan to use a spreadsheet program like Microsoft Excel to view the data. Here’s how to do it (item numbers below correspond to the red numbers in the screenshot):
- At the top of the User-Defined Export screen, click the button next to “Create New Export Definition.”
- Name the Export. This will be the name you see in TimePilot Central menu when you use this feature. Let’s call it “My New Export.”
- In the “File Type” box, choose “Comma Separated.” (Optional explanation: This choice means that when TimePilot creates the file, each piece of data will be separated by a comma, like this: LastName,FirstName,Department,RegularHours,OTHours, etc. Spreadsheet programs know to put each piece of data between commas into a separate cell. Other programs might require that the data be separated by tabs.)
- In the "Default Export Location" box, choose where you want to save the Export. Each time you use this Export, the file it generates will be saved here. You can save it anywhere you want, but for now let’s keep the location suggested in the screenshot (C:\tpData\Export).
- In the "Default File Name" box and "Default File Extension" box, you’ll name the file and its extension. We’ll keep the defaults here.
That’s it for the “File Setup” screen. Now click the “Field Selection” tab at the top of the box. This is where you tell TimePilot exactly the data you want to export.
- In the “Information Type” box, choose one of the three options. Each time you choose an option, you’ll see the “Available Fields” choices change at the bottom left of the screen (Item 5 in the screenshot above). For this exercise, we’ll click “Transaction Detail.”
- In the "Time Format" box, choose the time format you’d like to use. For this example, we’ll choose "Hours and Minutes."
- Do you want the name of each field to appear at the top of each column of the spreadsheet? We’ll say yes, and click the checkbox next to "Include Header."
- Do you want “Ignored Transactions”—clock-ins or clock-outs that you have told the TimePilot software to ignore when calculating employee hours—to show up in the Export File? For this example, they'd just clutter things up, so we’ll click the checkbox so they won't appear.
- Now we choose the data that we want to appear in the export file. If you clicked “Transaction Detail” in Step 1 of this section, you’ll see a list of 39 types of data under “Available Fields.” If you clicked “Transaction Summary,” you’ll see 20 types of data and if you clicked “Profile Detail,” you’ll see 32 types of data. For this example we've clicked "Transaction Detail," so we have 39 types of data to choose from.
- We've decided that we want to see these columns, in this order, on our spreadsheet:
- Company Name
- Department Name
- Long Name (the name of the employee that appears in their Employee Profile, as opposed to what appears on the screen of the TimePilot Standard, TimePilot Vetro or TimePilot PC clock)
- Regular Total (of hours)
- Overtime 1 Total (of hours)
- Overtime 2 Total (of hours)
- Overtime 3 Total (of hours)
- Weekly Total (of hours)
- First, scroll down the “Available Fields” until you find “Company Name.” Click it to highlight it and click the “Add” button. You’ll see “Company Name” appear in the “Included Fields” box on the right side of the screen. One thing to remember: The columns will appear on the spreadsheet in the order you choose them here.
- Now look in the “Available Fields” list for “Department Name.” Highlight it and click “Add.”
- Continue the process for each of the types of data you want to see (Long Name, Regular Total, etc.).
- When you’re done, click “Save.”
That’s it! Now you can use your new export whenever you want. You can also set up other exports that will contain different data by following the directions above.
Using the export
Here's how to use your new export:
- You’ll generally use your export to display data from extracted pay periods, so open an extracted pay period in TimePilot Central (Periods Menu > Open Past Time Period).
- Click the “File” menu in TimePilot Central, then run your cursor over “User-Defined Export.” Your export(s) will show up on the menu that appears.
- Click the name of the export. You’ll be asked where to save the file (the default location you specified when you set up the export will appear; you can change it here if you want) and what you want to name the file. Please note: If you keep the defaults, every time you export data, your computer will create a file called Export.csv and place it into the C:\tpData\Export folder on your computer. That means that if you exported your data last week, and are going to do it again this week, both files will have the same filename and last week’s data will be overwritten. It’s a good idea to add the date to the name of the file; something like “Export-7-6-2016.csv”
- Click “Save.” The export will be generated. Then open your spreadsheet program, browse to the C:\tpData\Export folder and take a look!