Viewing a .DBF with a little help from PSHELL

[Joe Caverly]

Hey @dcantor, this may be of interest to you.

What follows is a little example of how to access some of the header, and all of the records, of a .DBF file.

I have attached a small FoxPro database to this message for testing.

Again, just an example to work with.

Joe

@setlocal
@echo off

COMMENT
This .BTM requires TCC v25

It also requires the Linux file command,
  which I am using from the Windows Subsystem for Linux
 
     _x64: 1
   _admin: 1
_elevated: 1

TCC  25.00.11 x64   Windows 10 [Version 10.0.18362.239]
OS Name:                   Microsoft Windows 10 Pro
OS Version:                10.0.18362 N/A Build 18362
ENDCOMMENT

iff %# gt 0 then
  set thedbf=%1
  :: Does the file exist?
  iff not exist %thedbf then
    echo %thedbf cannot be found.
    quit
  endiff
else
  echo USAGE: %_batchname E:\Utils\myfile.dbf
  quit
endiff

:: @wslpath is new to TCC v25
::
set dbfHeader=%@execstr[wsl file %@wslpath[%thedbf]]

set MaxWords=%@words[",",%dbfHeader]
set MaxWords=%@dec[MaxWords]

do kount=0 to %MaxWords
  echo %@word[",",%kount,%dbfHeader]
enddo
echo.

set ws=%@word[",",1,%dbfHeader]

set RecCount=%@word[0,%ws]
echo Record Count : %RecCount
pshell /s "$RecCount=%RecCount"

set RecLength=%@word[-0,%ws]
echo Record Length: %RecLength
pshell /s "$RecLength=%RecLength"

set ws=%@word[",",4,%dbfHeader]

set FirstRecord=%@word[2,%ws]
echo First Record : %FirstRecord
pshell /s "$FirstRecord=%FirstRecord"

::Read the entire .dbf file into a memory variable
pshell /s "$bytes = [System.IO.File]::ReadAllBytes('%thedbf')"

:: Looping through a large .dbf using PSHELL is slow.
:: It is much faster using a for loop on the PowerShell side.
echo.
do Kount=1 to %RecCount
  pshell /s "$text = [System.Text.Encoding]::ASCII.GetString($bytes, $FirstRecord+($RecLength*%Kount)-$RecLength, $RecLength); $text"
  echo.
enddo
endlocal

 

[dcantor]

Hey @dcantor, this may be of interest to you.

What follows is a little example of how to access some of the header, and all of the records, of a .DBF file.

I have attached a small FoxPro database to this message for testing.

Again, just an example to work with.

Joe

@setlocal
@echo off

COMMENT
This .BTM requires TCC v25

It also requires the Linux file command,
  which I am using from the Windows Subsystem for Linux
 
     _x64: 1
   _admin: 1
_elevated: 1

TCC  25.00.11 x64   Windows 10 [Version 10.0.18362.239]
OS Name:                   Microsoft Windows 10 Pro
OS Version:                10.0.18362 N/A Build 18362
ENDCOMMENT

iff %# gt 0 then
  set thedbf=%1
  :: Does the file exist?
  iff not exist %thedbf then
    echo %thedbf cannot be found.
    quit
  endiff
else
  echo USAGE: %_batchname E:\Utils\myfile.dbf
  quit
endiff

:: @wslpath is new to TCC v25
::
set dbfHeader=%@execstr[wsl file %@wslpath[%thedbf]]

set MaxWords=%@words[",",%dbfHeader]
set MaxWords=%@dec[MaxWords]

do kount=0 to %MaxWords
  echo %@word[",",%kount,%dbfHeader]
enddo
echo.

set ws=%@word[",",1,%dbfHeader]

set RecCount=%@word[0,%ws]
echo Record Count : %RecCount
pshell /s "$RecCount=%RecCount"

set RecLength=%@word[-0,%ws]
echo Record Length: %RecLength
pshell /s "$RecLength=%RecLength"

set ws=%@word[",",4,%dbfHeader]

set FirstRecord=%@word[2,%ws]
echo First Record : %FirstRecord
pshell /s "$FirstRecord=%FirstRecord"

::Read the entire .dbf file into a memory variable
pshell /s "$bytes = [System.IO.File]::ReadAllBytes('%thedbf')"

:: Looping through a large .dbf using PSHELL is slow.
:: It is much faster using a for loop on the PowerShell side.
echo.
do Kount=1 to %RecCount
  pshell /s "$text = [System.Text.Encoding]::ASCII.GetString($bytes, $FirstRecord+($RecLength*%Kount)-$RecLength, $RecLength); $text"
  echo.
enddo
endlocal

Thank you very much.