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

Question

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.

Answer 1

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.

Answer 2

If you don't have the option of fixing the spreadsheet I would load the data into a SQL table and update it in SQL with the below code.

Load test data

CREATE TABLE FillTest (MyDate date, MyType char(3), MyAccount char(3),
    Id INT NOT NULL IDENTITY(1,1))
GO

INSERT INTO FillTest VALUES
    ('1/1/2014','AAA','111'),
    (NULL, NULL, '112'),
    (NULL, NULL, '113'),
    (NULL, 'BBB','114'),
    (NULL, NULL, '115'),
    ('1/4/2014', 'AAA','116'),
    (NULL, NULL, '117'),
    (NULL, 'CCC','118')
GO

Fix the data. There is probably a way to do this in a single pass.

-- Fill in missing dates
;WITH FillTest_Dates AS (SELECT MyDate, Id
        FROM FillTest
        WHERE MyDate IS NOT NULL)
UPDATE FillTest SET MyDate = FillTest_Dates.MyDate
--SELECT *
FROM FillTest
JOIN FillTest_Dates
    ON FillTest.Id >= FillTest_Dates.Id
    AND FillTest_Dates.Id = (SELECT MAX(Id) FROM FillTest_Dates
                                WHERE FillTest.Id >= FillTest_Dates.Id)
WHERE FillTest.MyDate IS NULL
GO
-- Fill in missing types
;WITH FillTest_Type AS (SELECT MyDate, MyType, Id
        FROM FillTest
        WHERE MyType IS NOT NULL)
UPDATE FillTest SET MyType = FillTest_Type.MyType
FROM FillTest
JOIN FillTest_Type
    ON FillTest.Id >= FillTest_Type.Id
    AND FillTest.MyDate = FillTest_Type.MyDate
    AND FillTest_Type.Id = (SELECT MAX(Id) FROM FillTest_Type
                                WHERE FillTest.Id >= FillTest_Type.Id
                                  AND FillTest.MyDate = FillTest_Type.MyDate)

This will update both in one pass.

;WITH FillTest_Dates AS (SELECT MyDate, Id
        FROM FillTest
        WHERE MyDate IS NOT NULL),
    FillTest_Type AS (SELECT MyDate, MyType, Id
        FROM FillTest
        WHERE MyType IS NOT NULL)
UPDATE FillTest SET MyDate = FillTest_Dates.MyDate,
                MyType = FillTest_Type.MyType
FROM FillTest
JOIN FillTest_Dates
    ON FillTest.Id >= FillTest_Dates.Id
    AND FillTest_Dates.Id = (SELECT MAX(Id) FROM FillTest_Dates
                                WHERE FillTest.Id >= FillTest_Dates.Id)
JOIN FillTest_Type
    ON FillTest.Id >= FillTest_Type.Id
    AND FillTest_Type.Id = (SELECT MAX(Id) FROM FillTest_Type
                                WHERE FillTest.Id >= FillTest_Type.Id)