Product Research ... Combine Multiple Files Into 1 With Differing Formats
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.
