Import data from Excel tables into the database

This is the second time, the marketing department has asked to change the data. They want to change the database of a table similar to the price of goods such as commodity prices, need to change the place and more, we last relied on the new brother to update to go in, this time the eldest to teach them update statements, the annoying ball kicked to them. But such an update is obviously not the way, I think through Excel directly to the data into the database should be feasible, it began to find ways

I think there are at least two easy ways to achieve this:

1. Query directly with SQL statement

2, first use the data in excle to generate XML files, and then import XML into the database

The first method (try again after two), find the SQL statement that implements this feature in Books Online as follows:

select*

Fromopendatasource (' Microsoft.Jet.OLEDB.4.0 ',

' datasource= ' C:financeaccount.xls '; Userid=admin; password=; extendedproperties=excel5.0 ') ... xactions

The statement is there, but I still tried for a long time, because the parameters of the specific how to set it did not say. Data source is the path to the Excel file, which is simple; UserId, password and extended properties These three attributes I changed to a variety of computer-related user names, passwords and Excel versions are not correct, and finally use the above example of the value "User id=admin; password=; Extended Properties=excel 5.0 "was successful, Dizzy Ah, the last" Xactions "is a lot of data, in fact, just Excel file in the selected worksheet name, I am not familiar with Excel, Also note that the default Sheet1 to write [sheet1$]

Finally, look at my successful test.

The database built a table testtable_1, there are 5 field IDs, name, date, money, content,c disk Book1.xls file Sheet1 worksheet to write the corresponding data and set the data type, execute the following insert statement:

Insertintotesttable_1 ([name],[date],[money],[content])

select[name],[Date],[amount],[content]

Fromopendatasource (' Microsoft.Jet.OLEDB.4.0 ',

' datasource= ' C:book1.xls ';

Userid=admin; password=; extendedproperties=excel5.0 ') ... [sheet1$]

Column names in select I started with * instead, but found that the output order and I expected different, is "amount, content, date, name", do not know what the specific rules, and honestly wrote the name. Operation successful

Looking back at the requirements of the marketing department, suppose that in my table implementation, you can first determine the existence of the same as the record in Excel in the Name field (name to be the only non-empty) when the record is deleted, and then inserted, so simple, but the increase in the ID field will be inserted because of the change, it is not. It is possible to read all of the records in Excel first, and then analyze them with a cursor, or insert them if the name is present. OK, next time you don't have to update them on the document.