Link to home
Create AccountLog in
Avatar of jasgot
jasgot

asked on

Need to export 248K records from MSSQL server to EXCEL or TABBED file

I have a query in MS Access that gets exactly what I want from the MS SQL server. But I am limited to 65K records being transferred at a time. The error is telling me that it is a clipboard limitation.

How can I get these records to EXCEL or a tab delimited file.  Tab delimited is my goal, and I have been doing that from excel, but if I can go straight to a flat file, that would be fine.

Any suggestions on this would be very helpful.  Need to get 248K records from SQL to tabbed flat file.

Thanks.
Avatar of dannywareham
dannywareham
Flag of United Kingdom of Great Britain and Northern Ireland image

You'd probably be better asking this in teh SQL Server area, but I'll give it a go anyway.

In the table, do you have an incremented unique ID (an autonumber or similar)?

What you can do is create a loop that counts the number of records and exports them to a worksheet.
Once it reaches record 65,000, change the worksheet that it appends to...

Alternatively, you can use SQL to export a query of the first 65000 records, then use a second query to match teh next 65,000, where they don't match the original ones...

idea?
Hmm, Access has no such limitation.
Best to link the MS SQL table (view) using ODBC when you want to access the data.
For the export to excel you'll need to split the data into 64,000 slices per datasheet, but a transfer to a textfile will be just one command and will hold everything.

Need more info ?

Nic;o)
Avatar of jasgot
jasgot

ASKER

Nic;o)

More info please. I am already talking to SQL with odbc. That's how my query is being populated.
It's the automated slice that has me stuck. If I can go to a tabbed flat file one fell swoop, then count me in!
The "easy" way would be to use a query collecting 64000 row slices like:

select top 64000 * from tblMSSQL where ID>[startvalue];

This needs to be repeated for multiple sections, thus we have to get the MAX retrieved key from such a query and loop untill no longer 64000 rows are found.
As stated by danny a unique key will be required and from my query you can read it's ID.

Now we can loop manipulating the query like:

dim rs as dao.recordset
dim qd as querydef
dim maxID as long
dim nextID as long

set qd = currentdb.querydefs("qryDummy")
set rs = currentdb.openrecordset("select * from tblMSSQL")

if rs.eof and rs.bof then
   exit sub
endif
' get first set
if rs.recordcount <= 64000 then
   qd.SQL="select top 64000 * from tblMSSQL;"
   docmd.transferspreadsheet acExport,acSpreadsheetTypeExcel5,qd.name,"c:\test00000.xls",true
else
   maxID = DMAX("ID","tblMSSQL")  
   nextID = 0
   while nextID < maxID
      qd.SQL="select top 64000 * from tblMSSQL where ID>" & dblCount
      nextID = DMAX("ID",qd.name)  
      docmd.transferspreadsheet acExport,acSpreadsheetTypeExcel5,qd.name,"c:\test" & nextID & ".xls",true
   wend
endif

Getting the idea ?
I guess you can change the keyfield and the tablename and create a dummy query named "qryDummy".

Nic;o)
Bear in mind that if you are trying to export to an Excel file in a format prior to 2003 (I think), you cannot hold more than 65K records in a single file (this is a limitation of Excel).

However, exporting as a text file should successfully do it. What method are you using to export? TransferText should certainly work to get it into a tab delimited file. You can also write your own function to export the query:

Sub SaveQueryToFile(strQuery As String, strFile As String)
Dim rst As DAO.Recordset
Dim I As Long
Dim strFieldVal As String

Set rst=CurrentDb.OpenRecordset(strQuery)
Open strFile For Binary As #1
While Not rst.EOF
   For I=0 To rst.Fields.Count-1
      strFieldVal=CStr(rst(I))
      Put #1, ,strFieldVal
      If I<>(rst.Fields.Count-1) Then
         strFieldVal=Chr(9)
      Else
         strFieldVal=vbCrLf
      End If
      Put#1,,strFieldVal
   Next
   rst.MoveNext
Wend
rst.Close
Set rst=Nothing
End Sub
Avatar of Gustav Brock
Sounds like you do some copy and paste ... but if you need to output an CSV file, why not do that directly?

Look here (File I/O in VB) how to write the retrieved records using your query as the source directly to a text file:

  http://puma.agron.ksu.edu/~sgsax/download/

It is not that difficult, and it is very fast.

/gustav

Avatar of jasgot

ASKER

Gustav,

I found this:
But can't get it to run with out a tone of errors, the first being: User defined type not defined.

    Dim dbs As Database
    Dim rst As Recordset
    Dim intFileDesc As Integer      'File descriptor for output file (number used by OS)
    Dim strOutput As String         'Output string for entry
    Dim strRecordSource As String   'Source for recordset, can be SQL, table, or saved query
    Dim strOutfile As String        'Full path to output file

    Kill strOutfile                 'Delete the output file before using it.
                                    'Not necessary, but ensures you have a clean copy every time
    intFileDesc = FreeFile          'Get a free file descriptor
    Open strOutfile For Binary As #intFileDesc 'Open the output file for writing
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strRecordSource) 'Open the recordset based on our source string
    While Not rst.EOF               'Loop until end of recordset
        strOutput = rst!Field1 & ";" & rst!Field2 & ";" & rst!Field3
        Print #intFileDesc, strOutput   'Print output string to file
        rst.MoveNext                     'Advance to next record in recordset
    Wend
    Close #intFileDesc              'Close output file
    rst.Close                       'Close this recordset
    Set rst = Nothing               'Garbage handling before we exit the function
    Set dbs = Nothing
You could try mine, but you will need a reference to DAO (Tools->References in the VBA window, check Microsoft DAO 3.6). Doing that should also makeyour code above work. Note that the code I provided will output a TAB delimited file, not a comma delimited file.
Did you try mine ?

Nic;o)
Yes, it sounds like you miss a reference to DAO ...

/gustav
Avatar of jasgot

ASKER



Gustav,
error:
Object variable or with block variable not set
on this line:
Set rst = dbs.OpenRecordset(strRecordSource) 'Open the recordset based on our source string
Avatar of jasgot

ASKER

shanesuebsahakarn

Tried yours, but don't where to put it or how to trigger it.
Avatar of jasgot

ASKER

Nic;o)

I want to do it on one pass with out having to set values after every 65k
Avatar of jasgot

ASKER

I have a form with one button whose name is "export2tabbed"

Jasgot,

You can just put it into a separate module. To trigger it manually, type:

SaveQueryToFile "<your SQL or query name goes here","<Your Filename goes here>"

You can trigger it in the same way from VBA - you can put the call into a command button, for example.
Avatar of jasgot

ASKER

Object variable or with block variable not set on this line:
Set rst=CurrentDb.OpenRecordset(strQuery)
Did you add the reference to DAO?

Is the data you are trying to export a linked table/view, or is it a passthrough query? If you are passing SQL to either my code or the one that you found, could you paste the SQL here?
You need to state these as:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
 
/gustav
Avatar of jasgot

ASKER

I can't answer question. I know that where I normally look to get the sql in a query is greyed out here.
The top of my query says "View" not query........
Avatar of jasgot

ASKER

Hey I found it!

SELECT     dbo.Client_Desc.Label, dbo.Client_Desc.Rate_Group_ID, dbo.Client_Desc.First_Name, dbo.Client_Desc.Last_Name, dbo.Client_Desc.Address1,
                      dbo.Client_Desc.Address2, dbo.Client_Desc.City, dbo.Client_Desc.Postal, dbo.Client_Desc.Plate_Number, dbo.Client_Desc.Vehicle_Length,
                      dbo.Client_Desc.Description, dbo.Client_Desc.Email, dbo.Client_Desc.Phone, dbo.Client_Desc.Prov_State, dbo.Client_Desc.Country,
                      dbo.Rez_Desc.Label AS Expr1, dbo.Rez_Desc.Unit_ID, dbo.Rez_Desc.Open_Date, dbo.Rez_Desc.Close_Date, dbo.Rez_Desc.Hold_Date,
                      dbo.Rez_Desc.Cancelled, dbo.Rez_Desc.Rez_Number, dbo.Rez_Desc.Made_Date, dbo.Client_Desc.Company_Name, dbo.Client_Desc.Fax_Number,
                      dbo.Client_Desc.Work_Number, dbo.Vehicle_Type.Vehicle_Type, dbo.Transactions.Trans_Total, dbo.Transactions.Trans_Desc
FROM         dbo.Client_Desc INNER JOIN
                      dbo.Rez_Desc ON dbo.Client_Desc.Client_ID = dbo.Rez_Desc.Client_ID INNER JOIN
                      dbo.Vehicle_Desc ON dbo.Client_Desc.Vehicle_ID = dbo.Vehicle_Desc.Vehicle_ID INNER JOIN
                      dbo.Vehicle_Type ON dbo.Vehicle_Desc.Vehicle_Type_ID = dbo.Vehicle_Type.Vehicle_Type_ID INNER JOIN
                      dbo.Transactions ON dbo.Client_Desc.Client_ID = dbo.Transactions.Client_ID
OK, there doesn't look to be anything wrong with the SQL. I assume you have it linked as a table in your Access front end?
Avatar of jasgot

ASKER

Nope, I'll try to do that now.
Avatar of jasgot

ASKER

Based on what is about to happen, I think they are already all linked.
OK, so in the Access database, there is a table or a query that you can open directly with the results of the above query? Is the name of that table/query what you are passing into the function?
Avatar of jasgot

ASKER

Yep. I created a query called qJason.  When I open it, I see all 248k records just the way I want.
Hmm, so doing something like this:

SaveQueryToFile "qJason","C:\Temp\Myfile.tsv"

results in the error message?

How about changing the line that errors to:
Set rst=CurrentDb.OpenRecordset(strQuery,dbOpenSnapshot)
Avatar of jasgot

ASKER

Same error. When I hold my mouse over the above line in debug, is says rst = nothing

Remember, what I am calling my query says "view" at the top, is this something different because I am using a adp file to get to my data?
Hmm, should still work. OK, this is the code modified for ADO:

Sub SaveQueryToFile(strQuery As String, strFile As String)
Dim rst As ADODB.Recordset
Dim I As Long
Dim strFieldVal As String

rst.Open strQuery, CurrentProject.Connection
Open strFile For Binary As #1
While Not rst.EOF
   For I=0 To rst.Fields.Count-1
      strFieldVal=CStr(rst(I))
      Put #1, ,strFieldVal
      If I<>(rst.Fields.Count-1) Then
         strFieldVal=Chr(9)
      Else
         strFieldVal=vbCrLf
      End If
      Put#1,,strFieldVal
   Next
   rst.MoveNext
Wend
rst.Close
Set rst=Nothing
End Sub

Call it with:
SaveQueryToFile "SELECT * FROM qJason","C:\Temp\Myfile.tsv"
Avatar of jasgot

ASKER

same error on this line:
rst.Open strQuery, CurrentProject.Connection


the strquery is correct when I hold the mouse over it, and the CurrentProject.Connection appears correct also, but I cannot see the whole line, it ends with "..."
Just out of interest, what happens if you run a single line:

DoCmd.TransferText acExportDelim, , "qJason", "C:\MyFile.csv"

? Just type this into the Immediate window. Does it export a CSV file with all of your records?
Avatar of jasgot

ASKER

Error:
Microsoft Office cannot find the object qJason

It is a valid view/query, I'm looking at it now.!
OK....what if you do this - create a new query within Access:

SELECT * FROM qJason

Save this query as qExport, and try to run the above line with qExport?
Avatar of jasgot

ASKER

Same error: Runtime Error 7874
OK, I'm stumped now. So far as I know, the above should work - except I've just noted you're using an ADP. Let me check a bit, the code might not work in an ADP - although AFAIK, it should.
Avatar of jasgot

ASKER

Still looking for an answer.  I'll double the points.....
Did the code in the link not work?
Avatar of jasgot

ASKER

No. Object not found on the view name. I'm re-linking the tables now.  Trying to find something to use as a unique value.....
Ran into: https://www.experts-exchange.com/questions/21309935/Refresh-Back-End-Table-Links.html
Looks like you can skip the error 7874 with the error handler.

Nic;o)
Avatar of jasgot

ASKER

Just created a new mdb file.  Linked the five tables I need. Created my query. I get this error when I open ONE of the tables OR my query.
ODBC -- Call Failed
Avatar of jasgot

ASKER

So it appears the only way to get to the data is through an apo file.
Avatar of jasgot

ASKER

Got it resolved. Found an App called "Toad for SQL Server" (freeware) took less than five minutes to install it, configure it and get all 248K records into a tabbed flatfile.

HooRAH!!!!!!!
ASKER CERTIFIED SOLUTION
Avatar of RomMod
RomMod

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer