I in receiving supplied list how much work is done to comply with your workflow?

Most lists we get we do a Cass certifacation on and usally have some seperation or combining of fields to do. We're presently using excel to work on list but find that additional checks are needed to maintain leading zeros and other formatted information. I'm looking for alternative applications to work with and have started to try MS Access, but seam to have other issues when I open up in Access so I just go back to Excel and deal with what I know how to fix there.

Would like to know how others have gotten past this are are now dealing with it. My background is not with data bases or programming and has been with prepress fom convetional to digital.

Thank you,

______________
Craig

Personally I prefer to recieve data in ascii text files. Either fixed width or deliminated by something other than a comma. I usually recommend ! or |.

Up until recently, the first thing I did was bring whatever file was provided to me into an ascii text file. Then from there I did all of my subsequent manipulation using SQL. Through sql I did things like added leading zeros, searched for duplicates, combined or seperated fields, etc. Then I would output my sql table(s) to a new ascii file for my rips. Usually I was feeding my data into a vipp application.

Today, I am at a similar point as you. Excel is lousy, XML is ugly what should be used.

I think xml will be the best long term solution coupled with a database tool that reads the xml into a database table and outputs xml based on your scripts for the necessary production. Unfortunately, I do not think things are to that point yet.

We get some pretty bad data files and usually get the pleasure of massaging them into something usable. It's not unusual to get a list with 5 address fields and no rhyme or reason as to which field they put what type of data into. On a large database merging 5 address fields down to 2 for the Post Office can be very time consuming. I typically receive files in Excel but get quite a few delimited in various ways and the occasional db4 file. Every now and then there is an Access file. I open everything in Excel and do the necessary merging or separating of fields (Thank goodness for data autofiltering). I'm really good in Excel but a large database can be very time consuming.

I use Desktop Mailer from First Logic for CASS certification, de-duping and presorting. Desktop mailer takes care of the dropped leading zero in zips as well as case conversions pretty well.

If anyone has any tips or tricks for data massaging I would love to hear them.
One of my favorites is:

=A1&", "&B1

This will merge cell A1 and cell B1 together separated by a comma and space. You can string more than 2 cells but seldom have I had a situation where this was useful.

Vince

Do you every have to do anything like eliminate duplicates?

Do you ever merge multiple variable data runs for mail automation (i.e. 2 different mailings with a single database) where you have to add fields for piece identification?

We always de-dupe our lists. This function is built into Desktop Mailer and called "Merge/Purge". With this function you can search for duplicate records within the same data list or compare and de-dupe against several other lists. You can use rules set up within the program or create your own. We usually use zip, last name and address field 2 but you can use any fields that are in the file(s).

We have added fields for our own purposes like sequential numbering or dates. We usually add the desired fields in Excel. We can then add or create the the fields in Destop Mailer before we import the Excel file. Adding a field for piece ID would be easy enough.

Vince

Excel is good for most things - once you understand it.
A couple quick tips for you...try text to columns and see how wonderful it is when needed.

Leading zeroes can be added and maintained simply, again by understanding the program. To add them, you can use the format cells as zip function or use "custom" and show excel what you want. To maintain them do this: Add a duplicate of your header row. How easy! When this is done, you will see that your zeroes remain when you use the file.

Cheers.

I just came across the column to text feature and find that useful, small tips like that are what I'm looking for. It would be nice if there were a book or pdf that deals with formating and cleansing of data for varaible print but since this is still fairly new little is available. If anyone has other tips however small it might be please share,

Thank you
_____________
Craig

After years of searching the 'net looking for helper utilities I thought I would give back, atleast a little.

I found some free & very useful utilities for Excel that helps us a great deal with data manip & correcting.

http://www.asap-utilities.com/
&
http://www.andrewsexceltips.com/ (when doubling checking the address before posting, it seems his is now a paid for utility.)
They are essentially the same but vs 2 of Andrew's Utilities had a great very easy to use "Select until end of Range", it may be worth Googling for.

&

Subpad from http://www.xtort.net/xtort/xtort.php It has a very, very fast find & replace for .txt files. I use it to delete the field seperators that Excel puts in after exporting as tab delimited. Use it to replace the quotes.

I also had a tip that Ikon's VDP software analyst had never heard of before so I thought I would share it.
To keep from dealing with a variable length list, where the last column of a row is always changing from row to row. or if you simply want to guarantee that all rows end at the same column. Put the word End as the last column heading and Paste it all the way down until the last used row. This is where the 2nd utilities "Select Until end of Range" came in handy. The Variable Length List really throws PlanetPress for a loop.

I haven't put much work in to it, but I am looking for a easy way of Splitting cells. ie. Split a cell based on a comma or even a space. I usually end up Replacing spaces with commas, saving as CSV then re-importing into Excel.

Just a little pay back to the 'net for the help over the years.