dcsimg
Results 1 to 11 of 11

Thread: problem while importing

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2009
    Posts
    14

    problem while importing

    I am trying to export the excel data to sql server using the following code. It works fine. but i want to select a range of data from excel instead of select * i.e for example to extract data from 5th row... How do i do that...??? any valuable replies??

    Code:
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
     
    'Dim con1 As New ADODB.Connection
     
    Dim CreateStr As String
    Dim Sqlstr As String
     
    Dim i As Integer
     
    If con.State = adStateOpen Then con.Close
    'If con1.State = adStateOpen Then con1.Close
     
    If rs.State = adStateOpen Then rs.Close
     
    'con1.Open "Driver={SQL Server};" & _
    '           "Server=MYSEVER;" & _
    '           "Database=MYDATABASE;" & _
    '           "Uid=MYUID;" & _
    '           "Pwd=MYPWD" ''''''''''''''''' 
     
    con.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
               "DriverId=790;" & _
               "Dbq= D:/test.xls;" & _
               "DefaultDir=D:\"
               
    rs.Open "select * from [Sheet1$]", con, 3, 1
     
        For i = 0 To rs.Fields.Count - 1
          CreateStr = CreateStr & rs.Fields(i).Name & " varchar(" & rs.Fields(i).ActualSize & "),"
        Next
        
        objCon.Execute " Create table Temptable" & PID & "(" & Mid(CreateStr, 1, Len(CreateStr) - 1) & ")"
        
        i = 0
        
        While Not rs.EOF
        
            For i = 0 To rs.Fields.Count - 1
                Sqlstr = Sqlstr & "'" & rs.Fields(i).Value & "',"
            Next
            
            Sqlstr = "Insert into Temptable" & PID & "  Values(" & Mid(Sqlstr, 1, Len(Sqlstr) - 1) & ")"
            objCon.Execute Sqlstr
            Sqlstr = ""
          rs.MoveNext
       Wend
            
    End Sub
    Last edited by RobDog888; Oct 8th, 2009 at 12:47 AM. Reason: Added [code] tags

  2. #2
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,698

    Re: problem while importing

    Welcome to the Forums.

    Excel is not really a database. Its just a spreadsheet. So 5th row means nothing to sql. You would need to create a query that will extract the contents from the 5th row down but only if there is some valid criteria for duplicating what you see from that point down.

    For example if your sheet was sorted so that the data was sorted then performing an ORDER BY clause along with something to prevent thos first 5 rows to be included. Maybe getting a Count(*) and subtracting 5 from that to be placed in your final query.
    Last edited by RobDog888; Oct 8th, 2009 at 12:46 AM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2009
    Posts
    14

    Re: problem while importing

    sorry did not get that clearly.. Actually in the excel first 5 rows have some title and formatting stuffs....the rest the data... so was asking to extrct only those... attached is the excel..hope i am clear with my requiremnet... if any other means to do that... tht will be great..!!!
    Attached Images Attached Images  

  4. #4
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,698

    Re: problem while importing

    Oh so its not all data. Well then maybe a way to skip that with a sql statement example like...

    SELECT * FROM Sheet1 WHERE Column1 Not Like 'Filter%' and Column1 Not NULL;

    Best to get it all and in your recordset just move absoluteposition to the 7th row where your data starts but I see you are doing that already. Just a good example of why Excel is not good to use as a database.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    New Member
    Join Date
    Oct 2009
    Posts
    14

    Re: problem while importing

    Thanks for ur reply...n sorry for the late response..!!!

    Now one more issue to add on.If in the excel i hav a datecolumn i want to check if it is a date type and only then proceed to import to sql server. How would i accomplish??

  6. #6
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,698

    Re: problem while importing

    Sure no problem. Just use the .Type property of your recordset. Date/Time is type 135.

    Code:
    If oRs.Fields("WhateverField").Type = 135 Then
        'Date/Time field type
        '...
        '...
    End If
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7

    Thread Starter
    New Member
    Join Date
    Oct 2009
    Posts
    14

    Re: problem while importing

    Two more issues??
    1.
    for looping through the recordset i am using the below code.
    While Not rs.EOF

    For i = 0 To rs.Fields.Count - 1
    Sqlstr = Sqlstr & "'" & rs.Fields(i).Value & "',"
    Next

    Sqlstr = "Insert into Temptable Values(" & Mid(Sqlstr, 1, Len(Sqlstr) - 1) & ")"
    objCon.Execute Sqlstr
    Sqlstr = ""
    rs.MoveNext
    Wend

    Here how will i set to the absolute position to 7.
    2.
    in between if i have date columns with adition to the columns in the attached excel how do i identify the datecolumn values alone and validate using ur code.?
    col1 col2 col3-date col4 col5-date
    Hope i am clear with my question

  8. #8
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,698

    Re: problem while importing

    You shouldnt rely upon the AbsolutePosition property as it will change when you add or delete records. What is it you are trying to do?

    You can look at the example I posted and test each feild of concern to you. Are you looking for specific values or if the column contains a particular type for the column?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  9. #9

    Thread Starter
    New Member
    Join Date
    Oct 2009
    Posts
    14

    Re: problem while importing

    let me explain u my requirements.
    i have an excel as attached earlier and that excel will contain date columns also... Now i want to validate the date column values in the excel and only if its fine want to import the data except the first few rows to the sql server.

  10. #10
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,698

    Re: problem while importing

    Then if you need to validate each date entry then you need to loop through the entire column checking each cells data as a valid date, use isDate(), and then if sucessful proceed with the impoirt.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  11. #11

    Thread Starter
    New Member
    Join Date
    Oct 2009
    Posts
    14

    Re: problem while importing

    how do i loop through the column?? while rs.eof wend loops through rowwise... please can u provide any sample code.. it will be of great help...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width