Author Topic: Results in Row or Column Order (also formatted to disk)  (Read 236 times)

cj

  • Master Geek
  • ****
  • Posts: 578
    • View Profile
Results in Row or Column Order (also formatted to disk)
« on: March 29, 2017, 02:14:37 PM »


Formatted Row and Column Arrays Optionally To Disk

Solid State Drives and disk cache make writing to disk very fast.
Other programs may require TAB or other delimited formats to read in data.
If another program needs the same data it may already be in memory.

Data passed using a file uses little memory and has advantages.
Receiving programs can process the data lines at a time or read all in at once.
It also allows users to view the output on any station at any time.
Results may be produced in ROW order and COLUMN order without processing twice.
4 new functions are highlighted (no more too much data for a MSGBOX while testing.)

FUNCTION PBMAIN AS LONG

 h& = freefile           
 OPEN "output.txt" FOR APPEND AS #h&                  'open output file
 slopen "sample.db3"                                  'open database
 slselary "select * from parts",sArray()              'select data
 WriteElementsRowOrder h&,sColRowArray(),$Delimiter   'format to disk in row order
 PRINT #h&                                            'blank line

 ReverseDimensions sArray(),sColArray()               'create array in column order
 WriteElementsColumnOrder h&,sColArray(),$Delimiter   'format to disk in column order
 CLOSE #h&                                            'close output file to allow display
 DisplayTextFile "output.txt"                         'display using program for txt files

END FUNCTION

output.txt

NO|MANUF|REDREF|PRICE
1|3COM|00100283|3365
2|3COM|00100284|160420
3|3COM|00100285|49218
4|3COM|00100286|51861
5|3COM|00100287|2857
6|3COM|00100289|239358
7|3COM|00100290|779
8|3COM|00100295|68922
9|3COM|00100296|16941
10|3COM|00100298|6746
11|3COM|00100299|8376

NO|1|2|3|4|5|6|7|8|9|10|11
MANUF|3COM|3COM|3COM|3COM|3COM|3COM|3COM|3COM|3COM|3COM|3COM
REDREF|00100283|00100284|00100285|00100286|00100287|00100289|00100290|00100295|00100296|00100298|00100299
PRICE|3365|160420|49218|51861|2857|239358|779|68922|16941|6746|8376



Code: [Select]
#DIM ALL
#INCLUDE "win32api.inc"
#INCLUDE "sqlitening.inc"
$Delimiter = "|"

FUNCTION PBMAIN () AS LONG

 LOCAL sColRowArray() AS STRING
 LOCAL sRowColArray() AS STRING
 LOCAL sOutputFile    AS STRING
 LOCAL h              AS LONG

 sOutputFile = "output.txt"
 KILL sOutputFile:ERRCLEAR
 h = FREEFILE
 OPEN sOutputFile FOR APPEND AS #h
 PRINT #h, "Formatted Row and Column Arrays Optionally To Disk";TAB(62)DATE$ + " " + TIME$
 PRINT #h
 PRINT #h, "Solid State Drives and disk cache make writing to disk very fast."
 PRINT #h, "Other programs may require TAB or other delimited formats to read in data."
 PRINT #h, "if another program uses the data on the same machine it may be in memory."
 PRINT #h, "Receiving programs can process the data lines at a time or read all in at once."
 PRINT #h, "It also allows users to view the output on any station at any time."
 PRINT #h, "Results may be produced in ROW order and COLUMN order.
 PRINT #h, "This program shells to the default program associated to read .TXT files."
 PRINT #h,

 REM slConnect "123.123.123.123"  'optional, get data far,far away

 slopen "sample.db3"
 slselary "select rowid as NO,MANUF,Redref,price from parts limit 11",sColRowArray()

 WriteElementsRowOrder h,sColRowArray(),$Delimiter
 PRINT #h, STRING$(80,"-")

 ReverseDimensions sColRowArray(),sRowColArray() 'create array in column order
 WriteElementsColumnOrder h,sRowColArray(),$Delimiter

 CLOSE #h
 DisplayTextFile sOutputFile

 slDisconnect

END FUNCTION

FUNCTION ReverseDimensions(sSourceArray() AS STRING,sDestArray() AS STRING) AS LONG

 'NOTE: Do not REDIM sDestArray() before or after calling to prevent corrupting SourceArray()

 LOCAL lpSource AS LONG PTR
 LOCAL lpDest   AS LONG PTR
 LOCAL COL,ROW,lCol,lRow,uCol,uRow,Cols,Rows AS LONG

 lCol = LBOUND(sDestArray,2)
 UCol = UBOUND(sDestArray,2)
 LRow = LBOUND(sDestArray,1)
 URow = UBOUND(sDestArray,1)

 rows = UROW-LRow '0 based number of rows
 cols = UCOL-lCol '0 based number of columns

 lpDest   = VARPTR(sDestArray  (lRow,lCol)) 'first element of destinstion array
 FOR ROW = 0 TO rows
  FOR COL = 0 TO cols
   @lpDest[ROW OF rows , COL OF cols] = 0
  NEXT COL
 NEXT ROW

'----------------------------------------------------------------------------------
 lCol = LBOUND(sSourceArray,1)
 UCol = UBOUND(sSourceArray,1)
 LRow = LBOUND(sSourceArray,2)
 URow = UBOUND(sSourceArray,2)

 rows = UROW-LRow '0 based number of rows
 cols = UCOL-lCol '0 based number of columns

 REDIM sDestArray(LRow TO URow,LCol TO UCol) AS STRING

 lpSource = VARPTR(sSourceArray(lCol,lRow)) 'first element of source array
 lpDest   = VARPTR(sDestArray  (lRow,lCol)) 'first element of destinstion array

 FOR ROW = 0 TO rows
  FOR COL = 0 TO cols
   @lpDest[ROW OF rows , COL OF cols] = @lpSource[COL OF cols, ROW OF rows]
  NEXT COL
 NEXT ROW

END FUNCTION

FUNCTION DisplayTextFile(sFileName AS STRING) AS LONG
 LOCAL zFileName AS ASCIIZ * 257
 zFileName = sFileName
 ShellExecute (0, "OPEN", zFileName, BYVAL 0, CURDIR$, %SW_SHOWNORMAL)
END FUNCTION


FUNCTION WriteElementsColumnOrder(hFile AS LONG, sArray() AS STRING,sDelimiter AS STRING) AS LONG

 LOCAL COL,ROW,lCol,lRow,uCol,uRow,Cols,Rows AS LONG

 lCol = LBOUND(sArray,2)  'row/column order values
 UCol = UBOUND(sArray,2)
 LRow = LBOUND(sArray,1)
 URow = UBOUND(sArray,1)

 rows = UROW-LRow '0 based number of rows
 cols = UCOL-lCol '0 based number of columns
 'array elements could be negative, 0, or positive so simple IF used
 FOR COL = lCol TO UCol
  FOR ROW  = lRow TO Urow
   IF ROW < uRow THEN
    PRINT #hFile,sArray(ROW,COL);sDelimiter;
   ELSE
    PRINT #hFile,sArray(ROW,COL)
   END IF
  NEXT
 NEXT COL

END FUNCTION

FUNCTION WriteElementsRowOrder(hFile AS LONG, sArray() AS STRING,sDelimiter AS STRING) AS LONG

 LOCAL COL,ROW,lCol,lRow,uCol,uRow,Cols,Rows AS LONG

 lCol = LBOUND(sArray,1)  'column/row order values
 UCol = UBOUND(sArray,1)
 LRow = LBOUND(sArray,2)
 URow = UBOUND(sArray,2)

 rows = UROW-LRow
 cols = UCOL-lCol

 'array elements could be negative, 0, or positive so simple IF used
 FOR ROW  = lRow TO Urow
  FOR COL = lCol TO UCol
   IF COL < UCol THEN             'not last column use delimiter
     PRINT #hFile,sArray(COL,ROW);sDelimiter;
   ELSE
     PRINT #hfile,sArray(COL,ROW) 'last column no delimiter
   END IF
  NEXT COL
 NEXT ROW

END FUNCTION
« Last Edit: March 31, 2017, 07:08:19 AM by cj »