Thursday, October 20, 2011

Much ADO About Text Files

I've recently been trying to automate Excel and Access from a web service so that I could take three text files and insert them into an Excel Spreadsheet and then from there insert them as tables in an Access Database. This has worked well running with Office 2007 and Windows XP using Video Studio 2010, but when I try it on a web Server IIS 7.0 and Windows Server 2008 (64-bit) it faceplants with no errors meaning nothing happens with the spreadsheet or database. While it's been stated several times that such a server is not designed to automate Office programs I decided to do it programmatically using ADO.Net in the web service. The problem is that I wasn't sure how to query a text file that wasn't comma or tab delimited. Fortunately, the textfiles do have column headers. I discovered the secret was to make sure the format "Fixedlength" is clearly stated and to add a Schema.ini file. In my case, all I need is to put the following in the schema for each text file:

[File.txt] Format=Delimited( ) 
Where File.txt is the name of the file. The following article tells you everything you need to know!

Much ADO About Text Files
Enhanced by Zemanta

No comments: