Post reply

Data cleansing - struggle with data types

  • November 5, 2010 at 2:09 pm

    #388175

    I have a task to upload data from excel to a table

    Problem: The data in excel doesn't fit in nvarchar(30), so I was instructed to delete all the data that is more than nvarchar(30). To know which data is deleted I created a flag column ='Y' and upload the data.

    Can I have any suggestions please .........

    Note : I am not allowed to change the data types.

    Thanks,

    Swetha K

  • November 5, 2010 at 2:20 pm

    #1247320

    I have two suggestions:

    1) Format the data in Excel as to how you want it imported into the table. This function will give you the first 30 characters: =LEFT(<cell>,30) This formula will set your truncation flag: =IF(LEN(<cell>) >30, "Y", "N")

    2) My other suggestion is to create a "conversion work table" in SQL and import the Excel data "as is" into that table. Then create the SQL using substring and CASE statements to truncate the cell to 30 and set the truncation flag.

    No doubt this could be done directly in SSIS, but I personally would use one of these suggesion. I'm sure there are others who will chime-in as well.

    HTH,

    Cindy

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply