Logos People - Data Prep Excel

Logos Management Software provides Data Conversion Services to bring your data into Logos II. Or, you may choose to use Logos II’s Import Data routine (People > Utilities > Import/Export). This routine requires that you first make sure your data conforms to the requirements defined in the Logos II Help file (Import Data ). Microsoft Excel is an excellent tool to aid you in this process.

 

Open data file in Excel

 

1. Data should be in Excel (*.xls), text (*.txt or *.csv) format or dBase (*.dbf) format. The Import Data routine cannot import Excel 2007 (*.xlsx) files. Note the location where the file is saved.

2. Open Excel, then pull down the File menu or Office Button (Excel 2007)  and choose Open.

3. In the Open window, from the list at the bottom of the form, select the type of file that matches your data file. Then browse to locate the data file. Select the Open button.

4. Opening a text file brings up Excel’s Text Import Wizard. Indicate the layout of your data file:

 

 

• Delimited – Characters such as commas or tabs separate each field. If delimited, select Next to indicate the delimiters (commas, tabs, etc.) used in the file.

• Fixed – Fields are aligned in columns with spaces between each field. If fixed, select Next to note where breaks occur between fields. You can drag lines that Excel inserts and/or click to add a new break line. Delete a line by double-clicking on it.

5. Select Next again to set the formats (General, Text or Date) of any columns, or choose to skip columns you do not wish to import. Then select Finish to open the file.

6. Scroll down through the rows to see if you have one row per record and that the same type of data is in each column. Delete any blank rows and shift any data that is in the wrong column.

 

Add ID Number (if data records do not contain ID numbers)

 

1. Highlight Column A of the data file, select the Insert menu and choose Columns. In Excel 2007, select Insert in the Cells group on the Home tab’s ribbon and select Insert Cells.

2. Double click in the first cell in new Column A. (If your data file has a header row, click in the cell A2.) Type the lowest ID Number you wish to use, with a .01 extension (e.g., 101.01).

3. Scroll to end of the file and make a note of the total number of records.

4. Select the cell in which you typed the first ID number. Then select the Edit menu. In 2007, on the Home tab, go to the Editing group. Click Fill and then select Series.

5. In the Series window, click the Columns button and type the number of records in the Stop Value box. Select OK to automatically copy the numbers 2.01, 3.01, etc. into each cell in the column, up to the row that matches the Stop Value.


6. Scroll through the list to locate family members. Change the Spouse and Child ID numbers to the same whole number as the first family member, giving the Spouse a .02 extension, and each Child a .03, .04, etc. extension. If this is not done now, after importing, use Logos’ Update ID Number routine to link family members together, a simple, but longer procedure.

 

Tips in Editing Family Member ID Numbers:

• Select the ID cell for the first family member. Drag the fill handle for that cell (the small black square in the corner of the selected cell) down over the ID cells for each additional family member. When you release, they will all have the same ID. Change the decimal extension for the spouse (.02) and children (.03+).

• Do not worry about gaps between numbers that this procedure creates. It really doesn’t matter in Logos, as the ID Numbers will rarely be used by your staff or volunteers. The ID Numbers are for use by the computer, and gaps are not an issue.

• If any duplicate IDs are inadvertently created, the Import routine will identify them if you choose to Clear all existing data, and to Retain Existing ID Numbers.

Insert Decimals into ID Numbers

If your ID Numbers are being created from numbers that do not contain decimals, but do use the last two digits to identify the person’s position in the family, you can use Excel to insert decimals. The ID Number should be in Column A.

1. Highlight Column A. On the Format tool bar or the Number group on the Home tab, select the Increase Decimal icon (a decimal and zeros with a left arrow) twice to add a decimal  followed by two zeros to all ID Numbers (101.00, etc.).

2. Highlight Columns B and C. Select the Insert menu and choose Columns. In Excel 2007, select Insert in the Cells group on the Home tab and select Insert Cells. This will insert two blank columns B and C, moving all other columns two position to the right.

3. Click in the first data cell of the new blank Column B and type: =A1/100 and press Enter. This cell will now display the ID Number from Cell A1 (101.00) with the decimal in the correct position (101.01).

 

 

4. Drag Cell B1 down the length of the B column. All the ID numbers in column A will now be calculated with the decimal correctly positioned.

5. Highlight Column B and select Copy (Edit menu or Home tab > Clipboard). Highlight Column C and select Paste Special (Edit menu) or Paste > Paste Special (Home tab > Clipboard), and choose Paste Values to paste only the cell values and not formulas into the new column. Click OK.

6. Highlight Columns A and B and select Delete (Edit menu or Home tab > Cells).
Split a Column (if it contains data needing to be in two separate columns)

1. Highlight the column to the right of the column you wish to split

2. Select Insert menu and choose Columns. In Excel 2007, select Insert in the Cells group on the Home tab’s ribbon and select Insert Cells. Repeat to create any additional columns to fill.

3. Highlight the column to be split.

4. Select Data menu or tab and choose Text to Columns.

5. Follow the wizard to format the data as desired.

Combine Data in Two Columns
If your file has the family ID number (i.e., 101) in one column and the relationship number (01, 02, 03, etc.) in another column, here is how to combine the data. Similarly, if phone numbers are in separate fields from the area code, follow these steps.

1. Place your cursor in the first cell you want to merge into.

2. Type =A1&""&B1 and press Enter.

• A1 is the cell that contains the first part of the field and B1 is the cell with the rest.

• &""& indicates a join (anything appearing between the "" will be merged between the joining data). For example: If A1 contains 805 and B1 contains 555-7433 and these numbers were joined with &"-"&, they will merge as 805-555-7433.

3. Copy the merged cell into an empty column and paste downward the full range of the column.

4. Copy the newly merged cells (the full column range) and select Paste Special (Edit menu or Home tab), then choose Paste Values to paste the values and not the formulas to a new column.

5. Delete the original columns and the column containing the formulas.
Move a Column to Align with Recommended Field Order —see Help file lists)

1. Insert a blank column in the position where the data is to be placed. Highlight the first column following, and then select Column on the Insert menu or Insert on the Home tab.

2. Highlight the column to move. Select Cut (Ctrl+X) on the Edit menu or Home tab > Clipboard.

3. Select the column where the data is to be placed. Select Paste (Ctrl+V).
Define a Column (Dates)
Some columns contain specialized data (e.g., dates). To define a column’s data as dates (especially important if data shows only two digits for the year):

1. Highlight the column you wish to define, then select the Format menu and choose Cells.

2. Prior to Excel 2007, in the Format Cells window, on the Number tab, select Date from the Category list. Then scroll down through the options in the Type list to locate the format option showing the 4-digit year (3/14/2001). Select OK.
3. In Excel 2007, click on General in the Home tab’s Number group. Select Short Date from the list.


NOTE: You must set all date columns to the 4-digit date format when you make your final save of the file before importing. Otherwise, Excel saves only the last two digits, and the Import routine will convert all dates to the current century.

See Save As instructions below.

Replace Data in a Column (to change codes to text, to clear or correct errors, etc.)
Highlight the column, and then use the Replace function (Edit menu) or Find & Select (Home tab > Editing).

NOTE: Data must not contain commas (,) or quotes (”) as the Import routine will read those as field delimiters, pushing subsequent data into the wrong fields.
Save As

1. When you finish preparing the data, select Save As from the File or Office Button menu. In Excel 2007, choose Other Formats.

2. The Save As window opens.

• Prior to Excel 2007, you may select Microsoft Office Excel Workbook (*.xls), Text (Tab delimited — *.txt) or CSV (Comma delimited — *.csv) in the Save as type list at the bottom of the form. Then name the file and select a location for it.

• In Excel 2007, select Text (Tab delimited) (*.txt), as the Import routine is not compatible with the file format changes made in Excel 2007.

 

 

• DO NOT SAVE the file in a .dbf format, as they are not fully compatible with the Import routine.

3. Click the Save button. Excel will display an alert box telling you that your file “may contain features that are not compatible with Text (or CSV). Do you want to keep the workbook in this format?” Select Yes.

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.