The effect on the table creation was not completely as I would have expected: What did I change? I identified ProductID as being a number without any decimals, changed StandardCost and ListPrice to a currency with four decimal digits and I changed SellStartDate and SellEndDate to a custom date/time format showing both date and time. This is the result when no formatting was put on the cells in Excel.Īs an experiment I've changed the format of some fields in the Excel file and then retried the SELECT INTO statement. Here's the part of the output in which we're interested:Īs you can see, anything that looks like text will be put in a field of type nvarchar(510) and anything that looks like a number (integers, floating-point numbers, datetime values. Use the following command to describe the metadata of the temporary table:īecause a temporary table is stored in the tempdb, the sp_help command should be issued against that database. What Type Is Your Data? Let's have a look if this method of using a SELECT INTO in combination with OPENROWSET and a temporary table is smart enough to interpret the correct data types of the data coming in. With the INTO clause uncommented and the query executed, the temporary table can now be queried just like any other table: Note: if you get an error message when running the query, look further down in this article. If that's not the case, replace HDR=YES with HDR=NO. When uncommented, the statement retrieves the data from the Excel sheet and puts it into a newly-created local temporary table called #productlist.įurthermore, the query assumes that the first row contains the header. To insert the data into a table, uncomment the INTO clause. These queries are just returning the data from the Excel file into the Results window, when executed using the Management Studio. 'Excel 8.0 HDR=YES Database=C:\temp\Products.xls', 'Excel 12.0 Xml HDR=YES Database=C:\temp\Products.xlsx',įROM OPENROWSET('.4.0', The sheet containing the list of products is called ProductList.įROM OPENROWSET('.12.0', The first file is saved in the old format, Excel 97-2003, while the second file was saved from Excel 2010. This folder contains two files: Products.xls and Products.xlsx. My sample Excel files are located in C:\temp\. The oldest version which I could confirm that contains this function is SQL Server 7.0, good enough to say that any version supports it. This is a T-SQL function that can be used to access any OLE DB data source. In this article I will use the OPENROWSET() function. Using OPENROWSET() To Query Excel Files There are actually several different ways to achieve this. That's what I'm going to show you in the next paragraphs: how to import data from Excel into SQL Server. And they'll ask you to put it in a real database such as SQL Server. I took all records from the Production.Product table in the AdventureWorks 2008R2 database and dumped them in Excel.Īt some point people will realize, either because someone told them or because they lost some data due to inattentiveness, that it wasn't a really good idea to keep all that data in an Excel sheet. The following screenshot contains an example, and is also the file that I will be using in this article. Come on, you know what I'm talking about, with the first row containing the column headers followed by possibly thousands of data rows. Anyway, let's get back on track now.Īnother use, and the one that's the subject of this article, is when Excel has been used as a database. He's now putting his Photoshopped pictures in PowerPoint. :-) ) Ever since he had this specific YACI, or "Yet Another Computer Issue", because his PC wasn't powerful enough to open his 45 MB Excel file, uh, "picture collection", he took some evening classes. (Sorry dad, but I know you won't be reading this anyway. I've even seen it used as a picture album. Its main purposes are to perform calculations and create charts and pivot tables for analysis.īut people have great imagination and invent new uses for it every day. Introduction Anyone who's ever used a computer for a significant amount of time has probably come into contact with Excel, the spreadsheet application part of the Microsoft Office suite. The purpose of this article is to demonstrate how to retrieve data from an Excel sheet and put it in a table in a SQL Server database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |