How to Enable Dark mode in dbeaver | Step-by-Step Guide ?
🕤 25 Dec 2024 Read more
Excel is a powerful tool for data analysis and visualization that is useful in our daily tasks. It appears to be useful for all types of jobs, whether you are a developer, analyst, scientist, or anyone else who uses it.
In the business world, raw data is typically provided in the form of an Excel file, with the request that you insert all the data back into the database. If there are only a few rows, there is no problem; however, if there are many rows, you must think of a way to insert the data into the database.
There are several approaches, but I'll show you how Excel can make it easier to insert data into a database. In Excel, there are two popular approaches.
Table of Contents
Using the Concatenate function in the Insert Script
The concatenate function is used to combine text from multiple cells into a single cell. If you want to combine the text from columns A2 and B2 and create a result in Column C, write the script in Column C2 as shown below:
=Concatenate(A2,B2)
Now let's look at how to use it to insert data into the database
Suppose the data present in Excel look like this:
Then your actual insert scripts for this data look like this:
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values('East','Pencil','1/6/2021',95,1.99,189.05);
Then we write the same in Excel with the help of the Concatenate function. Let's look at and implement step by step.
Note that we will focus first on the Second Row (A2, B2 ....) of the data, and later we can generate all the data.
Step 1:
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) remains as it is :
=CONCATENATE("INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values("
Step2: Insert first column data ie. The region is located at A2 Cell.
=CONCATENATE("INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(" , A2 , "," ,
Step3: Insert second column data ie. the Item is located at the B2 cell
=CONCATENATE("INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(" , A2, "," , B2 , "," ,
Step 4: Insert third column data ie. the OrderDate, which is located in the C2 cell.
=CONCATENATE("INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(", A2, "," , B2 , "," , "'" , TEXT(C2,"MM/dd/YYYY") , "'" , ",",
Step 5: Insert fourth column data ie. Units which are located in the D2 Cell.
=CONCATENATE("INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(", A2, "," , B2 , "," , "'" , TEXT(C2,"MM/dd/YYYY") , "'" , "," , D2 , "," ,
Step 6: Insert Fifth Column data ie. UnitCost which is located in E2 Cell.
=CONCATENATE("INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(", A2, "," , B2 , "," , "'" , TEXT(C2,"MM/dd/YYYY") , "'" , "," , D2 , "," , E2 , "," ,
Step 7: Insert Sixth Column data ie. The total is located at the F2 Cell
=CONCATENATE("INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(", A2, "," , B2 , "," , "'" , TEXT(C2,"MM/dd/YYYY") , "'" , "," , D2 , "," , E2 , "," , F2 ,
Step 8: We do not have data in the 7th column. So we have to close the insert with ");" as shown below.
=CONCATENATE("INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(", A2, "," , B2 , "," , "'" , TEXT(C2,"MM/dd/YYYY") , "'" , "," , D2 , "," , E2 , "," , F2 , ");"
Step 9. Close the Closed Bracket for the Concatenate function.
=CONCATENATE("INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(", A2, "," , B2 , "," , "'" , TEXT(C2,"MM/dd/YYYY") , "'" , "," , D2 , "," , E2 , "," , F2 , ");" )
So your final insert scripts for the given data look like this:
=CONCATENATE("INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(", A2, "," , B2 , "," , "'" , TEXT(C2,"MM/dd/YYYY") , "'" , "," , D2 , "," , E2 , "," , F2 , ");" )
You will write in Excel as shown below:
After pressing Enter you will get Insert Script with data as shown below
Then click on the small rectangle at the right corner of the script as shown below:
Then you will get all insert script required for the data:
Then your complete script looks like this:
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(East,Pencil,'01/06/2021',95,1.99,189.05);
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(Central,Binder,'01/23/2021',50,19.99,999.5);
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(Central,Pencil,'02/09/2021',36,4.99,179.64);
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(Central,Pen,'02/26/2021',27,19.99,539.73);
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(West,Pencil,'03/15/2021',56,2.99,167.44);
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(East,Binder,'04/01/2021',60,4.99,299.4);
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(Central,Pencil,'04/18/2021',75,1.99,149.25);
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(Central,Pencil,'05/05/2021',90,4.99,449.1);
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(West,Pencil,'05/22/2021',32,1.99,63.68);
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(East,Binder,'06/08/2021',60,8.99,539.4);
INSERT INTO Sales(Region,Item,OrderDate,Units,UnitCost,Total) Values(Central,Pencil,'06/25/2021',90,4.99,449.1);
Then copy this into your database management and then execute the scripts.
Also read : Open CSV files in Excel with the correct seperator/delimiter
Using the ampersand symbol (&) in the Insert Script
Here we use the help of the ampersand symbol (&) operator to include dynamic content.
Complete Insert Scripts using & for the given data as shown below:
Step 1:
="INSERT INTO Sales (Region,Item,OrderDate,Units,UnitCost,Total) values(
Step 2:
="INSERT INTO Sales (Region,Item,OrderDate,Units,UnitCost,Total) values("&A2&",
Step 3:
="INSERT INTO Sales (Region,Item,OrderDate,Units,UnitCost,Total) values("&A2&","&B2&",
Step 4:
="INSERT INTO Sales (Region,Item,OrderDate,Units,UnitCost,Total) values("&A2&","&B2&", '"&TEXT(C2, "mm/DD/YYYY")&"',
Step 5:
="INSERT INTO Sales (Region,Item,OrderDate,Units,UnitCost,Total) values("&A2&","&B2&", '"&TEXT(C2, "mm/DD/YYYY")&"', "&D2&",
Step 6:
="INSERT INTO Sales (Region,Item,OrderDate,Units,UnitCost,Total) values("&A2&","&B2&", '"&TEXT(C2, "mm/DD/YYYY")&"', "&D2&","&E2&",
Step 7:
="INSERT INTO Sales (Region,Item,OrderDate,Units,UnitCost,Total) values("&A2&","&B2&", '"&TEXT(C2, "mm/DD/YYYY")&"', "&D2&","&E2&","&F2&"
Step 8:
="INSERT INTO Sales (Region,Item,OrderDate,Units,UnitCost,Total) values("&A2&","&B2&", '"&TEXT(C2, "mm/DD/YYYY")&"', "&D2&","&E2&","&F2&");"
So the final Insert scripts looks like this:
="INSERT INTO Sales (Region,Item,OrderDate,Units,UnitCost,Total) values("&A2&","&B2&", '"&TEXT(C2, "mm/DD/YYYY")&"', "&D2&","&E2&","&F2&");"
-(1).jpg)
