Importing Microsoft Excel Lead files
How to get error free imports every time when you get your leads as an Excel worksheet (XLS) or CSV file.
The lead file import features of LMS were designed to be as simple and flexible as possible and yet reduce opportunities for errors when you import your sales leads. This document contains detailed instructions on how to get perfect results every time you import leads into LMS. If you would like a PDF version of the below instructions click here (right click on the link and select Save Target As to save the file on your computer, then just navigate to the folder where you would like to save the file).
Assumptions:
The following instructions assume that you have a BASIC knowledge of how to use Microsoft Excel (2000 or later) and that you understand how to open and save files that can be viewed and edited with that program. Furthermore, we must assume you have a basic understanding of how to navigate your computer's file storage system using the built-in Windows Explorer program. If you need help with that issue, please call LMS support for advice on how you can acquire these critical skills as quickly as possible.
| Definitions used in this document |
| Windows Explorer (WE) |
A fundamental part of the Microsoft Windows operating system (all versions) that allows you to view and navigate your computer file system to locate, save, rename, move and delete files AND folders on your computer. |
| Lead source file (LSF) |
The electronic document you receive from your lead supplier -- typically as an attachment on an email message. |
| Lead Source Database (LSD) |
The portion of LMS that provides a common area to store all relevant information about your lead suppliers and the files they send to you that contain sales leads. This system is used to tell LMS WHERE (on your computer file system) you store the files you receive from each supplier and what type of files they are. Once you have set up a lead supplier record in the LSD for a particular supplier, you need not be concerned with it again. So it's a one-time task for each lead supplier. |
| Target LMS folder |
The initial storage destination for all records from your LSF. In LMS, that destination is ALWAYS the Prospect Folder, which is the point of entry for all prospect data. |
| Row (record) |
The HORIZONTAL series of data items in your lead file. |
| Row Label |
The box at the far left position on a record. It contains the row numbers (like "1", "2", "3", etc). This box is NOT part of the data you will import. |
| Column (field) |
The VERTICAL series of data items in your lead file. |
| Column label |
The box at the very TOP of each column of data that contains the column letter (like "A", "B", "C", etc). This box is NOT part of the data you will import. |
| Cell |
A box that appears at the intersection of a Row (also called a record) and a Column (also called a field). These boxes contain a specific item of data, like a name or a phone number or an email address, etc. |
| XLS File |
A Microsoft Excel Workbook file -- so called because they always have a ".xls" extension at the end of the file name. This type of file can ONLY be viewed/edited with Microsoft Excel. |
| CSV file |
Stands for "Comma Separated Values" -- so called because they always have a ".csv" extension at the end of the file name. This type of file can be viewed/edited with a common text editor like "Notepad" OR by opening it with Microsoft Excel. |
| Comma, quote delimited text file (CQD) |
A structured, plain text file that ALWAYS has a ".txt" extension at the end of the file name. So called because the columns are separated by commas and the individual data items are "delimited" by double quote characters. This type of file can be viewed/edited with a common text editor like "Notepad". |
NOTE: Even though you CAN import CSV and CQD files into LMS, this document will ONLY discuss importing XLS files. If you need to import CSV or CQD files, please call LMS Support. CQD files are actually the IDEAL type of file to import into ANY database but they are not commonly offered by the smaller lead suppliers. We have no idea why this is because they are really easy to create. In fact, the web form that captures the prospects information can be set up to create the file FOR the lead supplier.
The number one challenge presented by the lead import process is the condition of the source file. MOST (not all) of the files you will receive from various lead suppliers are in "XLS" format. This is a very popular format that is used by most suppliers because most people already know how to use Microsoft Excel (for good reason -- it's simply the best). Unfortunately, some of the features and power of Excel that make it so popular for numeric analysis also make it difficult to import into ANY data base. However, those problems can be easily overcome by following the instructions below.
NOTE: ALL, repeat ALL of the problems you encounter when importing XLS files into a database like LMS have nothing whatsoever to do with LMS. They are caused ENTIRELY by the lead supplier not understanding how to create an Excel file that is INTENDED for import into a database (assuming that YOU have not modified the file yourself) as opposed to one created for printing. We strongly recommend that you contact your lead supplier and ask them to read this document and start sending you files that ARE properly prepared for the import process. If needed, you may ask them to call LMS support. We will gladly show them exactly how to create a lead distribution file that is properly formatted for error free import into ANY data base. We offer this service free of charge for anyone who distributes lead files to LMS users.
The goal of the import process is to transfer the records in your LSF to the proper location in the LMS Prospect Folder. The scope of this document (and your limited time) does not allow a full discourse on the REASONS why these steps are required. Just heed the following steps and your files WILL import correctly every time!
ONE MORE NOTE: This may LOOK like a lot of detailed instructions, but, once you have done this a few times, it only takes about three or four minutes to accomplish all of these tasks. In other words, it is MUCH simpler to do that it is to explain.
The steps are divided in to two sections:
A. Prepare Your Lead File:
- Starting in your email program Inbox, locate and open the message from your lead supplier that has the lead file attached.
- Right-Click on the attachment icon (in Outlook, not Outlook Express) and click on "Save As". This will open the standard Windows file save-navigation dialog box.
- Use the drop-down list on the "Save in" box to locate the folder in which you save files from this lead supplier. Once your target folder is displayed in the "Save in" box, click on the "Save" button to save the file in that folder. We strongly recommend that you save the file into the folder structure recommended in the help boxes on the LSD form in LMS. Please call LMS Support if you need help with this.
- Minimize your email program and open Windows Explorer. You can open Windows Explorer from inside of LMS by clicking on the Tools menu and then selecting "Open Windows Explorer". Otherwise, you go to Start > Programs > Accessories > Windows Explorer.
- Locate the file you just saved in step 3 above and double-click it to launch Microsoft Excel and display the file contents.
- Make sure the first row of the data contains a name for each of the data columns. Make sure that none of the column names has a space to the left of the name text.
- Make sure there are no blank rows or columns inside the data block (the area of the worksheet that contains your lead data).
- Remove any solid grid lines, bold fonts, colors from the data rows or columns. ALL of the data (including the column name row) should be nothing but simple, unformatted plain text.
- NOW you are ready to do the final step of this section -- copy the DATA ONLY to a new worksheet file. This is the step that will guarantee that there is nothing hidden in the data or the empty rows and columns that can trick LMS into thinking there are more records than you actually have in your lead file. The reasons for this are too complicated to list here. So here goes:
- Look at the TOOL BAR just below the Excel Menu bar. There should be a small icon near the left hand side of the too bar with a small white sheet of paper pictured on it. When you hover your mouse pointer over it, you should see a small flag pop-up showing the word "New". Click that icon to create a brand new, empty worksheet like this picture:
- Just to the left and above the A1 cell, at the intersection where the row labels and the column labels meet, there is a small box. Click that box to SELECT the entire worksheet (all cells turn blue).
- CRITICAL STEP: WHILE the cells are all selected, click on the Format menu > Cells > Number tab and then click the "Text" entry on the list below the Number Tab. Click OK to format all selected cells as text data type. This is what the Format Cells Box looks like:
- Now that all the cells in the new worksheet are PRE-formatted as TEXT, use the Window menu to select the original worksheet.
- In the original worksheet, select ONLY the data block (NONE of the blank columns or rows should be selected). You do this by placing the cursor in the A1 cell, then hold down the left shift button on your keyboard with your left hand and use your right hand to move press the RIGHT arrow key until all the cells in the first row are selected. DO NOT select any of the blank cells to the right of the last column of data. THEN, while STILL HOLDING THE SHIFT KEY, press the DOWN arrow on your keyboard until all the ROWS of data are selected. NOW you should have a worksheet that looks like this:
- Now RIGHT-CLICK on the selected area and click on "COPY". A flashing line should appear around the selected data to show that you have copied that data to the Windows clip board.
- Now use the Window menu to switch back to the new worksheet.
- In the new worksheet, RIGHT-CLICK on the A1 cell and click "PASTE". This will paste the leads data into the new worksheet.
- Save the new worksheet in the same folder as the original worksheet as the same file name but put the word "Fixed_" in FRONT of the old file name.
- You are now ready to close Excel and go back to LMS to import the file.
B. Import the file into LMS:
- You MUST have a valid record in the Lead Source Database (LSD) that tells LMS the name of the supplier (shortest possible name) AND the folder name where you just saved the file you have prepared per the above instructions. Of you need help with that, just open the LSD (Reference > Lead Source Database) and click on the question mark button [?] for instructions on how to use the LSD.
- When you are SURE you have the LSD record for the lead supplier properly set up, then click the LMS menu "ADMIN > IMPORT LEADS > DO Import now".
- Click the [?] button on the form and follow instructions. Pay particular attention to the instructions on the Right side of the form when it appears.
- If you need help, please call LMS Support, but DO NOT CALL until you have read ALL of the help on the Help menu about importing leads and on the Import form under the [?] button.
FINAL NOTE: ALL of the steps in section A above would be unnecessary if your lead supplier would just call LMS Support to find out how to create and distribute a lead file that is intended for import into a database.
Back to top of page |
