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,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[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
Parameters
Name $top
Value 0
Then
Type Set property
Name CustomerData
Value ${result.value}
On the Button Event
Type Execute Code
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")]
else
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.
