CODEWITHSUNDEEP
sql-servert-sqlssis
shorton
shorton

Reputation: 353

SQL Server Import from Excel with SQL, duplicate column names

I have a source Excel spreadsheet I import to my SQL server table. I have no control over the source. I use a SQL command in an SSIS package to import from this spreadsheet. Sample import command on the Excel Source Editor:

select 
[job number]
,[Cart]
,[Housing 1]
,[Housing No]
 from [Site Report$a2:X10000]
where region = 'South'

The data is located in columns A (Job), and columns U-W for the other pieces. The problem is there is a second "cart" field at Column M. So my query as written picks that column up as "cart" data instead of the correct, second occurrence. Any way to work around that?

Upvotes: 0

Views: 1925

Answers (1)

Thom A
Thom A

Reputation: 95544

If you have columns in your excel document with the same name, you're going to have to therefore change the file, or ignore the headers.

Personally I would speak to person that provides you the file and have them change it. I know you've said you can't, but i actually suggest you try. If not, then you'll have to do it the harder way.

This example uses a Sheet I have with the column headers in row 1, and the Import/Export wizard (which isn't great, but helps show what you need to do).

Firstly, configure your source, but untick the "First row has column names" tick box. enter image description here

Next, continue as normal, and use the option to provide a query to get the data. Select the rows only where your data is stored, and exclude the header. For example: enter image description here

Then, when you select your destination, click Edit mappings, and select each column individually that it maps to. F1 is the first column that you returned, F2 is the second, etc, etc. For my Query, that would be column A for F1, and F2 for B. If, however, your query was something like SELECT * FROM [Sheet2$F9:AQ];, then F1 would be column F, F2 columnG, etc, etc. Thus it looks like this: enter image description here

Then you can import your data as normal.

Upvotes: 4

Related Questions