Korean MemorialKorean War & Korea Defense Service Veterans, Lake Co. (FL), Chapter 169















Association Membership Database (MDB) User Manual -- Chapter 4, Using Excel and Comma Separated Values to Extend the Value of the MDB for Chapters and Departments.

by Tom J. Thiel, Past President of Chapter 169, date written June 2014; revised August 16, 2014. 

In this Chapter we will provide you with a procedure that will enable you to download the report information to your personal computer where you can apply it to better address the needs of your Chapter or Department.

But, you need to have Microsoft Office, specifically Excel, and Notepad installed on your computer to use this procedure.

Office consists of several general programs such as Word, a word processing package, Excel, a spreadsheet, Access, a database system, Publisher, a print layout program which I use for all the newsletters I do, and some others. Costs vary with the number of these applications included.

Perhaps Open Office, which is free of cost to you but does not provide anywhere near the capability of Office, will also work. I have never tried it.  NOr have I ever tried to use Cloud software, so cannot speculate how that might work.

Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows and Mac OS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel is available with all versions of MS Office.

You need to have Excel on your computer, and some knowledge on how to use it. I am using Excel 2010; I believe older (2007) and newer (360 - this is MS's Cloud software) will also work the same as described herein.

You also need Notepad; it is the quite elementary word processor that comes with a Windows system. You should know where it is. It should be viewable in “All Programs” or you can type Notepad in the search window bottom left of your screen when you hit the Windows 7 key.

Comma Separated Values

Comma-separated value (CSV) files store tabular data (numbers and text) in plain-text form. Plain text means that the file is a sequence of characters, with no data that has to be interpreted instead, as binary numbers.

A CSV file consists of any number of records, separated by line breaks of some kind; each record consists of fields, separated by some other character or string, most commonly a literal comma or tab. Usually, all records have an identical sequence of fields.

That sounds a bit complex, but you will soon see that it really is not.

Using CSV output capabilities

4.01  You will recall seeing the small outlined box shown in the center-right of this graphic displayed with many reports.



 

It says “Display in CSV Format for exporting.”

Exporting in this case means transferring the information you see when you do a MDB retrieval from the KWVA computer to your computer.

While it is exporting at the KWVA site, it actually importing to your computer.

You first have to do a specific retrieval. The one above is the basic Chapter retrieval we showed in Chapter 2. All but one line of output has been cropped from the graphic.

Your goal is to transform it from a report on the MDB output screen to an Excel file on your own computer.

When you see this box, “Display in CSV Format for exporting,” with MDB system reports (not all retrievals have this capability) the following steps will allow you to export the results you see on screen to an Excel file on your computer.

Some data elements vital to Chapter operations are not provided in the CSV output option, e.g.,  date joined chapter, and unit are three. But in July 2014, Mr. Doppelhammer added email addresses and this is a highly significant addition since this report can now be used to prepare both email and snail-mail labels for sending for example Chapter Newsletters.

So this form of output can now contribute greatly to Departments and Chapters.

We first have to do a retrieval. When the report is on our computer screen. Click on the “Display in CSV Format for exporting” option with the report.

4.02R  You will see a window on your screen, which is soon followed by data filling that window with the report output items as is shown in the graphic below.



 

If you examine this graphic carefully, especially the top several lines, you can see what csv means and also the data elements that are being provided (they will become columns in your Excel spreadsheet soon).

The data still resides on the KWVA computer even though it appears on your computer's monitor.

We will now proceed to move it from there to your computer (import).

Place your cursor (pointer) anywhere inside this box (4.02) and hit “Control + A” (press down on the Control Key and while holding it down press the A key) on your keyboard. This should highlight the whole block of text. This step has been done in the above graphic. I could not do a screen capture ot the actual pointer in the displayed data.

Next hit “Control + C” to Copy it to your Clipboard. You will not see anything but a copy of the data on the screen will have been downloaded to your computer's clipboard. The data is now on your computer.

Now (For Windows 7) hit the Windows Key in the lower left hand corner of your screen, and find Notepad and open it. It should be viewable in “All Programs” or you can type Notepad in the search window bottom left of your screen.

Open Notepad (your cursor or pointer will already be in Notepad).

4.03R  Then hit “Control + V” which will paste the contents of your computer's clipboard to Notepad.



 

You now have the data as a Notepad file on your computer. All you need to do now is save it on your computer.

4.04  In upper left corner of Notepad click on File, and then select Save As. You will then be asked where you want to save it, and under what name it should be saved.



 

Notice that I have decided to place it on my Desktop (I normally place it there; you may place it where you wish). Also notice I gave it the name, "Name.txt". This will then place the file, “Name.txt” on my desktop.

 

4.04c  View of my desktop icons after the download is completed, and after I have changed the name of the file from "Name.txt" to "Name.csv" and from that to "Name.xlsx".



 

To make this change, Right click on "Name.txt"; then select Rename. Next carefully change the ".txt" to ".csv".

You will get a caution message warning you that the "file may become unstable."

Reply "Yes" you want to change it.

You will then get the icon "Name.csv" on your desktop. Note that this icon indicates it is an Excel file.

The file "Name.xlsx" is the final file format desired; it will be described shortly.

4.04b  The above is is the procedure given by Mr. Doppelhammer in the “Display in CSV Format for exporting” box shown in 4.01 above. But I generally follow a slightly altered procedure that will save it to my desktop as a csv file directly.




 

Instead of saving it as "Name.txt" I save it as "Name.csv" as is shown below. This avoids having to rename "Name.txt" to "Name.csv" if you follow the first option.

You will then get "Name.csv" directly without the txt version and a subsequent name change operation.

 

Open "Name.csv" with Excel. Reformat with "auto fit column width" under Format. And you will get a file that looks something like that below. You will then want to do a File, Save as an Excel file, which will then have the name "Name.xlsx"

4.05R  Excel file named "Name.xlsx" with the information contained in the original MDB retrieval.



 

What can you do with this? You can:

1.  Print mailing labels to send letters or newsletters to your members.

2.  Sort the file in RENEW date order so that you know when your members Association Dues are payable (our chapter has the same Chapter Dues renewal date).

3.  Construct the core of your own Chapter Membership File in case you do not already have one.

4.  And as of July 2014, you can get email addresses with the CSV output, a major improvement.

There still may be some items you might like to have, i.e., unit served in, date joined, and perhaps others..

 

Outline of csv steps

This sounded and looked like a complicated procedure, but it really is not. Here is an outline of the steps.

1. Do a retrieval

2. Select “Display in CSV Format for exporting.”

3. Press "Control + A" to Select the data ready for downloading

4. Press "Control + C" to Copy this data to your computer clipboard.

5. Open Notepad; press "Control + V" to Paste from your clipboard to the Notepad file.

6. Save the Notepad file, preferably as a name.csv file to your Desktop.

7. Open with Excel, Reformat, and Save as name.xlsx

8. Good luck 

Excel provides one additional capability, and that will be discussed in Chapter 5. Copy and Paste for Downloading Directly from the Displayed Report to Excel.

Thank you. tjthiel

Cover    Introduction    Chapter Access    Department Access    Excel & CSV Output    Copy & Paste Output    Deceased Members

How to use this manual.