Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mfemenel
Professor Frink
1421 Posts |
 Posted - 2007-08-28 : 11:06:00
|
| Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.Mike"oh, that monkey is going to pay" |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 08:20:36
|
| Are there a finite number of variations?If so I would go for a "definitions" table:VendorA : [first name] -> MyCol1, ...VendorB : [first_name] -> MyCol1, ...VendorC : [firstname] -> MyCol1, ...then you are not just dealing with "sounds like" column names, they can be anything. But any XLS from a new Vendor needs a set up before its any use.I've seen a number of packages that do this - offer a file, get a list of columns, "map" them to actual columns, save the "import format" for a subsequent run. You can even do the sounds-like thingie to pre-assign the columns using a best-guess basis, which would then make setting up a new Vendor a bit quicker.Kristen |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2007-08-29 : 08:49:55
|
| Well I'm sure there are thousands of ways I could come up with to butcher a column name but I can set a reasonable amount. Actually I was thinking as long as they get the the words "First" and "Name" in there somewhere I could figure out the column name. What packages have you seen that do this?Mike"oh, that monkey is going to pay" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 09:03:51
|
| Oh, just in general. My wife has some vertical-market application that allows them to just cut&paste a delimited file into a Text Box, first row has to be column names, and then it goes from there. I've seen tit done in other applications too, but probably all vertical-market Apps now I think about itAnd I suppose DTS does that too - you get to assign Incoming-column-name to target-table's-column-name. But you then have to save it as a whole job, you can;t save the "format table" part separately.Kristen |
|
|
|
|
|
|
|
|
Dynamic Column Names