Solved

Microsoft.Jet.OLEDB.4.0 Extended Properties

Posted on 2006-06-30
2
6,036 Views
Last Modified: 2010-10-05
I am trying to read a delimted file using this as a connection String:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""text;HDR=No;FMT=Delimited;"";"

I have setup a Schemi.ini file to specify my delimiter.

This code works perfect if I create the text file from MSAccess. If I create the file using the .NET streamWriter using UTF8Encoding the
property does not work. I have tried using UTF7Encoding and UTF32Encoding.

Question:
Is there a way to specify in the Extended properties which encoding to use? OR is there a different way to write the file that will be compatible with
the jet.oledb.4.0 connection string?
0
Comment
Question by:mre531s
2 Comments
 
LVL 34

Accepted Solution

by:
Sancler earned 350 total points
ID: 17020118
I am not aware of any Extended Properties setting that deals with encoding.  Nor so far as I know can anything be added to your Schema.ini to deal with it.  I'm not certain on either point but, if I'm correct, that just leaves the writing.  I've just tried the following code

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Dim sr As New StreamWriter("C:\Test\SRTest.txt")
        'Dim sr As New StreamWriter("C:\Test\SRTest.txt", False, System.Text.Encoding.Default)
        'Dim sr As New StreamWriter("C:\Test\SRTest.txt", False, System.Text.Encoding.UTF7)
        Dim sr As New StreamWriter("C:\Test\SRTest.txt", False, System.Text.Encoding.UTF8)
        For i As Integer = 0 To 10
            sr.WriteLine("A,B,C")
        Next
        sr.Close()
        Dim cs As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & "C:\Test" & ";" & _
        "Extended Properties=""text;HDR=No;FMT=Delimited;"";"
        Dim cn As New OleDbConnection(cs)
        Dim da As New OleDbDataAdapter("SELECT * FROM SRTest.txt", cn)
        Dim dt As New DataTable
        da.Fill(dt)
        Dim msg As String = dt.Rows.Count & vbCrLf
        msg &= dt.Rows(0)(0) & vbCrLf
        msg &= dt.Rows(0)(1) & vbCrLf
        msg &= dt.Rows(0)(2)
        MsgBox(msg)
    End Sub

All of the three lines at the beginning that are now commented out worked OK for me.  The last sr declaration - with UTF8 - which is not commented out did produce a problem.  But even that was only the addition, right at the start, of the 3 byte code which indicates the encoding and it could be stripped off the first field of the first row.  It may be (although I don't see why) that your use of variable delimiters with a Schema.ini file is having some effect that would not be apparent with a straight write and read with comma delimiters as tried here.  EVen so, can I suggest you try writing with the approach of one of the first two lines above?

The other thing you might be able to check is what encoding Access (which you say is OK) is using, and try that in your streamwriter.  I can't check at the moment but I seem to recall that some versions of Access allow the encoding to the specified.

Roger
0
 

Author Comment

by:mre531s
ID: 17020455
Thanks for the help.

I played around with the settings today and I changed the initial StreamReader
From: Dim mystreamreader As New StreamReader(CPSFile, Encoding.UTF7, False)
To:     Dim mystreamreader As New StreamReader(CPSFile, Encoding.Default, False)

Then Wrote my new .txt file using the same Encoding.Default with the StreamWriter and the OLEDB Connection worked for the new .txt file.

I was using the UTF7 because it was the first option I found that would allow me to read ALT characters such as Ç (ALT 0199) which we use for delimiters.
But I see that the Default will now work

thanks,



0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question