Subject Re: import a csv file from excel
From Mervyn Bick <invalid@invalid.invalid>
Date Fri, 4 Aug 2023 10:45:28 +0200
Newsgroups dbase.getting-started
Attachment(s) APPEND_CSV.prg

On 2023/08/02 18:07, ED FURCHE wrote:
> how long should it take to import an excel.csv file of 2400 recs to a dbf?
 > dbf is about 100 characters
Seconds rather than minutes.  I have a 5 field table (1 x autoinc, 2 x
char and 2 x date) that imports 9500 records from a .csv file in just
under 9 seconds.  The time taken does, however, depend on the computer,
the number of records and the number of fields.

How long is it taking and what code are you using?

The built-in way of appending from a .csv file

     USE WHATEVER
     APPEND FROM WHATEVER.CSV DELIMITED
     USE

has major drawbacks.  It will not import into memo fields and it only
saves dates to a date type field if they are in the format
..,yyyymmdd,... i.e no delimiters and no separators irrespective of the
date format on the computer.

As very few (if any) programs other than dBASE save dates in .csv files
in this format it means that, unless you are going to save dates to
character fields, you need a little program to import from a .csv file.
Fortunately the function breakstring() in stringex.cc in the dUFLP makes
this easy.

The biggest problem when adding data from a .csv to a .dbf file is
dealing with dates.  For what it's worth (it was written for my own use
and was never really intended to be distributed), the little program I
use is attached.  The program can deal with virtually any date or
datetime format in the .csv file.

If your date format in the .csv file is mm/dd/yyyy you only need to pass
the name of the .csv and .dbf files as parameters.  See the comments
under if nArg = 2  for a list of the various values that can be passed
as parameters.

   append_csv('whatever.csv','whatever.dbf')


With hindsight I should perhaps have placed the parameter for setting
the date format third as the date is the most likely to be different in
varous .csv files.  I have, however, used the program in too many
"wrapper" programs to make changes now.

If you need to set a specific parameter you will need to provide values
for ALL parameters up to, and including, that parameter.

Mervyn.





parameters cCSV,cTable,cSeparator,bSkipFirstRecord,cDateFormat,cDateSeparator,bIsWindows,bIsAutoInc
nArg = argCount()
if nArg < 2
   msgbox('At least an input file and an output table must be supplied')
   return
endif
if nArg = 2
  cSeparator= ','
  bSkipFirstRecord = false
   cDateFormat = 'a'
   // 'a'  mm/dd/yyyy; 'b' dd/mm/yyyyy; 'y'  yyyy/mm/dd; 'd' yyyymmdd
   cDateSeparator = '/'
   // '/' for British or American, '-' for Italian, '.' for German
   bIsWindows = true
   //False for .csv created in Unix.  As it stands the program can only deal with standard
   //Windows and Unix end-of-line markers.
   bIsAutoinc = false
   //True if first field in table is AutoInc.  
elseif nArg = 3
   bSkipFirstRecord = false
   cDateFormat = 'a'
   cDateSeparator = '/'
   bIsWindows = true
   bIsAutoInc = false
elseif nArg = 4
   cDateFormat = 'a'
   cDateSeparator = '/'
   bIsWindows = true
   bIsAutoInc = false
elseif nArg = 5
   cDateSeparator = '/'
   bIsWindows = true
   bIsAutoInc = false
elseif nArg = 6
   bIsWindows = true
   bIsAutoInc = false
elseif nArg = 7
  bIsAutoInc = false
endif  
set procedure to :duflp:stringex.cc
q = new query()
q.sql = 'select * from '+cTable
q.active = true
f = new file()
f.open(cCSV)
if bSkipFirstRecord
   cRead = f.gets(1000,iif(bIsWindows,chr(13)+chr(10),chr(10))) //Skip first record with field names
endif  
do while not f.eof()
   cRead = f.gets(1000,iif(bIsWindows,chr(13)+chr(10),chr(10)))
   if not empty(cRead)
      aArray = new stringex(cRead).breakstring(cSeparator, true)
      q.rowset.beginAppend()
      for n = iif(bIsAutoInc,2,1) to q.rowset.fields.size //Skip first field if it is autoinc
         if q.rowset.fields[n].type = 'DATE' or q.rowset.fields[n].type = 'DATETIME'
            q.rowset.fields[n].value = create_date(aArray[n],cDateFormat)
         else  
            q.rowset.fields[n].value = aArray[n]
         endif  
      next
      q.rowset.save()
   endif
enddo
f.close()
q.active = false

function create_date(cDate,cDateFormat)
    local nHr,nMin,nSec
    nHr = 0
    nMin = 0
    nSec = 0
   if cDateFormat <> 'd'
      c1 = substr(cDate,1,at(cDateSeparator,cDate)-1)
      c2= substr(cDate,at(cDateSeparator,cdate,1)+1,at(cDateSeparator,cDate,2)-at(cDateSeparator,cDate,1)-1)
      c3= substr(cDate,at(cDateSeparator,cdate,2)+1)
   else   // cDateFormat = 'd'
       if len(cDate) = 6 or at(' ',cDate) =7
         c1 = substr(cDate,1,2)
         c2= substr(cDate,3,2)
         c3= substr(cDate,5,2)
      else
         c1 = substr(cDate,1,4)
         c2= substr(cDate,5,2)
         c3= substr(cDate,7,2)
      endif
   endif
   if cDateFormat = 'y' or cDateFormat = 'd'
   //If the date format in the CSV file is yyyy/mm/dd or yyyymmdd
      yy = c1
      mm = c2
      dd = c3
   elseif cDateFormat = 'b'  
   //If the date format in the CSV is dd/mm/yyyy (British) use the following
      yy = c3
      mm = c2
      dd = c1
   elseif cDateFormat = 'a'  
  //If the date format in the CSV is mm/dd/yyyy (American) use the following
      yy = c3
      mm = c1
      dd = c2
   endif
   if len(cDate)>10  //timestamp
      cTime = rtrim(ltrim(substr(cDate,at(' ',cDate))))
      if ':'$cTime
         nHr = val(substr(cTime,1,at(':',cTime)))
      endif  
      if 'PM'$upper(cDate) and nHr <>12
         nHr += 12
      endif
      if 'AM'$upper(cDate) and nHr = 12
         nHr -= 12
      endif
      if at(':',cTime,1) < len(cTime)
         nMin = val(substr(cTime,at(':',cTime,1)+1))
      endif
      if at(':',cTime,2) > 0
         nSec = val(substr(cTime,at(':',cTime,2)+1))
      endif      
   endif
   if len(yy) = 2
      if substr(str(set('epoch'),4,0),3)>=yy
         yy = '20'+yy
      else
         yy = '19'+yy
      endif        
   endif
    dDate = new date(val(yy),val(mm)-1,val(dd),nHr,nMin,nSec)
   return dDate