Is pick and choose columns from excel possible in SSIS

Question

I have to read a 450 column excel file and dump few of the column (150+) information into SQL.

I have splitted the excel into 2 parts and then merging. Is it possible to replace the excel column names in the below named range query .

SELECT  *
FROM    [Sheet1$A:GR]

Expected query:

SELECT  F1, F2, F3, F45, F78, .....
FROM    [Sheet1$]

SELECT  colName, colPlace, colAnimal, colThing, .....
FROM    [Sheet1$]

I have tried the above 2 options but this doesn't work.

Answer 1

You can specify columns names in SQL command while querying an excel file, you have to make sure that:

1. If the Excel connection is configured to read first row as header then you should use a similar approach:

   SELECT [column name 1], [column name 2] FROM [Sheet1$]
   

2. If first row doesn't contain header then you should use [1], [2], ...

You can refer to the following articles for more details:

• Writing SQL Queries against Excel files (Excel SQL)
• How to select specific columns Excel. Then saved in a database. C#
• How can i select specific columns from excel sheet in c#?
• Read Write Excel file with OLEDB in C# (without Interop)