COPY FROM with Delimiter
You will learn to copy the data from a file into a table using Delimiter.
A delimiter is a character that separates text strings. Common delimiters are:
- Commas (,)
- Semicolon (;)
- Quotes ( ", ' )
- Dash (-)
- Pipes (|)
- Slashes ( / \ ).
By default, the COPY FROM function accepts commas (,). Therefore, if you use a character other than commas, you should add a DELIMITER param in the syntax.
The syntax for COPY FROM 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.
- DELIMITER 'delimiter': the delimiter used in the CSV file.
Let’s have a look at the step-by-step below:
Step #1: Create a CSV File
First, you should create a CSV file and store it on your local computer. In this case, we use Dash ( - ) character to separate the text.
Step #2: Import FIle from Local to Server
You can use the syntax below for importing the file to the server:
Next, import the file to the server using the above syntax:
If it’s successfully imported, you will get the following result:
Step #3: Connect to Oxla Server
Connect to the Oxla server using the command below:
You are now in the Oxla environment if you get the output below.
Step #4: Create a Table
Before creating a table, check for duplicate tables with the statement below:
In return, you will retrieve a list of existing tables in Oxla.
Ensure you are not creating duplicate tables.
Create a “featurelisttable” table using the command below:
Step #5: Copy the CSV File Into Table
Because we are using Dash ( - ), we need to add a DELIMITER param with a specified character, as shown below:
You will get the following successful result:
Otherwise, you will get the error message below:
Step #6: Retrieve the Table
To verify that the data was imported correctly from the server, retrieve all the data using the SELECT statement:
You will have the same data in the table as in the CSV file.