Sql-server – best way to deal with imperfect xls source file that needs to be imported into SQL via SSIS

excelsql serverssis

The data is formatted like this:

Date       Type       Account
1/1/14     AAA        111   
                      112 
                      113

           BBB        114
                      115

1/4/14     AAA        116
                      117

           CCC        118

Each new line represents a new row in the Excel source file. So accts 112 and 113 are date 1/1/14 type AAA, and accts 114 and 115 are date 1/1/14 and type BBB. Accts 116 and 117 are 1/14/14 and type AAA, etc. There are lots of blank rows and cells.

What I want, is to import this Excel file into SQL and have every account record include the date and type.

I have SQL server 2008 and Office 2010. I can't edit the Excel source file, but I could save a local copy I suppose if necessary, but I'm guessing there's an easier way in the SSIS import process or in SQL.

Best Answer

Two options come to mind. If you go with the local copy approach you can manually use the AutoFill feature to fill in the blank values. For an automated approach, the one approach I would think of in T-SQL is to use a cursor to loop through all the rows, capture the non-NULL Dates and Types into variables and when you encounter a NULL value, UPDATE the current record with the last non-NULL value. An integer identity may be needed in the table you're importing to confirm the rows have been inserted in the order as found in the file.

Related Question