In Elliott Firearms Software, there's a menu item under A/R -> Maintenance -> Customer File -> FFL.  FFL stands for Federal Firearms License.  Every single US company involved in the firearms business -- including the manufacturers, importers, distributors and dealers - -should all have a valid FFL number for each physical premise.  The firearms business is regulated by the BATF (Bureau of Alcohol, Tobacco, Firearms and Explosives).  Every month, the BATF publishes the current month's FFL listing on their Web site at the following URL: 
      https://www.atf.gov/content/firearms/firearms-industry/listing-FFLs

You may download the current FFL listing and import it into your Elliott FFL database.  Since a new FFL listing comes out once per month, you may want to do this on a monthly basis. The Elliott FFL database will help you to determine if: (1) a customer's FFL is valid, and; (2) a customer's address matches the FFL license address.  This will help you not only to be BATF compliant, but also to be proactive in enforcing BATF regulations.

On the BATF Web site, you should download the "Complete Listing" instead of state by state.  There are two types of files to download: XLS and TXT.  The XLS is in an Excel spreadsheet file format.  We like the XLS format because it can be saved as a CSV file for import purposes. But there's an issue here that has yet to be resolved by the BATF.  The XLS file is in a Microsoft Excel 2003 format.  It is limited to a maximum of 65,536 rows. The full FFL list as of September 2014 has 77,405 records.  Therefore, if you use the XLS format, you will lose some records.  You can optionally download the XLS file state by state and append them together into the XLSX format.  But this is a tedious process. Therefore, your best option at this time is to download the TXT file.

The BATF has made some recent changes in the TXT file format.  Before September 2014, the TXT file was in a TAB delimited format.  In September 2013, the TXT file changed to a fixed-length ASCII format.  We prefer the TAB delimited format since the column is well defined.  But it is not up to us to tell the BATF what to do with their downloaded files. Since Elliott's FFL import is based on the CSV file format, this document  will show you how to convert a fixed-length TXT file to a CSV file. 

We suggest you right click on the complete listing for the current month TXT file in the above URL page and choose "save target as" to download the TXT file. After you download the TXT file, save it in a place that your Elliott Firearms Software can access.  Use Excel 2007 or higher to open this TXT file.  For example, the current naming convention for the September 2014 TXT file is 0914-ffl-list.txt.  In Excel 2007 or after, choose to "Open."  You must specify "Files of Type" as "All Files," otherwise, you will not see the TXT file.  Highlight the TXT file, and choose Open.



Excel will recognize this file as a "fixed-width" file.   Accept it, and choose "Next" to proceed.



In the next step, it is critical that you review Excel's Text Import Wizard on how it determines each column.  Even though Excel will try to do its best, it will not be entirely correct.  You will have to help Excel determine where each column starts and ends.  In the next example, you will see Excel make the "Business Name" into three columns.  This is wrong.  You need to double click on the wrong column borders to remove them.  Use the first row's column heading as a guide to help you do this.  Once you are done, click "Next."



In the next step, Excel will ask you to determine the data format of each column. The program will default each column to the "General" format, but you want to change it to "Text."  The reason is that the "General" format will drop leading zeroes.  Therefore, a zip code of 01234 will become 1234 after Excel imports the text file into a spreadsheet format.  This will create problems. Even though you could leave some of the columns as "General," for the sake of consistency we advise you to change every single column data format from "General" to "Text."  Once you are done, you may choose "Finish."



Now Excel will bring up the fixed-width TXT file in the spreadsheet.  If you use Excel 2003, you will receive a message indicating that not all data is loaded because Excel 2003 has a limitation of 65,536 rows.

Since the Elliott FFL CSV file assumes there's only one heading row, you will need to delete the second row that contains the dashes.  Highlight the second row and right click to choose "Delete."



Now you can save this file in the XLSX format.  Choose "Save as." Then in the "Save as type," choose "Excel Workbook (*.xlsx)." Don't save it in the 2003 format because the maximum limit for an XLS file is 65,536 rows.



Then you should save this file in a CSV format.  Choose "Save as," and in the "Save as type" field choose "CSV (Commad delimited) (*.csv)" and "Save."



The reason we ask you to save in both XLSX and CSV file formats is because CSV is for Elliott import purposes.  You should never edit CSV files in Excel directly.  If you do, then when you choose to save, the leading zeroes of the column will be dropped off and that will cause problems.  Therefore, we suggest that our users always edit in the XLSX format.  Only when you are ready to import, at the last moment, should you choose to save as the file in CSV format.  Now, close your Excel so the CSV file is no longer opened.

To import this FFL List CSV file, go to Elliott Firearms Software, from A/R -> Maintenance -> Customer File -> FFL -> Licensee CSV Import. Choose "Pre-Interface" first.  Pre-Interface is for you to verify the import CSV file and give you a print listing so you can confirm if everything is OK.  In the CSV File Path & Name, either type the file path and name, or right click on the field and choose "browse" to select the file.



There's no need to print out the pre-interface FFL import edit list.  You can just choose to print to the screen.  Once the report is done, scroll to the end of the report to check the number of records and see if there are any warnings or errors.  As of September 2014, there are a total maximum of 77,405 records allowed.  You should not import if there are any errors.  For each warning, find out the reason and determine if it is OK.

Some of the warnings we have seen so far are actually caused by the BATF's TXT file itself.  In the next example, you can see that an FFL licensee's city, state, and zip code are out of alignment.  We suspect this has to do with the special symbol "1/2" in the address field.  

In other examples, we have seen the BATF listing showing the mailing state as "NULL."  Since "NULL" is four characters and exceeds the maximum two-digit state abbreviation, the import utility gives a warning. You can ignore this kind of warning.

In any case, you can manually fix this problem in the TXT file and go through the procedures in this document again.




If the pre-interface result looks OK, then you may proceed with "Interface" and import the CSV file to your Elliott FFL database.