Vilhelm-Ion Praisach: septembrie 2015

miercuri, 30 septembrie 2015

ADIR() and SYS(2000) with mask

ADIR() and SYS(2000) behaves like the DIR command.
Dir command have a MS-DOS legacy, which carry a CP/M one.
http://blogs.msdn.com/b/oldnewthing/archive/2007/12/17/6785519.aspx

The behavior can be unexpected for files whose name contains spaces, have more than 12 characters, or the extension is longer than 3 characters.

And that's because the generated 8.3 short names
This https://support.microsoft.com/en-us/kb/142982 gives a clue about how short names are generated, but is not exhaustive.

When the folder or computer searched with ADIR() or SYS(2000) support short names, files whose short name respect the mask, will be included in the final result.

Moreover, the result include files whose short name contains characters included in the mask, even these characters are not part of the long name.
A classic example is the ~1(2,3..) suffix
http://stackoverflow.com/questions/11107044/strange-windows-dir-command-behavior

This behavior can be stopped, by removing the support for short names.

To remove short names for a single directory

DECLARE INTEGER ShellExecute IN SHELL32.DLL INTEGER nWinHandle,STRING cOperation,STRING cFileName,STRING cParameters,STRING cDirectory,INTEGER nShowWindow

ShellExecute(0,"runas","fsutil","8dot3name strip " + lcPathToDesiredDirectory,"",1)

1) example for ADIR()
******************
* Begin ADIR() example
******************

STRTOFILE("","aabb.xl")
STRTOFILE("","aa bb.xl")
STRTOFILE("","aabb.xls")
STRTOFILE("","aa bb.xls")
STRTOFILE("","aabb.xlsx")
STRTOFILE("","aa bb.xlsx")
STRTOFILE("","aabb.xlsxp")
STRTOFILE("","aa bb.xlsxp")
STRTOFILE("","aabb.xlsxpm")
STRTOFILE("","aa bb.xlsxpm")

*!*    On my PC, this is the result of
*!*    DIR a*.* /x
*!*    17.09.2015 10:35                 0 AABB~1.XL    aa bb.xl
*!*    17.09.2015 10:35                 0 AABB~1.XLS   aa bb.xls
*!*    17.09.2015 10:35                 0 AABB~3.XLS   aa bb.xlsx
*!*    17.09.2015 10:35                 0 AA3C12~1.XLS aa bb.xlsxp
*!*    17.09.2015 10:35                 0 AA96D4~1.XLS aa bb.xlsxpm
*!*    17.09.2015 10:35                 0              aabb.xl
*!*    17.09.2015 10:35                 0              aabb.xls
*!*    17.09.2015 10:35                 0 AABB~2.XLS   aabb.xlsx
*!*    17.09.2015 10:35                 0 AABB~4.XLS   aabb.xlsxp
*!*    17.09.2015 10:35                 0 AA84DD~1.XLS aabb.xlsxpm

?ADIR(la,"*.xl") && 2
?ADIR(la,"*.xls") && 8
?ADIR(la,"*.xlsx") && 2
?ADIR(la,"*.xlsxp") && 2
?ADIR(la,"*.xl?") && 10
?ADIR(la,"*.xl*") && 10

WAIT
?ADIR(la,"a*.xl") && 2
?ADIR(la,"a*.xls") && 8
?ADIR(la,"a*.xlsx") && 2
?ADIR(la,"a*.xlsxp") && 2
?ADIR(la,"a*.xl?") && 10
?ADIR(la,"a*.xl*") && 10

WAIT
?ADIR(la,"*b*.xl") && 2
?ADIR(la,"*b*.xls") && 5
* DIR "*b*.xls" /x
* aabb.xls
* aa bb.xls
* aabb.xlsx
* aa bb.xlsx
* aabb.xlsxp
?ADIR(la,"*b*.xlsx") && 2
?ADIR(la,"*b*.xlsxp") && 2
?ADIR(la,"*b*.xl?") && 7
* DIR "*b*.xl?" /x
* aabb.xl
* aa bb.xl
* aabb.xls
* aa bb.xls
* aabb.xlsx
* aa bb.xlsx
* aabb.xlsxp
?ADIR(la,"*b*.xl*") && 10

WAIT
?ADIR(la,"*b.xl") && 2
?ADIR(la,"*b.xls") && 2
?ADIR(la,"*b.xlsx") && 2
?ADIR(la,"*b.xlsxp") && 2
?ADIR(la,"*b.xl?") && 4
?ADIR(la,"*b.xl*") && 10

WAIT
?ADIR(la,"*1.xl") && 1
?ADIR(la,"*1.xls") && 4
?ADIR(la,"*1.xlsx") && 0
?ADIR(la,"*1.xlsxp") && 0
?ADIR(la,"*1.xl?") && 5
?ADIR(la,"*1.xl*") && 5

WAIT
?ADIR(la,"*~?.xl") && 1
?ADIR(la,"*~?.xls") && 7
?ADIR(la,"*~?.xlsx") && 0
?ADIR(la,"*~?.xlsxp") && 0
?ADIR(la,"*~?.xl?") && 8
?ADIR(la,"*~?.xl*") && 8

WAIT
?ADIR(la,"*D*.xls") && 2
* DIR "*D*.xls" /x
* aa bb.xlsxpm aka AA96D4~1.XLS
* aabb.xlsxpm aka AA84DD~1.XLS
?ADIR(la,"????.xls") && 1
?ADIR(la,"?????.xls") && 2
?ADIR(la,"??????.xls") && 5
* DIR "??????.xls" /x
* aabb.xls
* aa bb.xls aka AABB~1.XLS
* aabb.xlsx aka AABB~2.XLS
* aa bb.xlsx aka AABB~3.XLS
* aabb.xlsxp aka AABB~4.XLS
?ADIR(la,"????????.xls") && 8
* all except the one with xl as extension
******************
* End ADIR() example
******************

2) Example for SYS(2000)
******************
* Begin SYS(2000) example
******************

STRTOFILE("","aabb.xl")
STRTOFILE("","aa bb.xl")
STRTOFILE("","aabb.xls")
STRTOFILE("","aa bb.xls")
STRTOFILE("","aabb.xlsx")
STRTOFILE("","aa bb.xlsx")
STRTOFILE("","aabb.xlsxp")
STRTOFILE("","aa bb.xlsxp")
STRTOFILE("","aabb.xlsxpm")
STRTOFILE("","aa bb.xlsxpm")

*!*    On my PC, this is the result of
*!*    DIR a*.* /x
*!*    17.09.2015 10:35                 0 AABB~1.XL    aa bb.xl
*!*    17.09.2015 10:35                 0 AABB~1.XLS   aa bb.xls
*!*    17.09.2015 10:35                 0 AABB~3.XLS   aa bb.xlsx
*!*    17.09.2015 10:35                 0 AA3C12~1.XLS aa bb.xlsxp
*!*    17.09.2015 10:35                 0 AA96D4~1.XLS aa bb.xlsxpm
*!*    17.09.2015 10:35                 0              aabb.xl
*!*    17.09.2015 10:35                 0              aabb.xls
*!*    17.09.2015 10:35                 0 AABB~2.XLS   aabb.xlsx
*!*    17.09.2015 10:35                 0 AABB~4.XLS   aabb.xlsxp
*!*    17.09.2015 10:35                 0 AA84DD~1.XLS aabb.xlsxpm

? MyAdir("*.xl") && 2
?MyAdir("*.xls") && 8
?MyAdir("*.xlsx") && 2
?MyAdir("*.xlsxp") && 2
?MyAdir("*.xl?") && 10
?MyAdir("*.xl*") && 10

WAIT
?MyAdir("a*.xl") && 2
?MyAdir("a*.xls") && 8
?MyAdir("a*.xlsx") && 2
?MyAdir("a*.xlsxp") && 2
?MyAdir("a*.xl?") && 10
?MyAdir("a*.xl*") && 10

WAIT
?MyAdir("*b*.xl") && 2
?MyAdir("*b*.xls") && 5
* DIR "*b*.xls" /x
* aabb.xls
* aa bb.xls
* aabb.xlsx
* aa bb.xlsx
* aabb.xlsxp
?MyAdir("*b*.xlsx") && 2
?MyAdir("*b*.xlsxp") && 2
?MyAdir("*b*.xl?") && 7
* DIR "*b*.xl?" /x
* aabb.xl
* aa bb.xl
* aabb.xls
* aa bb.xls
* aabb.xlsx
* aa bb.xlsx
* aabb.xlsxp
?MyAdir("*b*.xl*") && 10

WAIT
?MyAdir("*b.xl") && 2
?MyAdir("*b.xls") && 2
?MyAdir("*b.xlsx") && 2
?MyAdir("*b.xlsxp") && 2
?MyAdir("*b.xl?") && 4
?MyAdir("*b.xl*") && 10

WAIT
?MyAdir("*1.xl") && 1
?MyAdir("*1.xls") && 4
?MyAdir("*1.xlsx") && 0
?MyAdir("*1.xlsxp") && 0
?MyAdir("*1.xl?") && 5
?MyAdir("*1.xl*") && 5

WAIT
?MyAdir("*~?.xl") && 1
?MyAdir("*~?.xls") && 7
?MyAdir("*~?.xlsx") && 0
?MyAdir("*~?.xlsxp") && 0
?MyAdir("*~?.xl?") && 8
?MyAdir("*~?.xl*") && 8

WAIT
?MyAdir("*D*.xls") && 2
* DIR "*D*.xls" /x
* aa bb.xlsxpm aka AA96D4~1.XLS
* aabb.xlsxpm aka AA84DD~1.XLS
?MyAdir("????.xls") && 1
?MyAdir("?????.xls") && 2
?MyAdir("??????.xls") && 5
* DIR "??????.xls" /x
* aabb.xls
* aa bb.xls aka AABB~1.XLS
* aabb.xlsx aka AABB~2.XLS
* aa bb.xlsx aka AABB~3.XLS
* aabb.xlsxp aka AABB~4.XLS
?MyAdir("????????.xls") && 8
* all except the ones with xl as extension

FUNCTION MyAdir
    LPARAMETERS lcMask
    LOCAL lnCount
    lnCount = 0
    IF !EMPTY(SYS(2000,m.lcMask))
        lnCount = 1
        DO WHILE !EMPTY(SYS(2000,m.lcMask,1))
            lnCount = m.lnCount + 1
        ENDDO
    ENDIF
    RETURN m.lnCount
ENDIF
******************
* End SYS(2000) example
******************

Paul Gibson's thread about it

luni, 28 septembrie 2015

ImportFromXlsx 1.2

AppendFromXlsx 1.4

New version with small correction and the possibility to extract the XML files using Winrar.

AppendFromXlsx 1.4 appends data from MSExcel 2007+ Workbooks into an existing table or cursor.
MSExcel is not used, thus not required.
AppendFromXlsx 1.4 simply extract and parse the corresponding XMLs, read the data stored into them and populate the desired table / cursor.

Beginning with version 1.4, the XMLs can alternatively be extracted with Winrar.

Parameters :
- lcFileName (rquired) Name of the xlsx file
- cCur (optional)              Name of the table / cursor ;                                               default ALIAS()
- lcFFields (optional)       List of fields to be imported ;                                           default all fields
- lnStartRows (optional) starting row (the first lnStartRows - 1 rows are skipped) default 1 (all rows)
- lcSheet (optional)         sheet name | number                                                           default first sheet

Append From XLSX 1.4

Related posts
http://praisachion.blogspot.com/2016/06/inspectpptx-10.html
http://praisachion.blogspot.com/2016/06/inspectdocx-10.html

MSOffice -> DBF
http://praisachion.blogspot.com/2017/08/importfromxlsx-40.html
http://praisachion.blogspot.com/2017/06/append-from-xlsx-30.html
http://praisachion.blogspot.com/2016/08/importfromxlsx-34.html
http://praisachion.blogspot.com/2016/08/importfromxlsx-33.html
http://praisachion.blogspot.com/2016/06/importfromxlsx-32-appendfromxlsx-21.html
http://praisachion.blogspot.com/2016/06/appendfrompptx-10.html
http://praisachion.blogspot.com/2016/06/appendfromdocx-21.html
http://praisachion.blogspot.com/2016/06/importfromdocx-31.html
http://praisachion.blogspot.com/2016/06/importfrompptx-11.html
http://praisachion.blogspot.com/2016/06/importfromxlsx-31.html
http://praisachion.blogspot.com/2016/06/importfrompptx-10.html
http://praisachion.blogspot.com/2016/06/importfromdocx-30.html
http://praisachion.blogspot.com/2016/05/importfromxlsx-22.html
http://praisachion.blogspot.com/2016/02/import-dbf-from-msoffice-2007-xlsx-docx.html
http://praisachion.blogspot.com/2016/01/importfromdocx-20.html
http://praisachion.blogspot.com/2016/01/appendfromdocx-20.html
http://praisachion.blogspot.com/2016/01/importfromxlsx-13.html
http://praisachion.blogspot.com/2016/01/appendfromxlsx-20.html
http://praisachion.blogspot.com/2015/12/appendfromxlsx-15.html
http://praisachion.blogspot.com/2015/11/importfromxlsx-13.html
http://praisachion.blogspot.com/2015/09/importfromxlsx-12.html
http://praisachion.blogspot.com/2015/09/appendfromxlsx-14.html
http://praisachion.blogspot.com/2015/09/appendfromnxlsx-13.html
http://praisachion.blogspot.com/2015/08/importfromxlsx-11.html
http://praisachion.blogspot.com/2015/07/import-from-xlsx.html
http://praisachion.blogspot.com/2015/07/insert-from-focx.html
http://praisachion.blogspot.com/2015/06/append-from-docx.html

DBF -> MSOffice
Dbf2Xlsx6 4.2 (VFP6)
http://praisachion.blogspot.com/2017/04/export-dbf-to-excel-2007.html
http://praisachion.blogspot.com/2017/01/export-dbf-to-msexcel-xlsx.html
http://praisachion.blogspot.com/2017/01/export-dbf-to-mspowerpoin-pptx.html
http://praisachion.blogspot.com/2017/01/export-dbf-to-msword-docx.html
http://praisachion.blogspot.com/2016/11/export-to-xlsx.html
http://praisachion.blogspot.com/2016/11/export-to-pptx.html
http://praisachion.blogspot.com/2016/11/export-to-docx.html
http://praisachion.blogspot.com/2016/11/export-from-vfp6-to-msoffice-docx-pptx.html
http://praisachion.blogspot.com/2016/09/copytoxlsx6-10.html
http://praisachion.blogspot.com/2016/01/export-dbf-to-msoffice-2007-xlsx-docx_24.html
http://praisachion.blogspot.com/2016/01/export-dbf-to-msoffice-2007-xlsx-docx.html
http://praisachion.blogspot.com/2016/01/exporttopptx-20.html
http://praisachion.blogspot.com/2016/01/exporttodocx-20.html
http://praisachion.blogspot.com/2016/01/copytopptx-20.html
http://praisachion.blogspot.com/2016/01/exporttoxlsx-20.html
http://praisachion.blogspot.com/2016/01/copytoxlsx-30.html
http://praisachion.blogspot.com/2015/12/exporttoxlsx-19-class.html
http://praisachion.blogspot.com/2015/12/copytoxlsx-210-procedure.html
http://praisachion.blogspot.com/2015/12/exporttoxlsx-18-class.html
http://praisachion.blogspot.com/2015/12/copytoxlsx-29.html
http://praisachion.blogspot.com/2015/02/export-pptx-13.html
http://praisachion.blogspot.com/2015/01/copytodocx-12.html
http://praisachion.blogspot.com/2015/01/exportdocx-1.html

OOffice -> DBF
http://praisachion.blogspot.com/2016/11/import-from-openoffice-libreoffice-for.html
http://praisachion.blogspot.com/2016/09/importfromoowrtext-10.html
http://praisachion.blogspot.com/2016/09/importfromooffice-1o.html
http://praisachion.blogspot.com/2016/08/importfromoocalc.html

DBF -> OOffice
http://praisachion.blogspot.com/2016/09/export-dbf-to-openoffice-libreoffice_52.html
http://praisachion.blogspot.com/2016/09/export-dbf-to-openoffice-libreoffice_16.html
http://praisachion.blogspot.com/2016/09/copy-to-ods-10-openoffice-calc.html
http://praisachion.blogspot.com/2016/09/export-dbf-to-openoffice-libreoffice.html
http://praisachion.blogspot.com/2016/09/copy-to-odt-10.html

joi, 3 septembrie 2015

AppendFromnXlsx 1.3

Vilhelm-Ion Praisach

Faceți căutări pe acest blog

miercuri, 30 septembrie 2015

ADIR() and SYS(2000) with mask

luni, 28 septembrie 2015

ImportFromXlsx 1.2

AppendFromXlsx 1.4

joi, 3 septembrie 2015

AppendFromnXlsx 1.3

Translate

Faceți căutări pe acest blog

miercuri, 30 septembrie 2015

ADIR() and SYS(2000) with mask

luni, 28 septembrie 2015

ImportFromXlsx 1.2

AppendFromXlsx 1.4

joi, 3 septembrie 2015

AppendFromnXlsx 1.3

Translate

Abonați-vă la