| Subject | Re: Exportdata sinks |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Sun, 2 Aug 2020 15:43:41 +0200 |
| Newsgroups | dbase.getting-started |
| Attachment(s) | MySQL_to_Excel.prg |
On 2020-08-02 14:19, Bamidele Onwu wrote: > Wow, I didn't know about this. Thanks to you Mervyn. > > I seriously need help but I use mysql as backend. I will be very grateful if I can get sample program of how to export from sql to excel. > There is a comprehensive article on moving data from dBASE to Excel in the Advanced Topics of the knowledgebase http://www.dbase.com/Knowledgebase/adv/activex/ExcelOle/excel.htm Attached is a rudimentary (but working) program to extract 3 fields from a MySQL table and place them in an Excel spreadsheet. You will, of course, need to change the connection string as well as the table and field names. Mervyn. | |
d = new adodatabase() d.connectionString = "Provider=MSDASQL.1;Persist Security Info=True;Data Source=energydata" d.active = true q = new adoquery() q.sql = "select siteId,eTimestamp,econs from energydata where siteId = '227105' " q.sql += "and eTimestamp between '2020-06-05 010:00:00' and '2020-06-05 12:55:00' " q.active = true oExcel = new oleAutoclient("excel.application") // oExcel.visible = true oExcel.workbooks.add() nRow = 1 //Add headings for i = 1 to q.rowset.fields.size oExcel.ActiveSheet.cells( nRow, i ).select() with ( oExcel.ActiveCell ) if i = 1 ColumnWidth := 8 formula := 'siteID' elseif i = 2 ColumnWidth := 20 formula := 'Timestamp' elseif i = 3 ColumnWidth := 10 formula := 'Econs' endif endwith next nRow++ do while not q.rowset.endofset for i = 1 to q.rowset.fields.size oExcel.ActiveSheet.cells( nRow, i ).select() if i = 1 oExcel.Activecell.formula = "'"+q.rowset.fields[i].value elseif i = 2 oExcel.Activecell.formula = "'"+dttoc(q.rowset.fields[i].value) elseif i = 3 oExcel.Activecell.formula = q.rowset.fields[i].value endif next nRow++ q.rowset.next() enddo q.active= false d.active = false oExcel.visible = true | |