Subject Re: csv import into dbf From Mervyn Bick <invalid@invalid.invalid> Date Sun, 18 Aug 2024 16:44:56 +0200 Newsgroups dbase.getting-started On 2024/08/18 11:06, Akshat Kapoor wrote: > I agree with you on most counts. > > But it is not a thing that requires tooooo much coding if you opt in for > DIY route. > > I have successfully imported from finger print attendance machine (the > format/end of line marker of txt file is unix. And the date format is > yyyy-mm-dd). > Using file object open the source file. Read lines make desired > alterations to the line and save it to a new file. That file is ready > for append from ....... If it works it's not wrong :-) but you've made a two-step operation of appending to a table from a .csv file. It is only meant for use with .dbf tables but if you use append_from_csv.prg in the dUFLP you can do it in one line of code.     append_from_csv('whatever.dbf','whatever.csv','y',false,',','-',true) If there's a heading record in the .csv file change the false to true. Adding records to a SQL table requires a different approach especially with MySQL or MariaDB (and perhaps others but not Firebird) which can handle up to 1000 records with a single INSERT statement. > > I have successfully imported from bank statements downloaded in the > delimited format. > Open the source file. Using breakstring() of stringex.cc in duflp and > use the desired values to append rows into a table. > > And this is not counting the import from a csv that I have created in > excel. > > Yes the coding has to be meticulous. You have to know the source > precisely but after that it is not that difficult No argument from me on this at all.  Writing a program to import from .csv to .dbf is a simple matter if one uses breakstring() from stringex.cc in the dUFLP to do the heavy lifting.  It only takes a few minutes and for years I used to write an individual import_whatever.prg for each table.  Before I discovered breakstring() parsing each .csv line was a real PITA. In many cases the code was identical except for the .dbf and .csv names. This, in itself, cried out for the code to be factored out to a library function. There is no standard for .csv files.  South Africa uses a comma for the decimal point.  A .csv file created by Excel on my computer uses ; as the field separator and dates are saved as ...;yyyy/mm/dd;... and numeric values are saved as ...;1234,56;...  Dealing with these issues is not difficult but not every .csv has these problems.  The answer was to use switches in the library program so that they could be dealt with if necessary.  Over the years, the number of switches needed grew. append_from_csv.prg has reached the stage where it should be able to deal with virtually any .csv file but if a new variation ever turns up it is a simple matter to deal with it. Charlie's .csv file contains a heading record which is easy enough to deal with.  If Charlie writes his own code it will probably be about a dozen lines and probably longer if he has to deal with dates.  By using append_from_csv.prg in the dUFLP all he needs is two lines once he's determined which switches to use. :-) Mervyn.