Update or Insert Into SQL table from Excel worksheet

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.

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 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

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.

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)

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

glad that the proposed solution works for you cheers