Fix your database CSV report woes using Excel : Texxors.com

Fix your database CSV report woes using Excel June 19, 2007

Posted by Beth in : How-to, Microsoft, Tips , trackback, Email this post

This article refers to Excel 2003, but should apply to most other versions as well.

I work for a large company that uses a vast mainframe system to track data. We’ve got programmers who’ve written fabulous scripts, so that we can just go click a button, make a couple of choices, and BAM - there’s a CSV file with the data we need to analyze. Problem is, the data is often not in a very useful format. The names are all upper case, first name and last name are in the same cell, phone numbers are regarded as generic numbers and are dropping zeros, etc. What a lot of people at my company don’t know, and that I try to help them learn, is that Excel has functionality specifically to help you work around these problems.

Problem #1. My text is in all caps

A lot of times, databases export all the data in UPPER CASE. This doesn’t work when you need to print a directory or make mailing labels, does it? So here’s how to fix it so that only the first letter of the word is capitalized, using the PROPER() function. Let’s start assuming you just need to fix one column of data. If you need to fix more, just rinse and repeat.

Insert a new column next to the column you need to change (Insert menu > Columns)
In the new column, select the cell immediately adjacent to the first cell of data you want to change
Type =PROPER(cell_reference) and press Enter, where cell_reference is the first cell of data to change, i.e., =PROPER(B2)
Copy the function down the column by double-clicking the fill handle (the little black box circled in red here –> )
If you want to replace your original data with the “fixed” data, copy all of the new values (select cells, then Edit menu > Copy)
Select the first cell of the original data, then Paste Special values only (Edit menu > Paste Special > check Values and click OK)
Delete the column with the PROPER() functions.

You may need to do a little cleanup for names such as McGruber or de la Rosa.

Problem #2. My text got brought in as numbers and is missing its leading zeros

Databases and Excel working together are not always smart enough to recognize that a number sequence should actually be regarded as text, like Social Security Numbers or telephone numbers (not that we recommend you store SSNs unencrypted on your computer!). So if you find that Excel is treating your data as numbers and annoyingly dropping leading zeros, you can easily fix it.

If it’s a standard format such as zip code, phone number, or SSN:

Select all the cells you need to fix
Open Format Cells (Ctrl+1)
On the Number tab in the Categories list, select Special
Select the appropriate format type and click OK

If it’s not a standard format (for example, it’s a product code), try importing your file instead of simply opening it in Excel:

Open Excel - do not double-click the csv file; instead, open the Excel program from the Start menu Program List
In a blank workbook, go to the Data menu and select Import External Data > External Data
Browse to where your csv file is saved, select it, and click Open
On the first screen, select Delimited and click Next
Check the delimiter Comma and uncheck Tab, then click Next
Highlight the column that contains the numbers that should be stored as text
Change the Column data format to Text
Click Finish
Put the data in $A$1 and click OK

Problem #3. First names and last names are in one column

A very common problem with database-downloaded cvs files is that the first name and last name are lumped together in one “Full Name” column. If you are trying to address a letter, make nametags, or import into your Contacts list, this can be annoying. Excel provides a handy feature called Text to Columns to separate names (or other data) out.

Insert several blank columns next to your column of data - as many columns as the maximum number of names you’ll need to separate into. So if you have someone with 4 names (i.e., Joey Joe-Joe Junior Shabadoo), insert 4 blank columns (Insert menu > Columns)
Select all of the cells that contain text you need to separate to columns
From the Data menu, select Text to Columns
On the first screen, select Delimited and click Next
Check the delimiter Space and uncheck Tab, then click Next
Set the destination to be the first blank cell in your new column(s) (for example, $B$2)
Click Finish
If you want to get rid of the original data, delete that column

You may need to do a little cleanup - if only one person had a middle name, you can delete it or add it to their first name, and then move their last name to the correct column. Delete any remaining blank columns.

As always, the goal here is to give you some ideas that will help you in your specific situation. You may not need to do exactly what I’ve done above, but now that you know the basics you can adapt these tips to your needs.

Share this page:These icons link to social bookmarking sites where readers can share and discover new web pages.