Copy/pasted Excel results into Angular

I was inspired by the following page “Create Insert Statements From copy/pasted Excel results”

So here are the steps.

Create a table in SQL

CREATE TABLE [dbo].[Customers](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] nvarchar NOT NULL,
[LastName] nvarchar NOT NULL,
[Company] nvarchar NOT NULL,
[Address] nvarchar NULL,
[Phone] nvarchar NULL,
[CustomerID] ASC

Create an Application and create a data source adding the table as well.

Next, create an empty page.

Add a TextArea control and call it inputarea ,then add a Button add the Text "Update Database"

On the Page Load Event add

Type “Invoke data source method”

Name getCustomers


Name $top

Value 0


Type Set property

Name CustomerData

Value ${result.value}

On the Button Event

Type Execute Code


var columns = [];
var row = {};
var j = JSON.parse('[["' + this.inputarea.value.replace(/'/g, "''").replace(/\t/g, '", "').replace(/\n/g, '"],["') + '"]]');
//'Removed leading blank row
while (j[0].join(",") == "") {
var x = j.splice(0, 1);
//Array of headers
columns = j.splice(0, 1)[0].toString().split(',');
// Loop over every row:
for (var i = 0; i < j.length; i++) {
// Ignore empty rows:
if (j[i].join(",") != "")
//create an array of data
row = j[i].toString().split(',');
//Check to see if there is a header for the field add if so add the value to this.CustomerData
if (columns.indexOf("FirstName") > -1)
this.CustomerData.FirstName = row[columns.indexOf("FirstName")];
//Do an else for required fields
if (columns.indexOf("LastName") > -1)
this.CustomerData.LastName = row[columns.indexOf("LastName")]
this.CustomerData.LastName = "none";
if (columns.indexOf("Company") > -1)
this.CustomerData.Company = row[columns.indexOf("Company")];
if (columns.indexOf("Address") > -1)
this.CustomerData.Address = row[columns.indexOf("Address")];
if (columns.indexOf("Phone") > -1)
this.CustomerData.Phone = row[columns.indexOf("Phone")];
//Insert the data into the database
this.excelImports.createCustomer(null, this.CustomerData)
.subscribe((result: any) => {
}, (result: any) => {
this.notificationService.notify({ severity: "error", summary: Error, detail: Unable to create new Customer! });
this.inputarea.value = "Complete";

Run the Application

Create a Sample Excel Spreadsheet with the following Header Rows
FirstName LastName Company Address Phone

Copy and Paste the data into the TextArea and press the button
Hopefully, there will be data in the database table.