I have this requirement to pull data from an excel spreadsheet and perform some transformationms and convert it to a csv file. Now the problem is The excel file has the useful data starting from some 7th row and all the initial rows have some information and metadata.
I need to query the excel spread sheet.
I have tried this query,
'Excel 8.0;Database=C:\Documents and Settings\ \Myfile0282010.xls;IMEX=1',
'SELECT * FROM [Sheet2$]')
This query doesnt work. I get an error which says " sysntax error in the FROM clause"
It gives me an error even when I try it on a sheet with data starting from the second row, and column names on the first row.
Please help. I use a data flow task and use an excel connection manager and use the option, "Sql command" on the data access mode
Appreciate your response