[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Select first row of excel spreadsheet and import into SSIS temp table as a column

Posted on 2011-11-01
Medium Priority
Last Modified: 2012-05-12
Hi Experts,

Can someone tell me how to import the first row of an excel spreadsheet into a temp table in SSIS as a column?  For example, I have an excel spreadsheet that has 5000 rows and 50 columns.  I need to import the 50 columns into one row into a temp spreadsheet in sql.  Please help.
Question by:daintysally
  • 3
  • 3
LVL 16

Expert Comment

ID: 37070457
Just write a SQL Statement ... appending all columns with a comma delimiter and or any other delimiter Like

SELECT TOP 1 col1+','+col2+','+col3
FROM excel tablename

Also, while selecting the Path of the Excel (while creating an Excel connection), you should uncheck the option "1st row has column names" which will treat all the data in the excel file as columns including the headers

You can also use a script task and add another Column called RowNo.. which iterates as the data flows and gives you the row no.. which you can use below to divert the columns to different destinations

Author Comment

ID: 37070946
So for example; I have a table on 'Sheet1' in my excel workbook that is on my desktop.  The first row contains the column names from A to Z.  I only need to pull the column names into a temp table in the SSIS package and compare them to the column names in an existing table in sql.  Then I need to see the differences in report that can be emailed.  Is this possible, if so, how?
LVL 16

Expert Comment

ID: 37071545
Well, it is possible

As suggested above just use a query to pull all the column names and dump into your staging Table (Temp table)

Then you can use an execute SQL task to compare the columns in both the tables (use sys.columns View) and then store the values in a variable and Email the List
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 37073125
So, I would have to list each (26) column name out in the query that you posted earlier?

Author Comment

ID: 37075724
I am really a novice at this.  Can you help me further? For the query, is there not a way that I could just select 'Row 1' of the excel spreadsheet to get the column names?
LVL 16

Accepted Solution

vdr1620 earned 2000 total points
ID: 37076148
Yes you can, Well all you will need to do is create a Excel connection - source,

Select the input as SQL command and Try using

SELECT TOP 1 * FROM ExcelTAbleName

That should work,If that doesn't work for any reason

After selecting SQL command , click on Query builder and add the Excel table and select all your columns by clicking on the (*) First column in the Table and Click oK..That should do ..

rest is explained above

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question