Subject Re: date problem in appending from Excel
From Emeka Ossai <megameks@yahoo.com>
Date Sun, 07 Mar 2021 01:09:18 -0500
Newsgroups dbase.getting-started

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.

Thank you

Emeka



Mervyn Bick Wrote:

> 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
 >
>
>
>
>