f



copy data from .xlsx/.xls file as new records to an EXISTING .dbf file (via programming in a clipper 5.3 .PRG file)

 .xlsx/.xls data be saved as new records in an existing .dbf file
(to be done by programming in a clipper 5.3 .prg file)


i have this in an .xlsx/.xls file:

Name   Hours     Date            Assign     OPE          Comments   
XXT     4.50     20/12/2014      AUDIT      Conveyance   checked 1314 books
P01    12.05     23/12/2014      DUED1      Lunch        computed net worth
XXT      .10     23/12/2014      M&A                     merged stock...
90H     2.00     02/01/2015      AUDIT      Xerox        found discrepancies

can the 4 (or more records) be read & saved as 4 records in an existing .dbf file
the field names in xlsx file (Name,Hours,Date...) may be same or different in the EXISTING .dbf file. 
note: (reading & saving to be done by programming in a .prg file)

anyone with a working solution:
please type in the exact code/syntax/command/function... to be used in a .prg file.
(it would be terrific, if you use the exact field names & values i used)

regards

0
timecosting
1/5/2015 5:28:50 PM
comp.lang.xharbour 5470 articles. 0 followers. Post Follow

8 Replies
1054 Views

Similar Articles

[PageSpeed] 28

On Monday, January 5, 2015 at 10:58:52 PM UTC+5:30, timec...@gmail.com wrote:
> .xlsx/.xls data be saved as new records in an existing .dbf file
> (to be done by programming in a clipper 5.3 .prg file)
> 
> 
> i have this in an .xlsx/.xls file:
> 
> Name   Hours     Date            Assign     OPE          Comments   
> XXT     4.50     20/12/2014      AUDIT      Conveyance   checked 1314 books
> P01    12.05     23/12/2014      DUED1      Lunch        computed net worth
> XXT      .10     23/12/2014      M&A                     merged stock...
> 90H     2.00     02/01/2015      AUDIT      Xerox        found discrepancies
> 
> can the 4 (or more records) be read & saved as 4 records in an existing .dbf file
> the field names in xlsx file (Name,Hours,Date...) may be same or different in the EXISTING .dbf file. 
> note: (reading & saving to be done by programming in a .prg file)
> 
> anyone with a working solution:
> please type in the exact code/syntax/command/function... to be used in a .prg file.
> (it would be terrific, if you use the exact field names & values i used)
> 
> regards

Save the file as .CSV in Excel and write a program to read records and save it in DBF file.

0
cyril19sara
2/6/2015 2:10:39 PM
On Friday, February 6, 2015 at 7:40:40 PM UTC+5:30, cyril...@gmail.com wrote:
> On Monday, January 5, 2015 at 10:58:52 PM UTC+5:30, timec...@gmail.com wrote:
> > .xlsx/.xls data be saved as new records in an existing .dbf file
> > (to be done by programming in a clipper 5.3 .prg file)
> > 
> > 
> > i have this in an .xlsx/.xls file:
> > 
> > Name   Hours     Date            Assign     OPE          Comments   
> > XXT     4.50     20/12/2014      AUDIT      Conveyance   checked 1314 books
> > P01    12.05     23/12/2014      DUED1      Lunch        computed net worth
> > XXT      .10     23/12/2014      M&A                     merged stock...
> > 90H     2.00     02/01/2015      AUDIT      Xerox        found discrepancies
> > 
> > can the 4 (or more records) be read & saved as 4 records in an existing .dbf file
> > the field names in xlsx file (Name,Hours,Date...) may be same or different in the EXISTING .dbf file. 
> > note: (reading & saving to be done by programming in a .prg file)
> > 
> > anyone with a working solution:
> > please type in the exact code/syntax/command/function... to be used in a .prg file.
> > (it would be terrific, if you use the exact field names & values i used)
> > 
> > regards
> 
> Save the file as .CSV in Excel and write a program to read records and save it in DBF file.

thanks for responding...

you mean in excel, save as .csv & then read & copy to a .dbf file.

would you please get me started on how to read a .csv file & save it in DIFFERENT/RELEVANT .dbf fields.  (coding in a .prg file)

regards
0
timecosting
2/6/2015 2:52:29 PM
On Saturday, February 7, 2015 at 1:52:30 AM UTC+11, timec...@gmail.com wrote:
> On Friday, February 6, 2015 at 7:40:40 PM UTC+5:30, cyril...@gmail.com wrote:
> > On Monday, January 5, 2015 at 10:58:52 PM UTC+5:30, timec...@gmail.com wrote:
> > > .xlsx/.xls data be saved as new records in an existing .dbf file
> > > (to be done by programming in a clipper 5.3 .prg file)
> > > 
> > > 
> > > i have this in an .xlsx/.xls file:
> > > 
> > > Name   Hours     Date            Assign     OPE          Comments   
> > > XXT     4.50     20/12/2014      AUDIT      Conveyance   checked 1314 books
> > > P01    12.05     23/12/2014      DUED1      Lunch        computed net worth
> > > XXT      .10     23/12/2014      M&A                     merged stock...
> > > 90H     2.00     02/01/2015      AUDIT      Xerox        found discrepancies
> > > 
> > > can the 4 (or more records) be read & saved as 4 records in an existing .dbf file
> > > the field names in xlsx file (Name,Hours,Date...) may be same or different in the EXISTING .dbf file. 
> > > note: (reading & saving to be done by programming in a .prg file)
> > > 
> > > anyone with a working solution:
> > > please type in the exact code/syntax/command/function... to be used in a .prg file.
> > > (it would be terrific, if you use the exact field names & values i used)
> > > 
> > > regards
> > 
> > Save the file as .CSV in Excel and write a program to read records and save it in DBF file.
> 
> thanks for responding...
> 
> you mean in excel, save as .csv & then read & copy to a .dbf file.
> 
> would you please get me started on how to read a .csv file & save it in DIFFERENT/RELEVANT .dbf fields.  (coding in a .prg file)
> 
> regards



nFile1        := "C:\mycsvfile.csv"
dbselectarea("MyDBF")
APPEND FROM &nFile1 delimited


0
poopall
2/11/2015 4:31:16 AM
On Wednesday, February 11, 2015 at 10:01:17 AM UTC+5:30, poopall wrote:
> On Saturday, February 7, 2015 at 1:52:30 AM UTC+11, timec...@gmail.com wrote:
> > On Friday, February 6, 2015 at 7:40:40 PM UTC+5:30, cyril...@gmail.com wrote:
> > > On Monday, January 5, 2015 at 10:58:52 PM UTC+5:30, timec...@gmail.com wrote:
> > > > .xlsx/.xls data be saved as new records in an existing .dbf file
> > > > (to be done by programming in a clipper 5.3 .prg file)
> > > > 
> > > > 
> > > > i have this in an .xlsx/.xls file:
> > > > 
> > > > Name   Hours     Date            Assign     OPE          Comments   
> > > > XXT     4.50     20/12/2014      AUDIT      Conveyance   checked 1314 books
> > > > P01    12.05     23/12/2014      DUED1      Lunch        computed net worth
> > > > XXT      .10     23/12/2014      M&A                     merged stock...
> > > > 90H     2.00     02/01/2015      AUDIT      Xerox        found discrepancies
> > > > 
> > > > can the 4 (or more records) be read & saved as 4 records in an existing .dbf file
> > > > the field names in xlsx file (Name,Hours,Date...) may be same or different in the EXISTING .dbf file. 
> > > > note: (reading & saving to be done by programming in a .prg file)
> > > > 
> > > > anyone with a working solution:
> > > > please type in the exact code/syntax/command/function... to be used in a .prg file.
> > > > (it would be terrific, if you use the exact field names & values i used)
> > > > 
> > > > regards
> > > 
> > > Save the file as .CSV in Excel and write a program to read records and save it in DBF file.
> > 
> > thanks for responding...
> > 
> > you mean in excel, save as .csv & then read & copy to a .dbf file.
> > 
> > would you please get me started on how to read a .csv file & save it in DIFFERENT/RELEVANT .dbf fields.  (coding in a .prg file)
> > 
> > regards
> 
> 
> 
> nFile1        := "C:\mycsvfile.csv"
> dbselectarea("MyDBF")
> APPEND FROM &nFile1 delimited

thank you poopall:

--should the EXACT fieldnames be in the 1st row ONLY - starting from column 1
--each new fieldname should be a separate column - right ?
(A1-Date   B1-Voucher   C1-Credit   D1-Debit   E1-Amount)
 04/02/15  A51099       BANKI       TRAVEL      156.50
 05/02/15  A51100       CASHH       FOOD       4189.03
 05/02/15  A51123       BANKI       TRAVEL      90.00
 06/02/15  A51126       CITI        STAM P       8.19

--what if there is a blank column
--instead of APPEND FROM - can it check if fieldnames match before saving.  
--what is fieldnames do not match (in APPEND FROM)
--what if there is data type mismatch

regards
0
timecosting
2/11/2015 11:21:53 AM
Dear timec...:

On Wednesday, February 11, 2015 at 4:21:56 AM UTC-7, timec...@gmail.com wrote:
....
> --should the EXACT fieldnames be in the 1st row ONLY
> - starting from column 1

With the method presented, the field names are treated as the first record, and imported also.  Does not matter.

> --each new fieldname should be a separate column - right ?
> (A1-Date   B1-Voucher   C1-Credit   D1-Debit   E1-Amount)
>  04/02/15  A51099       BANKI       TRAVEL      156.50
>  05/02/15  A51100       CASHH       FOOD       4189.03
>  05/02/15  A51123       BANKI       TRAVEL      90.00
>  06/02/15  A51126       CITI        STAM P       8.19

Sure.  But does not matter.

> --what if there is a blank column

Garbage in, garbage out.  And if columns are resequenced, garbage out.  The database "MyDBF" impresses its structure and names on what is imported.

> --instead of APPEND FROM - can it check if
> fieldnames match before saving.  

You can hand-code this, read in the first line, and see if you can parse it into a DbCreate array from some sort of lookup table.

> --what is fieldnames do not match (in APPEND FROM)

"MyDBF" does not care, but you might.

> --what if there is data type mismatch

Plows right over it.

Excel is a horrible free-form interface, to try and funnel into a structured data table.

Add that some nitwit thinks you should add asterisks and notes to different values... because that is what I did.

David A. Smith
0
dlzc
2/11/2015 3:45:58 PM
I think I should add a word of caution here.
From XLS or XLSX if you save directly as .csv file, the coma (,) character within some fields will be retained.
When you use the clipper .prg with a line which says

append from myfile.csv to mydatabase.dbf deli

all the saved fields will remain intact, and if there was a piece of data within a field, e.g. 27,Mars Rd., Timbuctu, Nigeria  it will come into your DBF as such.

If your intention is to split the information in this field into 
House, Street, City, Country
then you should cut and paste the selected fields into a word file.  Then use "convert table to text" and save as .csv to .txt
I use the added caution of doing a cut and paste from word to wordpad or notepad  and save it from one of these as a .txt file.

Then when your "append from' command is invoked from your .prg you will have separated the four fields of the long address which was originally in one field of the XLS. file.  
I have not tried it fully, but the Open office and Libre office would also work similarly.
0
Grella
2/11/2015 7:06:30 PM
Yes the comma delimiter is a bugger if its relevant in the Excel data,=20

Excel also wanting to convert dates to some format which may not be the one=
 that you want to import.=20

I live in Australia and this is a constant headache, sometimes its imported=
 as a date sometimes blank, sometimes what appears on the screen in Excel i=
s not what gets saved in the CSV, so be aware. I normally have to read date=
s in as a string, do some checks to see the date is formatted OK,  then wri=
te it away in the format that I need. Its irritating as its not always cons=
istent if you received excel files from external sources.

My solution to the comma being used as a delimiter, is I normally write a m=
acro in Excel to extract the data into txt file delimiting each cell with  =
something like a ":".

The read each line of the text file in and use token to split it into an ar=
ray. I can then split the data and do all the validation checks before I ap=
pend to the dbf.

0
poopall
2/12/2015 12:47:34 AM
On Thursday, February 12, 2015 at 12:36:31 AM UTC+5:30, Grella wrote:
> I think I should add a word of caution here.
> From XLS or XLSX if you save directly as .csv file, the coma (,) character within some fields will be retained.
> When you use the clipper .prg with a line which says
> 
> append from myfile.csv to mydatabase.dbf deli
> 
> all the saved fields will remain intact, and if there was a piece of data within a field, e.g. 27,Mars Rd., Timbuctu, Nigeria  it will come into your DBF as such.
> 
> If your intention is to split the information in this field into 
> House, Street, City, Country
> then you should cut and paste the selected fields into a word file.  Then use "convert table to text" and save as .csv to .txt
> I use the added caution of doing a cut and paste from word to wordpad or notepad  and save it from one of these as a .txt file.
> 
> Then when your "append from' command is invoked from your .prg you will have separated the four fields of the long address which was originally in one field of the XLS. file.  
> I have not tried it fully, but the Open office and Libre office would also work similarly.

thank you

and, one more to my ever growing tribe of helpers...

regards to all
0
timecosting
2/12/2015 5:54:03 AM
Reply:

Similar Artilces:

program that will open xls file and read it and use the data in another program and print the output in xls file
How can we write program that will open xls file and read it and use the data in another program and print the output in xls file ? filename=input('enter the name of the xls file =') sheet =input('NO. of worksheet you want to import [for input] =') range =input('specify the range - for example A4:B5 [for input] =') N = xlsread('filename', sheet, 'range') -For xlsread command did we have to imort and save file before -If we put filename as the name of the xls file ==> Undefined variable But if we put it as data will give the numerical data only. -s...

Write to text file. Append to existing file. Create file if file doesn't exist.
Hi folks,   up to LabVIEW 7 there was a wonderful "Write to Text File" vi which allowed the option "Append to File". Now, in LabVIEW 8, this old vi is not longer supported anymore. Instead, I am suggested to use a new "Write to Text File" vi. This vi does not offer the option "Append to File" anymore. Rather it is proposed in the online help that one should use the "Set File Position" in order to append text to the file. This much less straightforward than the old solution. Furthermore, I miss an option which would create the ...

How to copy a file to a file with a prefixed file name
I'm trying to write a program that will archive selected files to an archive directory. The files in the archive directory will have a prefix in the format yyyymmdd-hhmmss-filename.ext. So, if the file is called "somefile.dat", then the filename in the archive directory should be "20090522-164325-somefile.dat". I'm using Clipper's COPY FILE command to do the copy. I can't figure out how to get the prefix in front of the file name. Creating the prefix string is no problem, but I can't figure out how to tack that onto the front of the filename. If I...

FAQ 5.2 How do I change one line in a file/delete a line in a file/insert a line in the middle of a file/append to the beginning of a file? #3 554638
This is an excerpt from the latest version perlfaq5.pod, which comes with the standard Perl distribution. These postings aim to reduce the number of repeated questions as well as allow the community to review and update the answers. The latest version of the complete perlfaq is at http://faq.perl.org . -------------------------------------------------------------------- 5.2: How do I change one line in a file/delete a line in a file/insert a line in the middle of a file/append to the beginning of a file? Use the Tie::File module, which is included in the standard distribution sinc...

FAQ 5.2 How do I change one line in a file/delete a line in a file/insert a line in the middle of a file/append to the beginning of a file? #3 331649
This message is one of several periodic postings to comp.lang.perl.misc intended to make it easier for perl programmers to find answers to common questions. The core of this message represents an excerpt from the documentation provided with Perl. -------------------------------------------------------------------- 5.2: How do I change one line in a file/delete a line in a file/insert a line in the middle of a file/append to the beginning of a file? Use the Tie::File module, which is included in the standard distribution since Perl 5.8.0. ------------------------------------------...

clipper 5.3 : coding in .prg to convert ..txt to .xls file
in my .prg i save my reports as a .txt file.=20 what i am looking for is to find a way to convert that .txt to a .xls file (with a command/function/statement...) this is my coding in a .PRG file columntitle=3D'Date Client Partner Debit Credit Narration...' compname=3Dcompanyname+' '+companyaddr+' '+companycity header1=3D'Client Ledger Of:'+ trim(Clientname) header2=3D'Period:'+dtos(fromdate)+' To: '+dtos(todate) extraheader=3D'Mumbai Clients only' *actual printing set devi to prin nametextfile() &&a...

'Adding new data file' vs 'extending existing data file size' to increase Tablespace
As the Tablespace is almost filled on the running system now, we have to add additional space to the Tablespace. As I understand, the following are the two options available to do that: 1) Add a new data file to hold the additional data in Tablespace: SQL> alter tablespace my_TS add datafile '/someDir/my_TS2.dbf' size 100M; 2) Resize the existing Tablespace's data file. SQL> alter database datafile '/someDir/my_TS1.dbf' resize 200M; What is the best way to go? In my understanding, the 1st option is not preferable as the Tablespace is spread over 2 data files and ...

file.exe file generation from file.m file
How to generate application (*.exe) file from *.m file, which has lots of graphics. Program was written for image watermarking by using image processing toolbox. I want to send this program some where else but with out sowing the program codes. By this *.exe no need to use matlab6.5 platform. Also tell me How to generate *.p file from *.m file Which is hide the code to user but for run this program need matlab6.5 Please give me some idea about it. it is very urgent for me. I will be very great full to you "Biswajit Kar" <hibiswajitkar@rediffmail.com> wrote in message ne...

Program to open a file in binary, skip X bytes and write the rest of the file to a new file
How would I approach this? I cannot store the entire file in memory (they are 30GB+) but I need to skip the first 292 bytes and write the rest to a new file. Thanks, Scott scad wrote: > How would I approach this? I cannot store the entire file in memory > (they are 30GB+) but I need to skip the first 292 bytes and write the > rest to a new file. 1. Open the file to read (file_1) 1.a If file_1 in bad state, report and exit. 2. Counter = number of bytes to skip 3. While counter > 0 and not the end of file_1 read file_1 3.a. If file_1 in bad sta...

How Do I Copy Over An Existing File in an Obey File?
How embarassing is this. I have two files a and a/zip. When I'm happy with a/zip I'd like to rename it "a" (and do a few other things too, but we'll just stick to the copy / rename / delete problem for now). In Unix, I'd just type "mv a.zip a" and that would generally be that. But writing an Obey file to do it makes my head hurt. If I write "copy a/zip a DFQ~N~P" it does what I want, but prompts me if I want to move the file. So, I thought I'd try deleting, or wiping the file "a" first - but if the file "a" doesn...

Add a filed to existing dbf file
Dear Sir, Is there any way to Add a field in existing dbf file with xharbour (Programmatically) ? Regards Bapu. "Bapu" <learning_b@yahoo.com> ha scritto nel messaggio news:46a730be$0$1348$834e42db@reader.greatnowhere.com... > Dear Sir, > > Is there any way to Add a field in existing dbf file with xharbour > (Programmatically) ? No, you have to create a new empty DBF using the changed structure and copy records from the old. Then delete the old and rename the new. EMG -- EMAG Software Homepage: http://www.emagsoftware.it The EMG's ZX-Spectrum Page...

How to find New File,Write file and close file?
Hi Anyone can show me how to find New File,Write File and close file in block diagram.The picture of file names are as show in pics. Thanks &nbsp; Nyan Lin File find.JPG: http://forums.ni.com/attachments/ni/170/322096/1/File find.JPG Hi. The functions You're looking for are under "File I/O --&gt; Advanced File Functions" (At least the NEW File) The 2 others are in the "File I/O" menu on the functions palette. Good luck.Message Edited by BCL@Servodan on 05-08-2008 03:38 AM Hi again. My mistake. I was looking in my LabView7. In 8.5.1 the Icons have changed.....

new URL("c:/dir/file") OR new URL("c:\dir\file") OR new URL("file://c:/dir/file")
new URL("c:/dir/file") OR new URL("c:\dir\file") OR new URL("file://c:/dir/file") What is the correct syntax ? Is it the same as a new File ?FileDialog send: "c:\Program Files\dir\file". I have the feeling that different classes (File, URL, URl) have the different syntax. Please say a), b) or c) and answer me does it is same for all java classes which ask for a file path? FreeStyler =E3=81=AE=E3=83=A1=E3=83=83=E3=82=BB=E3=83=BC=E3=82=B8: > new URL("c:/dir/file") OR new URL("c:\dir\file") OR new > URL("file://c:/dir/fil...

copy txt file - only copies 1/3 of the file
File.open('oldFile.txt', 'r') do |f1| #file contains XML type data m = f1.gets File.open('newFile.txt','w') do |f2| #newFile to separate data in oldFile f2.puts m.split("<") end end Hello All. There is something that I did that was weird here. I say this because when I open the new file that it creates its 1/3 the size of the original. It seems to just completely cut off. I took off the .split and it still did this, so I know it has nothing to do with split. What do you think is going on? oldFile.txt contains XML formatted data. ...

Web resources about - copy data from .xlsx/.xls file as new records to an EXISTING .dbf file (via programming in a clipper 5.3 .PRG file) - comp.lang.xharbour

Mercury (programming language) - Wikipedia, the free encyclopedia
11.07.2 (August 3, 2012 ; 2 months ago ( 2012-08-03 ) ) Mercury is a functional logic programming language geared towards real-world applications. ...

The sheer volume and quality of Netflix’s original programming is unprecedented
... on its laurels, Netflix CEO Reed Hastings said in an earnings press release that Netflix plans to launch at least 600 hours of original programming ...

Learn any of these 16 programming languages and you'll always have a job
... world," venture capitalist Marc Andreessen famously declared. Someone has to write that software. Why not you? There are thousands of programming ...

Microsoft offers developers free tools for its R programming language software
Microsoft is giving developers and students a free crack at its new server software for the R programming language. [ Want to learn R? Download ...

Pay what you want to learn programming for web, Apple Watch, iOS 9 and more
The world of programming is wide and wild, bristling with enough different languages, platforms and workflows to leave anyone dizzy. These 10 ...

CBS, Sky Enter Major Pan-European Deal For Showtime Programming
Sky Atlantic is to become the exclusive home to Showtime programming in major European markets under a new long-term licensing agreement between ...

Donald Trump gets his own blustery programming language
Have you ever wondered what code would look like if it were dictated by a presidential candidate's grandiose statements rather than such radical ...

Verizon's CFO: Custom TV will 'refresh' to comply with programming contracts
Verizon CFO Fran Shammo said his company will make changes to its FiOS Custom TV product, which granularly controls which programming networks ...

The CW Doesn't Care About Live Views Because The CW Doesn't Have Major Stake In Its Programming
CW president Mark Pedowitz says he doesn't care about live viewership... but it's not for the reasons you think.

New Donald Trump programming language seeks to make programming great again
A team of software engineers at Rice University has introduced a new programming language called TrumpScript. Based on Python, the new language ...

Resources last updated: 1/26/2016 2:59:54 AM