Subject Re: date problem in appending from Excel
From Mervyn Bick <invalid@invalid.invalid>
Date Sun, 7 Mar 2021 10:33:20 +0200
Newsgroups dbase.getting-started
Attachment(s) append_csv.prg

On 2021/03/07 08:09, Emeka Ossai wrote:
> Hello Mervyn,
 >
> Thank you so far.
 >
> With the example attached, a date of 15/02/2021 is giving 14/08/2026. Again I will appreciate if I can get example of importing direct from excel.
 >

I apologise. There was a typo in the code for handling dates in the
dd/mm/yyyy format in the program I posted. The corrected version is
attached.

There is an article on moving data between Excel and dBASE in the
Advanced Topics in the dBASE knowledgebase.

http://www.dbase.com/Knowledgebase/adv/activex/ExcelOle/excel.htm

Read the article in the meantime.  If you post a spreadsheet with 2 or 3
lines of data and an empty copy of the table you want to fill I'll
create some sample code for you.  As this is only sample data simply
overwrite anything in the spreadsheet that is confidential.  The
important thing is to have the correct format for the spreadsheet and
the table.

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 = c1
  //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