Home Tech Fix Best Nepal Finance

How to Generate Insert Scripts in Excel ?

👤 Diwas Poudel    🕒 11 Sep 2023    📁 TECH

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:

sample-data-for-generating-scripts
fig. Sample Data

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:

complete script concatenate in excel
fig. complete script concatenate in Excel

After pressing Enter you will get Insert Script with data as shown below

display insert script with data
fig. Insert Scripts with Data

Then click on the small rectangle at the right corner of the script as shown below:

click-on-dots-to-get-all-insert-scriptThen you will get all insert script required for the data:

generated-scripts-all-for-insert

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&");"
📧 Join our newsletter
📰 Latest Posts
📧 Join our Youtube Channel
Ourtechroom Main Logo

Ourtechroom.com is a technology-focused website that provides information and insights on various tech-related topics such as troubleshooting, emerging technology, games, gadgets, and fun facts. The website aims to offer valuable and informative content to its readers.
Contact : ourtechroom777@gmail.com