Update or Insert Into SQL table from Excel worksheet

Hi Experts;

In Delphi 7 I can display an Excel worksheet named "Spreadsheet$" in a DBGrid component using an ADOConnection component where the OLE Provider is Microsoft Jet 4.0, the Extended Properties value is "Excel 8.0" and the ADO query is: "Select * FROM [Spreadsheet$]".

Rather than displaying the Excel Spreadsheet$ data I need a query that will either UPDATE or INSERT INTO an SQL table named "Spreadsheet" using the Excel "Spreadsheet$" worksheet in a DB named SRSPRM2 which is connected with a second Delphi ADOConnection component.

I tried the following but it doesn't work...

IF EXISTS (SELECT Spreadsheet.Model FROM [Spreadsheet$])
UPDATE Spreadsheet
SET
Spreadsheet.ItemNo=ItemNo,
Spreadsheet.Mfr=Mfr,
Spreadsheet.Model=Model,
Spreadsheet.Qty=Qty,
Spreadsheet.Unit=Unit,
Spreadsheet.Category=Category,
Spreadsheet.Spec=Spec,
Spreadsheet.Markup=Markup,
Spreadsheet.Sell=Sell,
Spreadsheet.SellTotal=SellTotal,
Spreadsheet.List=List,
Spreadsheet.Discount=Discount,
Spreadsheet.Net=Net,
Spreadsheet.Weight=Weight,
Spreadsheet.Net Freight=Net Freight,
Spreadsheet.Net Install=Net Install,
Spreadsheet.Class=Class,
Spreadsheet.CaseCube=CaseCube,
Spreadsheet.Notes=Notes,
Spreadsheet.Taxable=Taxable,
Spreadsheet.Special=Special
ELSE
INSERT INTO Spreadsheet
(OrderNo,ItemNo,Mfr,Model,Qty,Unit,Category,Spec,Markup,Sell,SellTotal,
List,Discount,Net,Weight,Net Freight,Net Install,Class,CaseCube,Notes,
Taxable,Units Per Case,Special
)
SELECT
OrderNo,ItemNo,Mfr,Model,Qty,Unit,Category,Spec,Markup,Sell,SellTotal,
List,Discount,Net,Weight,Net Freight,Net Install,Class,CaseCube,Notes,
Taxable,Units Per Case,Special
FROM
(SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\MSRMS\AQ_Test.xlsx;HDR=YES',
'SELECT * FROM[Spreadsheet$]')) SRSPRM2
WHERE
Model=Model

Help with the query structure, method or syntax will be greatly appreciated.

Thank you,
Visionetv
visionetvAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
first, I think we need to know what's the logic to determine whether a record should be inserted or updated?

I'm not a Delphi developer but I guess you can try first import all your records in Excel into a temporary table, and then from there, based on your ETL (Extract, Transform, Load) process logic, then import the records into the real table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
visionetvAuthor Commented:
Hi Ryan,

The SQL table I'm trying to populate from Excel represents a series of commercial appliance orders that sales personnel 'build' in a Web interface based on customer requests and then export to Excel. The orders can be 'works in progress' over several weeks hence insert vs. update.

I can use the DB Grid or a temporary table to collect the Excel data and then save the data to the SQL table but I was trying to skip the step. Another approach would be to 'copy' the Excel data directly to the SQL table when the Excel sheet is first submitted and then direct changes to the orders through the DB table instead of generating another Excel export when the order is changed.

I know you can copy Excel to a SQL DB using the MS Server Management Studio I need to perform the same operation but with a query inside my program that can append data from  Excel worksheets.
visionetvAuthor Commented:
I have tried to copy the Excel file into a temp_table so it can saved into a table in the DB using the following;

SELECT * --INTO #AQoutes
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=YES;Database=C:\MSRMS\AQ_Test\AQTest1A.xlsx',
'SELECT * FROM [Spreadsheet$]');

The query keeps executing without returning a result. I also ran;
master.dbo.xp_cmdshell 'dir C:\MSRMS\AQ_Test|AQTest1A.xlsx'
to make sure the server sees the file, which it does.

I've tried saving the Excel file in the Delphi DBGrid to an SQL Table using the following ADOQuery

Declare @OrderNo as varchar(20) =:OrderNo
Declare @ItemNo as Int =:ItemNo
Declare @Mfr as varchar(100) =:Mfr
Declare @Model as varchar(20) =:Model
Declare @Qty as Int =:Qty
Declare @Unit as varchar(12) =:Unit
Declare @Category as varchar(50) =:Category
Declare @Spec as varchar(600) =:Spec
Declare @Markup as varchar(10) =:Markup
Declare @Sell as varchar(20) =:Sell
Declare @SellTotal as varchar(20) =:SellTotal
Declare @List as varchar(20) =:List
Declare @Discount as varchar(20) =:Discount
Declare @Net asvarchar(20) =:Net
Declare @Weight as varchar(20) =:Weight
Declare @NetFreight as varchar(20) =:NetFreight
Declare @NetInstall as varchar(20) =:NetInstall
Declare @Class as varchar(20) =:Class
Declare @CaseCube as varchar(20) =:CaseCube
Declare @Notes as varchar(255) =:Notes
Declare @Taxable as varchar(10) =:Taxable
Declare @UnitsPerCase as int =:UnitsPerCase
Declare @Special as varchar(255) =:Special

INSERT INTO Spreadsheet
(OrderNo,ItemNo,Mfr,Model,Qty,Unit,Category,Spec,Markup,Sell,SellTotal,
List,Discount,Net,Weight,NetFreight,NetInstall,Class,CaseCube,Notes,
Taxable,UnitsPerCase,Special)

VALUES(@OrderNo,@ItemNo,@Mfr,@Model,@Qty,@Unit,@Category,
@Spec,@Markup,@Sell,@SellTotal,@List,@Discount,@Net,@Weight,
@NetFreight,@NetInstall,@Class,@CaseCube,@Notes,@Taxable,
@UnitsPerCase,@Special);which executes but doesn't write any data to the table.

with an ADODataSet using the following Delphi code to iterate through the Excel data rows;

procedure TForm1.Button2Click(Sender: TObject);
begin
with ADODataSet1 do
begin
Active := True;
DisableControls;
try
First;
while not Eof do
begin
ADOSaveAQ.Parameters.ParamByName('ItemNo').Value :=DBGrid1.Fields[0].AsInteger;
ADOSaveAQ.Parameters.ParamByName('Mfr').Value :=DBGrid1.Fields[1].AsString;
ADOSaveAQ.Parameters.ParamByName('Model').Value :=DBGrid1.Fields[2].AsString;
ADOSaveAQ.Parameters.ParamByName('Qty').Value :=DBGrid1.Fields[3].AsInteger;
ADOSaveAQ.Parameters.ParamByName('Unit').Value :=DBGrid1.Fields[4].AsString;
ADOSaveAQ.Parameters.ParamByName('Category').Value :=DBGrid1.Fields[5].AsString;
ADOSaveAQ.Parameters.ParamByName('Spec').Value :=DBGrid1.Fields[6].AsString;
ADOSaveAQ.Parameters.ParamByName('Markup').Value :=DBGrid1.Fields[7].AsString;
ADOSaveAQ.Parameters.ParamByName('Sell').Value :=DBGrid1.Fields[8].AsString;
ADOSaveAQ.Parameters.ParamByName('SellTotal').Value :=DBGrid1.Fields[9].AsString;
ADOSaveAQ.Parameters.ParamByName('List').Value :=DBGrid1.Fields[10].AsString;
ADOSaveAQ.Parameters.ParamByName('Discount').Value :=DBGrid1.Fields[11].AsString;
ADOSaveAQ.Parameters.ParamByName('Net').Value :=DBGrid1.Fields[12].AsString;
ADOSaveAQ.Parameters.ParamByName('Weight').Value :=DBGrid1.Fields[13].AsString;
ADOSaveAQ.Parameters.ParamByName('NetFreight').Value :=DBGrid1.Fields[14].AsString;
ADOSaveAQ.Parameters.ParamByName('NetInstall').Value :=DBGrid1.Fields[15].AsString;
ADOSaveAQ.Parameters.ParamByName('Class').Value :=DBGrid1.Fields[16].AsString;
ADOSaveAQ.Parameters.ParamByName('CaseCube').Value :=DBGrid1.Fields[17].AsString;
ADOSaveAQ.Parameters.ParamByName('Notes').Value :=DBGrid1.Fields[18].AsString;
ADOSaveAQ.Parameters.ParamByName('Taxable').Value :=DBGrid1.Fields[19].AsString;
ADOSaveAQ.Parameters.ParamByName('UnitsPerCase').Value :=DBGrid1.Fields[20].AsInteger;
ADOSaveAQ.Parameters.ParamByName('Special').Value :=DBGrid1.Fields[21].AsString;
ADOSaveAQ.ExecSQL;
Next;
end;
Finally
EnableControls;
end;
end;
end;

The procedure executes but doesn't write any data to the SQL table.

Any suggestions?

Visionetv
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>The procedure executes but doesn't write any data to the SQL table.
there could be some error when you trying to execute the line:

ADOSaveAQ.ExecSQL;

?

you may try to catch if there's any exceptions in this
visionetvAuthor Commented:
I have the query writing the first row of the Delphi DBGrid to the database, working on having the query run an iteration through the rows until the end of the RecordCount. I should be using the DataSet rather than the DBGrid but I can't seem to get it to work.

I also can't get the 'Microsoft.ACE.OLEDB.12.0 OPENROWSET function to work with a #temp table either; the latter being the preferable method for writing the Excel data into the DB table.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
do you think can do line debugging in Delphi and detect any errors to see how the records are not being written ? (sorry as I'm totally zero experience in Delphi IDE)
visionetvAuthor Commented:
Hi Ryan;

Thank you for the help by pointing me to the use of a temporary table, while not in the conventional sense, the concept solved the issues and I have the queries working properly. I will award the points to you.

By using the underlying dataset for the Delphi DBGrid, ADOQuery1 retrieves the Excel data and essentially acts as a temporary table while the DBGrid displays the data and provides the parameters for ADOQuery2. ADOQuery2 loops through the records from ADOQuery1 and writes them to the SQL table. Here is the code and queries if someone else is subsequently interested:

ADO Query1: (retrieves Excel data rows)
Select * FROM [Spreadsheet$]
WHERE
Taxable <> ''
__________________________

ADO Query1: (writes Excel data to SQL Table)
Declare @ItemNo as Int=:ItemNo
Declare @Mfr as varchar(100)=:Mfr
Declare @Model as varchar(20)=:Model
Declare @Qty as Int=:Qty
Declare @Unit as varchar(12)=:Unit
Declare @Category as varchar(50)=:Category
Declare @Spec as varchar(600)=:Spec
Declare @Markup as varchar(10)=:Markup
Declare @Sell as varchar(20)=:Sell
Declare @SellTotal as varchar(20)=:SellTotal
Declare @List as varchar(20)=:List
Declare @Discount as varchar(20)=:Discount
Declare @Net as varchar(20)=:Net
Declare @Weight as varchar(20)=:Weight
Declare @NetFreight as varchar(20)=:NetFreight
Declare @NetInstall as varchar(20)=:NetInstall
Declare @Class as varchar(20)=:Class
Declare @CaseCube as varchar(20)=:CaseCube
Declare @Notes as varchar(255)=:Notes
Declare @Taxable as varchar(10)=:Taxable
Declare @UnitsPerCase as int=:UnitsPerCase
Declare @Special as varchar(255)=:Special

INSERT INTO SRSaqOrders
(ItemNo,Mfr,Model,Qty,Unit,Category,Spec,Markup,Sell,SellTotal,
List,Discount,Net,Weight,NetFreight,NetInstall,Class,CaseCube,Notes,
Taxable,UnitsPerCase,Special)

VALUES(@ItemNo,@Mfr,@Model,@Qty,@Unit,@Category,
@Spec,@Markup,@Sell,@SellTotal,@List,@Discount,@Net,@Weight,
@NetFreight,@NetInstall,@Class,@CaseCube,@Notes,@Taxable,
@UnitsPerCase,@Special);
____________________________________

Delphi Code:
procedure TForm1.Button1Click(Sender: TObject);
begin
ADOQuery1.Open;
Label1.Caption := IntToStr(ADOQuery1.RecordCount);
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
while not ADOQuery1.EOF do begin
ADOSaveAQ.Parameters.ParamByName('ItemNo').Value :=DBGrid1.Fields[0].AsString;
ADOSaveAQ.Parameters.ParamByName('Mfr').Value :=DBGrid1.Fields[1].AsString;
ADOSaveAQ.Parameters.ParamByName('Model').Value :=DBGrid1.Fields[2].AsString;
ADOSaveAQ.Parameters.ParamByName('Qty').Value :=DBGrid1.Fields[3].AsString;
ADOSaveAQ.Parameters.ParamByName('Unit').Value :=DBGrid1.Fields[4].AsString;
ADOSaveAQ.Parameters.ParamByName('Category').Value :=DBGrid1.Fields[5].AsString;
ADOSaveAQ.Parameters.ParamByName('Spec').Value :=DBGrid1.Fields[6].AsString;
ADOSaveAQ.Parameters.ParamByName('Markup').Value :=DBGrid1.Fields[7].AsString;
ADOSaveAQ.Parameters.ParamByName('Sell').Value :=DBGrid1.Fields[8].AsString;
ADOSaveAQ.Parameters.ParamByName('SellTotal').Value :=DBGrid1.Fields[9].AsString;
ADOSaveAQ.Parameters.ParamByName('List').Value :=DBGrid1.Fields[10].AsString;
ADOSaveAQ.Parameters.ParamByName('Discount').Value :=DBGrid1.Fields[11].AsString;
ADOSaveAQ.Parameters.ParamByName('Net').Value :=DBGrid1.Fields[12].AsString;
ADOSaveAQ.Parameters.ParamByName('Weight').Value :=DBGrid1.Fields[13].AsString;
ADOSaveAQ.Parameters.ParamByName('NetFreight').Value :=DBGrid1.Fields[14].AsString;
ADOSaveAQ.Parameters.ParamByName('NetInstall').Value :=DBGrid1.Fields[15].AsString;
ADOSaveAQ.Parameters.ParamByName('Class').Value :=DBGrid1.Fields[16].AsString;
ADOSaveAQ.Parameters.ParamByName('CaseCube').Value :=DBGrid1.Fields[17].AsString;
ADOSaveAQ.Parameters.ParamByName('Notes').Value :=DBGrid1.Fields[18].AsString;
ADOSaveAQ.Parameters.ParamByName('Taxable').Value :=DBGrid1.Fields[19].AsString;
ADOSaveAQ.Parameters.ParamByName('UnitsPerCase').Value :=DBGrid1.Fields[20].AsString;
ADOSaveAQ.Parameters.ParamByName('Special').Value :=DBGrid1.Fields[21].AsString;
ADOQuery1.Next;
ADOSaveAQ.ExecSQL;
end;

Thank you,
Visionetv
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
glad that the proposed solution works for you cheers
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.