Solved

Microsoft.Jet.OLEDB.4.0 Extended Properties

Posted on 2006-06-30
2
6,122 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses

617 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