smeyer1984
Member
What application do users use to manipulate date for VDP? Access, SQL, Excel etc..
Thanks
Thanks
Data Manipulation
- Thread starter smeyer1984
- Start date
I use TextWrangler - it's free and has some nice features. We have a data department that uses various software to generate our data lists, and opening the final list in Excel can cause problems, like dropping the leading zero off of zip codes, so I try to avoid using Excel if possible. The only time I use it is to take a look at the data before working on the job, or if the variable work is real simple and doesn't need to go through the data/mail dept I will use Excel and save as .csv.
MailGuru
Well-known member
I Use FoxPro. Have used it for years and years. Love it! It was bought out by Microsoft, and, the last release was several years ago (Visual FoxPro 9), which, was Microsoft's basic intent (they felt FoxPro was a competing product to Access, so, they bought it and sunk it). You can still buy it, but, it's no longer supported by Microsoft. But, for quick, immediate, "down and dirty" data manipulation, you can't beat it.
FoxPro is an extension of the old "dBase", "dBase II", "dBase III", "dBase IV", line of software. MAC has a version called "FileMaker Pro". In fact, native dBase statements will work in FoxPro, as is, with no modifications (so will SQL). Unlike Access, FoxPro contains a "Command Window" where you can directly enter a command, and have it execute immediately on your data file. Only way to do that in Access, is to write a Visual Basic script macro, and then execute it.
For instance, say I want to add "Or Current Resident" as a second address line. It's just this simple:
Use Mailfile
Replace all Address2 with "Or Current Resident"
Close All
Or, if I want to put sequential numbers in an "Award" field:
Use Mailfile
Replace all Award with Recno()
Close All
I agree with you about the use of Excel for transporting or manipulating data. It's too "open" and too "dangerous". Not only can it mess up your date fields, but, someone who doesn't know a lot about what they are doing can corrupt an entire mail file. I have had customers who will put their mail info in an Excel file, and then, sort on ONE COLUMN (Address 1) of that file, and then save it. Now, NONE of the recipient names match their original addresses, or, the original city state and zip that corresponded with that Address 1 field.
FoxPro is an extension of the old "dBase", "dBase II", "dBase III", "dBase IV", line of software. MAC has a version called "FileMaker Pro". In fact, native dBase statements will work in FoxPro, as is, with no modifications (so will SQL). Unlike Access, FoxPro contains a "Command Window" where you can directly enter a command, and have it execute immediately on your data file. Only way to do that in Access, is to write a Visual Basic script macro, and then execute it.
For instance, say I want to add "Or Current Resident" as a second address line. It's just this simple:
Use Mailfile
Replace all Address2 with "Or Current Resident"
Close All
Or, if I want to put sequential numbers in an "Award" field:
Use Mailfile
Replace all Award with Recno()
Close All
I agree with you about the use of Excel for transporting or manipulating data. It's too "open" and too "dangerous". Not only can it mess up your date fields, but, someone who doesn't know a lot about what they are doing can corrupt an entire mail file. I have had customers who will put their mail info in an Excel file, and then, sort on ONE COLUMN (Address 1) of that file, and then save it. Now, NONE of the recipient names match their original addresses, or, the original city state and zip that corresponded with that Address 1 field.
Stephen Marsh
Well-known member
I am really liking Talend Data Preparation for cleaning up client data exports before importing them into the Accura MIS!
https://www.talend.com/products/data-preparation
Stephen Marsh
Last edited:
gabrielp
Member
We preflight our VDP databases at all steps with data-preflight: https://github.com/shawmut/data-preflight
We use a combination of Excel and BCC to do all manipulation work. With each revision, the database is tracked in a version control system which automates the creation of preflights.
We use a combination of Excel and BCC to do all manipulation work. With each revision, the database is tracked in a version control system which automates the creation of preflights.
Tim-Ellis
Well-known member
Hello,
We're very lucky in that 99% of the data supplied to us is squeaky clean. However, every once in a while it needs a push in the right direction.
Like DCurry I am a big, big fan of Text Wrangler. So many great features and it's free. For those of you with a PC, it's like 'Notepad++'
Hold down the ALT key and you can highlight and cut text in vertical columns. It's is worth it for that feature alone.
If you work a lot with .CSV files then check out the Quick Look Plug in for Macs.
http://www.quicklookplugins.com/2010/04/05/csv/
If you work with Tab Delimited .TXT files then this Quick Look Plug in will also work for you if you just add .CSV on the end of your .TXT file.
e.g PPR819_INDIGO edited copy.txt.csv
We're very lucky in that 99% of the data supplied to us is squeaky clean. However, every once in a while it needs a push in the right direction.
Like DCurry I am a big, big fan of Text Wrangler. So many great features and it's free. For those of you with a PC, it's like 'Notepad++'
Hold down the ALT key and you can highlight and cut text in vertical columns. It's is worth it for that feature alone.
If you work a lot with .CSV files then check out the Quick Look Plug in for Macs.
http://www.quicklookplugins.com/2010/04/05/csv/
If you work with Tab Delimited .TXT files then this Quick Look Plug in will also work for you if you just add .CSV on the end of your .TXT file.
e.g PPR819_INDIGO edited copy.txt.csv
michaelejahn
Well-known member
Date ? I think you should not manipulate your date ! Now your WIFE is an entirely different matter..
Okay, since you do not bother mentioning what sort of manipulation ... are we including NCOA, PAVE, CASS ? If So, you might look into services or purchasing an app...
( wait for it --- SHAMELESS VENDOR PLUG )
http://www.smartsoftusa.com/index.php?section=product
http://www.smartsoftusa.com/poor-data-raises-marketing-costs.html
Similar threads
