Subject Re: date problem in appending from Excel From Gaetano D. <gaetanodd@hotmail.com> Date Sun, 7 Mar 2021 17:42:53 +1000 Newsgroups dbase.getting-started On 7/03/2021 16: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. > > 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 >> >> >> >> >> there is an importdata.wfm form in dUFLP unfortunately it is very unstable, it will crash dBase 50% of the time on open and/or on closing the form. Can you clarify what you mean by "importing directly from Excel"? CSV is a text format, not an Excel format. Do you mean that you have an Excel file and you convert that to CSV at the moment and you would like to export directly to DBF from Excel (.XLS)? Excel used to have that option but I see it is no longer available. One thing you may want to try is to change the format of the date inside Excel in CSV format. Even though CSV is a text format, Excel allows to change the date format and saves it in the defined format - I have tried it recently and it worked: 1. save the file as CSV format 2. select the dates in the sheet one column at a time, hit Ctrl+1 (Ctrl key plus number 1) to open the cell formatting window, it can take a few seconds to appear. 3. select the date data type, then look for the predefined formats in the right pane. If none of them suits, you can specify your own format using y for year, m for month...etc., e.g. yyyy-mm-dd hh:mm:ss or yyyy/mm/dd hh:mm:ss, then save/Ok that format. Repeat for every column required, and save the file (don't use save as CSV, just hit the Save icon) and close it. Open it in notepad and check that the format was saved as expected, from there you can append from the CSV in dBase. HTH Gaetano.