ferguson_jerald
asked on
How can I automatically update a SQL table from Excel file?
Hello Experts.
I have a database with two tables. One of the tables was built using data from an Excel file (the tables are the same). I would like an executable file that I can schedule Windows to run at a set time each night that will replace the existing data in the SQL table with the new data from the Excel file. I don't know how to write an executable such as this, but was I'm hoping somebody else has already done something like this.
Here's what I have right now:
Excel file titled Website Contact List.xlsx
It's path is D:\DailyBatchJobs\Website Contact List.xlsx
The columns are, in order from left to right (these are the column headers also): FIRST_NAME, LAST_NAME, EMAIL, JOB_TITLE, PHONE, DIVISION_CODE, DIVISION_NAME (I have attached the file.)
The database is emailDB.mdf
The path for the database is: C:\inetpub\wwwroot\WebSite
I would like the script to replace the data in the table titled emailTbl.
The table has the following fields, in order from left to right:
id - this field is automatically incremented (primary key)
firstName
lastName
title
phoneNumber
emailAdd
divisionCode
divisionName
I'm working with SQL Express 2008.
Please let me know if I need to provide more information.
Thank you,
Jerald
Website-Contact-List.xlsx
I have a database with two tables. One of the tables was built using data from an Excel file (the tables are the same). I would like an executable file that I can schedule Windows to run at a set time each night that will replace the existing data in the SQL table with the new data from the Excel file. I don't know how to write an executable such as this, but was I'm hoping somebody else has already done something like this.
Here's what I have right now:
Excel file titled Website Contact List.xlsx
It's path is D:\DailyBatchJobs\Website Contact List.xlsx
The columns are, in order from left to right (these are the column headers also): FIRST_NAME, LAST_NAME, EMAIL, JOB_TITLE, PHONE, DIVISION_CODE, DIVISION_NAME (I have attached the file.)
The database is emailDB.mdf
The path for the database is: C:\inetpub\wwwroot\WebSite
I would like the script to replace the data in the table titled emailTbl.
The table has the following fields, in order from left to right:
id - this field is automatically incremented (primary key)
firstName
lastName
title
phoneNumber
emailAdd
divisionCode
divisionName
I'm working with SQL Express 2008.
Please let me know if I need to provide more information.
Thank you,
Jerald
Website-Contact-List.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can do that ... The below link will help you achieve it.
Basically you will need to write a script in SQL and Execute it using a batch file and also schedule the batch file .. before that you will need to choose the way to import the Excel file..Below link gives you different ways on how to import from Excel File and also how to run the script from batch file using SQL cmd
http://support.microsoft.com/kb/321686
http://www.sqlbook.com/SQL-Server/SQLCMD-command-line-utility-13.aspx
http://www.1keydata.com/sql/sqlupdate.html
http://www.1keydata.com/sql/sqlinsert.html
Basically you will need to write a script in SQL and Execute it using a batch file and also schedule the batch file .. before that you will need to choose the way to import the Excel file..Below link gives you different ways on how to import from Excel File and also how to run the script from batch file using SQL cmd
http://support.microsoft.com/kb/321686
http://www.sqlbook.com/SQL-Server/SQLCMD-command-line-utility-13.aspx
http://www.1keydata.com/sql/sqlupdate.html
http://www.1keydata.com/sql/sqlinsert.html
This would be pretty simple for an experienced programmer to do, but you will need it customized for your specific database. As for someone having it pre-made... that is unlikely, since the conection string to your database woudl need to be specified.
Ummm... hello....other experts... he specifically stated that he was using SQL Express, there is no job scheduling.... there is no server instance, read the question please.
ASKER
I can save the excel file saved as a tab delimited text file. Would that be easier to deal with?
you can do the same way for a text file, the code i posted earlier.
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\mssql7\binn\filename.x
NULL,
'Excel 5.0'
GO
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\mssql7\binn\fil
You change this for your settings and with the use of cursors or temporary tables you could simply use it on a Job. All you have to do after is put the desired file into the directory.