none
To import data from Excel into sql Db

    Question

  • Kindly let me know what ll be the easiest way to import data from Excel sheet into SQL 2005 DB after validating each column.

    Also repeatedly the method needed to be used for different data periodically

    Thanks!

    (Sorry i dont know under which forum header should i post, only so posted here.)

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Tuesday, February 15, 2011 10:22 AM

Answers

  • Kalman, I always use the Code Insert block, but don't know why the code spitted.

    Here it is again:

    ' VBA Macro code | START
    Private
     Sub
     Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Dim errorCode As Integer
    Dim errorMsg As String
    Dim numOfRecs As Integer
    Dim counter As Integer
    
    '---------------------
    'Start - Validate Date
    '---------------------
    
    
    Sheet1.Activate Range("A2").Select
    errorCode = 0
    numOfRecs = 0
    
    Do
     Until
     ActiveCell.Value = vbNullString
     If
     IsDate(ActiveCell.Value) <> True
     Then
     errorCode = 1
     ActiveCell.Interior.ColorIndex = 3 'Red
     Else
     ActiveCell.Interior.ColorIndex = 2 'White
     End
    
     If
     numOfRecs = numOfRecs + 1
     ActiveCell.Offset(1, 0).Select
    Loop
    If
     errorCode = 1 Then
     errorMsg = errorMsg + vbCrLf & "- Invalid Insert Date"
    End
     If
    '-------------------
    'End - Validate Date
    '-------------------
    '--------------------------
    'Start - Validate Customer#
    '--------------------------
    
    
    Sheet1.ActivateRange("B2").Select
    
    errorCode = 0
    Do
     Until
     ActiveCell.Value = vbNullString
     If
     IsNumeric(ActiveCell.Value) <> True
     Or
     Len(ActiveCell.Value) > 12 Then
     errorCode = 1
     ActiveCell.Interior.ColorIndex = 3 'Red
     Else
     ActiveCell.Interior.ColorIndex = 2 'White
     End
    
     If
     ActiveCell.Offset(1, 0).Select
    Loop
    If
     errorCode = 1 Then
     errorMsg = errorMsg + vbCrLf & "- Invalid Customer #"
    End
     If
    '------------------------
    'End - Validate Customer#
    '------------------------
    Range("A1").Select
    If
     errorCode = 1 Then
     MsgBox "Workbook not saved. Following are the fields that contain invalid values."
    _
     & vbCrLf & errorMsg & vbCrLf & vbCrLf & _
     "Please correct the values highlighted in RED color."
    , vbCritical, "Data Validation ERROR"
    
     Cancel = True
    
    End
     If
    End
     Sub
    ' VBA Macro code | END
    
    


    ~Manu
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Monday, February 21, 2011 1:43 PM

All replies

  • if you have to automate it, try SSIS.

    Now, if it's one shot, you can do a copy and paste in SSMS.

    Tuesday, February 15, 2011 10:23 AM
  • not automating, it has to be done manually as of now.
    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Tuesday, February 15, 2011 10:24 AM
  • the right click on a tabl in SSMS, then "Edit".

    make sure the columns are ordered in the same way in SSMS and Excel. then cut and paste

    Tuesday, February 15, 2011 10:25 AM
  • Why don't you import all data from Excel to a table and then validate column values, finally transfer the records to the main table.

    Also to validate each column you can write a VB macro on excel.


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Tuesday, February 15, 2011 10:27 AM
  • Why don't you import all data from Excel to a table and then validate column values, finally transfer the records to the main table.

    Also to validate each column you can write a VB macro on excel.


    ~Manu
    http://sqlwithmanoj.wordpress.com

    Hi Manu Could u please tell me how to do that VB macro work ?

    would be grateful !

     

    Hi StefDBA,

    i tried the SSIS as given in 

    http://www.builderau.com.au/program/sqlserver/soa/How-to-import-an-Excel-file-into-SQL-Server-2005-using-Integration-Services/0,339028455,339285948,00.htm

    but i dint get any validation option over there, like the a column values should fall within a defined range.

     

    Thanks !

     

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Tuesday, February 15, 2011 10:37 AM
  • What you can do is create a macro enabled excel file (*.xlsm), if you are on MS Office 2007. Or if less than 2007 then .xls would be fine.

    Open the excel file, click ALT+F11 & copy the code below in Sheet1, or where your data is. You would need to tweak & customize the code according to your needs.

     

    ' VBA Macro code | START
    
    
    
    
    Private
    
    
    
     Sub
    
    
    
     Workbook_BeforeSave(ByVal
    
    
    
     SaveAsUI As
    
    
    
     Boolean
    
    
    
    , Cancel As
    
    
    
     Boolean
    
    
    
    )
    
    Dim
    
    
    
     errorCode As
    
    
    
     Integer
    
    
    
    
    Dim
    
    
    
     errorMsg As
    
    
    
     String
    
    
    
    
    
    Dim
    
    
    
     numOfRecs As
    
    
    
     Integer
    
    
    
    
    Dim
    
    
    
     counter As
    
    
    
     Integer
    
    
    
    
    
    '---------------------
    
    
    
    
    'Start - Validate Date
    
    
    
    
    '---------------------
    
    
    
    
    Sheet1.Activate
    Range("A2"
    
    
    ).Select
    
    
    
    
    
    errorCode = 0
    numOfRecs = 0
    
    Do
    
    
    
     Until
    
    
    
     ActiveCell.Value = vbNullString
    
     If
    
    
    
     IsDate(ActiveCell.Value) <> True
    
    
    
     Then
    
    
    
    
     errorCode = 1
     ActiveCell.Interior.ColorIndex = 3 'Red
    
    
    
    
     Else
    
    
    
    
     ActiveCell.Interior.ColorIndex = 2 'White
    
    
    
    
     End
    
    
    
     If
    
    
    
    
     
     numOfRecs = numOfRecs + 1
     
     ActiveCell.Offset(1, 0).Select
    
    
    
    
    
    Loop
    
    
    
    
    
    If
    
    
    
     errorCode = 1 Then
    
    
    
    
     errorMsg = errorMsg + vbCrLf & "- Invalid Insert Date"
    
    
    
    
    End
    
    
    
     If
    
    
    
    
    '-------------------
    
    
    
    
    'End - Validate Date
    
    
    
    
    '-------------------
    
    
    
    
    
    
    '--------------------------
    
    
    
    
    'Start - Validate Customer#
    
    
    
    
    '--------------------------
    
    
    
    
    Sheet1.Activate
    Range("B2"
    
    
    ).Select
    
    
    
    
    
    errorCode = 0
    
    Do
    
    
    
     Until
    
    
    
     ActiveCell.Value = vbNullString
    
     If
    
    
    
     IsNumeric(ActiveCell.Value) <> True
    
    
    
     Or
    
    
    
     Len(ActiveCell.Value) > 12 Then
    
    
    
    
     errorCode = 1
     ActiveCell.Interior.ColorIndex = 3 'Red
    
    
    
    
     Else
    
    
    
    
     ActiveCell.Interior.ColorIndex = 2 'White
    
    
    
    
     End
    
    
    
     If
    
    
    
    
     
     ActiveCell.Offset(1, 0).Select
    
    
    
    
    
    Loop
    
    
    
    
    
    If
    
    
    
     errorCode = 1 Then
    
    
    
    
     errorMsg = errorMsg + vbCrLf & "- Invalid Customer #"
    
    
    
    
    End
    
    
    
     If
    
    
    
    
    '------------------------
    
    
    
    
    'End - Validate Customer#
    
    
    
    
    '------------------------
    
    
    
    
    
    
    Range("A1"
    
    
    ).Select
    
    
    
    
    
    If
    
    
    
     errorCode = 1 Then
    
    
    
    
     MsgBox "Workbook not saved. Following are the fields that contain invalid values."
    
    
    
     _
     & vbCrLf & errorMsg & vbCrLf & vbCrLf & _
     "Please correct the values highlighted in RED color."
    
    
    
    , vbCritical, "Data Validation ERROR"
    
    
    
    
    
     Cancel = True
    
    
    
    
     
    End
    
    
    
     If
    
    
    
    
    
    End
    
    
    
     Sub
    
    
    
    
    ' VBA Macro code | END
    
    
    
    
    

    --////////////////////////////////////////////////////////////////////////////////////////////////////

    The above code will run when you will try to save the excel, or you can change the event.

    This will validate the excel with 2 columns of Date & customer number.

     

    InsertDate CustomerNo
    11/16/2009 91878552
    11/16/2009 101899768
    11/16/2009 101768884
    11/16/2009  123456789123
    11/16/2009 101768800

    ~Manu
    http://sqlwithmanoj.wordpress.com/2011/02/04/excel-data-validation-with-vba-macros/
    Tuesday, February 15, 2011 10:48 AM
  • What you can do is create a macro enabled excel file (*.xlsm), if you are on MS Office 2007. Or if less than 2007 then .xls would be fine.

    Open the excel file, click ALT+F11 & copy the code below in Sheet1, or where your data is. You would need to tweak & customize the code according to your needs.

     

    ' VBA Macro code | START
    Private
     Sub
     Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Dim errorCode As Integer
    Dim errorMsg As String
    Dim numOfRecs As Integer
    Dim counter As Integer
    
    '---------------------
    'Start - Validate Date
    '---------------------
    
    
    Sheet1.Activate Range("A2").Select
    errorCode = 0
    numOfRecs = 0
    
    Do
     Until
     ActiveCell.Value = vbNullString
     If
     IsDate(ActiveCell.Value) <> True
     Then
     errorCode = 1
     ActiveCell.Interior.ColorIndex = 3 'Red
     Else
     ActiveCell.Interior.ColorIndex = 2 'White
     End
    
     If
     numOfRecs = numOfRecs + 1
     ActiveCell.Offset(1, 0).Select
    Loop
    If
     errorCode = 1 Then
     errorMsg = errorMsg + vbCrLf & "- Invalid Insert Date"
    End
     If
    '-------------------
    'End - Validate Date
    '-------------------
    '--------------------------
    'Start - Validate Customer#
    '--------------------------
    
    
    Sheet1.ActivateRange("B2").Select
    
    errorCode = 0
    Do
     Until
     ActiveCell.Value = vbNullString
     If
     IsNumeric(ActiveCell.Value) <> True
     Or
     Len(ActiveCell.Value) > 12 Then
     errorCode = 1
     ActiveCell.Interior.ColorIndex = 3 'Red
     Else
     ActiveCell.Interior.ColorIndex = 2 'White
     End
    
     If
     ActiveCell.Offset(1, 0).Select
    Loop
    If
     errorCode = 1 Then
     errorMsg = errorMsg + vbCrLf & "- Invalid Customer #"
    End
     If
    '------------------------
    'End - Validate Customer#
    '------------------------
    Range("A1").Select
    If
     errorCode = 1 Then
     MsgBox "Workbook not saved. Following are the fields that contain invalid values."
    _
     & vbCrLf & errorMsg & vbCrLf & vbCrLf & _
     "Please correct the values highlighted in RED color."
    , vbCritical, "Data Validation ERROR"
    
     Cancel = True
    
    End
     If
    End
     Sub
    ' VBA Macro code | END
    

    --////////////////////////////////////////////////////////////////////////////////////////////////////

    The above code will run when you will try to save the excel, or you can change the event.

    This will validate the excel with 2 columns of Date & customer number.

     

    InsertDate CustomerNo
    11/16/2009 91878552
    11/16/2009 101899768
    11/16/2009 101768884
    11/16/2009  123456789123
    11/16/2009 101768800

    ~Manu
    http://sqlwithmanoj.wordpress.com/2011/02/04/excel-data-validation-with-vba-macros/

    Hi Manu ! Thks for your code.

    I guess this code will be very helpful in validating the entry data.

    Will this be used for importing the same to the DB also ?

    Kindly let me know...

    Thanks !!

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Tuesday, February 15, 2011 12:26 PM
  • No, this would only validate your record values. You have to import this excel sheet by using import/export wizard or any other approach.

    But yes you can also add you custom ADO DB connections to the VBA code to insert records from there, but I would not prefer this.


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Tuesday, February 15, 2011 12:35 PM
  • Hi Thanks Manu!

    Fine, ... i can say that the answer proposed by Naomi will be only for validating the data.

    the  need is to get solution for importing as well the validation...

    Thanks !

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Wednesday, February 16, 2011 5:46 AM
  • Hi Thanks Manu!

    Fine, ... i can say that the answer proposed by Naomi will be only for validating the data.

    the  need is to get solution for importing as well the validation...

    Thanks !

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------

    So, In this case you would need to do extra coding as I mentioned above to create DB connections to SQL Server and then fire sql queries dynamically created or some other way from excel.

    Why don't you create a Linked Server to excel and import data in SQL, here is a link where I've mentioned to create one: http://sqlwithmanoj.wordpress.com/2010/11/12/query-excel-file-source-through-linked-server/


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Wednesday, February 16, 2011 5:54 AM
  • Manub22 - Can you repost your code with Insert Code Block? Thanks.

       TO ALL: Use INSERT CODE BLOCK

           for posting code. Thanks!                           

     


    Kalman Toth, Admin/Prog, SSAS, SSIS, SSRS; SQL 2008 GRAND SLAM
    Monday, February 21, 2011 1:34 PM
    Moderator
  • Kalman, I always use the Code Insert block, but don't know why the code spitted.

    Here it is again:

    ' VBA Macro code | START
    Private
     Sub
     Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Dim errorCode As Integer
    Dim errorMsg As String
    Dim numOfRecs As Integer
    Dim counter As Integer
    
    '---------------------
    'Start - Validate Date
    '---------------------
    
    
    Sheet1.Activate Range("A2").Select
    errorCode = 0
    numOfRecs = 0
    
    Do
     Until
     ActiveCell.Value = vbNullString
     If
     IsDate(ActiveCell.Value) <> True
     Then
     errorCode = 1
     ActiveCell.Interior.ColorIndex = 3 'Red
     Else
     ActiveCell.Interior.ColorIndex = 2 'White
     End
    
     If
     numOfRecs = numOfRecs + 1
     ActiveCell.Offset(1, 0).Select
    Loop
    If
     errorCode = 1 Then
     errorMsg = errorMsg + vbCrLf & "- Invalid Insert Date"
    End
     If
    '-------------------
    'End - Validate Date
    '-------------------
    '--------------------------
    'Start - Validate Customer#
    '--------------------------
    
    
    Sheet1.ActivateRange("B2").Select
    
    errorCode = 0
    Do
     Until
     ActiveCell.Value = vbNullString
     If
     IsNumeric(ActiveCell.Value) <> True
     Or
     Len(ActiveCell.Value) > 12 Then
     errorCode = 1
     ActiveCell.Interior.ColorIndex = 3 'Red
     Else
     ActiveCell.Interior.ColorIndex = 2 'White
     End
    
     If
     ActiveCell.Offset(1, 0).Select
    Loop
    If
     errorCode = 1 Then
     errorMsg = errorMsg + vbCrLf & "- Invalid Customer #"
    End
     If
    '------------------------
    'End - Validate Customer#
    '------------------------
    Range("A1").Select
    If
     errorCode = 1 Then
     MsgBox "Workbook not saved. Following are the fields that contain invalid values."
    _
     & vbCrLf & errorMsg & vbCrLf & vbCrLf & _
     "Please correct the values highlighted in RED color."
    , vbCritical, "Data Validation ERROR"
    
     Cancel = True
    
    End
     If
    End
     Sub
    ' VBA Macro code | END
    
    


    ~Manu
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Monday, February 21, 2011 1:43 PM