Hello,
Is it possible to export SQLitening Table to MS Excel?
Hi Frederick,
I do that everyday in my applications; you have just to read the records with SQLitening commands and write the fields separated by a semi-column character ";" in a sequential PB file in order to create a .csv file that could be read by Exel or Openoffice.
Here is a simple example:
[code]
'--------------------------------------------
' Export the records from the table "Domains"
Sub DomainsExport(ByVal pFileName As String)
'--------------------------------------------
'--------------------------------
' variables for the SQL statement
'--------------------------------
Local lSql As String
Local lResult As Long
'----------------------------------
' variables for the "Domains" table
'----------------------------------
Local lDomainID As Long
Local lDomainDesc As String
Local lDomainTypeDesc As String
'-----------------------------
' open the file in OUTPUT mode
'-----------------------------
ErrClear
Local lFileNum As Long : lFileNum = FreeFile
Open pFilename For Output As lFileNum
If Err Then
MessageBox(HWND_FORM1, "Unable to open the file "+pFileName +" !" & $CrLf, ProductNameAndProductVersion(), %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONWARNING Or %MB_TASKMODAL)
Exit Sub
End If
'------------------------------
' write the Header (first line)
'------------------------------
Print #lFileNum, "N
Thanks Jean. Would take a look at it
Jean-Pierre's code will work just fine. I just want you to be aware of what is possible using slPutFile and slSelStr together. They will allow you to do the same thing in just one statement. It will not run any faster, just less code on you part.
--------------------------------------------
' Export the records from the table "Domains"
Sub DomainsExport(ByVal pFileName As String)
'--------------------------------------------
slPutFile pFileName, slSelStr("Select * from Domains", "Q44.13"), "C"
End Sub
Hi Guys,
A quick question about slSelStr - presumably I can only use this and separate records with 13,10 if I modify the SQLitening source code to parse out more than one sep characters. Is this correct ?
Thanks for the great product by the way !
Steve.
Quote from: Steve Pardoe on February 06, 2013, 10:16:11 PM...
A quick question about slSelStr - presumably I can only use this and separate records with 13,10 if I modify the SQLitening source code to parse out more than one sep characters. Is this correct ?
...
slSelStr currently only supports single character delimeters. You could modify the source code to allow for $CRLF, or you could read the data a row at a time using slSel and one of the slF{x} functions.
Bern,
Yes, that's what I thought. I am using the 'longer and less elegant' route as we speak. It is a shame as 13,10 is such a common need.
Steve.
Hello All,
How about direct export to Excel? This is not very good. But its a start. Can anyone improve this?
This is from PBwin Sample file
Quote
#COMPILER PBWIN 10
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "Excel.inc"
#INCLUDE ONCE "SQLitening.inc"
FUNCTION PBMAIN () AS LONG
'------------------------------------------------------------------------------
' Main application entry point...
'
' Object Variables
DIM oExcelApp AS Excel_Application
DIM oExcelWorkbook AS Excel_Workbook
DIM oExcelWorkSheet AS Excel_WorkSheet
DIM oExcelChart AS Excel_Chart
' General Object param variables
DIM vBool AS VARIANT
DIM vVnt AS VARIANT
DIM oVnt AS VARIANT
DIM vText AS VARIANT
DIM vRange AS VARIANT
DIM vX AS VARIANT
DIM vY AS VARIANT
DIM a() AS WSTRING
DIM b() AS CURRENCY
DIM y AS LONG
DIM sArray() AS STRING
DIM dAmount() AS DOUBLE
' Chart Object param variables
DIM vSource AS VARIANT
DIM vGallery AS VARIANT
DIM vFormat AS VARIANT
DIM vPlotBy AS VARIANT
DIM vCatLabels AS VARIANT
DIM vSerLabels AS VARIANT
DIM vHasLegend AS VARIANT
DIM vTitle AS VARIANT
' SaveAs Object param variables
DIM vFile AS VARIANT
slOpen ("GuestsProDB.db3","C")
' Open an instance of EXCEL
oExcelApp = ANYCOM $PROGID_Excel_Application
' Could EXCEL be opened? If not, terminate this app
IF ISFALSE ISOBJECT(oExcelApp) OR ERR THEN
MSGBOX "Excel could not be opened. Please check that Excel and VBA are installed."
EXIT FUNCTION
END IF
' Create a new workbook in EXCEL
OBJECT CALL oExcelApp.WorkBooks.Add TO oExcelWorkBook
IF ISFALSE ISOBJECT(oExcelWorkbook) OR ERR THEN
MSGBOX "Excel could not open a new workbook. Please check that VBA is installed."
GOTO Terminate
END IF
OBJECT CALL oExcelWorkBook.WorkSheets.Add TO oExcelWorkSheet
IF ISFALSE ISOBJECT(oExcelWorkSheet) OR ERR THEN
MSGBOX "Excel could not open a new worksheet. Please check that VBA is installed."
GOTO Terminate
END IF
' Format and send data for cells A1:G1
LET vRange = "A1:O1"$$
' Create a Day of the Week array for A1:G1
DIM a(1 TO 15) AS WSTRING
a(1) = "Name" : a(2) = "BillNo"
a(3) = "DocketNo" : a(4) = "RoomNo"
a(5) = "Date" : a(6) = "ProductNo"
a(7) = "Description" : a(8) = "Category"
a(9) = "Department" : a(10) = "Quantity"
a(11) = "SellPrice" : a(12) = "Discount"
a(13) = "VAT" : a(14) = "SCharge"
a(15) = "Remark"
LET vText = a()
OBJECT LET oExcelWorkSheet.Range(vRange).Value = vText
' Format and send data for cells A2:G2
LET vRange = "A2:O2"$$
LOCAL x AS LONG
LOCAL st AS STRING
slSelAry "select SellingPrice from tblBarSales LIMIT 15",sArray()
LET vVnt = sArray()
OBJECT LET oExcelWorkSheet.Range(vRange).Value = vVnt
' Now we can make EXCEL visible to the user
LET vBool = 1
OBJECT LET oExcelApp.Visible = vBool
Terminate:
MSGBOX "Click the OK button to close Excel and exit the application"
' Close the current Worksheet and close EXCEL
OBJECT CALL oExcelApp.ActiveWindow.Close
OBJECT CALL oExcelApp.Quit
' Release the interfaces. We could omit this since the
' app is about to close, but "best practice" states we
' should clean our house before moving out.
oExcelApp = NOTHING
oExcelWorkbook = NOTHING
oExcelWorkSheet = NOTHING
oExcelChart = NOTHING
END FUNCTION
This reads a CSV file into a SQLItening database.
Could you reverse the process by creating a CSV file and load that into Excel?
#COMPILE EXE 'readcsv.bas
#DIM ALL
#INCLUDE "sqlitening.inc"
$DefaultDataBase = "sample.db3"
$DefaultTable = "T1"
$DefaultCSVFile = ""
$DefaultDropTable = "YES"
%UseAllDefaults = 0
%UseTransaction = 1 'speed up by wrapping statements within Begin/End
FUNCTION PBMAIN () AS LONG
LOCAL sInputLine, sOutputLine AS STRING
LOCAL sFieldNames, sInsert, s AS STRING
LOCAL x, Cols, hFile AS LONG
LOCAL sDataBaseName AS STRING
LOCAL sTableName AS STRING
LOCAL sCSVFile AS STRING
LOCAL sDropTable AS STRING
LOCAL RowsInserted AS LONG
DIM sArray() AS STRING 'final results array
IF %UseAllDefaults THEN
sDataBaseName = $DefaultDataBase
ELSE
sDataBaseName = INPUTBOX$("","Database name",$DefaultDatabase)
IF LEN(sDatabaseName) = 0 THEN EXIT FUNCTION
END IF
IF %UseAllDefaults THEN
sTableName = $DefaultTable
ELSE
sTableName = INPUTBOX$("","Table name",$DefaultTable)
IF LEN(sTableName) = 0 THEN EXIT FUNCTION
END IF
IF %UseAllDefaults THEN
sDropTable = $DefaultDropTable
ELSE
sDropTable = INPUTBOX$("Type yes to drop table","Drop table " + sTableName,$DefaultDropTable)
END IF
IF %UseAllDefaults THEN
sCSVFile = $DefaultCSVFile
IF LEN(sCSVFile) = 0 THEN ? "No CSV file",,"Exit":EXIT FUNCTION
IF ISFILE(sCSVFile) = 0 THEN ? sCSVFile + $DQ + " not found",,"Error":EXIT FUNCTION
ELSE
DO
sCSVFILE = INPUTBOX$("","Input CSV file",$DefaultCSVFile)
IF LEN(sCSVFile) = 0 THEN ? "No CSV file",,"Exit":EXIT FUNCTION
IF ISFILE(sCSVFile) THEN EXIT LOOP
? "CSV input file " + $DQ + sCSVFile + $DQ + " not found",,"Error"
LOOP
END IF
hFile = FREEFILE
OPEN sCSVFile FOR INPUT AS #hFile
LINE INPUT #hFile, sInputLine 'read first line
cols = PARSECOUNT(sInputLine) 'number of columns
CLOSE #hFile
FOR x = 1 TO cols 'fields for create statement
sFieldnames = sFieldNames + "F" + FORMAT$(x)+ ","
NEXT
sFieldNames = LEFT$(sFieldNames,-1)
slOpen sDataBaseName,"C"
IF UCASE$(sDropTable) = "YES" THEN slexe "Drop table if exists " + sTableName
slexe "Create Table if not exists " + sTableName + "(" + sFieldNames + ")"
IF %UseTransaction THEN
slexe "BEGIN EXCLUSIVE"
END IF
hFile = FREEFILE
OPEN sCSVFile FOR INPUT AS #hFile
DO UNTIL EOF(#hFile) 'process line
LINE INPUT #hFile, sInputLine
RESET sOutputLine
FOR x = 1 TO cols
s = PARSE$(sInputLine,x)
IF IsNumeric(s) = 0 THEN 'string value so quote it
REPLACE $SQ WITH $DQ IN s
s = "'" & s & "'"
END IF
sOutputLine = sOutPutLine + s + ","
NEXT
sOutPutLine = LEFT$(sOutputLine,-1)
sInsert = "Insert into " + sTableName + " values (" + sOutputline + ")"
slExe sInsert
INCR RowsInserted
LOOP
IF %UseTransaction THEN
slEXE "END"
END IF
slSelAry "select count(*) from " + sTableName,sArray(),"Q9c"
? "Count(*) = " + JOIN$(sArray(),$CRLF),,"Inserted" + STR$(RowsInserted)
END FUNCTION
FUNCTION isNumeric(Answer AS STRING) AS LONG
Answer = TRIM$(Answer) 'may be required if right-justified text
IF (LEN(Answer) = 0) OR _
(VERIFY (Answer , ".-0123456789")) OR _
(RIGHT$(Answer,1) = ".") OR _
(INSTR(-1,Answer,"-") > 1) OR _
(TALLY(Answer,".") > 1) THEN
EXIT FUNCTION
END IF
FUNCTION = -1
END FUNCTION
Hello CJ,
Thanks for your response. I was hoping this can be done directly without going through csv. Not possible you think?
Best regards,
Maybe Jose Roca has a com way?
To Fredrick Ughimi (and all others)
The method you suggest works great, I have usee it. But if it concerns many items, such as 100000, then you may bring your bed to work.
Hello Fin, do you mean exporting directly Excel without going through csv? if yes, some sample codes would be appreciated. In my own case records won't be up to 100,000.
Best regards,
If you are using PowerBASIC then maybe my xmlExcel code may help you. It creates an Excel workbook using the XML Spreadsheet specification.
http://www.planetsquires.com/files/xmlExcel.zip
...and this library looks really good but I haven't created any headers for it: http://www.libxl.com/
(edit) I didn't realize this library was $199.
http://www.libxl.com/ look like the perfect solution.
As I understand, Libxl creates and writes Xlib an Excel file directly without going over Excel.
And then it will go fast, probably even faster than importing a CSV file.
Hopefully you can also control whether a field should be entered as text or number,
which you can not do with a CSV file.
I have been looking for a long time after this ..
I have received the e-mail from subbport@libxl:
Hello Fim W
Hello Fin,
Sure sounds good. Thanks for sharing.
Best regards,