CODEWITHSUNDEEP
sql-server-2008import-from-excel
CodingInCircles
CodingInCircles

Reputation: 2807

How to insert particular columns of an XLS into MS SQL Server table?

I have an Excel spreadsheet of about 1000 rows and about 15 columns. What I would like to do is to import this data into a table, but selectively. I want data from Row 5(say) onwards till about 5 rows from the end of the sheet. This is because the number of rows may vary, but I am sure that I will not need the last 4-5 rows irrespective of the number of rows in the spreadsheet. Additionally, I would like to insert only a few columns, as, Col1-Col5, Col7-Col9, and Col12-Col15. Is there one command/sequence of commands that I can use to achieve this? Please let me know ASAP. Thanks a lot!

Upvotes: 0

Views: 650

Answers (1)

Etienne
Etienne

Reputation: 46

The code down there creates a file with SQL instructions based on what's in your xls. You just have to add a Macro, paste it, and change a few things (add a while to fill the ColFields Collection with the title line where the column names must match the table field names, declare what's not, give a value to fileName...). Then execute the Macro and you'll have a file with all the insert you want, then you'll just have to execute this file on your base.

Sub Macro1()
    Dim NbOfLines = Worksheets(Sheet1).Range("A65536").End(xlUp).Row - 5 'Not the 5 last lines
    Dim ColFields As New Collection 'Do a while on the title line to fill the collection with wanted columns titles (do not add ignored columns)

    Dim StartSql As String
        StartSql = "INSERT INTO " + TableName + "("
        For Each loopField In ColFields
            StartSql = StartSql + loopField + ","
        Next
        StartSql = Left(StartSql, Len(StartSql) - 1)
        StartSql = StartSql + ") SELECT "


    Dim Value As String
    For i = 1 To NbOfLines
    Sql = ""
        j = 1
        For Each loopField In ColFields
            Value = Worksheets(SheetName).Cells(i, j).Value
            Sql = Sql + IIf(Value = "", "NULL", "'" + Replace(Value, "'", "''") + "'") + ","
            j = j + 1
        Next
        Sql = Left(Sql, Len(Sql) - 1)
        Sql = StartSql + Sql + vbCrLf
        Call WriteLine(Sql, FileName)
    Next


    End Sub


    Public Sub WriteLine(Ligne As String, FileName As String)

    Open FileName For Append As #1
        Print #1, Ligne
    Close
    End Sub

edit : I know it's not the best method (nor the most beautiful one), I gave it to you cos' I used it a few weeks ago to import data from a DB to another (but I needed to do it only once, not everyday). I also know there is a way to do it with an OpenRowSet you're right about it, but I just don't know how (I'll go often on this page wishing someone'll teach me). Finally, I encourage you to read this page : A Blog Page With A Solution (You'll find the great procedure 'uftReadfileAsTable' here : The Procedure Code) Good Luck!

Upvotes: 0

Related Questions