• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Exporting SQLitening Table to MS Excel

Started by Fredrick Ughimi, August 09, 2012, 09:25:31 PM

Previous topic - Next topic

Fredrick Ughimi

Hello,

Is it possible to export SQLitening Table to MS Excel?

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Jean-Pierre LEROY

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

Fredrick Ughimi

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

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

Steve Pardoe

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.

Bern Ertl

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.

Steve Pardoe

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.

Fredrick Ughimi

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
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

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

Fredrick Ughimi

Hello CJ,

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

Best regards,

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj


Fim

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.
Fim W

Fredrick Ughimi

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,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Paul Squires

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
Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com

Paul Squires

#14
...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.
Paul Squires <br />http://www.planetsquires.com<br />support@planetsquires.com