Subject Re: date problem in appending from Excel
From Mervyn Bick <invalid@invalid.invalid>
Date Fri, 5 Mar 2021 11:18:53 +0200
Newsgroups dbase.getting-started
Attachment(s) append_csv.prg

On 2021/03/04 20:32, Emeka Ossai wrote:
> Hello,
 >
> I need your assistance.  I saved the file as a csv file. I am trying to append the data  from csv to dbase, but the date did not show at all. Other data showed except date.
 >
> I used 'append from c:\bike\bikal.csv delimited'

append from c:\bike\bikal.csv delimited will only append a date into a
date file if the .csv file contains the date in the format
...,yyyymmdd,....  The format in the .csv file must be yyyymmdd
irrespective of the date setting in dBASE.  It may not be a problem in
this case but append from ... delimited also does not import data into
memo fields.

Excel places separators between the year, month and day in a CSV file
and dBASE can't handle this.

Fortunately creating a little program to import the dates correctly is
quite straightforward.  A little example is attached.

You will need to edit the program to supply the names of the CSV file
and the table as well as the separator used in the dates in the CSV file.

Depending on the format of the dates in the CSV file you may also need
to make changes in the function create_date.

If you have to transfer data from Excel to dBASE on a regular basis keep
in mind that you can write a program to move data direct from Excel to
dBASE without first placing the data in a CSV file.

Mervyn.



cCSV = 'date_test.csv'
cTable = 'date_test'
cDateSeparator = '/'
set procedure to :duflp:stringex.cc
q = new query()
q.sql = 'select * from '+cTable
q.active = true
f = new file()
f.open(cCSV)
do while not f.eof()
   cRead = f.gets(100000)
   aArray = new stringex(cRead).breakstring(",", true)
   q.rowset.beginAppend()
   for n = 1 to q.rowset.fields.size
      if q.rowset.fields[n].type = 'DATE'
         q.rowset.fields[n].value = create_date(aArray[n])
      else  
         q.rowset.fields[n].value = aArray[n]
      endif  
   next
   q.rowset.save()
enddo
f.close()
q.active = false

function create_date(cDate)
   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)
   //The following is based on the date in the CSV file being yyyy/mm/dd
   yy = c1
   mm = c2
   dd = c3
   //If the date format in the CSV is dd/mm/yyyy use the following
//   yy = c3
//   mm = c2
//   dd = c3
  //If the date format in the CSV is mm/dd/yyyy use the following
//   yy = c3
//   mm = c1
//   dd = c2
    dDate = new date(val(yy),val(mm)-1,val(dd))
   return dDate