18.01 MS-Access

Noojee Campaigner provides some basic tools for managing your leads. However you will often have large numbers of leads which needs to be cleaned up or filtered before importing them into Noojee Campaigner.

Whilst MS-Excel is a very useful tool for doing this type of clean up it has a number of limitations including the ability to hold no more than 1 million rows  (65,000 for MS-Excel 2003 or earlier). MS-Excel also only has some very basic data manipulation tools available.

Microsoft Access is specialised data manipulation tool which is designed to store large amounts of data (up to 2 GB of data) with no explicit limit on the maximum number of rows.

MS-Access stores leads in a 'table' in a very similar manner to what MS-Excel does. Each column in the table is referred to as a 'Field' and each field has a name which can be used to reference the contents of the field. Each row in the table is also called a row as it is in MS-Excel.

Microsoft Access is technically a full relational database which can be accessed using the industry standard Structured Query Language (SQL) but it also a number of intuitive User Interface tools that allow you to manipulate the data without knowing anything about SQL or what a relational database is.

One great feature of MS-Access is that you can create 'queries' allows you to extract a subset of the data stored in a table. For example if you had load a list of every busiiness in Australia into a table you could ask MS-Access to give you a list of the businesses in Victoria that are Plumbers. (assuming you had load the State and Business Type into MS-Access ;) ).

Running a query in now way affect the leads stored in the 'table'.  This means that you can run different queries on the table at different times depending on the market you are going to target.

On neat advantage is that if you add more rows to your table and then re-run you query the query will display any matching rows from the new data as well as the pre-existing data.

This is particularly handy for Noojee Campaigner users as you can create a query to find all of the plumbers and then re-run that query each year and be assured that it will return any new data that has been added to the table in the mean time.

Extracting Data from MS-Excel

So now that I've convinced you that you need to use MS-Access the first question is how do you import your existing leads into MS-Access.

If you already have the data stored in a spreadsheet then its easy as MS-Excel can save your file to a CSV file and MS-Access can import data from a CSV file.

The first thing to do is to ensure that the first row of your spreadsheet is a heading row. This mean that each column of the first row contains a name  which will be a used as the field name in MS-Access. An example of a field name might be 'State' or 'Postcode'. The subsequent rows in your spreadsheet would have the actually State or Postcode values under the appropriate column.

e.g.

 

To save the data as a CSV file so that it is ready to be imported into MS-Access then see the following steps:

1. Choose Save As from the File menu. (If you are using Excel 2007, click the Office button and choose Save As.) Excel displays the Save As dialog box.
2. In the Save As Type drop-down list at the bottom of the dialog box, choose the CSV (Comma delimited) option.
3. Use the other controls in the dialog box to specify a file name and location.
4. Click on Save.
5. Excel may display a dialog box telling you that not all Excel features can be maintained in the format you have chosen. Click on Yes to continue saving the information in CSV format.

Extracting Data from Australian Business Directories

If you are a using for Australian Business directories (ABD), you will need to extract the data from ABD in seven separate files (on per state).

When exporting the data make certain that you include a 'header' in each file as we do in the above MS-Excel file.

Importing a CSV into MS-Access

The following link provides a step by step guide on importing a CSV file into Access 2007.

http://www.brighthub.com/computing/w...les/27511.aspx

The import guide mentions 'indexes'. An index should be added to each field that you may want to search on (e.g. postcode or business type) as an index greatly speeds up searches.

For the first CSV file, in 'Step 3' of the guide you need to select the 'Import the source data intao a new table...'. Choose an appropriate table name such as 'Sales Leads 2010'.

For each subsequent CSV file you need to select the option 'Append a copy of the records to the table'.

This will then place all of the different 'States' into a single table for easier manipulation and extraction.

Creating a query in MS-Access

Now that you have your data in MS-Access you will want to filter and extract the data so that you can import it into Noojee Campaigner.

You can create a query by using the following steps:

Step 1

Open the table created during the import process described above.  Go to the "Create" tab on the Access screen, then click "Other." Select "Query Wizard."

Step 2

Allow the "New Query" dialog box to open. Choose "Simple Query Wizard" and "OK."

Step 3

Pick the "Tables/Queries" option under the query wizard. Choose the table that contains your imported data.

Step 4

Select the "Available Fields" tab to create your query. Add the fields that you want into your "Selected Fields" list by clicking the 'right' arrow button. Choose fields such as "Contact," "Address," "Phone Number" and "City." Hit "Next" when you've chosen your fields.

If you are going to import the data into an existing Noojee Campaigner Campaign Type then make certain that the set of selected fields matches those previously imported into the Campaign Type.

Step 5

Now you can add one or more filters to your query so that it just displays the rows you require in you campaign.

Refer to the online tutorial on how to filter your query.

http://office.microsoft.com/en-us/access/ha100666111033.aspx#1

Note: To add a criteria to a query, you must open the query in Design view.

Step 6

Give your query a name, then select "Finish" to complete the wizard. Access will then show all of the lead records in a tabular view. The fields that you've specified in Step 4 will be displayed.

Step 7

Close the query when you're finished. The query automatically saves in Access so that you can reopen it later.

Exporting data from MS-Access

So you have created your query you now need to export it from MS-Access so that you can import it into Noojee Campaigner.

As Noojee Campaigner only supports CSV file you will want to export your query as a CSV file.

Step 1

Open the query

     File (Menu) --> Export

     Save as Type 'Text Files'

Don't use the 'save formated' option!

Step 2

That will bring up the Export Text Wizard

     Choose Delimited, click next

     Choose 'Comma' delimiter and " as Text qualifier.

     Choose 'Include Field names on First Row' if you want column headings

     Click next

Step 3

     Give the file a name and click finish