Import Data from SQL server to excel

[abhay_547]

Hi,

I have recorded below macro which works fine to extract data from SQL server but If I have more then 65536 rows of data in my sql table for the criteria which i entered in the below macro but still the macro exports only 65536 rows of data and then stops. ideally it should populate rest of the data in a new worksheet.

Can we modify the below code in such a way that it imports the rest of the data to new worksheet if the number of records exceed 65536.

Sub Extractdata()

'
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER=SQL Native Client;SERVER=XXXXXX;UID=admin;PWD=****;APP=Microsoft Office XP;WSID=XXXXXXX" _
        ), Array(";DATABASE=meta_data;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT mydata.CAC, mydata.Year, mydata.""Cost Element"", mydata.""Cost Element Name"", mydata.Name, mydata.""Cost Center"", mydata.""Company Code"", mydata.""Unique Indentifier 1"", ""Cost Center mapping"".""Produ" _
        , _
        "ct UBR Code"", ""Cost Element Mapping"".FSI_LINE2_code" & Chr(13) & "" & Chr(10) & "FROM sap_data.dbo.""Cost Center mapping"" ""Cost Center mapping"", sap_data.dbo.""Cost Element Mapping"" ""Cost Element Mapping"", sap_data.dbo.mydata myda" _
        , _
        "ta" & Chr(13) & "" & Chr(10) & "WHERE mydata.""Unique Indentifier 1"" = ""Cost Element Mapping"".CE_SR_NO AND mydata.""Cost Center"" = ""Cost Center mapping"".""Cost Center"" AND ((""Cost Center mapping"".""Product UBR Code""='G_0768') AND (""" _
        , "Cost Element Mapping"".FSI_LINE2_code='F1547000000'))")
        .Name = "Query from mydatanew"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Thanks for your help in advance.

[abhay_547]

Hi All,

Awaiting for a reply..Please help....

[NeedSomeAnswers]

I dont see how you can do this using a querytable approach without using 2, one on each worksheet which only select half of the records each. A querytable actually has a physical location on a sheet and is tied to a single sheet, it wont split across sheets.

I suppose the question to ask is do you specifically need to view this in excel ? it would be fairly easy to export to .csv directly from SQL Server, in effect the opposite way round to what you are doing now.

[abhay_547]

Hi,

Thanks a lot for your reply, But I want the data in excel only. Is there any way by which we can loop the exceeded records to next worksheet once the query results reach the row 65536 in current worksheet.

Thanks a lot for your reply in advance.

[NeedSomeAnswers]

Not using the Query table approach, as i said it is tied to 1 sheet. When you create a Query table you have to give it a destination range (in your querytable above it is A1) this in effect ties it to 1 sheet.

If you look at the querytable macro code you have recorded it does not loop through the records but instead just dumps the whole result set straight onto the worksheet.

To do what you would like to do, really the only way would be to use VBA to create a recordset object and loop through it.

You would then have the kind of control you want and be able to change worksheet when you got to row 65536.

Have you ever done any VB or VBA other than recording macros ?

[abhay_547]

Hi,

I have written some macros but as far as pulling data from external sources is concerned, I am not so good at the same. Following is the code which i have which at my end (which i have not recorded). But I have certain issues with the same.

Sub DataExtract()
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=XXXXXXX;INITIAL CATALOG=XXXXX;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnPubs.Open strConn

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
    .Open "SELECT * FROM [Cost Center Mapping]"
    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A1").CopyFromRecordset rsPubs
    
    ' Tidy up
    .Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing


End Sub

Issues which I have with the above code :

1) I am not sure how to replicate the below sql statement in the above code SQL Statement :

"SELECT mydata.CAC, mydata.Year, mydata.""Cost Element"", mydata.""Cost Element Name"", mydata.Name, mydata.""Cost Center"", mydata.""Company Code"", mydata.""Unique Indentifier 1"", ""Cost Center mapping"".""Produ" _
        , _
        "ct UBR Code"", ""Cost Element Mapping"".FSI_LINE2_code" & Chr(13) & "" & Chr(10) & "FROM sap_data.dbo.""Cost Center mapping"" ""Cost Center mapping"", sap_data.dbo.""Cost Element Mapping"" ""Cost Element Mapping"", sap_data.dbo.mydata myda" _
        , _
        "ta" & Chr(13) & "" & Chr(10) & "WHERE mydata.""Unique Indentifier 1"" = ""Cost Element Mapping"".CE_SR_NO AND mydata.""Cost Center"" = ""Cost Center mapping"".""Cost Center"" AND ((""Cost Center mapping"".""Product UBR Code""='G_0768') AND (""" _
        , "Cost Element Mapping"".FSI_LINE2_code='F1547000000'))")

2) Above mentioned code only gives the data but doesn't give the header rows.
3) How to loop the records if the data rows exceed 65536.

Please help..

Thanks for your help in advance.

[abhijit]

Are you using VBA within Excel to do that?

In any case you need to maintain a count of rows getting populated. Then switch over to a new sheet when the count = 65536.

The other option for you would be to try this code in Excel 2007 or Excel 2010.

[abhay_547]

Hi Abhijeet,

All users in our team are using Excel 2003 so the rows on a worksheet are limited to only 65536 rows. Can you please help me with the code so that I can loop the exceeded data to the next worksheet.

Thanks a lot for your help in advance.

[TysonLPrice]

I didn't look that closely at it but maybe....

http://excel.tips.net/Pages/T002533_...ata_Files.html

[abhay_547]

Hi Tyson,

Thanks a lot for your reply, but the link provided by you in your post is for importing the huge text files data into excel whereelse I need the macro which will pull the data from sql table and loop to next worksheet if the same data exceeds 65536 rows.

Please help..

Thanks a lot for your help in advance.

[abhay_547]

Hi All,

I have done some extensive google search and I have got the below code. Can we use looping part of the macro in my main macro which was posted by me in the first post of this thread. Please help.

Sub ExportChunks(Source As String, FolderPath As String, BaseName As String)

Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim fldF As DAO.Field
Dim lngFN As Long
Dim j As Long
Dim lngChunk As Long
Dim lngChunkCount As Long
Dim strTarget As String
Dim strLine As String

Const MAX_CHUNK = 65536
Const STD_CHUNK = 50000
Const DELIM = Chr(9) 'tab-delimited; change

' argument checking and error trapping omitted

'Get ready
Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset(Source, dbOpenForwardOnly)
rsR.MoveLast 'ensure we get full record count

If rsR.RecordCount = 0 Then
MsgBox "No records to export", vbOKOnly + vbInformation
   Exit Sub
 ElseIf rsR.RecordCount <= MAX_CHUNK Then
       lngChunk = MAX_CHUNK
   Else
   lngChunk = STD_CHUNK
End If

rsR.MoveFirst
lngChunkCount = 0

Do Until rsR.EOF 'Outer loop: once per file
'Open output file
j = 0
lngChunkCount = lngChunkCount + 1
strTarget = FolderPath & "\" & BaseName _
& Format(lngChunkCount, "00") & ".xls"
lngFN = FreeFile()

Open strTarget For Output As #lngFN

Do Until (j = lngChunk) Or rsR.EOF

'inner loop: once per record
'assemble fields into string
strLine = ""
For Each fldF In rsR.Fields
strLine = strLine & CStr(Nz(fldF.Value, "")) _
& DELIM
Next
Print #lngFN, strLine
j = j + 1
Loop 'inner
Close #lngFN
Loop 'outer

rsR.Close
Set rsR = Nothing
Set dbD = Nothing

End Sub

Thanks a lot for your help in advance.

[NeedSomeAnswers]

abhay what you need to do is to loop through your recordset, and place each item into the next position in your spreadsheet.

I would help you but unfortunately i have got deadlines this morning. If you still haven't got an answer this afternoon i will help you then.

In the mean time what i would suggest is to repost this in the OFFICE Development forum, or do a post with a link to this one, Westconn1 or Koolsid who virtually live in that forum would also be able to help you with this no problem, they would probably have a fix for you in no time !

[Siddharth Rout]

My Suggestion would be to export the database to a flat file (csv) using something like this below...

Private Sub ExportToFile(rs As Recordset, FileName As String, Delimiter As String)
    If rs.EOF Then Exit Sub
    
    Dim fNum As Integer
    fNum = FreeFile
    Open App.Path & "\" & FileName For Output As fNum
    Print #fNum, rs.GetString(adClipString, , Delimiter, vbCrLf)
    Close #fNum
End Sub

This is definitely faster than writing to Excel file directly...

Once the .csv file is ready then count the number of lines and if it exceeds 65536 then simply split it

[abhay_547]

Hi,

Thanks a lot for your reply, First up all I am not sure how to replicate whatever you have mentioned in your post to my macro and apart from that I think instead making it more complex by involving a text file in between we should try to loop the records to the next worksheet in excel itself if the record exceed 65536 rows.

Here is my initial code which I had posted.

Sub Extractdata()

'
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER=SQL Native Client;SERVER=XXXXXX;UID=admin;PWD=****;APP=Microsoft Office XP;WSID=XXXXXXX" _
        ), Array(";DATABASE=meta_data;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT mydata.CAC, mydata.Year, mydata.""Cost Element"", mydata.""Cost Element Name"", mydata.Name, mydata.""Cost Center"", mydata.""Company Code"", mydata.""Unique Indentifier 1"", ""Cost Center mapping"".""Produ" _
        , _
        "ct UBR Code"", ""Cost Element Mapping"".FSI_LINE2_code" & Chr(13) & "" & Chr(10) & "FROM sap_data.dbo.""Cost Center mapping"" ""Cost Center mapping"", sap_data.dbo.""Cost Element Mapping"" ""Cost Element Mapping"", sap_data.dbo.mydata myda" _
        , _
        "ta" & Chr(13) & "" & Chr(10) & "WHERE mydata.""Unique Indentifier 1"" = ""Cost Element Mapping"".CE_SR_NO AND mydata.""Cost Center"" = ""Cost Center mapping"".""Cost Center"" AND ((""Cost Center mapping"".""Product UBR Code""='G_0768') AND (""" _
        , "Cost Element Mapping"".FSI_LINE2_code='F1547000000'))")
        .Name = "Query from mydatanew"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

I have done some google search and I got the below link may be this thing may help in thinking ahead in this direction.

http://books.google.co.in/books?id=a...page&q&f=false

Page 137 to 140.

Thanks for your help in advance.

[Siddharth Rout]

instead making it more complex by involving a text file in between we should try to loop the records to the next worksheet in excel itself if the record exceed 65536 rows.

1) It's not complex. In fact it is much more simpler.
2) It's not exactly a textfile. This explains what a CSV file is. And this is what it looks like.
3) Looping through that many records and writing to Excel file is definitely cumbersome and time consuming...

However if you still want to do it by looping and writing directly to the Excel File then I leave that decision to you

[abhay_547]

Hi koolsid,

Thanks a lot for your reply, I would like to stick to the excel solution. Is it possible for you to provide me with some line of code which will loop the exceeded row records to next worksheet so that I can replicate the same to my main recorded macro.

Thanks a lot for your help in advance.

[abhay_547]

Hi Koolsid / All,

I have got the below code through a extensive google search which I think can be helpful. Can you please help me to replicate the below in my macro recorded macro in such a way that it works fine as per my requirement.

Sub checkup_backup()
Dim cn As Object, rs As Object, Status As Range
Dim MySql As String, dbfullname As String, myCnt As Long
dbfullname = "c:\Shweta\EFT.mdb"
Set Status = ActiveSheet.Range("A2") 'SQL Variable
MySql = "Select * from Countrymaster"
'Status = Empty 'Clear SQL variable string

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbfullname & ";" 'Create DB connection

Set rs = CreateObject("ADODB.Recordset")
With rs
Set .ActiveConnection = cn
.Source = MySql 'Pass your SQL
.Open , , adOpenStatic, adLockOptimistic
myCnt = .RecordCount
If myCnt > 0 Then
.MoveLast: .MoveFirst
'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
3 fields in the sql pass
Sheets(1).Range(Cells(1, 1), Cells(myCnt, 2)).CopyFromRecordset rs
End If
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub

Thanks a lot for your help in advance.