Excel Import Question

[EdwinGene]

This may be a very basic question, but up until now I have only imported and exported flat text files.

When I attempted to import a simple Excel2002 spreadsheet, the Transform Data Task excluded the first row of the spreadsheet. I checked the Excel Connection and Transform Data Task properties, but I could not find anything which would define or not define the first row as a header row.

How do I get the first row of the spreadsheet to import?

Thanks.

 

[dbomrrsm]

I always find it better to save the excel sheet as a csv or text file then import with DTS.

[blue]DBomrrsm[/blue]

 

[EdwinGene]

Thank you for letting me know how you would do it, but that doesn't answer my question.

I need to know how to correctly import an Excel spreadsheet because...

Why should a client be required to take the extra effort and save their Excel spreadsheet in csv format just because I don't know how to correctly import a spreadsheet? The client would, very justifiably, want to know why he should change his business practice just because the IT department is not competent enough to perform this task.

 

[dbomrrsm]

ok - point taken but excel is notoriously difficult to import with DTS and even if you still have to import from the spreadsheet it will mean the client changing their business practice to format the spreadsheet in a different format that is acceptible to SQL Server - the formatting in excel is - I have found - very unusual.

[blue]DBomrrsm[/blue]

 

[MDXer]

in the data pump object go to the options tab. On the options tab there is an entry for First Row set this to the row you want as your start row.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[EdwinGene]

MDXer,

No dice. I set it to 1, and it still omitted the first row.

 

[gradley]

My experience with using the Excel connection instead of a .csv is that you are required to specify a worksheet name and it is assumed that the first row of data is for column headings. I am not aware of any way around this other then (as DBomrrsm mentioned), saving as a .csv first and using a standard text file import connection.

We receive Excel spreadsheets from our clients every month but have pre-defined the format that they must use, so that it can be imported using DTS without any user interaction.

 

[EdwinGene]

gradley - I was afraid of that.

dbomrrsm - Sorry I was short with you, but in my ignorance I was still holding out hope that I could imort the first line of the spreadsheet.

Thanks to everyone who contributed.

 

[dbomrrsm]

No problem - hope you have it sorted now.

[blue]DBomrrsm[/blue]