Process CSV files with WinPODUtil tool

In the previous article we saw how to query POD databases with the WinPODUtil application, in this article we will see how to use the tool to process the CSV files generated by the queries, or any other CSV file. The format of these files consists in a series of text lines, the first of which must contain the column headings, separated by the semicolon character (;). Each line, except the first, represents one data record.

To open one of these files, use the File / Process... option and select the file with the data:

Open CSV file with WinPODUtil — Open a CSV file with WinPODUtil

On the left side you will see a list with the names of the different fields, and, on the right side, a pene with parameters related with the selected columns.

The Column Operation parameter allows you to define the type of data contained in the selected column or columns, which may be Factor or Character for alphanumeric data, Date Time, Numeric or Undefined. Selecting one of these values, the program will make changes to the data based on the value type contained in the column. For example, to the data of type character or factor, the double quote character will be added ("), and, for Date and Numeric formats, you can check and apply custom formats.

Select column operations on CSV columns — Select actions on the CSV columns

You can also drop the column from file by selecting the Drop Column value.

To change the format of the dates that contains the file, there are two parameters, Actual Date Time Format and Final Date Time Format. With the first of them, you can define the original date format of the dates contained in the file. With the second, you can provide a new format to convert the dates. In this link you can find the custom date and time format strings.

You can also rename the column with the Column Name parameter, the original name can be seen in the Original name parameter.

You can add new columns to the data set, by using the New Column button, located on the left side of the form, below the file column list. To initialize these columns with a concrete value, provide it in the Initial Value parameter, this value must be consistent with the data type that you have assigned to the new column.

Combining two CSV files in only one

It is also possible to combine two CSV files into one, provided that the data have common fields that can be used as keys to match the different data records. To do this, select a second file using the Merge With... option, in the File menu:

Merge files option in WinPODUtil tool — Open another CSV file to combine

A second panel will appear in the center of the form with the columns of the second CSV file, which can also be configured with the column parameters.

The first thing to do is define all the key fields in the two sets of data, and how they are mapped to each other. In the example of the image, we have data from the PISA studies database, whose key is formed by the year (YEAR), school (SCHOOLID) and the student (STUDENTID) fields, so, we must select each of these fields and change the value of the Key property to true, so they will pass to the key panel, at the top.

It is important to define the correct order of the fields when the key consists of multiple columns, as in this case. To do this you can use the Key Index property (1 for YEAR, 2 for SCHOOLID and 3 for STUDENTID).

Then you must make the same with the dataset containing the fields to merge. In the key fields of this dataset, It should also be defined how they map to each key fields in the main data set, by using the Main Key property.

Define the key columns in the two files — Define the keys in the two files

Finally, select the columns you want to add to the main file and drag them with the mouse over the New Column panel:

Copy columns from one CSV file to the other

And that's all; the last thing to do is just press the Process button and wait for all indicated changes are made. You can save the changes at the end by using the option File / Save or Save As...