Subject Re: separator for CSV file to be imported into dBase
From Mervyn Bick <invalid@invalid.invalid>
Date Wed, 23 Sep 2020 23:13:13 +0200
Newsgroups dbase.getting-started
Attachment(s) test_semicolon.prg

On 2020/09/23 17:59, Jean R. from Paris wrote:

> Thank you Mervyn
 >
> I am using the latest dBase 2019-1
 >
> At this moment dates are not a concern because I am mostly dealing with names and numerical values which can be kg or parices with "," as a separator.
 >
> fields are separated by the ";" only without double quotes which I use here for the sake Clarity only.
 >
> as an example one line of the CSV file can be as follows:
 >
> 2014;INTOS;INTOS;S&P;CANDE;SP 75 - S25;SP 75;25;16,40;USD;IT;ETE;0;0;0;0;0;0;50;0;100;0;25;0;175;CC
 > the end of the line will be "CR LF" standing for carriage return - line feed
 >
> another line can look like :
 >
> 2014;CHEL;CHEL COMPIGE;NOF-OC;WBRID;DE S-753 - F200;DE S 753;200;21,95;EUR;FR;CRA;0;0;0;0;0;0;600;0;600;0;800;0;2000;CC
 > the end of the line will be "CR LF" Standing for carriage return - line feed
 >
> This file can be thousands of lines
 >
> Other similar files to be processed can be 500 or more successive fields which DBIMPORT cannot handle either.
As with any .csv file you will need to set up a suitable .dbf file to
receive the data.  In the attached example I've simply made all the
character(15) fields except fld9 which is numeric(10,2) to show that
numeric fields are handled correctly.  You will obviously use more
appropriate field names and sizes.

The "meat" of the program is the code below the ****************

500 fields is a rather unwieldy table but dBASE can handle up to 1024
fields as long as the total length of a record doesn't exceed 32767
bytes.  The little program shouldn't even raise a sweat. :-)

If you don't already have a copy of the dUFLP you can get it from
http://www.goldenstag.net/dbase/index.htm#duflp where you will also find
instructions for installing it.


Mervyn






f = new file()
f.create('test_semicolon.csv')
a =  "2014;INTOS;INTOS;S&P;CANDE;SP 75 - S25;SP 75;25;16,40;USD;IT;ETE;0;0;0;0;0;0;50;0;100;0;25;0;175;CC"
b = "2014;CHEL;CHEL COMPIGE;NOF-OC;WBRID;DE S-753 - F200;DE S 753;200;21,95;EUR;FR;CRA;0;0;0;0;0;0;600;0;600;0;800;0;2000;CC"
f.puts(a)
f.puts(b)
f.close()

if file('test_semicolon.dbf')
drop table test_semicolon
endif

if not file('test_semicolon.dbf')
   create table test_semicolon  (fld1 character(15),;
   fld2 character(15),;
   fld3 character(15),;
   fld4 character(15),;
   fld5 character(15),;
   fld6 character(15),;
   fld7 character(15),;
   fld8 character(15),;
   fld9 numeric(10,2),;
   fld10 character(15),;
   fld11 character(15),;
   fld12 character(15),;
   fld13 character(15),;
   fld14 character(15),;
   fld15 character(15),;
   fld16 character(15),;
   fld17 character(15),;
   fld18 character(15),;
   fld19 character(15),;
   fld20 character(15),;
   fld21 character(15),;
   fld22 character(15),;
   fld23 character(15),;
   fld24 character(15),;
   fld25 character(15),;
   fld26 character(15))

endif

*********************
cSource = 'test_semicolon.csv'
cTarget = 'test_semicolon.dbf'
clear
s = seconds()
_app.allowYieldOnMsg := true
set procedure to :duflp:stringex.cc
q = new query()
q.sql = 'select * from '+cTarget
q.active = true
f = new file()
f.open(cSource)
nCount = 0
do while not f.eof()
   cStr = f.gets()
   aArray = new stringex(cStr).breakstring(';')
   q.rowset.beginappend()
   for n = 1 to aArray.size
       q.rowset.fields[n].value = aArray[n]
   next
   q.rowset.save()
   nCount ++
   if nCount%50 = 0
      ?nCount+ '   Records'
   endif  
enddo
      ?nCount+ '   Records'
      ?seconds()-s +'   Seconds'
f.close()
q.active = false