Tuesday, January 2, 2018

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.lcDBFALTER Column a c(8RENAME Column To po RENAME Column To supplier ALTER Column c D RENAME Column To Date RENAME Column To Status RENAME Column To workorder RENAME Column To location ALTER Column g N(12,2RENAME Column To amount ALTER Column h N(12,2RENAME Column 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!!!

1 comment: