asked
formatting the cell values
Hi, I have a excel file which stores details about properties, one of the columns is property_type. it contains values in the likes of "Apartmetns (45)/Units (4) & Town House - 7 Floors". I am in process of uploading this excel file using SSIS 2008 into sql database. I need to format the property_type to look like "Apartment/Units/Town house". Please provide any suggestion or sql query to do so. In the excel file i could do the same using trim and substitute function. But as now loading this spreadsheet into the db i need to write a query so that every time we load the file this transformation takes place. Any help or suggestion is highly appreciated. thanks
I suggest that you try using a regular expression to parse the string. Try one of the many regex builders that are available online to design the expression to fit your needs: [Google search for "regex builder"][1] You would then use that in SSIS to remove all the unnecessary data from the cells, or push some values from one cell into a separate column. [1]:
http://www.google.com/search?q=regex+builder&sourceid=ie7&rls=com.microsoft:en-us:IE-SearchBox&ie=&oe=&rlz=1I7GZAZ_de
