Navigation:  Import & Export > 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)

---