Data Extract/Excel Export

Ease-E-Club

Client Management Software by Computerease

Excel Extracts

The Data Extract/Excel export was designed to allow the end user to select fields from the client data file to be exported to an Excel worksheet. Export conditions may be specified through a Conditions or Filters function. Defined extracts may be saved and recalled for use as required.

Extracts may be Exported to Excel, Printed, Viewed, Exported to Text, PDF or HTML.

NOTE: A version of the Microsoft Excel application MUST be installed on the computer to allow exporting to Excel. Problems may occur if attempting to run this over a network that does not have the correct permissions set.

We cannot provide training on Excel functions because this is an external application developed by Microsoft and may have features that are different due to version changes since its’ initial release.

Excel is a VERY powerful tool. It can be used for everything from adding up a simple list of numbers to complex sales analysis. This is why all reports can export to Excel to allow for unique manipulation using the Excel tools available, like sorting.

Many good visual Excel learning books are available from local book stores.

The only elements that are currently available are fields within the client file. Other data files may be added as development is completed.

The word "Client" is used throughout this document as the "Client Display Name". If you have chosen a different term, such as "Member", it will be used by the extract screen for both on screen prompts and field names.

Saved Extract:

Defined Extracts/Reports may be saved and recalled by assigning an "Extract I.D." code and Description. To recall a saved extract either enter the Extract I.D. or use the query button to view the list of saved extracts and select from there. The Description will be used to name the extract file created. Saved extracts can also be changed once created and may be copied to a new I.D. An "Extracts List" button to the right of the Name/Description field can be used to obtain a list of all saved extracts with their parameters.

Extract Sequence:

By default the extract will be sequenced By Number however you may also chose the By Name or Other (RuntimeSort) options. As Excel allows you to easily resequence data leaving the default sequence is usually sufficient. However if you will be printing the extract as a report you may want to pick one of the other options. The "Other (Runtime Sort)" option will allow you to select any available field from the list and sort the output using this field. Examples would be an extract by Client Age or by ZIP/Postal Code. See "Field Selection" below for instructions on using the field selection screen.

Extract From / To Range:

Subject to any conditions or filters, by default the extract will include all records in the sequence selected. You can restrict the records included by setting the Extract From and To values. For example for an extract sequenced by By Number to include only clients with numbers between 0 and 5000 you could specify a From/To range of 0000000 to 0005000. Similarly for an extract sequenced By Name to include only clients with last names starting with an A or B you would specify a From/To Range of "A" to "B". If the Extract Sequence is Other (Runtime Sort) the From/To range can also be specified as long as you understand how the field is stored. As Excel allows you to easily resequence data leaving the default From/To range is usually sufficient.

Page Break and Total Page Only:

These options are only applicable when using the "Other (Runtime Sort)" extract Sequence and when outputing as a report. Otherwise they are ignored.

Inactive Clients:

This option allows you to easily include or exclude Inactive Clients from the extract. The default choice for this option is "Exclude" and the other choices are "Include" and "Only". The "Status" field is referenced for this filtering and is set to either "Active" or "In-Active" for each client.

Field Selection:

Fields are chosen using the "Field Selection" button which then displays a screen with the available fields listed alphabetically on the left and the selected fields on the right. In the middle several action buttons are used to manipulate the list of fields selected. Fields may be added to the list by highlighting them and pressing the "Add" button or by double-clicking the field. Similarly fields may be removed from the list by highlighting them and pressing the "Drop" button or by double-clicking the selected field. "Add All" and "Drop All" buttons are also available. To change the order of the selected fields the field is first highlighted and the "Move Up" or "Move Down" button pressed to move the field. Once all fields are chosen press the "O.K." button. The selected fields are then checked and if any are password protected the user will be required to enter the correct password in order to continue. If numeric fields are chosen the user will be asked if sub-totals and/or grand totals are required. These totals are suppressed for Excel Exports.

Conditioning (Filters):

Conditioning allows you to set up to five conditions which must be met before a record will be included in the extract. Conditions are established by pressing the "Conditioning (Filters)" button. If conditions already exist a warning will be given to indicate the existing conditions will be replaced. Next the field selection screen displays. Only ONE field may be selected for each condition. The selected field does not have to be one of the fields chosen for the extract however in most cases it should be to allow easier verification that the conditioning worked as expected. Once the condition field is selected the "Condition Rule" is chosen and the condition value entered. Condition rules available are "Equal To", "Not equal To", "Does Not Contain","Does Contain", "Less Than", "Greater Than", "Found In List" and "Not In List". The selected field will be evaulated against what is entered in the "Condition Value" field. Enter this value or string of characters in the "Condition Value" field. Once the rule and value are specified press the "O.K." button and a window will display which allows you to either indicate another condition is required or to indicate the conditioning is finished. The buttons displayed are "AND", "OR", "DONE" and "Back". Pressing the "DONE" button indicates conditioning is complete while pressing the "Back" button will return to re-start defining the condition. Using the "AND" option indicates that the current condition and the next condition MUST both be true in order for the record to be included. Using the "OR" option indicates that EITHER the current condition OR the next condition MUST be true in order for the record to be included. When using more than two conditions combining "AND" and "OR" in the filtering may yield unexpected results depending on your understanding of how logical operations are evaluated. Some experience with or knowledge of conditional logic statements will help with this function. Once the "DONE" button is pressed the conditions defined are displayed in the conditions list field of the main screen.

When selecting the "Found in List" and "Not in List" Conditions you must enter a comma separated list of values in the Condition Value field. The Lookup at the end of the Value field will allow selection of multiple codes. As an example, if the Membership Type field was selected the Lookup would display a list of Membership Type codes to select from and then bring that list back into the Value field.

Field Notes:

If password protected client bank or credit card fields are selected the user will be required to enter the valid password in order to proceed.
For any fields selected which are recognized as numeric the user will be asked if totals are required. These totals are suppressed for Excel Exports.
When referencing Date Fields to condition or filter an extract use the YYYYMMDD format to enter the date in the "Condition Value" field. Internally all dates will be in this format when tested for the condition.
ALL Date Fields are exported in “YYYY-MM-DD” format which Excel turns into a date field in the format of the local country settings (DD/MM/YYYY, MM/DD/YYYY, YYYY/MM/DD). Within Excel the date columns can be sorted using the SORT option on Excel Tools menu.

Action Buttons:

The following action buttons appear on the lower part of the screen and are used as follows:

Export Print:

NOTE: Before starting the export to Excel, all Excel spreadsheets must be closed.

After you have selected the fields to export, press the “Export Print” button. If the extract has not been assigned an I.D. you will be asked if you would like to assign an I.D. and save this extract for future use. If you answer yes to this question you will then need to enter the I.D. and a description or name for the extract. If you do not want to save this extract you will still need to enter a description or name which will then be the name assigned to the extract file created.

The standard "Print Selection" window will appear and you can select the Excel Export or any of the other Print Selection Options.

If the export is to Excel then when the export is complete the following message will appear:

“Records have been exported to Excel file: ‘C:\Tmp\<extract name>.xls”

“Exported ## records out of ## records to Excel.”

“Excel will now be started”

When the “OK” button is pressed, an Excel spreadsheet will be displayed with your selected data lined up in columns.

Note: If you see a column in the Excel spreadsheet with a series of “###”’s, this means the column display width is too small to display the number; expand the column width by clicking at the top of the column edge and dragging it to the right.

Save:

Pressing the "Save" button will save the current extract using the I.D. which you provide. If the I.D. record already exists a warning is given asking that you confirm it is O.K. to overwrite the existing record.

Copy:

Pressing the "Copy" button will copy the current extract to a new I.D. and name which you select. If the copy to I.D. already exists a warning is given asking that you confirm it is O.K. to overwrite the existing record.

Delete:

Pressing the "Delete" button will delete the current extract record from the saved extracts file once you confirm the action.

Clear:

Pressing the "Clear" button will reset all of the current extract parameters on the screen.

Navigate Saved Extracts:

Four navigation buttons are provided on the bottom of the screen and these can be used to toggle between all of the extracts you have previously saved.

EXAMPLES of Extracts with FILTER:

Export a List of Active clients Only:

1. Press the “Field Selection” button

2. Select “Member/Client Nbr” field

3. Select “Member/Client Name-Full” field

4. Select any other fields you need included in the spreadsheet and then press the "O.K." button.

5. Select the "Exclude" option from the "Inactive Clients" parameter field.

6. Enter an "I.D." to assign to this extract. (optional)

7. Enter a Name/Description for this extract. (required)

8. Press the “Save” button at the bottom of the screen. (optional)

9. Press the "Start" button at the bottom of the screen to start the extract.

10. Select "Excel Export" from the "Print Selection" window.

11. Once the data is exported a message box called “Export Complete” will appear showing the number of records exported.

12. Excel will now load with the exported records displayed.

13. The fields selected will appear for all Active clients.

14. You can save this file or print it as needed.

Export a List of Active clients Only Excluding No Contact clients:

1. Press the “Field Selection” button

2. Select “Member/Client Nbr” field

3. Select “Member/Client Name-Full” field

4. Select any other fields you need included in the spreadsheet and then press the "O.K." button.

5. Select the "Exclude" option from the "Inactive Clients" parameter field.

6. Press the "Conditioning (Filters)" button.

7. Select the "Contact Preference" field and press OK.

8. Select the "Not Equal to" Condition Rule.

9. Enter "NO Contact" in the Condition Value field (without the quotes; Exactly as shown with capital letters and small letters).

10. Press the "O.K." button to accept the condition.

11. Press the "DONE" button to end condition entry. The main screen will display.

12. Enter an "I.D." to assign to this extract. (optional)

13. Enter a Name/Description for this extract. (required)

14. Press the “Save” button at the bottom of the screen. (optional)

15. Press the "Start" button at the bottom of the screen to start the extract.

16. Select "Excel Export" from the "Print Selection" window.

17. Once the data is exported a message box called “Export Complete” will appear showing the number of records exported.

18. Excel will now load with the exported records displayed.

19. The fields selected will appear for all Active clients who do not have "No Contact" in their "Contact Preference" field.

20. You can save this file or print it as needed.

Export a Telephone List of Inactive clients:

1. Press the “Field Selection” button

2. Select “Member/Client Nbr” field

3. Select “Member/Client Name-Full” field

4. Select "Phone-Home" field.

5. Select any other fields you need included in the spreadsheet and then press the "O.K." button.

6. Select the "Only" option from the "Inactive Clients" parameter field.

7. Enter an "I.D." to assign to this extract. (optional)

8. Enter a Name/Description for this extract. (required)

9. Press the “Save” button at the bottom of the screen. (optional)

10. Press the "Start" button at the bottom of the screen to start the extract.

11. Select "Excel Export" from the "Print Selection" window.

12. Once the data is exported a message box called “Export Complete” will appear showing the number of records exported.

13. Excel will now load with the exported records displayed.

14. The fields selected will appear for all In-Active clients.

15. You can save this file or print it as needed.

This file may now be sent to a telephone service provider for them to contact your inactive clients.

Export a list of On-Hold clients between two dates (Feb 1^st to March 31^st):

1. Press the “Field Selection” button

2. Select “Member/Client Nbr” field

3. Select “Member/Client Name-Full” field

4. Select "Hold Start Date" field.

5. Select "Hold Stop Date" field.

6. Select "Pymt Mthly/Periodic" field.

7. Select any other fields you need included in the spreadsheet and then press the "O.K." button.

8. Select the "Exclude" option from the "Inactive Clients" parameter field.

9. Press the "Conditioning (Filters)" button.

10. Select the "Hold Start Date" field and press OK.

11. Select the "Greater Than" Condition Rule.

12. Enter "20130131" in the Condition Value field (without the quotes).

13. Press the "O.K." button to accept the condition.

14. Press the "AND" button to move to the next condition.

15. Select the "Hold Stop Date" field and press OK.

16. Select the "Less Than" Condition Rule.

17. Enter "20130401" in the Condition Value field (without qoutes).

18. Press the "O.K." button to accept the condition.

19. Press the "DONE" button to end condition entry. The main screen will display.

20. Enter an "I.D." to assign to this extract. (optional)

21. Enter a Name/Description for this extract. (required)

22. Press the “Save” button at the bottom of the screen. (optional)

23. Press the "Start" button at the bottom of the screen to start the extract.

24. Select "Excel Export" from the "Print Selection" window.

25. Once the data is exported a message box called “Export Complete” will appear showing the number of records exported.

26. Excel will now load with the exported records displayed.

27. The Client Number, Full Name, Hold Start Date, Hold Stop Date and Monthly Fee will appear for all clients that are on hold from February 1st to March 31st 2013.

28. You can now have Excel add up the Monthly/Periodic Fee column if needed and save this file or print it as required.

Export to “call-em-all.com” call list:

The web based service called www.call-em-all.com allows you to upload an Excel (xls) file of phone numbers in the following format:

Column Order:

Column 1 = Phone Number with Area Code

Column 2 = First Name (Optional)

Column 3 = Last Name (Optional)

Column 4 = Notes (Optional)

Column 5 = Phone 2 (Optional)

Column 6 = Phone 3 (Optional)

Procedure:

1. Press the “Field Selection” button

2. Select “Phone-Home” field

3. Select “Member/Client Name-First” field

4. Select "Member/Client Name-Last" field

5. Select the "Inactive Members/Clients" option: Exclude, Include, Only" as required.

6. Enter an "I.D." to assign to this extract. (optional)

7. Enter a Name/Description for this extract. (required)

8. Press the “Save” button at the bottom of the screen. (optional)

9. Press the "Start" button at the bottom of the screen to start the extract.

10. Select "Excel Export" from the "Print Selection" window.

11. Once the data is exported a message box called “Export Complete” will appear showing the number of records exported.

12. Excel will now load with the exported records displayed.

13. The Home Phone, First Name and Last Name number will appear for all In-Active or Active clients or both.

14. Edit the file inserting a comment column if needed.

15. In Excel, select the "File" option at the top, then select "Save As" and save your file with a .xls" extension.

16. Note the folder location of where the file is saved.

17. Log in to the www.call-em-all.com web site and upload this call file.

Export a list of all Female Active clients:

1. Press the “Field Selection” button

2. Select “Member/Client Name-Full” field

3. Select “Status” field

4. Select "Gender" field

5. Select any other fields you need included in the spreadsheet and then press the "O.K." button.

6. Select the "Exclude" option from the "Inactive Clients" parameter field.

7. Press the "Conditioning (Filters)" button.

8. Select the "Gender" field and press OK.

9. Select the "Equal to" Condition Rule.

10. Enter "Female" in the Condition Value field (without the quotes; Exactly as shown with capital letters and small letters).

11. Press the "O.K." button to accept the condition.

12. Press the "DONE" button to end condition entry. The main screen will display.

13. Enter an "I.D." to assign to this extract. (optional)

14. Enter a Name/Description for this extract. (required)

15. Press the “Save” button at the bottom of the screen. (optional)

16. Press the "Start" button at the bottom of the screen to start the extract.

17. Select "Excel Export" from the "Print Selection" window.

18. Once the data is exported a message box called “Export Complete” will appear showing the number of records exported.

19. Excel will now load with the exported records displayed.

20. The client Full Name, Status and Gender fields selected will appear for all Active clients.

21. You can save this file or print it as needed.

Export a range of active client numbers from 0 to 5000:

1. Press the “Field Selection” button

2. Select “Member/Client Name-Full” field

3. Select “Member/Client Nbr” field

4. Select "Phone-Home" field.

5. Select any other fields you need included in the spreadsheet and then press the "O.K." button.

6. Select the "Exclude" option from the "Inactive Clients" parameter field.

7. Press the "Conditioning (Filters)" button.

8. Select the "Member/Client Nbr" field and press OK.

9. Select the "Less Than" Condition Rule.

10. Enter "00005001" in the Condition Value field (without the quotes).

11. Press the "O.K." button to accept the condition.

12. Press the "DONE" button to end condition entry. The main screen will display.

13. Enter an "I.D." to assign to this extract. (optional)

14. Enter a Name/Description for this extract. (required)

15. Press the “Save” button at the bottom of the screen. (optional)

16. Press the "Start" button at the bottom of the screen to start the extract.

17. Select "Excel Export" from the "Print Selection" window.

18. Once the data is exported a message box called “Export Complete” will appear showing the number of records exported.

19. Excel will now load with the exported records displayed.

20. The Client Full Name, Client Number and Phone Number-Home will appear for all client numbers between 0 and 5000.

Note:

When conditioning a “Data Field” which may be padded with zeros leading zero’s MUST be put into the condition value field. (eg. client Number = 5001 must be entered as 0005001).

Also, the above extract could have been accomplished without conditioning by using the Extract From / To range parameters.

Label Print procedure using Mail Merge with Excel and MS Word

The following procedure can be used to print address labels on any label form supported by the “Microsoft Word” word processor.

Note: The following steps are for MSWord 2003 or later. The same procedure can be done on older versions of MSWORD, but the screen options are slightly different.

Using the Excel Export option on the Report Menu select the name and address fields you want and export these to an Excel File. Check the records in excel to be sure you have what you need.
From within Excel, save the Excel file into a temporary folder such as “C:\Temp\” and name it “Label.xls”. Make sure when saving the Excel file change the “Save as Type” to “Microsoft Excel Workbook (*xls)”.
Exit Excel and start up Microsoft Word application.
From the Tools menu select “Letters and Mailings; Mail Merge Wizard…:”
On the right side under “What type of document are you working on?”; select “Labels”.
On the bottom right: press the “Next: Starting document” link.
Under “How do you want to set up your mailing labels?” select “Label Options”.
On the Label Options window select the Product Number/Form Number of the labels you are printing (eg. Avery 5160). After pressing the OK button the label template will be loaded and you will see the outline of the labels in your document.
On the bottom right: press the “Next: Select recipients” link.
Under “Select recipients” press the “Browse” link.
In the Data Source Window go to the “C:\Temp” folder where you saved the Excel file in step 2 and select the “Labels.xls” file. If you get a message that the file is not in the required format, start up Excel, load the file and save it in Excel format and try again.
In the Select Table window select the “Sheet1$” line and press OK.
In the Mail Merge Recipients window all items will be selected by default; press the OK button.
On the bottom right: press the “Next: Arrange your labels” link.
NOTE: The first label is the place where you will define what is to be included in all labels and the format.
Under Arrange your labels select the “More items…” option.

You will see a list of items to choose, such as Member/Client Name-First, Member/Client Name-Last, Addr-Street, Addr-City, Addr-State/Prov, Addr-ZIP/P.Code.

Click on each one and press the “Insert” button. You will see on your label that that item has been added into the first label.
You now have a first label that looks like this: «Member/Client Name-First»«Member/Client Name-Last»«Addr-Street»«Addr-City»«Addr-State/Prov»«Addr-ZIP/P.Code». It may be spread over several lines depending on the size of the label.
The “«” and “»” marks designate the start and end of where that item or field will be inserted into the Word document from the source Excel file.
You can now arrange these fields within the first label to make it look like an address. Insert a space between «Member/Client Name-First» and «Client Name-Last». Insert a Return key after «Member/Client Name-Last» to move the «Addr-Street» field to the next line. Continue this procedure of inserting either spaces or returns until the first label is formatted the way you need it. You may find that the fields move down a line when you enter a space, but this will be corrected on the actual labels.

Press the “Update all labels” button. The formatting from the first label will be replicated on all other labels.

On the bottom right: press the “Next: Preview your labels” link.

You should now see labels with the actual data that was exported to Excel.

If any formatting problems exist press the “Previous…” link at the bottom and re-arrange the fields on the first label and press the “Update all labels” button again. Repeat this procedure until the labels are properly formatted.

On the bottom right: press the “Next: Complete the merge” link and you are done.

You should test the label printing on plain paper before loading your label forms and printing.

You can now print the labels or save the file for later printing.

Note: The printer may not start printing until you load paper into a manual or secondary tray because these are form labels and it expects a special label form.

This same procedure can be used to print Member/Client number or product bar codes by exporting to excel or creating just a list of numbers in excel, then printing the fields onto labels. A Bar Code font called “ FREE3OF9” is supplied with our software and gets installed when the main system is installed. This font can be used to print bar codes on labels. NOTE: An “*” MUST be inserted before and after the bar code to signal the scanner to read the bar code.