Mailing Lists File Conversion
When ordering from a Producer Mailing List, the information you receive will be emailed in zip compressed attachment. The information in the zip attachment is in an ASCII text file format. This file can be converted and placed onto an excel spreadsheet by following the procedures listed below:
- Copy the file to be imported to your local C drive or your local network drive.
- Open Excel.
- In Excel, go to the toolbar and select File, Open.
- Select the Files of type drop down box and select "Text Files".
- Open the text file to be imported that is located on your C drive or your local network drive as shown on step 1.
- Make sure that the "Fixed Width" radio button is selected in the Original Data Type box.
- Press Next once. Be sure there are no vertical lines with arrows between the address and city columns. If there are, please remove them.
- On the Qualification Data extract text files, any files that start with RGENI, NGENI, RGENA, NGENA on the final columns should also list dates in this format. Example: 17-SEP-9830-SEP-1203-JAN-11. The dates are to be separated. Using the above example, separate the dates as follows: 17-SEP-98 30-SEP-12 03-JAN-11 by placing a separator, which is an upwards facing arrow, between the 98 and the 30 of the first and second date, between the 12 and 03 of the second and third date.
- Press next once. Check that the "General" radio button is selected in the Column Data Format box. In the column that contains the license numbers, highlight the column and press Text. Then press Finish.
- Your file will now be loaded into Excel. However, the document may need additional formatting.
- To format the column width, click the cell that is on the upper most left corner of your screen (above the "1" and to the left of column "A"). This will highlight all data. Go to the toolbar and select Format to see the drop down menu. Select Column and select AutoFit.
- Once the desired format is achieved, save file as an Excel spreadsheet. To do that, select File, Save As. Change the Save as type to "Microsoft Excel Workbook". You can also rename the file at this time.
- You're done!
- Please be aware that several of the California Department of Insurance's files are far too large for the early versions of Excel. Early versions will accept approximately 65,000 records. The additional information will be lost during the conversion process. To remedy this problem, please ensure that you have Excel 2003 or later before you convert the zip attachment.
- Life-Only Agent and/or Accident and Health Agent
- Property Broker-Agent and/or Casualty Broker Agent
- Admitted Company
- Continuing Education Deficiency Renewal
- Order Forms
- Frequently Asked Questions
- Price List
- File Conversion
- Return to Producer/Company List Request Main Page
If you need further assistance, please email your inquiry to the Producer Mailing List Technician at email@example.com or leave a message on the voice mail box at (916) 492-3063 at the California Department of Insurance Producer Licensing Bureau.