Special Import
Go to Import->Special Import. This is where special import programs are set up for certain customers. (If you don't see a menu selection here, then this section does not apply to your committee.
Creating an Excel file from a Table
If you're viewing website data in table format, you can usually cut-and-paste the information in Excel, which recognizes most tables. Here's how:
1. Select your table. Go to the web page that includes your table. To select the entire page, hit <Ctrl>A . Or use the mouse or the <Shift>-arrow keys to select certain parts of the page or table. Hit <Ctrl>C to Copy the table. 2. Paste your table. Open a blank Excel worksheet. Place the cursor in the upper-left cell. (Cell A-1) Hit <Ctrl>V to Paste the table into your worksheet. 3. Clean up your table. Get rid of unneeded rows and columns. Highlight the sections and hit the <Del> key. To delete a column, go to the top of the column, and click on the column label (A - ZZ) to highlight an entire column. Go to the left and click the row label (1 - 65,000) to highlight the row. Hold down the <Shift> key while scrolling up or down with the arrow keys to highlight multiple rows. Note: If you're saving a table for auto-import into the Filpac program, make sure you save the header row, i.e. the one with the column labels (First Name, Last Name, Address, etc.). Otherwise you will lose the first row of data because most Filpac import routines start at Row 2, assuming the first row is a label. If you're saving the table as a CSV file (see below), don't worry about changing fonts because the CSV format saves only the text. However, you'll need to make sure that all columns are properly formatted. Click on the upper-left square (above Line 1 and to the left of Column A) to select the entire worksheet. Look for a column that's truncated (i.e. not wide enough) and drag the width until it is wide enough. Then, in the header row (above Line 1), double-click on the margin between two columns to automatically transform each column to its proper width. Do the same for lines. Don't forget to go to the bottom of the table and delete unnecessary lines. Hit <Ctrl><End> to go to the end of the spreadsheet. 4. Save your table. While there are many file formats that can be read and saved by Excel, the two most common are Workbook (XLS extension) and Comma-Separated Values (i.e. "delimited", with CSV extension). Go to File->Save to save the file as an Excel workbook. If you wish to save the file as a CSV, follow the instructions below.Saving an Excel worksheet as CSV
Most of the Special Import programs involve a special file with the extension CSV, which means comma-separated values. Also known as "delimited", this format stores data in fields that are separated with commas. Some fields are also enclosed in quotes in case there are commas within each field that might be mistaken for a field separator.
Here is an example of a header row and data line from an excel worksheet, saved in CSV format: File,FirstName,LastName,Address1,Address2,City,State,Zip123,Joe,Curran,"4900ReedRd.,Ste209",,Columbus,OH,43220
You can see the file this way when you open it using Notepad. Notice that only the text is saved, and that the first address line is enclosed in quotes so that the program knows the comma isn't a field separator. Notice how the blank second address line (Address2) is represented.
Go to File->Save As to save an Excel workseet in Comma-Separated-Values (CSV) format. The program will then ask you to identify the folder and file name. If you're saving the file for import into the Filpac system, you should save it in the Transfer subfolder: x:\fr\transfer (x: is the appropriate drive, which would be C: on a single-user system, and E: through Z: on a network.) This is the folder where most special import programs first look. The name that you give the file should be consistent. For example, if it's a payroll import that is performed regularly, the date should be part of the file name: PR_20140509
The way to represent a date in numeric format is the year followed by the month and day. If the month or day is only one digit, it shojuld be preceded by a leading zero. The example above demonstrates the proper way to represent May 9, 2014. It should not be represented as "201459" because the file name will not appear in the proper order when sorted.Import from Payroll file
If your system is set up for import from a payroll file, you'll find a utility under Tools->Import/Export->Special Import. It was written to enable the easy import of a comma-separated values file that contains five fields, in the following order:
NameDescription
Employee IDCURRJO
Name ("Last, First)CURRAN,JOE
Date9/26/2014
Account codePAC_CONT
Account descriptionPACContribution
Amount100.00
The system will use the ID number to fill in the name and address of the record. This process is useful for PACs that collect money via payroll deductions and receive an electronic report (an Excel file, for example) of these receipts. The import file used by this utility is a comma-separated values file (otherwise known as "comma-delimited" or CSV) in the structure enumerated below. It is not intended to be a "do it yourself" import program because of the difficulty in manipulating other files and structures. In other words: don't try this at home unless you know what you're doing.
Important note: If you cannot easily follow the instructions below, or if you don't know how to make and restore a backup, then you probably should not be trying this, because it makes permanent changes to your Filpac data files. Your Filpac warranty specificially does not apply to data file problems caused by using the Special Merge feature of the program. You can always send your file to us and we'll convert it for only a small charge.
Creating your own special merge file for import.
1. Make a backup of your current Filpac dataset, which is the collection of files in your FR folder with the extension *.ksh. The easiest way to do this is with PKZIP or WinZip. If you need to revert to your backup, you should run Data File Reorganization after you re-enter the program.
2. Create your import file, making it a comma-separated values file using the exact structure below. The most popular method is to create the file in Excel, and then save it as a CSV file in the c:\fr\transfer folder. When you click the Search button, your file will show up if it is located in the \fr\transfer folder and it has CSV for an extension.
3. Set up each record so that the NAMES are separated into last and first. The proper format is LAST, FIRST. ("CURRAN, JOE"). (If there's a middle name, leave it next to the first name and the program will separate it.)
4. Make sure that all appropriate Employee ID numbers have been entered into your Filpac system. Otherwise, without a valid Employee ID from which to look up the employee, the system will leave the addresses blank.
5. The import program uses the default Election Period. Go to Setup->Committee Setup and click on the Accounting tab. There you will see the default election period. If it's wrong, change it. If you need to add a new election period, go to File Maintenance->Support Codes.
6. Properly format the DATE field. For example, October 15, 2015 should be entered as "10/15/2015".
7. Don't worry about lower-case because the import program will convert from caps to lower case.
8. If the import program cannot find a match, it will add a new record. Which brings us to another issue: duplicates. The import program is not perfect and will create duplicates out of similar addresses. Before running this program, you should check the Last Number Assigned in Filpac (go to Data File Maintenance->Individuals and click on New Record to find the next file number), write it down, and then go to Tools->Duplicate Processing once the import process is finished to resolve any duplicates.
9. If the process fails despite your best efforts, then you can always re-load your backup before trying again. Make sure that you run Data File Reorganization after restoring your backup.
No.FieldTypeMaxWidthContents
1EMPLOYEEIDCharacter11mustbeuppercase
2NAME Character30LAST,FIRST
3ITEMDATECharacter10MM/DD/YYYYformat
4ACCOUNTCODECharacter10mustbeuppercase
5ACCOUNTDESC.Character40
6AMOUNTNumeric109999999.99format(makesuretoremovethe"$"symbol)
Here's an example of one record:
CURRAJO,"Curran,Joe",10/15/2015,PAC_CONT,PACContribution,25.00
Note: The import program will start at the second record. It assumes the field names are in the first record.