Looking for quality PB support libraries

[Jim Lyons]

Hi All

I am a newbe PowerBasic user and I am wondering if anyone knows of or has a good library for handing dates and times (to and from strings with formatting, adding and subtracting, and the like, conversion to other data types). I do not want to reinvent the wheel and am willing to pay for a good well tested library (if it comes with source code). As far as that goes I would be willing to pay for a library that handles all of the details of reading and writing to text CSV files if it handled all of the details of imbedded single and double quotes, allowed formatting on the fly (All caps, all lower case, first letter caped, etc.) and places the data into a UDT.

I have searched the forums and have found a lot of good info but am looking for a more complete and tested package so that I can concentrate on coding my app and not the support functions.

Thanks in advance for any help.
Jim

[Michael Mattias]

>have searched the forums and have found a lot of good info but am looking for a more complete and tested package

For date handling you are not going to find anything better that you find here, especially Mr. Zijlema's stuff. Egbert first posted that years ago and even now occasionally updates it.

As long as I have been doing BASIC language, I have never seen a "CSV library." Best guess is the requirements for 'what you want to do with the data' are just too application-specific.

However, there is a terrific run-time (source code not available) library available at no extra cost to you for reformatting CSV-input; this library has been around for more than twenty years and has been in service for literally hundred of thousands of users over that time span: INPUT# handles the (single) quotes thing; UCASE$ and LCASE$ the case conversions;.MID$ + UCASE$ to do "proper-casing"; and LSET ot TYPE SET to put the data into UDTs.

If you do need to worry about embedded double quotes between comma separators, see the EXTRACT$ function. Of course embedded commas or quotes in a file which is called a "comma-quote-delimited" file has been problematic for many, many years, probably because it is intrinsically oxymoronic.

MCM

[Edwin Knoppert]

My PwrDev tool has support for dynamic schema.ini creation to open CSV's via ADO or ADO.NET
ADO can be done via plain PowerBASIC syntax and eventually via PwrDev ADO calls.
For .NET you can use PwrDev calls to make use of a custom assembly.

[Jim Lyons]

Thanks for the great input. I need to adjust my thinking a little regarding PB programming. Do you have a link to what would be the most up to date code provided by Mr. Ziljema? I am looking for it and can only seem to find bits and pieces, also I am still trying to get my arms around the forums in general.

Jim

[Erich Schulman]

As long as I have been doing BASIC language, I have never seen a "CSV library." Best guess is the requirements for 'what you want to do with the data' are just too application-specific.

However, there is a terrific run-time (source code not available) library available at no extra cost to you for reformatting CSV-input; this library has been around for more than twenty years and has been in service for literally hundred of thousands of users over that time span: INPUT# handles the (single) quotes thing; UCASE$ and LCASE$ the case conversions;.MID$ + UCASE$ to do "proper-casing"; and LSET ot TYPE SET to put the data into UDTs.

MCM

There is a free CSV parsing class for C++ at http://www.mayukhbose.com/freebies/c-code.php, but I don't see it offering any advantage beyond INPUT, MID$, etc.

I could still see a PB library as feasible and potentially useful. I would probably design it to receive a string (a CSV line) and a pointer to a string array, and that string array would contain the parsed data. You would then have to deal with any necessary string to numeric conversions. A second function could be for CSV's you know will have numeric values only. It could receive a third parameter, one to specify which type is to be used.

[Michael Mattias]

I think the biggest problem I've seen over the years with CSV files is embedded commas.

as in ...
"John Smith, Jr."
"1234 Main Street, Suite 300"

Above easily handled by (BION) PB regular expressions (mask = "\s\q(.+)\q")

Other than that, I think the biggest problem has been mal-formed files resulting in ambuguity; but nothing "out of the box" will ever handle that anyway.

MCM

[David Roberts]

Do you have a link to what would be the most up to date code provided by Mr. Ziljema?

It is Zijlema.

Egbert's programming place

[Jim Lyons]

Thank you all for the help. Now let me see if I can digest and use this info.

Jim

PS Sorry for the typo.

[Walt Thompson]

CSV Handling

For handling CSV PB also offers: PARSE and PARSE$.

PARSE extracts comma delimited fields into an array.

PARSE$ returns the field identified by the argument to the command.

[William Burns]

Here is a couple free date functions I have used in my production code for a long time with no problems. (Note: Date format used is the standard mm/dd/yyy)

The following union is needed for both functions:

Union QUADFILETIME   'used in DateDiff function
   FT       As FILETIME
   qTime    As Quad
End Union

'==================================================
' DaysDiff() - Returns number of days diff between 2 dates
'==================================================
Function DaysDiff(ByVal sDate1 As String, ByVal sDate2 As String) As Long
   'returns positive days if sDate1 is greater than sDate2, 0 if equal, negitive if less than
   Local ST          As SYSTEMTIME
   Local qFT1        As QUADFILETIME
   Local qFT2        As QUADFILETIME
   ST.wMonth = Val(Parse$(sDate1,Any "-/",1))
   ST.wDay = Val(Parse$(sDate1,Any "-/",2))
   ST.wYear = Val(Parse$(Parse$(sDate1,Any "-/",3)," ",1))  ' - updated to remove time ex: 04/12/2007 08:35
   ST.wHour = 1
   ST.wMinute = 1
   ST.wSecond = 1
   Call SystemTimeToFileTime(ST,qFT1) 'calc Date1
   ST.wMonth = Val(Parse$(sDate2,Any "-/",1))
   ST.wDay = Val(Parse$(sDate2,Any "-/",2))
   ST.wYear = Val(Parse$(Parse$(sDate2,Any "-/",3)," ",1))  ' - updated to remove time ex: 04/12/2007 08:35
   ST.wHour = 1
   ST.wMinute = 1
   ST.wSecond = 1
   Call SystemTimeToFileTime(ST,qFT2) 'calc Date1
   If qFT1.qTime = qFT2.qTime Then
      Function = 0
   Else
      Function = Int((qFT1.qTime / 864000000000&&) - (qFT2.qTime / 864000000000&&))  'days(86400000 ms x 10000 ns)
   End If
End Function

'==================================================
'  Used to add days to a date
'==================================================
Function DateAdd(ByVal sDate1 As String, ByVal iDaysAdded As Long) As String
   'returns a new date with the days added
   Local ST          As SYSTEMTIME
   Local qFT1        As QUADFILETIME
   ST.wMonth = Val(Parse$(sDate1,Any "-/",1))
   ST.wDay = Val(Parse$(sDate1,Any "-/",2))
   ST.wYear = Val(Parse$(Parse$(sDate1,Any "-/",3)," ",1))  ' - updated to remove time ex: 04/12/2007 08:35
   ST.wHour = 1
   ST.wMinute = 1
   ST.wSecond = 1
   Call SystemTimeToFileTime(ST,qFT1) 'calc Date1
   qFT1.qTime = qFT1.qTime + (iDaysAdded * (864000000000&&))   'days = 86400000ms x 10000ns
   Call FileTimeToSystemTime(qFT1, ST)
   Function = Format$(ST.wMonth,"00") + "/" + Format$(ST.wDay,"00") + "/" + Format$(ST.wYear,"0000")
End Function

[Mike Doty]

These are on Paul's site:

http://pdptemp.dyndns.org/zipcode/dates

paul d purvis and I were working on these for some time. They seem to work well.
Neither of us broke them.

'compymmdd.bas7
 
'compute years month and days differences
'program considers end of months dates to be the same no matter the days in the month.
'
'this program is somewhat different in that if the first date is the last day of a month
'a whole month will not increase until any month has passed
'if you wanted to be calculate birthdays, it would be best to subtract 30 from the days variable and add one month to the totalmonths
#COMPILE EXE
#DIM ALL
DECLARE FUNCTION date2julian(mm_dd_yyyy AS STRING) AS LONG
DECLARE FUNCTION julian2date(jd AS LONG) AS STRING
DECLARE FUNCTION computeymdd(lowdate AS STRING, highdate AS STRING,usebirthdaytypemonth AS LONG) AS STRING
 
REM first day  1721060 01/01/0000
FUNCTION datevalidate(mm_dd_yyyy AS STRING) AS LONG
LOCAL temp1&,temp2&,temp3&,leapyearflag&
FUNCTION=-1&
IF LEN(mm_dd_yyyy)<9& THEN EXIT FUNCTION
mm_dd_yyyy=RIGHT$("0"+mm_dd_yyyy,10&)
temp3&=VAL(MID$(mm_dd_yyyy$,7&,4&))
temp2&=VAL(MID$(mm_dd_yyyy$,4&,2&))
temp1&=VAL(MID$(mm_dd_yyyy$,1&,2&))
IF temp1&=0& THEN
    IF temp2&=0& AND temp3&=0& THEN FUNCTION=0&
    EXIT FUNCTION
END IF
IF  temp3&<0& OR temp2&<1& THEN EXIT FUNCTION
'replaced an ON GOTO statement with IF statements to speed up this routine
IF TEMP1&=1& OR TEMP1&=3& OR TEMP1&=5& OR TEMP1&=7& OR TEMP1&=8& OR TEMP1&=10& OR TEMP1&=12& THEN
    IF temp2&<32& THEN FUNCTION=1&
    EXIT FUNCTION
END IF
IF TEMP1&=4& OR TEMP1&=6& OR TEMP1&=9& OR TEMP1&=11& THEN
     IF temp2&<31& THEN FUNCTION=1&
     EXIT FUNCTION
END IF
IF TEMP1&=2& THEN
   IF temp2&<29& THEN FUNCTION=1&:EXIT FUNCTION
   IF temp2&>29& THEN EXIT FUNCTION
   'here is where we know the day is the 29th of february
   IF temp3&=0& THEN
        leapyearflag&=1&
      ELSE
        IF temp3& MOD 4& = 0& THEN leapyearflag&=1&
        IF temp3& MOD 100& =0& THEN leapyearflag&=0&
        IF temp3& MOD 400& =0& THEN leapyearflag&=1&
   END IF
   IF leapyearflag& = 0& THEN EXIT FUNCTION
   FUNCTION=1&
END IF
'this function will return a -1 if the program gets here
END FUNCTION
 
 
FUNCTION PBMAIN AS LONG
' ex computeymdd(lowdate,highdate,0) 'mm/dd/yyyy required
' ex for counting birthdays computeymdd(lowdate,highdate,1) 'mm/dd/yyyy required
'goto start
STDOUT "the same dates with two methods"
STDOUT "1st method could be called a whole month method because"
STDOUT "  it treats days at the end of each month being the same"
STDOUT "  ex. one month from 06/30/0001 equals 07/30/0001 and 07/31/0001"
STDOUT "2nd method could be called a calendar method because it counts"
STDOUT "  the months and days since a certain date."
STDOUT "  ex. one month from 06/30/0001 equals 07/30/0001"
STDOUT "   07/31/001 is one month and one day, closer to counting a birthday"
STDOUT
STDOUT "09/22/1918  04/04/1994"
STDOUT computeymdd("09/22/1918","04/04/1994",0)
STDOUT computeymdd("09/22/1918","04/04/1994",1)
STDOUT "-----------------------------------------------------"
STDOUT "01/01/0000  02/03/0000"
STDOUT computeymdd("01/01/0000","02/03/0000",0)
STDOUT computeymdd("01/01/0000","02/03/0000",1)
STDOUT "-----------------------------------------------------"
STDOUT "01/01/0000  02/29/0000"
STDOUT computeymdd("01/01/0000","02/29/0000",0)
STDOUT computeymdd("01/01/0000","02/29/0000",1)
STDOUT "-----------------------------------------------------"
STDOUT "01/27/0001  02/28/0001"
STDOUT computeymdd("01/27/0001","02/28/0001",0)
STDOUT computeymdd("01/27/0001","02/28/0001",1)
STDOUT "-----------------------------------------------------"
STDOUT "01/27/1989  02/28/1989"
STDOUT computeymdd("01/27/1989","02/28/1989",0)
STDOUT computeymdd("01/27/1989","02/28/1989",1)
STDOUT "-----------------------------------------------------"
STDOUT "01/31/0000  02/28/0000"
STDOUT computeymdd("01/31/0000","02/28/0000",0)
STDOUT computeymdd("01/31/0000","02/28/0000",1)
STDOUT "-----------------------------------------------------"
STDOUT "01/31/0000  02/29/0000"
STDOUT computeymdd("01/31/0000","02/29/0000",0)
STDOUT computeymdd("01/31/0000","02/29/0000",1)
STDOUT "-----------------------------------------------------"
STDOUT "01/31/0001  02/28/0001"
STDOUT computeymdd("01/31/0001","02/28/0001",0)
STDOUT computeymdd("01/31/0001","02/28/0001",1)
STDOUT "-----------------------------------------------------"
STDOUT "01/31/0001  03/30/0001"
STDOUT computeymdd("01/31/0001","03/30/0001",0)
STDOUT computeymdd("01/31/0001","03/30/0001",1)
STDOUT "-----------------------------------------------------"
STDOUT "01/31/0001  03/31/0001"
STDOUT computeymdd("01/31/0001","03/31/0001",0)
STDOUT computeymdd("01/31/0001","03/31/0001",1)
STDOUT "-----------------------------------------------------"
STDOUT "02/28/0001  03/30/0001"
STDOUT computeymdd("02/28/0001","03/30/0001",0)
STDOUT computeymdd("02/28/0001","03/30/0001",1)
STDOUT "-----------------------------------------------------"
STDOUT "02/28/0001  03/31/0001"
STDOUT computeymdd("02/28/0001","03/31/0001",0)
STDOUT computeymdd("02/28/0001","03/31/0001",1)
STDOUT "-----------------------------------------------------"
STDOUT "02/28/0003  03/30/0004"
STDOUT computeymdd("02/28/0003","03/30/0004",0)
STDOUT computeymdd("02/28/0003","03/30/0004",1)
STDOUT "-----------------------------------------------------"
STDOUT "02/28/0003  03/31/0004"
STDOUT computeymdd("02/28/0003","03/31/0004",0)
STDOUT computeymdd("02/28/0003","03/31/0004",1)
STDOUT "-----------------------------------------------------"
STDOUT "02/29/0000  03/30/0000"
STDOUT computeymdd("02/29/0000","03/30/0000",0)
STDOUT computeymdd("02/29/0000","03/30/0000",1)
STDOUT "-----------------------------------------------------"
STDOUT "02/29/0000  03/31/0000"
STDOUT computeymdd("02/29/0000","03/31/0000",0)
STDOUT computeymdd("02/29/0000","03/31/0000",1)
STDOUT "-----------------------------------------------------"
STDOUT "02/29/1988  03/31/1989"
STDOUT computeymdd("02/29/1988","03/31/1989",0)
STDOUT computeymdd("02/29/1988","03/31/1989",1)
STDOUT "-----------------------------------------------------"
STDOUT "02/29/1988  02/28/1989"
STDOUT computeymdd("02/29/1988","02/28/1989",0)
STDOUT computeymdd("02/29/1988","02/28/1989",1)
STDOUT "-----------------------------------------------------"
STDOUT "02/29/1988  06/30/1988"
STDOUT computeymdd("02/29/1988","06/30/1988",0)
STDOUT computeymdd("02/29/1988","06/30/1988",1)
STDOUT "-----------------------------------------------------"
STDOUT "06/30/0000  07/31/0000"
STDOUT computeymdd("06/30/0000","07/31/0000",0)
STDOUT computeymdd("06/30/0000","07/31/0000",1)
STDOUT "-----------------------------------------------------"
STDOUT "12/31/0000  01/31/0001"
STDOUT computeymdd("12/31/0000","01/31/0001",0)
STDOUT computeymdd("12/31/0000","01/31/0001",1)
'start:
'local i,y as long
'y=timer
'for i=1 to 100000*15
'computeymdd("12/31/0000","01/31/0001",1)
'next i
'print  timer-y
WAITKEY$
END FUNCTION
 
FUNCTION computeymdd(lowdate AS STRING, highdate AS STRING,usebirthdaytypemonth AS LONG) AS STRING
'mm/dd/yyyy required for lowdate and highdate
'usebirthdaytypemonth will add a month if the days are over 30 days and subtract 30 from the days
'usebirthdaytypemonth could have an difference in years,months and days
LOCAL years, months, days, totaldays,totalmonths AS LONG
LOCAL lowdateateom, I AS LONG
LOCAL lowdatemth,lowdateday,lowdateyear,highdatemth,highdateday,highdateyear AS LONG
LOCAL tempdate AS STRING
'LOCAL  usebirthdaytypemonth as long
'usebirthdaytypemonth=0&
IF datevalidate(lowdate)<1& OR datevalidate(highdate)<1& THEN
   YEARS=-1&
   MONTHS=-1&
   DAYS=-1&
   TOTALDAYS=-1&
   totalmonths=-1&
  ' PRINT "error in dates"
   GOTO EXITTHEFUNCTION
END IF
totaldays=date2julian(highdate)-date2julian(lowdate)
IF totaldays<0& THEN
   YEARS=-1&
   MONTHS=-1&
   DAYS=-1&
   TOTALDAYS=-1&
   totalmonths=-1&
   'PRINT "error 2nd date prior to first date"
   GOTO exitthefunction
END IF
REM days are valid and computation can be done
lowdatemth=VAL(MID$(lowdate,1&,2&))
lowdateday=VAL(MID$(lowdate,4&,2&))
lowdateyear=VAL(MID$(lowdate,7&,4&))
highdatemth=VAL(MID$(highdate,1&,2&))
highdateday=VAL(MID$(highdate,4&,2&))
highdateyear=VAL(MID$(highdate,7&,4&))
IF lowdateyear=highdateyear THEN
   IF lowdatemth=highdatemth THEN
   YEARS=0&
   MONTHS=0&
   DAYS=totaldays
   totalmonths=0&
   GOTO EXITTHEFUNCTION
   END IF
END IF
'compute to see if the low date is a date at the end of a month and if so check the high date
'  if the high date is also at the end of the month do date math
IF usebirthdaytypemonth&=0 THEN
IF VAL(MID$(julian2date(date2julian(lowdate)+1&),1&,2&))<>lowdatemth THEN
        IF VAL(MID$(julian2date(date2julian(highdate)+1&),1&,2&))<>highdatemth THEN
       'both dates are end of the month, compute months"
        totalmonths=(highdatemth+(highdateyear*12&))-(lowdatemth+(lowdateyear*12&))
        years=totalmonths\12&
        months=totalmonths MOD 12&
        days=0&
        GOTO exitthefunction
        END IF
      lowdateateom=1&
    ELSE
      lowdateateom=0&
END IF
END IF
'set a temporary date
tempdate=highdate
FOR I=0& TO 3&
MID$(tempdate,4,2)=RIGHT$("0"+TRIM$(STR$(lowdateday-I)),2)
IF datevalidate(tempdate)=1 THEN EXIT FOR
NEXT I
IF date2julian(tempdate)=date2julian(highdate) THEN
        totalmonths=(highdatemth+(highdateyear*12&))-(lowdatemth+(lowdateyear*12&))
        years=totalmonths\12&
        months=totalmonths MOD 12&
        days=0&
        GOTO exitthefunction
    END IF
tempdate=highdate
MID$(tempdate,4,2)=RIGHT$("0"+TRIM$(STR$(lowdateday)),2)
IF datevalidate(tempdate)=1 THEN
    IF date2julian(tempdate)<=date2julian(highdate) THEN
      totalmonths=(VAL(MID$(tempdate,1,2))+(VAL(MID$(tempdate,7,4))*12&))-(lowdatemth+(lowdateyear*12&))
      years=totalmonths\12&
      months=totalmonths MOD 12&
      days=date2julian(highdate)-date2julian(tempdate)
      GOTO EXITTHEFUNCTION
    END IF
END IF
'find the last day of the month previous to the highdate
IF lowdateateom THEN
    tempdate=highdate
    MID$(tempdate,4,2)="01
    tempdate=julian2date(date2julian(tempdate)-1)
END IF
IF date2julian(tempdate)<date2julian(highdate) THEN
    totalmonths=(VAL(MID$(tempdate,1,2))+(VAL(MID$(tempdate,7,4))*12&))-(lowdatemth+(lowdateyear*12&))
    years=totalmonths\12&
    months=totalmonths MOD 12&
    days=date2julian(highdate)-date2julian(tempdate)
    GOTO EXITTHEFUNCTION
END IF
tempdate=highdate
MID$(tempdate,4,2)="01
tempdate=julian2date(date2julian(tempdate)-1)
FOR I=0& TO 3&
MID$(tempdate,4,2)=RIGHT$("0"+TRIM$(STR$(lowdateday-I)),2)
IF datevalidate(tempdate)=1 THEN EXIT FOR
NEXT I
totalmonths=(VAL(MID$(tempdate,1,2))+(VAL(MID$(tempdate,7,4))*12&)) - (lowdatemth+(lowdateyear*12&))
years=totalmonths\12&
months=totalmonths MOD 12&
days=date2julian(highdate)-date2julian(tempdate)
 
EXITTHEFUNCTION:
IF usebirthdaytypemonth THEN
       computeymdd=_lowdate+" to "+highdate+ "  using birthdate date math"+$CRLF+_
         STR$(years)+" yrs"+STR$(months)+" mths"+STR$(days)+" days    "+_
         STR$(totalmonths)+" total months actual days "+STR$(totaldays)
       ELSE
     computeymdd=_lowdate+" to "+highdate+ "  using simple calendar date math"+$CRLF+_
         STR$(years)+" yrs"+STR$(months)+" mths"+STR$(days)+" days    "+_
         STR$(totalmonths)+" total months actual days "+STR$(totaldays)
      END IF
END FUNCTION
 
FUNCTION date2julian(mm_dd_yyyy AS STRING) AS LONG
LOCAL month,day,year,k,julian AS LONG
month = VAL(MID$(mm_dd_yyyy,1,2))
day = VAL(MID$(mm_dd_yyyy,4,2))
year = VAL (MID$(mm_dd_yyyy,7,4))
k = INT((14 - month) / 12)
date2julian = (day + INT(367 * (month + (k * 12) - 2) / 12)+ INT(1461 * (year + 4800 - k) / 4) - 32113)-_
               (INT(3& * INT((year + 100& - k) / 100&) / 4&) - 2&) 'convert to gregorian
END FUNCTION
'
FUNCTION julian2date(jd AS LONG) AS STRING
LOCAL  m,d,y, q, r, s, t, u, v AS LONG
q = INT((jd / 36524.25) - 51.12264)
r = jd + q - INT(q / 4&) + 1&
s = r + 1524&
t = INT((s / 365.25) - 0.3343)
u = INT(t * 365.25)
v = INT((s - u) / 30.61)
d = s - u - INT(v * 30.61)
m = (v - 1&) + 12& * (v > 13.5)
y = t - (m < 2.5) - 4716&
FUNCTION=RIGHT$("0"+TRIM$(STR$(M)),2)+"/"+RIGHT$("0"+TRIM$(STR$(D)),2)+ "/" +RIGHT$("000"+TRIM$(STR$(Y)),4)
END FUNCTION

[Mike Doty]

http://www.powerbasic.com/support/pbforums/showthread.php?t=24500&highlight=julian

[Jim Lyons]

This support is awesome. Thank everyone for their help.

Jim

[Knuth Konrad]

Your best bet on a "quality CSV library" would be either using ODBC (Eric Pearson's SQL Tools library is a big helper with ODBC) or ADO.

Both technologies have handled CSV files for a long time now and are in use by thousands, if not millions, programmers all over the world.