Importing data from an external source to a database is a common database operation, whether you want to back-up your data, transfer databases, or simply create a meaningful dataset through the use of available analytical tools. The easy steps entailed to import a CSV file to your database will be covered in this short tutorial.
The first step for importing a CSV file is to provide a name for the table you will be creating. Type the name on the box provided next to the Table Name label.
Next to the CSV File row is the Schema which is contained within the database. Schemas are composed of database objects created by a particular database user. In this example, “strata_user” is our username filled in on the schema field.
A delimiter in SQL is a symbol used to separate or define the end of each command. It can be any symbol, but in our example, we will use the default comma.
In creating a table, the data under each column is generally represented by a label located in the first row. Row headings are used to label the data contained in each row. You can type 0 if the first row of data is used as headers for the columns; otherwise, leave the field empty if you don’t have a header row.
This drop-down menu provides you options if a table exists. We will choose “Append” in our example to insert data into the table.
Check this box if you want duplicate columns to be automatically specified as “X.0, X.1”.
Check this box if you don’t want the SQL to include spaces after a delimiter.
Enter the number of rows you want to skip at the start of the file. Otherwise, leave it blank.
Enter a number of rows of file you want the SQL to read.
If you don’t want to blank lines to be misinterpreted as NaN (not a number) values, make sure to check this box.
Sometimes you want to parse date values to easily store and manipulate data. Check this option to break down date values into predefined parts.
Select the box to automatically interpret the datetime format.
Enter a symbol you want to represent as a decimal point. The default is the common period symbol.
Supply this field with the index column that you will use as row labels of the dataframe. Otherwise, leave this box blank.
Select this box if you want to write the dataframe index as a column.
Provide a column label in this field if you are using index columns.
After you have supplied all the information needed for creating the table, you can now click the Save button located at the bottom of the page.
Now you have successfully uploaded your CSV file to the table you created.
In the editor, type the following commands:
SELECT * FROM schema_name.table_name
The SELECT command returns data from a single or multiple tables. Using an asterisk will allow you to retrieve all the rows of data from the table. The FROM statement includes the name of the table where you want to fetch your data. For example, we can type the following statements in the editor to fetch all the rows of data from the sample_db table.
Click the Run Query button below the editor to query the table.
If the query is successful, you should be able to see the output under the Results tab.