Alter Table Trick

Okay so it is now 2018 and I want to greet every one a more prosperous new year than before.  I am now back at work and immediately I got a request from a Colleague to create a small app that can import data from an excel generated by another app, and later use my ExcelPivot class to provide the needed reports.  Instead of them doing those manually.  So the raw data from excel looks like this:

Then of course the easiest way for me to do that is to import the file and then later adjust fields:

Import From (m.lcFile) Type Xl5

That resulted to 8 columns bearing the field names a,b,c,d,e,f,g and h.  So after that,  we need to change the field names and types to proper ones like this:

Alter Table (m.lcDBF) Alter Column a c(8)

Alter Table (m.lcDBF) Rename Column a To po

Alter Table (m.lcDBF) Rename Column b To supplier

Alter Table (m.lcDBF) Alter Column c D

Alter Table (m.lcDBF) Rename Column c To Date

Alter Table (m.lcDBF) Rename Column d To Status

Alter Table (m.lcDBF) Rename Column e To workorder

Alter Table (m.lcDBF) Rename Column F To location

Alter Table (m.lcDBF) Alter Column g N(12,2)

Alter Table (m.lcDBF) Rename Column g To amount

Alter Table (m.lcDBF) Alter Column h N(12,2)

Alter Table (m.lcDBF) Rename Column h To UpdAmt

As you can see above, some involves ALTER COLUMN which changes the type of that column to a target one and all involves RENAME COLUMN to give those fields proper names.

And that is how it is done.  Correcting fields to proper names and types. 

However, there is a simpler way to do that which I remembered to share here just now as I know this can come handy to you guys plus VFP help itself is not clear about this usage too.  Here is a counterpart of that, a single ALTER TABLE command that will perform all of those changes in one go.  Here goes:

ALTER Table (m.lcDBF) ALTER Column a c(8) RENAME Column a To po RENAME Column b To supplier ALTER Column c D RENAME Column c To Date RENAME Column d To Status RENAME Column e To workorder RENAME Column F To location ALTER Column g N(12,2) RENAME Column g To amount ALTER Column h N(12,2) RENAME Column h To UpdAmt

And as you can see, all you need to do is put a space inbetween each alteration syntax until you  applied all.  But that is a bit confusing to read so better break those up to several lines like this (also adding extra space on ALTER so those will align better):

ALTER Table (m.lcDBF) ;
      ALTER  Column a c(8) ;

      RENAME Column a To po ;

      RENAME Column b To supplier;

      ALTER  Column c D ;

      RENAME Column c To Date ;

      RENAME Column d To Status;

      RENAME Column e To workorder;

      RENAME Column F To location;

      ALTER  Column g N(12,2);

      RENAME Column g To amount;

      ALTER  Column h N(12,2);

      RENAME Column h To UpdAmt

And there you go!  I hope this comes useful to you when the time comes!  Happy New Year!!!