Solved

Microsoft.Jet.OLEDB.4.0 Extended Properties

Posted on 2006-06-30
2
6,021 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now