November 3, 2010 at 5:25 pm
Be kind to me, this is my first post!
I am creating a SSIS package that reads in data from an Excel 2007 spreadsheet. This uses a Connection Manager to the .xlsx that has been defined as an OLEDB Connection using the provider ‘Microsoft Office 12.0 Access Database Engine OLE DB Provider’. This works fine and data can be read, except (as it appears) if there are any Charts used within the .xlsx, in which case the error ‘External Table is not in expected format’ occurs. Can anyone confirm this behaviour? Is there a fix – other than deleting the charts?
regards,
Craig
November 4, 2010 at 9:21 am
I would definitely remove the chart. I also typically convert my excel files to CSV files. If I'm getting files from business, I request that they are in a flat file (CSV or text). I've run into a number of issues when extracting data from excel and the issues mainly occur in a 64 bit environment. Using flat files eliminates these issues and I've learned they are just in general a lot easier to work with. My 2 cents.
November 4, 2010 at 3:45 pm
Thanks for your reply - I suspected as much.
November 8, 2010 at 1:26 pm
I second that as well... excel used to give me a hard time trying to import data, date figures seldom did get messed up too. txt and csv extensions are the only formats we accept for data files from clients.
November 8, 2010 at 2:18 pm
You would think that two MS products would work seamlessly together. Does anyone know whether SQL2010 handles Excel any better?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply