Hi I'm trying to get the first four of five columns from an Excel file.
The column names do not seem to be consistent, so I'd prefer to do it with numeric indicies.
Someone on another forum said this was impossible.
Do you know how to do this?
An alternative is to create a temporary table and select the first four columns out of that.
Does anyone konw the syntax for this?
THANKS!
Here is the SQL to select the first 4 columns from an arbitrary table:
DECLARE @tableName [sysname]
SET @tableName = '[sys].[objects]'
DECLARE @sql [nvarchar](MAX)
SELECT TOP 4 @sql = ISNULL(@sql + ', ', '') + [name] FROM [sys].[all_columns] WHERE OBJECT_ID(@tableName) = [object_id]
ORDER BY [column_id]
SET @sql = 'SELECT ' + @sql + ' FROM ' + @tableName
EXEC (@sql)
Hope that helps.
as Kev says, we need some context here, what are you using to import the data?
You reference cells in an excel worksheet using a firstcell:lastcell syntax such as A1:C3 would select a range of 9 cells.
If you want to do this in straight T-SQL you can use ODBC with OpenDataSource or OpenRowSet. http://msdn.microsoft.com/en-us/library/ms179856.aspx is an MSDN piece with the basics and http://support.microsoft.com/kb/321686 has more details. I found those two very useful when I had to do something very similar.
The write up at http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/ is also fantastic, but a little dense and best read slowly.
With all that said, T-SQL is rarely the ideal way to read an excel file, even though you can do it. I personally tend to reach for Python as the glue between excel and SQL, especially when I want to do any kind of processing in between beyond just dropping the data straight into a table. XLRD with Python is extremely powerful and easy to use.
If you just want to dump the data straight into a table, then SSIS is very good at it and for something simple like that the wizard will walk you right through it effortlessly. Powershell is also quite good at it with its ability to use the .Net libraries.
Just a little too open on the question. Need to know more about the installation that you're reading to. For instance, under SQL-2000 it's easy to do a DTS package read of an Excel spreadsheet, and under SQL-2005 DTS is SSIS.
For the Excel sheets, there needs to be some standardization. i.e. Sheet(s), Column Data and maybe some other things that I can't think of right now.
The Office Open XML standard http://en.wikipedia.org/wiki/Office_Open_XML adopted by Excel 2007 allows you to open XLS files as XML files and extract data directly in SQL Server by using XQuery. The Excel files are just zipped packages containing the XML files. You'll see each sheet in a separate XML file and if you have a decent XML Editor is is pretty clear as to how to navigate around it. The format is fairly well documented too. It is also relatively easy to use an XSLT file to insert data into Excel from SQL Server or to read data from it into a database.
I wouldn't advise this for the faint-hearted but if you have a serious data feed to or from Excel, this is surely the fastest and safest way of doing it. There isn't really enough space to explain the whole process here but there are some resources around to show you how it is done. If you pester me, I might even write an article on how to do it!
