drop columns & rename column names

[kosta]

Hi All,
i'am importing an excel sheet from excel file and create dbf.. first record containings excel column names , naturally in dbf A,B,C,D goes etc...
And first record containings excel column names in dbf such as 16.09.2003 17.09.2003 18.09.2003 going on hierarchicaly .

Now i wantto do these steps,

1-first i wantto rename some column names eg S with 15.09.2003 T with 16.09.2003

1-after renaming ,wantto drop some columns as a user entered date ranges ( eg. <15.09.2003 ->07.10.2003)

how can i do this ?

TIA

Soykan OEZCELIK

 

[mm0000]

The syntax for renaming a column is :

alter table tablename rename column columnname as newcolumname

however remember in foxpro you cannot have a column name starting with a number so 15.09.2003 would be an invalid column name.

Drop column syntax :

alter table tablename drop column columnname

 

[nicuschor]

Use

COPY STRUCTURE EXTENDED

alternate with

CREATE <file1> FROM <file2>

(see fox help details).

as follows:

A.First, with the dbf file you want to modify in use, create a new table with &quot;copy stru extended&quot; and then modify field as you wish (warning! you cannot exceed the maximal lenght of field names, and you cannot use any character you want in the field name). You can doit manually or by program.

B.Create a new file with &quot;create ...from&quot;

C.Make a program to transfer data between those 2 files (the innitial dbf, to the last dbf)


Luck,
Nick

 

[ramani]

Hi

1. When you work on a table and extract a report per user input parameters, I would not suggest to modify the tables structure for the sake of those parameters. Because, that will not cause the failure for the next report generation. Very simple to see this, because you will be lost as to their field names. So Alter table column.. etc is ruled out here. (though your header of question asks for that).

2. You can extract the data per user choice using SQL SELECT statement.. this is next option..
Example SELECT field1 AS myField1, field2 AS myfield2 etc..
and use myField1, myField2 as per users choice. Here the limitation is that the field names have to follow the conventions of starting with alpha character, not exceeding 10 characters length etc. But the report header need is otherway. So this is also ruled out.

3. The next choice available is to cast the report into excel and then change the header line cells to suit the requirement. That is possible and a workable solution.

The header row is always in specific position.
Select the cell in the oExcell object created and then replace it with the new vales after the exporting is over.



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[kosta]

Hi Ramani,
may be step by step SQL select solve my request but i've no idea for this .

excel table look like below

A B C D E
Order Customer 15.09.2003 16.09.2003 17.09.2003
Ord1 Cust1 2000 1000 3000

i think first should select fixed colums for eg. A,B on this table then given user range dates only but how ?


Soykan OEZCELIK

 

[ramani]

Hi Sokyan,

You can do this..

SELECT myOrder, myDate, myAmt FROM myTable ;
WHERE BETWEEN(myDate,fromDate,ToDate)
DO (_genXtab)
BROW

You will get a return file which will hold your way of asking.. i.e. Order, Date1, Date2, date3.. and amount in rows in respective columns.

But to use _genXtab you can use only 3 columns..

Now set the relationship to the parent table.. and then generate your report.

If you are not in VFP8, then you can use multiple columns using my gsXtab available in my site.

http://www.winnersoft.coolfreepages.com

Look for gsXtab under utilities.

The way to go is create your table and call gsXtab with the column number you want across and the column you want tabulated.. example..
Create the cursor...
SELECT myOrder, myCust, myDate, myAmt FROM...
INTO CURSOR temp
=gsXtab(,,,,3,4)
This will return a table the way you want.

If you are using VFP8, gsXtab need a little tweaking, which I havent completed. The SQL selects and groupings are different in VFP8 and so gsXtab will not work.



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/