Product Research ... Combine Multiple Files Into 1 With Differing Formats

[JodyWood]

I am beginning research for products that may fill the following needs.

I realize to get everything that I am looking for - a custom application may be required. However, the custom application may be able to utilize tools to assist with the needs of the product. I am looking for suggestions for off the shelf products that may fill some or all of the needs I have listed below. Ideally, it would be great to find an off the shelf product that fulfills all of these needs or most of them. I appreciate your feedback.

Thanks,
Jody Wood


Product Needs

1) The product needs to take 1 or more files in differing formats (txt, csv, xml, xls, etc) and layouts and combine those files into 1 output file.

2) The output file type (txt, csv, xml, xls, etc) needs to be selectable as it may vary based upon project.

3) The product needs to have a method to map the input layout to the output file. The output map may be a combination of one or more input columns. This mapping may be different for each input file. The output file will contain the same columns.

4) The product needs to be able to specify an output format. For instance, phone number needs to be output like “8885551234” from its input of “888-555-1234”.

5) The product needs to include validation for the input data before it is moved to the output file. The output map needs to include what validation needs to be performed on that given mapping. This may be name validation, address validation, phone validation, email validation, etc. Upon failure to validate, the information needs to be placed in a validation failure output file that includes failure information, such as “failed email validation”.

6) The product needs to allow for review of the failures and possibly offer suggestions to correct the failed validation. The user can then edit the suggested value or enter something entirely different. Upon completion of editing the failure points, the user may resubmit the information through validation and to the output file upon successful validation.

7) The product’s target audience is non-technical staff. Therefore, with a bit of training, most anyone can use the product that fills a non-technical role.


Here is an example…

Input File 1:
First Name, Last Name, Address 1, Address 2, City, State, Zip, Phone, Email, Requested Details
“Bob”, “Smith”, “123 N. Main”, “”, “Chicago”, “IL”, “12345”, “888-555-1234”, “[email protected]”, “Yes”
“Jack”, “Smith”, “456 S. Main”, “PO Box 1”, “New York”, “NY”, “54321”, “888-123-5555”, “[email protected]”, “No”

Input File 2:
First Name, Last Name, Address1, City, State, Zip, Phone, Requested Details, Customer Level
“Mike”, “Johnson”, “678 Johnson St”, “Los Angeles”, “CA”, “87654”, “(888) 555-6789”, “Yes”, “Type A”
“Tom”, “Wilson”, “987 Wilson Rd”, “Seattle”, “CA”, “44332”, “(888) 123-0001”, “Yes”, “Type M”

Output file layout (csv):
Name, Address, City, State, Zip, Phone, Email, Requested Details
“Bob Smith”, “123 N. Main”, “Chicago”, “IL”, “12345”, “8885551234”, “[email protected]”, “Yes”
“Jack Smith”, “456 S. Main”, “New York”, “NY”, “54321”, “8881235555”, “[email protected]”, “No”
“Mike Johnson”, “678 Johnson St”, “Los Angeles”, “CA”, “87654”, “8885556789”, “”, “Yes”

Failed validation File:
Failure Reasons, Original Input
“Failed City and State validation”, “Tom”, “Wilson”, “987 Wilson Rd”, “Seattle”, “CA”, “44332”, “(888) 123-0001”, “Yes”, “Type M”

Notes…
1) Validation performed: Combination of City and State
2) Input files contained first name and last name. The output file was mapped to combine the inputs first and last name.
3) The phone number in the output file was reformatted.
4) Output file did not contain Customer Level from Input File 2.
5) The failed validation could be reviewed by the user. They would then adjust the state to “WA” and resubmit that row for validation and addition to the output file.

[SpywareDr]

Microsoft Excel can open the following file types:

• Microsoft Office Excel Files:
  *.xl*
  *.xls
  *.xlt
  *.xla
  *.xlm
  *.xlc
• Web pages:
  *.htm
  *.html
  *.mht
  *.mhtml
• XML files:
  *.xml
• Text files:
  *.prn
  *.txt
  *.csv

And then, if it's not a normal Excel file, it will allow you to specify the type of data it is, where the columns begin/end, etc.

However, after your data (as you specified above) is then loaded into the Excel spreadsheet, there would still need to be some work done on it ... which will require some Excel experience.

FWIW, I frequently import/export .CSV files, (and occasionally .TXT files [in columnar format]) into and out of Excel. Fairly easy to do ... but I've been messing with Spreadsheets since way back in the early days of Lotus 1-2-3 (mid 1980's).

[JodyWood]

So, how do you get excel to open each of the files and then output each one to the same output file?

I don't believe that excel is the way that I want to go. I know there are times when the output file will go beyond the limits of excel (65,536 rows). But maybe you have answers to my question above in this post that will resolve this issue.

In one case, I had to take 95 excel spreadsheets and combine them into 1 file (csv). There were approximately 10 different input formats (layouts) for the 95 excel spreadsheets. To complete the task, I used DTS within SQL Server 2000. I found that to be very combersome and it is not ideally suited for end business users and analysts to work with. So, DTS is not a tool that works for that requirement. Plus, you have to write any custom validations, which I am looking to avoid... thus my research.

Thanks,
Jody Wood

[jdc2000]

There may be some data translation products capable of doing what you want, but they will be very expensive and you will still have spend considerable time setting up the format translations to get the output results that you want.

A custom Visual Basic program might be the way to go. You could program it to recognize each of the formats and translate them, and it probably wouldn;t be much more work than setting up a generic data translation program.

[JodyWood]

Any thoughts on products such as Altova MapForce 2006 (http://www.altova.com/products_mapforce.html)? Microsoft BizTalk Server 2006 (http://www.microsoft.com/biztalk/default.mspx)?

[jdc2000]

The Altova products look like they should be capable of generating the translation code, although you would still need to take care of making an actual program using them. The low end version doesn't look too expensive.

The MS BizTalk has some high system requirements:
http://www.microsoft.com/biztalk/200...uirements.mspx

Pricing starts out ad double the other product and quickly graduates to ludicrously exorbitant.
http://www.microsoft.com/biztalk/howtobuy/default.mspx

Another product that can do the job is PlanetPress from ObjectifLune. It is also expensive.

[JodyWood]

jdc2000 - You mention PlanetPress from ObjectifLune. I did a google search for this. Is this the web site - http://www.printersource.com/? It looks like this has more to do with printing? Or maybe I'm wrong. Thanks for your additional information!

[JodyWood]

Here are some other products that I am reviewing...
http://www.softwarecompany.com/dotnet/netaddress.htm
http://www.cms-summit.com/softwareso...nameaddval.htm
http://www.worksright.com/perzip4call.html
http://www.worksright.com/perzip4.html
http://www.guysoftware.com/parserat.htm

If you have any other suggestions, I'd love to learn more about it. Thanks!

[jdc2000]

The manufacturer's web site for PlanetPress:

http://www.objectiflune.com/

This software was originally designed for creating form overlays for really large print output jobs that are sent to really high volume laser printers. It has eveolved into a suite of tools that can be used automated .pdf file creation, and data translation and reformatting. It has a programming language and a GUI interface. Personally, as a programmer, I find Visual Basic easier to use and more flexible in creating data translation programs, but PlanetPress works well on data that is consistently formatted.

[jdc2000]

The first four items appear to be targeted at postal name and address reformatting. If you are actually wanting to create mailing lists that will be presorted for reduced mailing rates, you will probably need to use software that is certified by the USPS, like PrintForm or Desktop Mailer.
Link:
http://www.firstlogic.com/solutions/...Processing.asp
This software can also translate various data formats.

The last item looks like more of a generic data translation utility.