Question Connecting to a text file from ADO.NET

[vbquestions]

Hi All,
I want to connect to a text file and load the data in the text file into a table. But I need to specify the delimiter used in the text file dynamically ( programmatically) while connecting to the file. I am using .NET(1.1) and SQL Server (2000). I have heard about schema.ini files. But that will be a static way of achieving the same. Please help me.

Thanks

 

[JohnH]

I have tried before without luck to find documentation or even a good article about this stuff, but there is some info here http://www.connectionstrings.com/?carrier=textfile. Perhaps the connection string could be used to specify additional format, the page mention registry settings, the schema.ini is also a basic text file you can generate at runtime.

 

[cjard]

I concur with john and I have used Jet drivers in text mode before to some success (better witha schema.ini), though if your file is delimited by a known character, i have written a bit of software that will transform, on-the-fly, the file so that it can be loaded into a datatable.

Basically, it's a Reader object that you attach to the file, and using regular expressions, it finds and replaces elements of the stream on the fly, a bit like sed on a unix system. I use it for transforming fixed-width files into XML that looks like a DataSet xml, and then use it with DataSet.ReadXml(myReader) method.

I wont assert it's a cinch to set up because you need to know regexes, but it's an option.

Note also that VB has a text file parser, and there are libraries around like File Helpers that can also do this