August 2006 - Posts
If you've ever worked with tables of text data you must have tried to copy data from a file or other place into excel and it all gets dumped in one column. You know its nicely delimited by commas or tabs or may be its fixed width and excel can convert this data into columns when you open a file so why can't excel translate the data into columns when you paste.
Well it can.
Once you have pasted your data into Excel goto the Data menu and then select Text to Columns and you will be prompted with the Text Import wizard where you can choose delimted or fixed width and then the usual options.
If you paste data in you may also get this as an option in the smart tag at the bottom right of the pasted area.
I'm sure I've seen this mentioned before but as I had forgot about it I thought others might benefit from a reminder.
The Lookup transformation matches data differently when in cached and non-cached (Enable Memory Restriction) mode.
Why is that you may ask? Well it comes down to where the matching is being done. When the lookup is in cache mode (the default) then the data is retirieved from the database and then compared on the client. This means it will be compared at the byte level. i.e it will be case and accent sensitive. When running in non-cached mode the match is done at the database level. In this case the match is done using the collation specified for the column being matched, which is probably not binary.
To get round this you will need to make sure you now what you want, and pick the right model. Unfortunately I don't think you can do an accent insensitive lookup in cached mode.
You can vote on this here https://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=126371
I've been quite for a while for a number of reasons, Holiday, baby finally catching up on me and some dreaded DIY.
Anyway I couldn't miss mentioning that CTP5 of Data Dude is now available for download. The great news is that this now supports SQL 2005 although not entirely.