Updated: 18-Aug-2010
Microsoft does not recommend or support the use of Office applications in a server-side environment (see http://support.microsoft.com/kb/257757 ). In the case of PowerPivot, the data refresh facility is implemented within an NT Service and, as such, falls under this restriction. In this article we will use the ACE provider to give you more control over importing text files. So long as the importing of data is done using the PowerPivot for Excel add-in, then you in a supported environment. However you should not be refreshing the data on the server as that uses the ACE provider in an unsupported environment.
Question: By default PowerPivot has two limitations when working with text files (using the Office ACE OLE DB provider): (1) will only import data from files with an extension of .txt, .tab, and .csv; and (2) it will only recognize tab, comma, semicolon, space, colon, and vertical bar and column delimiters. Is there a way to change PowerPivot’s configuration so that it will recognize other file extensions or column delimiters?
Answer: Yup — that is a technical term. You can use a schema.ini file in the same directory as the imported text file to specify all kinds of things that aren’t available through the PowerPivot UI, including fixed column rather than delimited text file format. Here’s a web page with the info:
http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx
(Thanks to Ron Pihlgren for this solution. Ron is the PowerPivot client test lead – and his blog is included in my blogroll.)
Enjoy.
Maybe it is just me or I am doing something wrong, how is this changing the ability to only import text files with the following extensions – .txt, .tab, and .csv? It seems like this is still a limitation, but you can control how the files are evaluated by the engine.
I get this message when I enter a file extension other than one of the ones mentioned above during the text file import process:
“The file that you attempted to import is not of a valid type. The valid file types are: .txt, .tab, and .csv.”
Is there some other location and setting that we can do to change this behavior?
Is it possible to keep the original publish date with your postings instead of changing them? This causes issues for other people that are referencing and redirecting people to your blog postings:) This issue came to my attention with this posting I did – http://denglishbi.wordpress.com/2010/08/11/powerpivot-data-refresh-with-excel-source-data/#comments and now I see it is an issue with a URL reference in Marco and Alberto’s book with http://tinyurl.com/SchemaIniPPG (page 152).
Just thought I would mention this so that people do not get 404 Page Not Found errors.
Thanks for the feedback. Sorry.
I got a better approach next time.
_-_-_ Dave
[...] Read more… [...]