All language subtitles for 5. Import data from File

In the last video, we learned how to insert data into a database using the insert into command.

Let us now learn how to import data from a file into a table.

This can be done using the copy command.

The copy command looks like this.

The syntax of copy command is copy, then the table name and then you mention the columns in which the

data is to be imported.

Then you give the location of the file.

You specify the delimiter.

The delimiter is the symbol which is segregating different columns of your data.

Then comes the type of file.

If it is CSV, we'll write CSV.

Then if the data has headers, we'll write a header.

The header keyword works only if it is a CSV file.

So if it is a text file, the header keyword will not work.

You will have to remove the header from the text file and then import the data.

So to practice this command, we will be importing data from a CSV file and a text file.

Two sample files are attached in the resources section of this lecture.

You can download the files and put it into the same installation directory where your post was installed.

I'll show you where I put my two files.

This is where Post was installed for me.

I created a new folder there and copied the two files that I've attached in the resources section.

Download these two files there and now we'll write the commands.

The reason why we copied these two files in the same installation folder is the SQL Server may not be

able to access the files on your system.

This is the only folder where the files can be accessed by the server.

So now we'll start typing our command.

It starts with copy.

Then they blame.

Then column names if data has different columns.

We did not mention the column names then from the location of file.

To get the address.

You go to the location and check the address bar.

Copy the address from the place within single codes.

Paste this address.

You will need to mention the file name also here.

We just copy CSV.

After this.

Will mention the delimiter, which is comma.

And yes, with the format and my data is headers.

To this query ran successfully.

Let us go and check our table.

Kinsey, five and six customer IDs have been added by this copy statement.

So if you go and open the CSV file in Microsoft Excel, you'll be able to see this is exactly how the

data is present in the Excel file also.

Now let us copy data from a text file.

We can copy this command only and make the changes.

The name is Copy Text.

The OECD.

Delimiter is comma, it is not a CSV file and it cannot identify the header column.

So that is why I deleted the header row from my text file.

Now let us run this command.

Unsuccessfully.

Let us see.

So you can see seven and eight customer IDs have been added by this copy command from the text file.

You can open the text file also to check that the data was separated by a comma.

If you want to change the delimiter, so I'll suggest you go and change the delimiter from comma to

a pipe or a and symbol probably, and then change the delimiter in this query and then run it again.

These are the two ways to insert data into a table.

One is to use the insert into command and the other one is to use the copy command from where you can

copy data from a file to a particular table.

In the next lecture, we will learn how to retrieve the data that we have added to our table.