Solved

Microsoft.Jet.OLEDB.4.0 Extended Properties

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 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

16 Experts available now in Live!

Get 1:1 Help Now