|
Horizontalize your data |
  
|
|
Horizontalize your data |
|
"Horizontalize" data : Transfer x RECORD values in x COLUMNS in ONE RECORD.
You are a fan of music and store all your songs on CD's
You would love to have all your songs available in one XLS or TPS or SQL Table
Open your favorite Music Player and export to CSV all your cdrom's titles
You will have a CSV file with this layout :
SOURCE-TABLE CSV
FILENAME STRING(11)
PATH STRING(14)
TITLE STRING(31)
ARTIST STRING(9)
ALBUM STRING(18)
LYRICS STRING(1)
PICTURES STRING(1)
LENGTH STRING(5)
BITRATE STRING(1)
FREQ STRING(5)
FILESIZE STRING(2)
RATING STRING(2)
MOOD STRING(1)
TEMPO STRING(1)
CATEGORY STRING(1)
END
Now you want to have all the "TITLE" values of your 24 RECORDS in this file set in 24 COLUMNS of ONE destination RECORD
Use DMC Creation Wizard or Read from TXT feature to create a TPS Table with this structure :
CDROM FILE,DRIVER('TOPSPEED'),PRE(CDR),CREATE,BINDABLE
BYCOUNT KEY(-CDR:COUNT),PRIMARY,NAME('CDR:ByCount')
BYARTIST KEY(+CDR:ARTIST),NAME('CDR:ByArtist')
RECORD RECORD
COUNT LONG
ARTIST CSTRING(100)
DISC_NAME STRING(100)
LOCATION STRING(100)
TRAY STRING(10)
DATE DATE
TITLE1 STRING(100)
TITLE2 STRING(100)
TITLE3 STRING(100)
TITLE4 STRING(100)
TITLE5 STRING(100)
TITLE6 STRING(100)
TITLE7 STRING(100)
TITLE8 STRING(100)
TITLE9 STRING(100)
TITLE10 STRING(100)
TITLE11 STRING(100)
TITLE12 STRING(100)
TITLE13 STRING(100)
TITLE14 STRING(100)
TITLE15 STRING(100)
TITLE16 STRING(100)
TITLE17 STRING(100)
TITLE18 STRING(100)
TITLE19 STRING(100)
TITLE20 STRING(100)
TITLE21 STRING(100)
TITLE22 STRING(100)
TITLE23 STRING(100)
TITLE24 STRING(100)
END
END
Then open the Import & Export module select your CSV File as source and TPS as destination - click on the "Horizontalize" feature BEFORE selecting your destination Table and do your mappings - DMC will do the rest for you
The mapping of each record offers you the possibility to define a column as "ROOT" and to "Horizontalize" the data of all "CHILDREN" columns
Select the TITLE1 column and double-click to open the record, this tab will let you define all which is needed
Simply click on "Horizontalize" and select in the drop down box the SOURCE column you want to find in the 24 "TITLExx' columns : in our case the column TITLE also (but it could be any name ...)
The "Common Root" field has been filled in for you and the "Root Column" checkbox also.
If you follow the convention of your columns as explained here and in the record form, then all will be fine
If you want to Horizontalize several source columns then you only need to have as many destination columns ....- the process is NOT LIMITED to only one source column!
If your source file contains only a part "set" (ie. : 10 or 12 records) and you want to Horizontalize those records to a 24 column destination file, then the code will ONLY map those columns found in source records.
By saving this record all the relevant columns will be mapped automatically for you
From TITLE 1to TITLE24 all will have "Horizontalize" checked but disabled
If you want to delete the mapping : DO NOT use the normal right-click but open the record and simply uncheck "Horizontalize" and save the record ....
Set you other columns normally (Artist or others) and click on "Transfer"
The destination table will be filled with ONE record and all 24 COLUMNS will receive the data from the source 24 RECORDS.
VERY IMPORTANT INFORMATION (for DEMO version ONLY) : you are ONLY allowed to do create 5 Horizontalized Tables in demo mode and no extension can be given to this limit (except very special situations)