CODEWITHSUNDEEP
visual-foxpro
GunnerFan420
GunnerFan420

Reputation: 198

How to export Foxpro 7 query results to CSV or XLS

I've run a query in the command window and now I want to export it out to a CSV or XLS. I've tried putting the following at the end of the query:

EXPORT TO c:\work\zip.csv XL5

and

COPY TO "C:\work\fac.csv" TYPE CSV

However when I try to run the query after adding either one to the end I get:

Command contains unrecognized phrase/keyword.

I've tried adding (and not adding) a semi-colon to the line above the EXPORT/COPY but I still get the same error. Is it really possible that you can't perform the simple action of kicking query results out to a file?

Thank you!

Upvotes: 0

Views: 3079

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23797

The codes you posted are not the source of the error. It should be about how you write it with your query. For copying as a CSV:

Select ... from ... into cursor crsMyCursor nofilter

COPY TO "C:\work\fac.csv" TYPE CSV

Forget about directly exporting as an excel file. Existing commands are prone to errors. Instead, if you want to save as an excel file, then use automation. For example you could use my VFP2Excel function (you might find variations of this on the internet). ie:

Local myQuery, loStream As 'Adodb.stream'

Text to myQuery noshow
Select Cust_Id As CustomerId, 
    Val(Order_Id) As OrderId, 
    Order_Date As OrderDate, 
    Cast(Evl(Shipped_On, .Null.) As Datetime) As ShippedOn 
    From Orders
EndText

m.loStream = GetDataAsAdoStream("Provider=VFPOLEDB;Data Source="+_Samples+"Data", m.myQuery)

*** Main Excel automation part now
oExcel = Createobject("Excel.Application")
With oExcel
    .DisplayAlerts = .F.
    .Workbooks.Add
    .Visible = .T.
    With .ActiveWorkBook.ActiveSheet
        .Name = 'SampleSheet'
        * Send the data - copy to replacement
        VFP2Excel(m.loStream, .Range("A1"), "Customer ID, Order ID, Ordered On, Shipped On")
        .Columns.AutoFit()
        .Activate
    Endwith
Endwith

Function VFP2Excel(toStream, toRange, tcHeaders)
    Local loRS As AdoDb.Recordset,ix
    loRS = Createobject('Adodb.Recordset')
    m.loRS.Open( m.toStream )
    * Use first row for headers
    Local Array aHeader[1]
    m.toRange.Offset(1,0).CopyFromRecordSet( m.loRS )  && Copy data starting from headerrow + 1
    For ix=1 To Iif( !Empty(m.tcHeaders), ;
            ALINES(aHeader, m.tcHeaders,1,','), ;
            m.loRS.Fields.Count )
        m.toRange.Offset(0,m.ix-1).Value = ;
            Iif( !Empty(m.tcHeaders), ;
            aHeader[m.ix], ;
            Proper(m.loRS.Fields(m.ix-1).Name) )
        m.toRange.Offset(0,m.ix-1).Font.Bold = .T.
    Endfor
    m.loRS.Close()
Endfunc

Procedure GetDataAsAdoStream(tcConnection, tcSQL)
    Local loStream As 'AdoDb.Stream', ;
        loConn As 'AdoDb.Connection', ;
        loRS As 'AdoDb.Recordset'
    loStream = Createobject('AdoDb.Stream')
    loConn = Createobject("Adodb.connection")
    loConn.ConnectionString = m.tcConnection
    m.loConn.Open()
    loRS = loConn.Execute(m.tcSQL)
    m.loRS.Save( loStream )
    m.loRS.Close
    m.loConn.Close
    Return m.loStream
Endproc

Upvotes: 2

Related Questions