Built-in Function or Add-in To Automate Data Transfer Workbook to Workbook?

[hwbenlarson]

Background: I apologize in advance for the lengthy background, but I am striving for clarity. I am a semi-retired outside contractor, sometime hired by a Software Publisher to receive data in Excel Format from one of their newly signed customers who previously had been using one of a dozen competitor database applications (each competitor with its own unique data structure, layout, and naming conventions). The new customers are not db savvy and the legacy vendors are far from cooperative, however, they will usually provide a dump of their data into Excel. That’s where I enter the picture.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The Publisher provides me with their own “Excel Data Upload Template” which cannot be altered with respect to Column Titles and the respective sequence of those columns.
<o></o>
My tasks are to unify, manipulate and cleanse the legacy data set(s); and then to transfer the legacy data into the appropriate columns of the target Data Upload Template. I am pretty adept up through intermediate Excel Data Functions and working within the Menu Structure and have some working experience creating lengthy macros using Recorder and then editing to fine tweak.
<o></o>
My challenge is the repetitiveness of the final Transfer process. Each competitor may have different Titles, Columns not relevant, Columns out of sequence compared to my target Data Upload Template. I am so tired of re-inventing the wheel with each new data transfer. Cut and paste is so stone age and time consuming, even though I indeed have time I haven’t even used yet.
<o></o>
Question: Is there an Excel 2003 Built-in functionality (or Add-in) that allows one to create an import script, which facilitates the “mapping” of source columns to target columns to automate the transfer of mega rows of legacy data into my target Data Upload Template. I'd love to create one reusable script for each competitor structure.
<o></o>
If wishes were horses, then beggars would ride! So I might also add to my wish list, functionality that would additionally allow the use of data functions, boolean logic, etc to change the data structure or formatting during the transfer process.
<o></o>
Ideas and suggestions sorely needed? Thanks.

 

[shawnhet]

I'm not sure if this is what you are looking for - but you can add a row on top of the source spreadsheets and in the top row put the number of the row you want it in on your upload template (ie if column 1 in your source workbook should be column 3 in your easy upload spreadsheet label the top cell in that column 3).

Then you can sort it by choosing Options under Data >> Sort and choose to sort from left to right. (remember to check that your list has no header row). This will sort the columns in to the order of your top columns - be sure that you fill have columns labelled for all the columns in your upload spreadsheet though, otherwise, you may have a case where the column after that is labelled 3 will be right next to the column labelled 1.

I hope that is reasonably clear. Let me know if you need some more details.

Cheers,

 

[hwbenlarson]

I'm not sure if this is what you are looking for - but you can add a row on top of the source spreadsheets and in the top row put the number of the row you want it in on your upload template (ie if column 1 in your source workbook should be column 3 in your easy upload spreadsheet label the top cell in that column 3).

Then you can sort it by choosing Options under Data >> Sort and choose to sort from left to right. (remember to check that your list has no header row). This will sort the columns in to the order of your top columns - be sure that you fill have columns labelled for all the columns in your upload spreadsheet though, otherwise, you may have a case where the column after that is labelled 3 will be right next to the column labelled 1.

I hope that is reasonably clear. Let me know if you need some more details.

Cheers,

Thank you for your kind suggestion. Sorry for the delayed response, as I was out for a week with a family challenge.

Yes, you suggestion is definately a workable consideration for me, albeit, somewhat limited. Thanx.

However, as a former Telemagic VAR I am use to the elegance of using a "visual drag and drop mapping of fields from the source table to a target table". This import tool I refer to also allows through use of functions the manipulation of data for each field during the transfer . So I am admittedly very spoiled.

I don't suppose anyone knows of a similar Excel Add-in that uses a "visual" approach to drag n drop fields (columns) to the target columns and the use of functions to thereby creating a reusable script?

If wishes were horses, beggars would ride! Thanks in advance for any suggestions.