Update From Excel

[watto]

Hi

I would like to know if i can construct appropriate SQL to update a field in one of my tables by connecting to an Excel Spreadsheet.

The original source of the spreadsheet was an export of the data from the same database and therefore i have the 'ID' of each record i wish to update.

I have found instructions that appear to work for SQlServer similar to this:

(These ones just doing a SELECT not an update)

I believe this one is attemptiing to use ADO:

SELECT *

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source=”c:ExternalMyExcel.xls”;

User ID=Admin;Password=;Extended properties=Excel 8.0',

'SELECT * FROM DataSheet$')

This one the OPENROWSET:

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:TestingDummyData.xls,

'SELECT * FROM DataSheet$')

any help will be appreciated.

[peterlaursen]

When you use the term 'field' do you mean 'column' or 'row'. I think you should provide a more detailed example (with a few rows of data before and after the process).

And also how do you plan to connect to Excel? From SQLyog import tool or 'MS Query' or what?

[watto]

'peterlaursen' wrote:

When you use the term 'field' do you mean 'column' or 'row'. I think you should provide a more detailed example (with a few rows of data before and after the process).

And also how do you plan to connect to Excel? From SQLyog import tool or 'MS Query' or what?

Hi Peter

I hope aliitle more information helps me to clarify waht i am looking for.

Here is my scenario:

My Database is called wbs_asstore

I have a table in my database called InfoStoreLog which has amny thousands of rows and approximately 60 columns

I downloaded my data from the table and have made adjustments to the values made in the 'Data Value' column on my downloaded copy which i have saved in an Excel file. I wish to update the 'Data Value' column entries on the table with the new values from my spreadsheet.

I am not very familiar with SQL and i really have no idea how to approach this.

Original Table

InfoStoreLog

ID Detail CostCode Data Amount

123 Test Record1 1548 15.82

567 Test Record20 1549 15.38

876 Test Record39 1581 15.57

359 Test Record58 1548 14.36

1357 Test Record77 1549 15.32

4689 Test Record96 2358 75.15

2743 Test Record115 2648 14.25

Excel Book: Path:C:Testing

Filename: DummyData.xls

SheetName: DataSheet

ID Detail CostCode DataAmount

123 Test Record1 1548 25.35

567 Test Record20 1549 24.69

876 Test Record39 1581 72.36

I was hoping i could do this direct from the SQLyog Query window using SQL similar to this:

Update wbs_asstore infostorelog Set infostorelog.DataAmount = DataSheet.DataAmount from

(Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:TestingDummyData.xls',

'SELECT * FROM DataSheet$'))

WHERE infostorelog.ID = DataSheet.ID

Any assistance you can give will be appreciated.

[peterlaursen]

You cannot connect to an Excel file directly from SQLyog Query Window. From here you will only connect to MySQL. SQLyog is built with the MySQL client/C-API that has no knowledge of JET or ADO or other such Microsoft database technologies . MySQL (server and client) is not designed for interaction with such Microsoft database technologies.

What you could try is to import the complete Excel sheet to a (temporary) MySQL table in your database. Next you could find a query moving what data you want from this temporary table to a 'permanent' table. To import from Excel directly to MySQL you'll need SQLyog ENTERPRISE or ULTIMATE versions – but with any version you can export to a CSV file from Excel and import using LOAD DATA syntax.

Alternatively (if you are a programmer) it is probably possible to write a .NET application doing this (but such application will use the MySQL Connector-.NET and not the C-API as SQLyog does). We cannot advise if you want to try this. We do not use .NET with MySQL – only C++, PHP, Python and LUA languages.

[watto]

'peterlaursen' wrote:

You cannot connect to an Excel file directly from SQLyog Query Window. From here you will only connect to MySQL. SQLyog is built with the MySQL client/C-API that has no knowledge of JET or ADO or other such Microsoft database technologies . MySQL (server and client) is not designed for interaction with such Microsoft database technologies.

What you could try is to import the complete Excel sheet to a (temporary) MySQL table in your database. Next you could find a query moving what data you want from this temporary table to a 'permanent' table. To import from Excel directly to MySQL you'll need SQLyog ENTERPRISE or ULTIMATE versions – but with any version you can export to a CSV file from Excel and import using LOAD DATA syntax.

Alternatively (if you are a programmer) it is probably possible to write a .NET application doing this (but such application will use the MySQL Connector-.NET and not the C-API as SQLyog does). We cannot advise if you want to try this. We do not use .NET with MySQL – only C++, PHP, Python and LUA languages.

Thanks Peter, at least i can stop trying now!

I do have the enterprise edition and i have used the import process and then an update SQL command but i was hoping i could do a little quicker without importing to a temorary table.

Youe help is apprceicated.

I do have a local copy of the database and i havebeen thinking, can i do the update from within SQLyog to update the table from my local copy?

[peterlaursen]

Well .. you cannot connect to more than a single server in a single connection. So only possible in a single SQL statement if the databases referred are on the same server and accessed in same connection.

But also the SQlyog 'import external data tool' has an option for user to specify a query to be executed on. This will bypass most of the wizard screens. This is actually something we never documented very well – so maybe we should try to do this now.

[Author]

Posts