Sql Read Csv File Into Variable Table

While creating a database, your client may demand to save onetime information in new database and he has asked y'all to import his CSV file into SQL server database, or you already have some data in .csv file and needs to import it, then nosotros can have ii possible means to import csv data into sql server, using Majority insert SQL query or using SQL server direction studio (SSMS) GUI options, let'due south have a expect on each of them ane by ane.

So, before we go on to await more details into importing methods, suppose, this is our .csv sample file.

Import CSV to SQL server using query

First, we volition try to import above Orders.csv file into SQL server tabular array "Orders" using query, so to simply import the to a higher place data into SQL server tabular array, we volition use beneath query

          Bulk INSERT Orders FROM 'D:\Orders.csv' WITH (     FIRSTROW = 2, -- as 1st one is header     FIELDTERMINATOR = ',',  --CSV field delimiter     ROWTERMINATOR = '\n',   --Employ to shift the control to adjacent row     TABLOCK )        

Subsequently running the above query you volition see output as beneath

and you can verify it in the table

Few things to note, hither

• In the above query, nosotros accept rights to read file from source "D:\Orders.csv", if you don't have proper permission, you may go error, then make sure you lot have proper permissions.
• In the in a higher place csv file, we are notifying "Id" information likewise, in that location can exist possibility, when we don't have Primary key, and nosotros need to auto-increment PK, we will evidence you how to import csv file into sql server with motorcar-increase id.

Import csv into SQL with motorcar-increment Columnd (Id)

When we accept a situation in which, nosotros have csv file, but we cannot provide PK "Id" field within the .csv file, and need's to car-increase it, we tin exercise information technology by providing space with comma in csv file, so above csv file will expect like this

          Id,Country,Price,OrderQuantity ,India,10.00,four ,Commonwealth of australia, 5.00,10 ,Brazil, ten.00,v ,China,5.50,5 ,Nepal,xx.20,10        

and you can import and salve information technology in same mode, similar you did earlier, using same query

          BULK INSERT Orders FROM 'D:\Orders.csv' WITH (     FIRSTROW = 2, -- as 1st one is header     FIELDTERMINATOR = ',',  --CSV field delimiter     ROWTERMINATOR = '\n',   --Use to shift the command to next row     TABLOCK )        

Import CSV file into SQL server using SQL server direction Studio

In this process, nosotros will use SQL server management studio GUI to import csv file in sql server database tabular array, equally majority copy and other bulk import options are non available on the SQL servers, so this GUI based selection is practiced and easy for big CSV files, since it allows you to import data, step by step and more easily.

Footstep i: Select database, right-click on information technology -> "Tasks"->Select "Import flat file"

At first, open up your SQL server management studio, and select the database ( in this example "OrderDetails") so right-click on it, after right-clicking on it, select "Tasks"-> Select "Import flat file"

Now, once you select "Import flat file" a new dialog box, will open up, click "Next"

Footstep 2: Browse file and give table name

Clicking "next", will bring the new screen, using which nosotros need to select the "csv" file to be imported, then click on "Browse", locate the .csv file and give table proper name.

Note: Table proper noun muste be unique, means that table should be new tabular array ( not already created table )

Step 3: Preview information before saving it

Once, you lot will click next subsequently selecting file, you lot can preview the data before saving it into tabular array, considering to a higher place CSV file, we accept can preview like below

As y'all can see in the above paradigm, nosotros tin can come across CSV data in preview, you can click on "Next"

Step 4: Cheque Data-blazon and map it properly, to successfully import csv

Now, you need to map csv file columns with database columns blazon properly, as shown in the beneath epitome

You can change data-type, as per your csv file, in one case done, click "Next"

Note: you need to map data-type with columns properly, otherwise you lot will become conversion error.

Step v: Cheque details and click terminate

Once y'all are done with data mapping, you lot tin can verify details and click "Finish", data will be imported, with creation of table.

Click "Terminate" and in the next Screen you will meet, data has been imported successfully ( if at that place is whatsoever mistake, you volition meet mistake and can check error.), so click "Close".

You can refresh the tables of database, select tabular array, it volition prove all the data imported properly.

Since, we imported the file into our "OrderDetails" database in the "Order" table, the "OrderDetails" database should comprise a table named "Club". Go to Object Explorer-> Databases -> OrderDetails-> Order

Then, you can run the query

          Use OrderDetails Select * from Orders        

Output:

That'southward it, as you can run across in the to a higher place tabular array, we were able to import CSV file in SQL Server database table successfully.

Y'all may also similar to read:

Aggregate Functions in SQL Server (SUM, AVG, COUNT, MIN, MAX)

Mutual Tabular array Expressions (CTE) in Sql server

Check database size in Sql server ( Diverse Ways explained)

Row constructor in SQL server (Caption With case)

Understanding SQL server Coalesce() with instance (ISNULL comparing included)

halcombsamphy.blogspot.com

Source: https://qawithexperts.com/article/sql/import-csv-into-sql-server-with-query-or-without-query-using/265

Share this post