Fixing datatable headers
All datatables require a single line of headers for column names.
Last updated
Was this helpful?
All datatables require a single line of headers for column names.
Last updated
Was this helpful?
If data have no column names, or if there is extra header space before the row containing column names, the data require editing before use. The top row will be interpreted as column names, so inputs need to be formatted that way.
A datatable has a single row of headers as column names, without any other rows above it or extra rows after it before the data rows begin.
This CSV has no headers, so the first row of data gets interpreted as column names.
If the file is small:
Whatever format your data are in, get them into CSV.
Use Concatenate-text adaptor to prepend column names to the data. Left argument (Bind to value) is the comma-separated series of desired column names, ending with a newline. Right argument (Bind to another transformation) is the csv from the data input.
Combination output argument then connects to the next step (probably CSV-to-datatable)
Use Datatable-columns to turn the header row (which is really supposed to be your first row of data) into a list
use List-to-datatable so the list becomes the data in a new datatable column
use Add-column to create a dummy column (for use in Spread-rows)
use Spread-rows to turn the column of column names into the values in a single row
use Remove-column to get rid of the dummy column. Now your first row of data is both the header and the first row of data.
Finally, use Merge-datatables with this as 'first data' and the original datatable (which has the first data-row as column names instead of as data) as 'second data'.
Now you can Rename-columns and have all your data as data.
In this situation, the extra information (or empty space) in the header gets interpreted as column names, so you need to remove it.
Use spreadsheet-file or google-spreadsheet adaptor to import the file.
Use the **RANGE ** argument to remove rows at the top of the sheet. In the example data above, the real headers are in Row3. The desired import Range will be A3 until end of file.
If you are unsure what the last row will be, choose something larger than expected (e.g. range=“A3:P20000”.
The output of the adaptor will only include rows and columns that contain data within this specified range; you won’t get empty cells.
To see this example, make a copy of so you can see how it is built.
To see this example, make a copy of so you can see how it is built.
