SQLitening Support Forum

Support Forums => You've got Questions? We've got Answers! => Topic started by: Fredrick Ughimi on August 09, 2012, 09:25:31 PM

Title: Exporting SQLitening Table to MS Excel
Post by: Fredrick Ughimi on August 09, 2012, 09:25:31 PM
Hello,

Is it possible to export SQLitening Table to MS Excel?

Title: Re: Exporting SQLitening Table to MS Excel
Post by: Jean-Pierre LEROY on August 10, 2012, 05:19:59 AM
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
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Fredrick Ughimi on August 10, 2012, 06:41:22 AM
Thanks Jean. Would take a look at it
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Fred Meier on August 10, 2012, 11:33:06 AM
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
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Steve Pardoe on February 06, 2013, 10:16:11 PM
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.
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Bern Ertl on February 07, 2013, 11:41:32 AM
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.
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Steve Pardoe on February 07, 2013, 09:56:08 PM
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.
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Fredrick Ughimi on August 26, 2014, 08:53:39 AM
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
Title: Re: Exporting SQLitening Table to MS Excel
Post by: cj on August 26, 2014, 10:37:48 AM
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
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Fredrick Ughimi on August 26, 2014, 05:23:46 PM
Hello CJ,

Thanks for your response. I was hoping this can be done directly without going through csv. Not possible you think?

Best regards,

Title: Re: Exporting SQLitening Table to MS Excel
Post by: cj on August 26, 2014, 05:27:52 PM
Maybe Jose Roca has a com way?
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Fim on September 01, 2014, 06:55:36 PM
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.
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Fredrick Ughimi on September 01, 2014, 08:30:58 PM
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,
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Paul Squires on September 01, 2014, 10:21:01 PM
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
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Paul Squires on September 01, 2014, 10:23:08 PM
...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.
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Fim on September 02, 2014, 07:33:21 AM
 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 ..
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Fim on November 25, 2014, 10:07:23 AM
I have received the e-mail from subbport@libxl:
Hello Fim W
Title: Re: Exporting SQLitening Table to MS Excel
Post by: Fredrick Ughimi on November 29, 2014, 07:09:18 AM
Hello Fin,

Sure sounds good. Thanks for sharing.

Best regards,