COPY FROM with HEADER
When it comes to a table, we deal with its components like rows, columns, and headers. In Oxla, we provide 3 possible options for the header as follows:
- HEADER OFF
This option will not skip the header of the CSV file. Below are the available syntaxes besides HEADER OFF:
This is a default behavior that will be applied if you do not provide the HEADER option in your query.
- HEADER ON
This option will skip the header of the CSV file and only follow the columns that have been specified before. Below are the available syntaxes besides HEADER ON:
- HEADER MATCH
This option will read the header and verify that the name matches the column names.
The syntax for COPY FROM with HEADER is as follows:
Two parameters need to be specified in the syntax:
- table_name: the table that will receive data from the file.
- file_path: a link to the file location in the server.
- Header_Syntax: the specified header options.
Say you have created a CSV file called idvals.csv, and the file has been uploaded to the server:
Then, you create a table by specifying the column with an integer data type:
Now, let’s see an example case by case:
1) With reference to the table above, run the following query:
2) An error output will appear.
This happens because we specified the table with an INTEGER column. While in the CSV file, we have STRING value which is “id” and “quantity”, which are not considered headers.
To include the headers, use the HEADER ON option.
Another Option
1) If you don’t want to include the headers (“id” and “quantity”), you can modify your CSV file by deleting the headers:
2) Run the COPY FROM query:
3) You will get the following output which indicates that the file has successfully imported to the table:
4) Display the table by using the SELECT statement to retrieve the table records:
1) With reference to the idqty table above, run the following query:
2) You will get the following output which indicates that the file has successfully imported to the table:
3) To verify, use the SELECT statement to retrieve the table records:
We will get the below result, which displays the idqty table:
In this case, the header may be anything that has been specified before. It does not need to have column names.
1) Based on the idqty table above, if we run the following query:
2) It will produce a successful output because the specified columns in the idqty table are matched with the header of the idvals.csv file:
3) But, you will get a mismatched output when the header isn’t matched. Say that the idvals.csv file has “id” and “qty” header, as shown below:
Then, you will get a mismatched output because it reads “qty” from the CSV file when the expected value is “quantity” as specified in the table.
Another Option
1) Furthermore, you can also define the columns that you want to match as shown below:
The following output shows a successful result:
2) But, if you change the ordering by switching the order of the columns:
You will get a mismatch error message.
