x

Get first 4 out of 5 columns from an EXCEL file.

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!

more ▼

asked Apr 21, 2010 at 04:49 PM in Default

avatar image

Joseph
21 1 1 1

is this in SSIS? or the import data wizard? or have you set up an excel sheet as a linked server?

Apr 21, 2010 at 04:58 PM Kev Riley ♦♦

He tagged it as T-sql, so I am assuming that he is looking for an answer in pure T-sql.

Apr 22, 2010 at 01:24 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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.

more ▼

answered Apr 22, 2010 at 01:22 PM

avatar image

TimothyAWiseman
15.6k 22 57 38

A little dense? Robyn and I will remember that! Well, OK, I'll admit that we'd had several cups of Kenya Peaberry coffee before we wrote it.

May 05, 2010 at 05:27 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 21, 2010 at 07:12 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 22, 2010 at 05:28 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered May 05, 2010 at 05:33 PM

avatar image

dvroman
1.1k 1 4 4

(comments are locked)
10|1200 characters needed characters left

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!

more ▼

answered May 06, 2010 at 05:26 AM

avatar image

Phil Factor
4.2k 8 27 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1096
x150

asked: Apr 21, 2010 at 04:49 PM

Seen: 2934 times

Last Updated: May 26, 2013 at 11:11 PM

Copyright 2018 Redgate Software. Privacy Policy