SQL Server Performance

Excel datasource: most annoying thing ever invented?!

Discussion in 'SQL Server 2005 Integration Services' started by merrillaldrich, Oct 17, 2007.

  1. merrillaldrich New Member

    OK, I have to share (read "vent"). My company almost daily gets data to load into SQL Server, and it comes in Excel, because Excel has that nice table-like interface that makes it easy to enter, well, tables of data. Oh, if it were that simple. Excel is WAY too smart for its own good.
    Said data always has addresses, and addresses, in the US anyway, always have ZIP codes and ZIP+4 codes. They look like this:
    01033-1234
    A clever person will even select Format Cells > Text to preserve the leading zero. Why oh why for the love of all that is decent in this world can't the Excel datasource read that correctly, and when it can't, why does it not give some, er, ERROR MESSAGE or WARNING that it is stripping most/all zip codes out of your data and replacing them with NULL.
    Here's what I do to work around this stupidity, and I would LOVE a real solution:
    1. Manually add another column to the incoming spreadsheet.
    2. Insert a formula that adds some nonsense in front of the zip code, like =concatenate( "THISSHOULDBETEXT:", C2)
    3. Fill down
    4. Import my dummy column of data into a temp table, so the zip code is like "THISSHOULDBETEXT:01033-1234"
    5. Use SQL substring() to strip the characters back off.
    This is enough, all by itself, to make me hate Excel. Grrr!
    Thanks for listening.

  2. FrankKalis Moderator

    To some degree I can understand you. [:)]
    However, not sure if this will help, but have you ever tried a ' before such a value? The ' lets Excel interpret the following value as text without the explicite need to change the format of that cell. That is when you enter something like '001 you will "see" 001 in the cell and when you look via Ctrl+1 at the format it will still show "Default" ("Standard" in my German version). Might be at least worth a try.
  3. merrillaldrich New Member

    Thanks, Frank. Yes, we know about that workaround too -- problem is, this is data that we receive, and those creating it either won't do that (nor would I expect them to!), or, even more likely, the data came from some electronic format to begin with, into excel to send to us. Either way we need to edit it, which is the frustrating part :)
  4. dineshasanka Moderator

    Is it possible to use Fuzzy logic mechanism. In that case you need to have "Clean" zip codes in a table and you can match that with the excel file column. Depend on the Confidentiality parameter you can select the correct zip code

Share This Page