Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Microsoft.Jet.OLEDB.4.0 Extended Properties

Posted on 2006-06-30
2
Medium Priority
?
6,263 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 1400 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

916 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