Script that creates a DBF file?

[KeithW]

Has anybody developed a script that would actually output a DBF file?  Dbase-III would be fine for my needs...

Keith

[stanl]

There is his old script in Tech DB
https://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/getarticle.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP+dBase~IV~Creation.txt

Should work with ACE 12.0 Provider as I don't think Jet is on many systems today. And as the thread below indicates you can use the FoxPro driver


https://social.msdn.microsoft.com/Forums/en-US/7034d87e-a8e3-42b6-aba7-14cc7b97e0ac/can-you-use-microsoftaceoledb120-with-dbase-iv-files

[KeithW]

Stan,

At this point in time, it ONLY has to work on my system & the code example DOES work... exactly what I was looking for...
now with the dBase file format description I can modify it to do what I need....  had my "DBF" search turned up that example
my post would have not been necessary at all, never thought of using "dBase"...oh well, such is life...all good now 1!!

THANX,
Keith

[stanl]

Yeah, that was one of the earliest scripts I uploaded. I have an archive of dbase scripts should you encounter any other challenges.

[KeithW]

Stan,

The above script works as both as dBase III/IV... if you have any others that you are willing to share... I need to create files with multi-fields both reading & writing (both initial load and updating) so I will take any examples you are willing to share.  Going straight from Excel would be great but most of the time it is either fixed-width text formatted data or CSV files.

Thanx,
Keith

[stanl]

I would stick with dbase IV, or even consider the FoxPro Driver. Most of the scripts I wrote are circa 2000-2001 but I can update one to ACE Provider that Inserts .csv into db4 and post here.


As for Excel. You may find the heavy lifting has already been done with an add-on. Though no longer supported for upgrades it should cover significant scenarios:


https://thexlwiz.blogspot.com/

[stanl]

OK. Here goes; relatively crude example - uses Ace Provider and attached .csv to create dbase IV file (which can be imported into Excel or Access, or opened with dbase if you have it). I will leave it up to you to investigate data types dbase IV can handle and how to signal them in a create statement. Amazed that I saved WB code after 20 years - but the fact that it still works is a good reason I and others stick with it.

Code (WINBATCH) Select Expand



;Winbatch - create dbase IV file based on .csv data
;assumes .csv file has headers and data types can be defined in dbase IV
path = "c:\temp\"
cFile = path:"data.csv"


If ! FileExist(cFile) Then Terminate(@TRUE,"Input File Missing",cFile)




cTable = "dbtest" ; will be named dbtest.dbf on %path%
;deletes output if it exists
dbf = path:"dbtest.dbf"
If FileExist(dbf) Then FileDelete(dbf)


;Use ACE Provider to create connection
cConn="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%path%;Extended Properties=dBase IV;Persist Security Info=False"
oConn = CreateObject("ADODB.Connection")
oConn.Open(cConn)
;create blank database structure based on .csv fields
oConn.Execute("Create Table %cTable% (OrderDate DateTime,Product Text(50),Location Text(50), Amt Long )")


;now that blank table is created open as an updateable recordset
oRS=CreateObject("ADODB.Recordset")
oRS.CursorLocation=3
oRS.Open("%cTable%",oConn,1,3,2)


;open csv file and parse
handle = FileOpen("%cFile%","READ")
x=FileRead(handle) ;assumes first row is header row
While 1
   x=FileRead(handle)
   If x=="*EOF*" Then Break
   oRS.addnew()
   oRS.Collect(0) = ItemExtract(1,x,",")
   oRS.Collect(1) = ItemExtract(2,x,",")
   oRS.Collect(2) = ItemExtract(3,x,",")
   oRS.Collect(3) = ItemExtract(4,x,",")
   oRS.Update()
EndWhile


FileClose(handle)
oRS.Close()
oRS=0
oConn.Close()
oConn=0
If FileExist(dbf) Then Display(2, dbf:" created","Please check if records inserted")
Exit
;//////////////////////////////////////////////////////////////////////////////////////




[td]

This is mostly a matter of style but you can use the ItemExtractCsv instead of ItemExtract or just use ArrayFileGetCsv. The only important difference that may or may not be significant is the handling of quote characters.

[stanl]

This is mostly a matter of style but you use the ItemExtractCsv instead of ItemExtract or just use ArrayFileGetCsv. The only important difference that may or may not be significant is the handling of quote characters.

It is, and this was a quick update of a 2000 script using ACE Provider rather than Jet.4.  I know the OP is cognizant of newer WB functionality. How many people even know what dBase IV is?

[KeithW]

The product I use is called AlphaAnywhere  by Alpha Software (AlphaFour was DOS, Alpha Five was Windows, Anywhere is the Five follow-on that supports Web & Mobile as well as DeskTop).  My situation is I work for a specialize Audit firm and contractually I have to keep files in a DropBox and within client folders so using MySQL/MariaDB or other Server based products does not fly.  I have used (thanx to Jim's work) SQLite when I can but was using Alpha Five & Anywhere for years.  Recently a MS Security Update broke the linkage between  Alpha and Excel/Access.  Things in that world are still not steady so I have had to fall back to creating dBase files externally and just attaching to Alpha due to the import/export linkage still being broken.

[td]

It is, and this was a quick update of a 2000 script using ACE Provider rather than Jet.4.  I know the OP is cognizant of newer WB functionality. How many people even know what dBase IV is?

I mentioned it not because I thought the OP wasn't aware of any WIL functions or because there is much interest in dBase.  I mentioned it because users and casual readers of this forum are often interested in CSV files and are often not aware of all the WIL functions available - given the number of functions it is understandable. 

[stanl]

Things in that world are still not steady so I have had to fall back to creating dBase files externally and just attaching to Alpha due to the import/export linkage still being broken.

Bummer. If the script I just posted helps: a lot of it is just a matter of data type transformations. As I remember, dbase only recognizes Text, Number, Date, Logical and 5k memo. I am pretty sure you can utilize SQL like 'SELECT INTO', or 'INSERT INTO' to move data from text or Office files into dbase.

[KeithW]

A lot of what I have to do with this process is importing large A/R related datasets... ATB's, Credit Memos populations, etc into a database to analyze.  This has taken me out of my original role of being  purely a developer into a SuperUser role to analyze the datasets.  I get the weird stuff that the other auditors cannot handle.... datasets that exceed Excel abilities (>1,048,576 rows) OR span multiple tabs, Print-File reports that need to be converted to Excel or a database file (love one of the clients that submits a 6,500 page captured print-file report that needs to have calcs performed on the contents... other supply flat-file databases  OR  CSV files, largest is just over 5MM rows.  So I am usually in "panic-mode" when something breaks and I have to figure out how to get access to the data and provide numbers.  I am a Sub of a Sub that is part of a 3-way contract between the client company and the bank.  I deal with A/R Securitazation Funding as part of an Independent Auditing Team.

That said, I very much appreciate those that respond with info or examples that I can modify to get done what I need to... this business is feast of famine, there is no in-between, and we are a small entity in the ocean that goes up again the major Big-4 Accounting Firms like PwC, E&Y, etc.

[stanl]

I mentioned it not because I thought the OP wasn't aware of any WIL functions or because there is much interest in dBase.  I mentioned it because users and casual readers of this forum are often interested in CSV files and are often not aware of all the WIL functions available - given the number of functions it is understandable.

Yes, proves something written in 2000 with functions available at that time have been greatly improved. Just reading Keith's last response as to the size of data, perhaps my Mickey-Mouse script could be improved or accelerated by the function improvements. 

[KeithW]

Stanl said...
Yes, proves something written in 2000 with functions available at that time have been greatly improved. Just reading Keith's last response as to the size of data, perhaps my Mickey-Mouse script could be improved or accelerated by the function improvements.


Stan,
Your Mickey-Mouse script has me 90+% of the way there...THANK-YOU.  Question, where can I find the info for the table field specs (docs for Create Table for this engine)?  I cannot come up with a Syntax that formats the Amt field with 2 digits of Decimal Precision for monetary values.  The files are fairly big and I would rather not have to reformat the database file after loading in order to format monetary values.  Everything else seems to work fine, not sure what the actual load time will be causing I am only playing around with a small test subset that goes quickly.

Regards,
Keith

[stanl]

try


https://www.promotic.eu/en/pmdoc/Subsystems/Db/dBase/DataTypes.htm

so this line can ne substituted



oConn.Execute("Create Table %cTable% (OrderDate Date,Product Char(50),Location Char(50), Amt Numeric(7,2) )")


but not sure it really affects decimal values

[KeithW]

Stan,

Did not seem to have any effect on the formatting as even with a (13,2)  it made it (19,5) in reaity ?
I emptied & packed the file, then Hex Edited the DBF and changed the Decimals to 2 and skipped the Creation part and loaded the file.
In essence I got to the desired end but NOT in a pretty fashion.

I have tried keywords of: long, double, float, number, numeric, currency and still it does not honor the decimal specifier.  Hopefully can
find an answer or will have to do the process in multiple parts where, create file, fix precision's specs then import data... should be easier
than all that in any case.

Thanx,
Keith

[stanl]

funny, I changed my .csv file [attached] and used Amt Numeric(7,2)


downloaded DBFView and data looked correct, but open up in Excel/Notepad/Access tends to go to 4 decimals. So the underlying .dbf field seems intact, just the way it is being presented by other apps.


EDIT: and even that data in Excel is 2 decimal places, after export [also attached]

[KeithW]

When I built the DBF file and attached it to Alpha (A DBF Based product) until I modified the Precision Byte (Before attaching to Alpha) it would display the 5 digits of decimals precision.   Once modified and imported and attached it honored the 2 digits setting in the DBF file.  Trying to determine what the proper command for the ACE Driver to create the file that way initially.

[stanl]

FINALLY! :-[


changed extended properties to dbase 5.0 - and got decimal precision



cConn="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%path%;Extended Properties=dBase 5.0;Persist Security Info=True"

Code (WINBATCH) Select Expand



;Winbatch - create dbase IV file based on .csv data
;assumes .csv file has headers and data types can be defined in dbase IV
path = "c:\temp\"
cFile = path:"data.csv"


If ! FileExist(cFile) Then Terminate(@TRUE,"Input File Missing",cFile)




cTable = "dbtest" ; will be named dbtest.dbf on %path%
;deletes output if it exists
dbf = path:"dbtest.dbf"
If FileExist(dbf) Then FileDelete(dbf)




;Use ACE Provider to create connection


cConn="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%path%;Extended Properties=dBase 5.0;Persist Security Info=True"
oConn = CreateObject("ADODB.Connection")
oConn.Open(cConn)
;create blank database structure based on .csv fields
oConn.Execute("Create Table %cTable% (OrderDate Date,Product Char(50),Location Char(50), Amt Numeric(7,2) )")


;now that blank table is created open as an updateable recordset
oRS=CreateObject("ADODB.Recordset")
oRS.CursorLocation=3
oRS.Open("%cTable%",oConn,1,3,2)


;open csv file and parse
handle = FileOpen("%cFile%","READ")
x=FileRead(handle) ;assumes first row is header row
While 1
   x=FileRead(handle)
   If x=="*EOF*" Then Break
   oRS.addnew()
   oRS.Collect(0) = ItemExtract(1,x,",")
   oRS.Collect(1) = ItemExtract(2,x,",")
   oRS.Collect(2) = ItemExtract(3,x,",")
   oRS.Collect(3) = ItemExtract(4,x,",")
   oRS.Update()
EndWhile


FileClose(handle)
oRS.Close()
oRS=0
oConn.Close()
oConn=0
If FileExist(dbf) Then Display(2, dbf:" created","Please check if records inserted")
Exit
;//////////////////////////////////////////////////////////////////////////////////////


;Foxpro Provider
;cConn="Provider=VFPOLEDB;Data Source=%dbf%;Collating Sequence=machine;"




[stanl]

Postscript:


Microsoft Visual FoxPro Provider will also work.
cConn="Provider=VFPOLEDB;Data Source=%path%;Collating Sequence=machine;"


The Provider is a free download. On my Win10 the setup .MSI installed but didn't work. Had to open Command Prompt as administrator and un .MSI from command - then it worked.


Can't understand why Alpha still thinks it can charge for dbf files. ???

[KeithW]

Even though they create & support DBF files they have moved to SQL...  DBF is purely legacy... they tell me
any issues I have are because I still use DBF rather than SQL.  Right now SQLite is all I can really use and I do,
because of the contractual file storage rules I have to live by.  Just with AA there are more hoops to jump thru
to use SQL and if things are working DBF is a lot easier.  This last fiasco was MicroSoft's doing. A recent MS
Security Patch broke the linkage.  It appears to be working now BUT I cannot afford to have that happen to me
again so I am developing alternative tools to fall back on as necessary.  I took a lot of needless arrows in the
past 2 weeks.

I appreciate all of your assistance, Stan.

Regards,
Keith

[stanl]

walk down memory lane.... light at end of tunnel...


as for they tell me any issues I have are because I still use DBF rather than SQL


sounds like they don't know the difference.

[td]

Thanks for taking the time to provide assistance. At least you have the memories to work with.  I haven't touched FoxPro since the mid-nineties and what I did know went the way of the Dodo bird.

[KeithW]

Stanl,

It worked !!!   (5.0) that is.  My concern was it would change the File Version Byte (1st Byte of file) to a value that Alpha did not like.  But that is not the case so far as I appear to be able to attach the externally generated DBF and access it...  I am sure there will be a gotcha somewhere down the line
but for the moment things look real good.   Glad you had the knowledge in your back pocket !!

Thanx again for sharing your knowledge here !!

Regards,
Keith

[KeithW]

Stan,

Can you tell me where I can find the info on these commands....

oRS.CursorLocation=3                        ; 3 = ?
oRS.Open("%cTable%",oConn,1,3,2)               ; 1, 3, 2 ?

I am trying to load a 14 field file and it chokes as soon as it creates the dbfile
the header is there but no data.

1261: COM/CLR Exception
oRS.Collect(4) = ItemExtract(5,x,",")


Using WB32 2020A
WIL Version 6.20ata

Regards,
Keith

[KeithW]

Stan,
"More Error Information" adds

COM/CLR Exception:

Microsoft Cursor Engine
Multi-step operation generated errors. Check each status value.



if that helps? I have not done COM/ADO/OLE programming so it is mostly greek to me.

Keith

[KeithW]

Stan,

Here are a few lines of data  (attachment).... and the script I am testing with at the moment.

Code Select Expand


path  = "d:\A5Test\"
cFile = path:"21906_care_test.csv"

If ! FileExist(cFile) Then Terminate(@TRUE,"Input File Missing",cFile)

cTable = "dBdata" ; will be named dBdata.dbf on %path%

dbf = path:cTable:".dbf"
If FileExist(dbf) Then FileDelete(dbf) ; deletes output if it exists ***

;Use ACE Provider to create connection,  Numeric fields are (19,5) and cannot seems to change without Hex Editing DBF
;cConn="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%path%;Extended Properties=dBase III;Persist Security Info=False"

;changed extended properties to dbase 5.0 - and got decimal precision
cConn="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%path%;Extended Properties=dBase 5.0;Persist Security Info=True"

;Microsoft Visual FoxPro Provider will also work.
;cConn="Provider=VFPOLEDB;Data Source=%path%;Collating Sequence=machine;"
;The Provider is a free download. On my Win10 the setup .MSI installed but didn't work.
;Had to open Command Prompt as administrator and un .MSI from command - then it worked.


oConn = CreateObject("ADODB.Connection")
oConn.Open(cConn)

;create blank database structure based on .csv fields   ; *** following line, too
;oConn.Execute("Create Table %cTable% (OrderDate Date,Product Char(50),Location Char(50),Amt Numeric(10,2))")
dbf  = "(Bus_Month Date,Acct_ID Char(10),SA_ID Char(10),"
dbf=dbf:"Cust_Name Char(50),Total_Due Numeric(10,2),"
dbf=dbf:"Curr_Bal Numeric(10,2),D31_60 Numeric(10,2),"
dbf=dbf:"D61_90 Numeric(10,2),D91_120 Numeric(10,2),"
dbf=dbf:"D121_150 Numeric(10,2),D151_180 Numeric(10,2),"
dbf=dbf:"D181_270 Numeric(10,2),D271_365 Numeric(10,2),"
dbf=dbf:"DAYS_365 Numeric(10,2))" ; 14 fields

oConn.Execute("Create Table %cTable% %dbf%")

;now that blank table is created open as an updateable recordset
oRS=CreateObject("ADODB.Recordset")
oRS.CursorLocation=3 ; 3 = ?  (Client side)
oRS.Open("%cTable%",oConn,1,3,2) ; 1, 3, 2


;open csv file and parse
handle = FileOpen("%cFile%","READ")
x=FileRead(handle) ; assumes first row is header row
While 1
   x=FileRead(handle)
   If x=="*EOF*" Then Break
   oRS.addnew()
   oRS.Collect(0) = ItemExtract(1,x,",")
   oRS.Collect(1) = ItemExtract(2,x,",")
   oRS.Collect(2) = ItemExtract(3,x,",")
   oRS.Collect(3) = ItemExtract(4,x,",")
   oRS.Collect(4) = ItemExtract(5,x,",")
   oRS.Collect(5) = ItemExtract(6,x,",")
   oRS.Collect(6) = ItemExtract(7,x,",")
   oRS.Collect(7) = ItemExtract(8,x,",")
   oRS.Collect(8) = ItemExtract(9,x,",")
   oRS.Collect(9) = ItemExtract(10,x,",")
   oRS.Collect(10) = ItemExtract(11,x,",")
   oRS.Collect(11) = ItemExtract(12,x,",")
   oRS.Collect(12) = ItemExtract(13,x,",")
   oRS.Collect(13) = ItemExtract(14,x,",")
   oRS.Update()
EndWhile


FileClose(handle)
oRS.Close()
oRS=0
oConn.Close()
oConn=0

If FileExist(dbf) Then Display(2, dbf:" created","Please check if records inserted")
Exit
;/////////////////////////////////////////////////////////////////////


[stanl]

I changed your file to data.csv and below worked. Couldn't do much with 0.00 as numeric - it is just 0. Also had to code for "" delimiters as that adds an extra comma. Also uses new WB $" for multi-line text

Code (WINBATCH) Select Expand



;Winbatch - create dbase IV file based on .csv data
;assumes .csv file has headers and data types can be defined in dbase IV
path = "c:\temp\"
cFile = path:"data.csv"


If ! FileExist(cFile) Then Terminate(@TRUE,"Input File Missing",cFile)




cTable = "dbtest" ; will be named dbtest.dbf on %path%
;deletes output if it exists
dbf = path:"dbtest.dbf"
If FileExist(dbf) Then FileDelete(dbf)




;Use ACE Provider to create connection
;cConn="Provider=VFPOLEDB;Data Source=%path%;Collating Sequence=machine;"
cConn="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%path%;Extended Properties=dBase 5.0;Persist Security Info=True"
oConn = CreateObject("ADODB.Connection")
oConn.Open(cConn)
;create blank database structure based on .csv fields


sql = $"
BUS_MONTH DateTime,
ACCT_ID Char(20),
SA_ID Char(20),
CUSTOMER_NAME Char(80),
TOTAL_DUE Numeric(7,2),
CURRENT_BAL Numeric(7,2),
DAYS_31_60 Numeric(7,2),
DAYS_61_90 Numeric(7,2),
DAYS_91_120 Numeric(7,2),
DAYS_121_150 Numeric(7,2),
DAYS_151_180 Numeric(7,2),
DAYS_181_270 Numeric(7,2),
DAYS_271_365 Numeric(7,2),
DAYS_365 Numeric(7,2))
$"


sql = "Create Table %cTable%(":sql
Message("Create Statement",sql)  ;just to test sql is valid


oConn.Execute(sql)


;now that blank table is created open as an updateable recordset
oRS=CreateObject("ADODB.Recordset")
oRS.CursorLocation=3
oRS.Open("%cTable%",oConn,1,3,2)


;open csv file and parse
handle = FileOpen("%cFile%","READ")
x=FileRead(handle) ;assumes first row is header row
While 1
   x=FileRead(handle)
   If x=="*EOF*" Then Break
   oRS.addnew()
   For i = 1 To 15  ;have to account for comma in Name field
      If i<4
         oRS.Collect(i-1) = ItemExtract(i,x,",")
      Endif
      If i==4
         oRS.Collect(i-1) = StrReplace(ItemExtract(i,x,","):",":ItemExtract(i+1,x,","),'"',"")
      Endif
      If i>5
         oRS.Collect(i-2) = ItemExtract(i,x,",")   
      Endif
   Next
   oRS.Update()
EndWhile


FileClose(handle)
oRS.Close()
oRS=0
oConn.Close()
oConn=0
If FileExist(dbf) Then Display(2, dbf:" created","Please check if records inserted")
Exit
;//////////////////////////////////////////////////////////////////////////////////////




[KeithW]

Well, below is the script that actually made it thru the data with a few surprises.

860 rows in, encountered a name field without a comma, CRASH, added that case ran again and 50,800+ rows in encountered a field with 2 commas within the name field, CRASH again.  So now we have name field handling for no commas, 1 comma, 2 commas... too bad the Provider just cannot handle quoted fields and be done with it.  Next surprise was around 1,559,720 rows when the numeric field was too small and tried to stuff a big negative number into a wet 5lb bag.  Finally, the below script processed 2,134,340 rows on a i7 notebook with SSD in about 6.5hrs ...

Stan, a lingering question if you might know.  The numeric are now defined as (10,2) but generate as (20,2)?  The (7,2) were not big enough.  The last time I did true dbase II/III (never went past that till AlphaFive) ... I do not remember if there was a breaking point as to the size of the field. Is the Provider messed up  OR  did dBase have a Long & Short version that if you went over a certain width you automatically jump to the max width?  I did not expect the fields to double in width from what I specified.

I added some messages & displays to follow along as the process is painfully slow overall.

Keith

Code Select Expand


; Winbatch - create dbase IV file based on .csv data
; assumes .csv file has headers, data types can be defined in dbase IV

AddExtender("wwsop34i.dll",0,"wwsop64i.dll") ; aStatusBar

path  = "d:\a5test\"
cFile = path:"data_all.csv"
trows = 2134340
If ! FileExist(cFile) Then Terminate(@TRUE,"Input File Missing",cFile)

cTable = "dbtest" ; will be named dbtest.dbf on %path%
; deletes output if it exists
dbf = path:"dbtest.dbf"
If FileExist(dbf) Then FileDelete(dbf)

aStatusBar(0,"CSV2DBF", "Initializing DBF",0,trows)

; Use ACE Provider to create connection
; cConn="Provider=VFPOLEDB;Data Source=%path%;Collating Sequence=machine;"
cConn="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%path%;Extended Properties=dBase 5.0;Persist Security Info=True"
oConn = CreateObject("ADODB.Connection")
oConn.Open(cConn)
; create blank database structure based on .csv fields

sql = $"
BUS_MTH DateTime,
ACCT_ID Char(12),
SA_ID Char(12),
CUST_NAME Char(50),
TOTAL_DUE Numeric(10,2),
CURR_BAL Numeric(10,2),
D_31_60 Numeric(10,2),
D_61_90 Numeric(10,2),
D_91_120 Numeric(10,2),
D_121_150 Numeric(10,2),
D_151_180 Numeric(10,2),
D_181_270 Numeric(10,2),
D_271_365 Numeric(10,2),
DAYS_365 Numeric(10,2))
$"

sql = "Create Table %cTable%(":sql
Message("Create Statement",sql)  ;just to test sql is valid

T_Start = GetExactTime()
oConn.Execute(sql)
rcnt = 0 ; rows processed

; now that blank table is created open as an updateable recordset
oRS=CreateObject("ADODB.Recordset")
oRS.CursorLocation=3
oRS.Open("%cTable%",oConn,1,3,2)

; open csv file and parse
handle = FileOpen("%cFile%","READ")
x=FileRead(handle) ; assumes first row is header row
While 1
   x=FileRead(handle)
   If x=="*EOF*" Then Break
If rcnt mod 100 == 0
aStatusBar(1,"CSV2DBF", "Processed: %rcnt% rows out of %trows%",rcnt,trows)
EndIf
rcnt = rcnt +1
   oRS.addnew()
y = ItemCount(x,",")
if y == 14  THEN
    For i = 1 To 14  ; No comma in Name field
 
          oRS.Collect(i-1) = ItemExtract(i,x,",")
Next
ELSEIF y == 16
    For i = 1 To 16  ; Have, 2 commas in Name field
      If i<4
          oRS.Collect(i-1) = ItemExtract(i,x,",")
      Endif
      If i==4
          oRS.Collect(i-1) = StrReplace(ItemExtract(i,x,","):",":ItemExtract(i+1,x,","):",":ItemExtract(i+2,x,","),'"',"")
      Endif
      If i>6
          oRS.Collect(i-3) = ItemExtract(i,x,",")   
      Endif
Next
ELSE
    For i = 1 To 15  ; Have, 1 comma in Name field
      If i<4
          oRS.Collect(i-1) = ItemExtract(i,x,",")
      Endif
      If i==4
          oRS.Collect(i-1) = StrReplace(ItemExtract(i,x,","):",":ItemExtract(i+1,x,","),'"',"")
      Endif
      If i>5
          oRS.Collect(i-2) = ItemExtract(i,x,",")   
      Endif
Next
EndIf   
   oRS.Update()
EndWhile

FileClose(handle)
aStatusBar(2,"CSV2DBF", "Processed: %rcnt% rows out of %trows%",rcnt,trows)
oRS.Close()
oRS=0
oConn.Close()
oConn=0
T_Finish = GetExactTime()
Elapsed = xGetExctTime(T_Finish,T_Start)
If FileExist(dbf) Then Display(2, dbf:" created","Please check if records inserted")
Message("CSV2DBF","I/P Records: %rcnt%, Processing Time: %Elapsed%")
Exit
;//////////////////////////////////////////////////////////////////////



[stanl]

Not going to get anywhere assuming the Provider is messed up. One alternative is to use ACE to load the .csv with the text driver to recordset [oRS] with parameters for HDR and Delimiter, accompanied by an .ini if needed to read column structure, then create and open the .dbf [oRS1] .... pseudo code follows


While ! oRS.eof()
        oRS1.addnew
        For i=0 to oRS.Field.Count-1
              oRS1.collect(i)  = oRS.Collect(i)
        Next
        oRS1.Update()
        oRS.MoveNext()
EndWhile




The above is just a first guess. I've done a lot of that before, just have to find code samples.


[EDIT]: this will get you started

Code (WINBATCH) Select Expand



path = "c:\temp\"
cFile = path:"data.csv"


If ! FileExist(cFile) Then Terminate(@TRUE,"Input File Missing",cFile)


cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%path%;Extended Properties='Text;HDR=YES,FMT=Delimited(,)'"


oConn  = ObjectOpen("ADODB.Connection")
oRS    = ObjectOpen("ADODB.Recordset")
oConn.Open(cConn)
cSQL = "SELECT * FROM [data.csv];"


oRS.Open(cSQL,oConn,1,3,1)
If ! oRS.eof
   Message("Records Found",oRS.Recordcount)
fcnt = oRS.Fields.Count
flds=""
ForEach f in oRS.Fields
    flds := f.Name:@LF
Next
Message("Fields",flds)
Endif
oRS.Close()
oRS=0
oConn.Close()
oConn=0
Exit

       

[stanl]

Here you go.

Code (WINBATCH) Select Expand



;Winbatch 2020A - Create dBase IV Table from .csv
;Stan Littlefield, October 1, 2020
;=========================================================================================
IntControl(73,1,0,0,0)
path = "c:\temp\"
cFile = path:"data.csv"


If ! FileExist(cFile) Then Terminate(@TRUE,"Input File Missing",cFile)


cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%path%;Extended Properties='Text;HDR=YES,FMT=Delimited(,)'"


oConn  = ObjectOpen("ADODB.Connection")
oRS    = ObjectOpen("ADODB.Recordset")
oConn.Open(cConn)
cSQL = "SELECT * FROM [data.csv];"


oRS.Open(cSQL,oConn,1,3,1)
If oRS.eof
   oRS.Close()
   oRS=0
   oConn.Close()
   oConn=0
   Terminate(@TRUE,"Terminating",cFile:" is empty")
Endif
oRS.MoveFirst()
cTable = "dbtest" ; will be named dbtest.dbf on %path%
;deletes output if it exists
dbf = path:"dbtest.dbf"
If FileExist(dbf) Then FileDelete(dbf)


cConn1="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%path%;Extended Properties=dBase 5.0;Persist Security Info=True"
oConn1 = CreateObject("ADODB.Connection")
oConn1.Open(cConn1)


sql = $"
BUS_MONTH DateTime,
ACCT_ID Char(20),
SA_ID Char(20),
CUSTOMER_NAME Char(80),
TOTAL_DUE Numeric(10,2),
CURRENT_BAL Numeric(10,2),
DAYS_31_60 Numeric(10,2),
DAYS_61_90 Numeric(10,2),
DAYS_91_120 Numeric(10,2),
DAYS_121_150 Numeric(10,2),
DAYS_151_180 Numeric(10,2),
DAYS_181_270 Numeric(10,2),
DAYS_271_365 Numeric(10,2),
DAYS_365 Numeric(10,2))
$"


sql = "Create Table %cTable%(":sql
Message("Create Statement",sql)  ;just to test sql is valid


oConn1.Execute(sql)


;now that blank table is created open as an updateable recordset
oRS1=CreateObject("ADODB.Recordset")
oRS1.CursorLocation=3
oRS1.Open("%cTable%",oConn1,1,3,2)
n = oRS.Fields.Count()
While ! oRS.eof()
   oRS1.Addnew()
   For i = 0 To n-1
      oRS1.Collect(i) = oRS.Collect(i)
   Next
   oRS1.Update()
   oRS.MoveNext()
EndWhile




oRS1.Close()
oRS1=0
oConn1.Close()
oConn1=0


oRS.Close()
oRS=0
oConn.Close()
oConn=0
If FileExist(dbf) Then Display(2, dbf:" created","Please check if records inserted")


Exit
;=========================================================================================
:WBERRORHANDLER
oTask = 0
oClient = 0
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Terminate(@TRUE,"Error Encountered",errmsg)


[KeithW]

Thanks Stan, I will take a look at the new version you posted...

In the meantime, the following is what I have used to-date WITHOUT the typos that existed in the previous version...
this actually worked from start to end... minimum time for 2.1MM rows was a little over 6 hours... some runs were over
7 hrs due to other activity on the system.  If it can be streamlined or more efficient that would be great BUT it serves as
a working backup to the original methods that MS screwed up and that is what I need at the moment.

Thanx again for all your help & knowledge sharing !!    Keith.

Code Select Expand


; Winbatch - create dbase IV file based on .csv data
; assumes .csv file has headers, data types can be defined in dbase IV

AddExtender("wwsop34i.dll",0,"wwsop64i.dll") ; aStatusBar
AddExtender("wilx44i.dll",0,"wilx64i.dll") ; xGetElapsed

path  = "d:\a5test\"
cFile = path:"data_all.csv"
trows = 2134340
If ! FileExist(cFile) Then Terminate(@TRUE,"Input File Missing",cFile)

cTable = "dbtest" ; will be named dbtest.dbf on %path%
; deletes output if it exists
dbf = path:"dbtest.dbf"
If FileExist(dbf) Then FileDelete(dbf)

aStatusBar(0,"CSV2DBF", "Initializing DBF",0,trows)

; Use ACE Provider to create connection
; cConn="Provider=VFPOLEDB;Data Source=%path%;Collating Sequence=machine;"
cConn="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%path%;Extended Properties=dBase 5.0;Persist Security Info=True"
oConn = CreateObject("ADODB.Connection")
oConn.Open(cConn)
; create blank database structure based on .csv fields

sql = $"
BUS_MTH   DateTime,
ACCT_ID   Char(12),
SA_ID     Char(12),
CUST_NAME Char(50),
TOTAL_DUE Numeric(10,2),
CURR_BAL  Numeric(10,2),
D_31_60   Numeric(10,2),
D_61_90   Numeric(10,2),
D_91_120  Numeric(10,2),
D_121_150 Numeric(10,2),
D_151_180 Numeric(10,2),
D_181_270 Numeric(10,2),
D_271_365 Numeric(10,2),
DAYS_365  Numeric(10,2))
$"

sql = "Create Table %cTable%(":sql ; contains opening "(" !!
Message("Create Statement",sql) ; just to test sql is valid

T_Start = GetExactTime()
oConn.Execute(sql)
rcnt = 0 ; rows processed

; now that blank table is created open as an updateable recordset
oRS=CreateObject("ADODB.Recordset")
oRS.CursorLocation=3
oRS.Open("%cTable%",oConn,1,3,2)

; open csv file and parse
handle = FileOpen("%cFile%","READ")
x=FileRead(handle) ; assumes 1st row, header row
While 1
   x=FileRead(handle)
   If x=="*EOF*" Then Break
If rcnt mod 100 == 0
aStatusBar(1,"CSV2DBF", "Processed: %rcnt% rows out of %trows%",rcnt,trows)
EndIf
rcnt = rcnt +1
   oRS.addnew()
y = ItemCount(x,",")
if y == 14  THEN
    For i = 1 To 14  ; No comma in Name field
 
          oRS.Collect(i-1) = ItemExtract(i,x,",")
Next
ELSEIF y == 16
    For i = 1 To 16  ; Have, 2 commas in Name field
      If i<4
          oRS.Collect(i-1) = ItemExtract(i,x,",")
      Endif
      If i==4
          oRS.Collect(i-1) = StrReplace(ItemExtract(i,x,","):",":ItemExtract(i+1,x,","):",":ItemExtract(i+2,x,","),'"',"")
      Endif
      If i>6
          oRS.Collect(i-3) = ItemExtract(i,x,",")   
      Endif
Next
ELSE
    For i = 1 To 15  ; Have, 1 comma in Name field
      If i<4
          oRS.Collect(i-1) = ItemExtract(i,x,",")
      Endif
      If i==4
          oRS.Collect(i-1) = StrReplace(ItemExtract(i,x,","):",":ItemExtract(i+1,x,","),'"',"")
      Endif
      If i>5
          oRS.Collect(i-2) = ItemExtract(i,x,",")   
      Endif
Next
EndIf   
   oRS.Update()
EndWhile

FileClose(handle)
aStatusBar(2,"CSV2DBF", "Processed: %rcnt% , Of: %trows%",rcnt,trows)
oRS.Close()
oRS=0
oConn.Close()
oConn=0
T_Finish = GetExactTime()
Elapsed = xGetElapsed(T_Finish,T_Start)
If FileExist(dbf) Then Display(2, dbf:" created","Please check if records inserted")
Message("CSV2DBF","I/P Records: %rcnt%, Processing Time: %Elapsed%")
Exit
;//////////////////////////////////////////////////////////////////////


[stanl]

Thanks Stan, I will take a look at the new version you posted...

I replied last night with a comment that would appear insensitive and later deleted it [apologize to anyone who read it, no bad intent]. Basically, the code I posted isn't a version, it is IMHO: the best WB can do to quickly convert .csv into db4 by using the ACE provider. Using both the text provider as well as dbase 5.0 - address issues with "" delimiters and commas as well as decimal precision. And I also think the execution time would improve.


That you might still have issues will be beyond my present ability to help.

[KeithW]

Thanx again, Stan.

Taking the last code you posted and turning it into the convertor yielded mostly the same results.
Speed wise on the 2.1MM rows was 6:18 (H:M) and 6:33, the second run contended with an Incremental Backup of the system.
Setting up the connection is a bit more involved, yet processing the records are simpler and varying comma within the
name field were easily handled and did not involved extra processing code to accommodate, a big plus as I have no idea what
the client is throwing at me in the first place.  I presume the numeric field sizing is just a traditional dBase issue, as Alpha does
not do that and it might be one of the areas that dBase & Alpha differ in.

In any case, this exercise has been to build a usable backup process.  Its not the first time my primary method has broken even
if the reasons were different I needed to have an alternative method of getting the job done when the primary method fails and
I now have that thanx to your help.

Below is a working result I used to very the process.

Regards,
Keith

Code Select Expand


;Winbatch 2020A - Create dBase IV Table from .csv
;Stan Littlefield, October 1, 2020
; KfW - Made into CSV2DBF - v3 - Oct 2, 2020
;=========================================================================================

AddExtender("wwsop34i.dll",0,"wwsop64i.dll") ; aStatusBar
AddExtender("wilx44i.dll",0,"wilx64i.dll") ; xGetElapsed

IntControl(73,1,0,0,0)

path = "d:\a5test\"
cFile = path:"data_all.csv"
trows = 2134340

If ! FileExist(cFile) Then Terminate(@TRUE,"Input File Missing",cFile)

cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%path%;Extended Properties='Text;HDR=YES,FMT=Delimited(,)'"

oConn  = ObjectOpen("ADODB.Connection")
oRS    = ObjectOpen("ADODB.Recordset")
oConn.Open(cConn)
cSQL = "SELECT * FROM [data_all.csv];"

oRS.Open(cSQL,oConn,1,3,1)
If oRS.eof
   oRS.Close()
   oRS=0
   oConn.Close()
   oConn=0
   Terminate(@TRUE,"Terminating",cFile:" is empty")
Endif

oRS.MoveFirst()
cTable = "dbtest" ; will be named dbtest.dbf on %path%

;deletes output if it exists
dbf = path:"dbtest.dbf"
If FileExist(dbf) Then FileDelete(dbf)

aStatusBar(0,"CSV2DBF":" ":GetExactTime(), "Initializing DBF",0,trows)

cConn1="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=%path%;Extended Properties=dBase 5.0;Persist Security Info=True"
oConn1 = CreateObject("ADODB.Connection")
oConn1.Open(cConn1)

sql = $"
BUS_MONTH DateTime,
ACCT_ID Char(12),
SA_ID Char(12),
CUSTOMER_NAME Char(50),
TOTAL_DUE Numeric(10,2),
CURRENT_BAL Numeric(10,2),
DAYS_31_60 Numeric(10,2),
DAYS_61_90 Numeric(10,2),
DAYS_91_120 Numeric(10,2),
DAYS_121_150 Numeric(10,2),
DAYS_151_180 Numeric(10,2),
DAYS_181_270 Numeric(10,2),
DAYS_271_365 Numeric(10,2),
DAYS_365 Numeric(10,2))
$"

sql = "Create Table %cTable%(":sql
Message("Create Statement",sql) ;just to test sql is valid

T_Start = GetExactTime()

oConn1.Execute(sql)

rcnt = 0 ; rows processed

; now that blank table is created open as an updateable recordset
oRS1=CreateObject("ADODB.Recordset")
oRS1.CursorLocation=3
oRS1.Open("%cTable%",oConn1,1,3,2)
n = oRS.Fields.Count()
While ! oRS.eof()
rcnt = rcnt +1
If rcnt mod 100 == 0
aStatusBar(1,"CSV2DBF", "Processed: %rcnt% rows out of %trows%",rcnt,trows)
EndIf

   oRS1.Addnew()
   For i = 0 To n-1
      oRS1.Collect(i) = oRS.Collect(i)
   Next
   oRS1.Update()
   oRS.MoveNext()

EndWhile

oRS1.Close()
oRS1=0
oConn1.Close()
oConn1=0

oRS.Close()
oRS=0
oConn.Close()
oConn=0
aStatusBar(2,"CSV2DBF", "Processed: %rcnt% , Of: %trows%",rcnt,trows)
T_Finish = GetExactTime()
Elapsed = xGetElapsed(T_Finish,T_Start)

If FileExist(dbf) Then Display(2, dbf:" created","Please check if records inserted")
Message("CSV2DBF","I/P Records: %rcnt%, Processing Time: %Elapsed%")

Exit
;=========================================================================================
:WBERRORHANDLER
oTask = 0
oClient = 0
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Terminate(@TRUE,"Error Encountered",errmsg)
;==========================================================================================