Link to home
Start Free TrialLog in
Avatar of BASit Bulbulia
BASit BulbuliaFlag for South Africa

asked on

Sancler's Solution (Roger) ----> Microsoft.Jet.OLEDB.4.0 --> How do I get this engine to read my CSV files which do not have a extenstion of .txt

This was a solution given by Sancler (Roger) ; which is really fast in getting a CSV file into a datagrid ; unfortunately I have csv files that are created by a legacy program that does not have the .txt extention; what do I change in this code to make these files laod (File names are  0011.1,  0111.2  1111.2 etc). If  I use these file names the system reports read only error . . . I am using VB STUDIO (2005)

One more thing how can you get a progess bar to display . . where should the hook be?

This is the code:-

Imports System.Data.OleDb

'at the start and

    Private ds As New DataSet


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim cn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & "C:\" & ";" & _
        "Extended Properties=""Text;HDR=NO;FMT=Delimited"""

        Dim conn As New System.Data.OleDb.OleDbConnection(cn)

        Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM trial.txt", conn)

        DataGrid1.DataSource = Nothing
        Dim ticks As Long = Now.Ticks
        DataGrid1.DataSource = ds.Tables(0)
        Console.WriteLine((Now.Ticks - ticks) / TimeSpan.TicksPerMillisecond & " milliseconds")

    End Sub

Sancler's comments :-
You will also need to add a text file file called Schema.Ini to the same directory as your tab-delimited text data file.  For the purposes of this demo, the directory is C:\ and the data file is called trial.txt.  Change those values as necessary above and in the following, which is what the Schema.Ini file should contain


Just a warning.  Don't use "text" as your data file's name (for some reason, this approach doesn't seem to like that) but do use .txt as its extension.

Avatar of AlexNek

It seems that file extention must be a "txt". Just rename files in your programm before import. You can remame it back after import too.
Avatar of BASit Bulbulia


Thanks, but  that would defeat the purpose of the speed improvement  . . I have hacked the registry  ->

In this key added all the extentions i require , ,


Since I am a rookie to VB was a legacy programmer all my life; I now need to know how I can in VB2005 change this registry key . . .

as for the progress bar any ideas . . .
Ummmmm thanks but this is how I did it :-

Sub RegInfo()

        Dim regObject As Object
        regObject = GetRegValue("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text", "DisabledExtensions")
        Dim regnew As String = regObject
        If regObject IsNot Nothing Then
            If InStr(regnew, ",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,0") = 0 Then
                regnew = regnew + ",1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,0"
                SetRegValue("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text", "DisabledExtensions", regnew, Microsoft.Win32.RegistryValueKind.String)
            End If
        End If
    End Sub

    Private Function GetRegValue(ByVal regKey As String, ByVal regValue As String) As Object
        Return My.Computer.Registry.GetValue(regKey, regValue, Nothing)
    End Function

    Private Sub SetRegValue(ByVal regKey As String, ByVal regValue As String, ByVal newValue As Object, ByVal regKind As Microsoft.Win32.RegistryValueKind)
        My.Computer.Registry.SetValue(regKey, regValue, newValue, regKind)

    End Sub

Now the big 1 . . .Progress BAR . . . can I or can I not . . .

Whoever gets a way with code to insert a workable progress bar gets the POINTS
Progress of what?
I think AlexNek's idea from the link, of the repeating progress bar updated by a timer, may be the best one.  The problem otherwise is that the very process of reporting progress may slow the operation down.  But if you do want to go down the specific road, you can get the total number of records with code (based on the names in the original post) like this

        Dim cmd As New OleDbCommand
        cmd.Connection = conn
        cmd.CommandText = "SELECT COUNT(*) FROM trial.txt"
        myProgressBar.Maximum = cmd.ExecuteScalar

And then you can use the overload of the dataadapter's .Fill method which allows you to specify the start record and the number of records to transfer - see this

- to bring over the records in batches, updating the progress bar's value as you do so.

Hi RogerGenius,

I can't thank you enough (500 points is way toooo li'l) for the snippet of code that I used for this question and your reply to it; being the rookie I am and the restrictive bandwidth availability and expertise we have in Africa you GOD sent  . .

I have played with many progress bar option's and even the one Alex provided was already been used in my listview version of the CSV routine (I just used it in the ToolStrip down in the status portion of the form); so when I converted to using your datagrid option with the Jet engine the speed to load my large legacy CSV files was like 500% improvement; but again the learning curve was steep and horrid ; just trying to get a way  to use files that had extension other than txt or csv was two nights of solid work when the solution was like so simple and glaring at me.

In my question I used the word “hook” and what I meant was simply where do I get a value that would allow me to move them bars . .  I knew the ToolStripProgressBar1.Value usage but where do I get the VALUE?; you then come along give me the tools and a link . .  I begin coding away and hopeless hit walls ; cmd.Scalar just crashes in the IDE with no debug info . .

What could I be doing wrong;  I would really appreciate it (you can even charge me for this) if you could use my code and do some changes to it that would allow me to use the progress bar and also show me how I could implement partial loading of the csv file as explained in the link.

You will note I created XML for CrystalReportsViewers and I face the same problem there for the progress bar usage.

Private Sub LoadCSV(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadCSV.Click

        Call RegInfo() ‘Check if registry has the correct extensions
        crvListViewInfo2.Hide() ‘<hide the CrytalReportViewer

        If GridLines > 0 Then Exit Sub

        ToolStripProgressBar1.Minimum = 0
        ToolStripProgressBar1.Step = 20

        Dim file As String
        file = trim(str(cgroup))

        Dim fileR As String
        fileR = trim(Str(ccode))

        If Len(file) = 1 Then file = "00" + file
        If Len(file) = 2 Then file = "0" + file
        file = file + fileR + "." + company

        'file = "0011.111" -< format of the legacy files cgroup and ccode used from Public variable

        Label1.Text = " <<< --- Loading " + file


        Dim cn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" + D_Drive + "\B0\" & ";" & _
    "Extended Properties=""Text;HDR=NO;FMT=Delimited"""
        Dim conn As New System.Data.OleDb.OleDbConnection(cn)
        Dim cmd As New OleDbCommand
        cmd.Connection = conn
        cmd.CommandText = "SELECT * FROM " + file
        ToolStripProgressBar1.Maximum = cmd.ExecuteScalar ‘&#61663;--------------Crashes the proggy
        conn.Close() ` I have tried leaving this line

        Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + file, conn)
        DataGridView1.DataSource = Nothing
        Dim ticks As Long = Now.Ticks
        DataGridView1.DataSource = ds.Tables(0)
        'MsgBox((Now.Ticks - ticks) / TimeSpan.TicksPerMillisecond / 1000 & " seconds")
        Dim c As Single = (DataGridView1.Rows.Count) - 1
        GridLines = c
        Label1.Text = " Loaded  " + Str(c + 1) + " Records "

    End Sub

'Second Button to for Crystal Report Viewer

    Private Sub SendToXML (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SendToXML.Click


        If GridFlag = True Then Exit Sub

        Dim ListRpt As New rptDataGrid
        Dim row As DataRow = Nothing
        Dim DSX As New DataSet
        ' Add Table To DataSet
        'Add The Columns
        With DSX.Tables(0).Columns
            .Add("Column1", Type.GetType("System.String"))
            .Add("Column2", Type.GetType("System.String"))
            .Add("Column3", Type.GetType("System.String"))
            .Add("Column4", Type.GetType("System.Single"))
            .Add("Column5", Type.GetType("System.String"))
        End With

        Dim rowX As DataGridViewRow
        Dim rr As Single
        For Each rowX In DataGridView1.Rows
            If rr < GridLines Then
                row = DSX.Tables(0).NewRow
                row(0) = rowX.Cells(0).Value.ToString
                row(1) = rowX.Cells(1).Value.ToString
                row(2) = rowX.Cells(2).Value.ToString
                row(3) = Val(rowX.Cells(3).Value.ToString) '<<<items sold/pur
                row(4) = rowX.Cells(4).Value.ToString
                rr = rr + 1
            End If

        'Set Report Source To the Database

        'Assign the Report Source to the Crystal ReportView Control
        crvListViewInfo2.ReportSource = ListRpt

        DSX = Nothing
        GridFlag = True

    End Sub

Hi RogerGenius,

Hey maaaaaaaaaaaaaaaaaaaaaaan ; when I just begun using expert-exhange you just got your Genius CAP and you assisted me on using old legacy data (CVI CVMS sh###T) :-) . . Just wanted you to know that I finally got it to work using a Delphi DLL (ta. . .again) and now I am addicted to VB :(

The CommandText I used was

        cmd.CommandText = "SELECT COUNT(*) FROM trial.txt"

which returns a single value, so ExecuteScalar was appropriate.  What you have is

        cmd.CommandText = "SELECT * FROM " + file

That returns a recordset, so ExecuteScalar is not (or at least is less) appropriate.  I have added the "(or at least is less)" to that because I don't quite see why it should crash the program.  Rather it should only return one result (but it would be the wrong one, being the value of the first column in the first row of the recordset).  Still, try altering that: and for safety's sake relace the + with & as this is a string concatenation rather than an addition.  And, in case it does still go wrong, try putting the line in a Try Catch block so we can get more information about why

            cmd.CommandText = "SELECT * FROM " & file
        Catch ex As Exception
        End Try

There isn't a ready made "hook" for determining the progress of a dataadapter's .Fill method.  Here's one workaround

The real question I would be asking here is whether the times involved are so protracted that the user needs a "how much longer to go" progress indicator or whether an "It's OK, something is happening" progress indicator wouldbe sufficient.  What sort of time scales are we talking about?

Sorry, I put the Try Catch in the wrong place

        ToolStripProgressBar1.Maximum = cmd.ExecuteScalar ‘&#61663;--------------Crashes the proggy
    Catch ex As Exception
    End Try

Using the try catch I got the exception :-

System.InvalidCastException: Conversion from string "13-03-07" to type 'Integer' is not valid. ---> System.FormatException: Input string was not in a correct format.
   at Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat)
   at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value)
   --- End of inner exception stack trace ---
   at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(String Value)
   at Microsoft.VisualBasic.CompilerServices.Conversions.ToInteger(Object Value)
   at sender, EventArgs e) in V:\1 .Net Pos\20070320\TestSpeedCsv.vb:line 76

You were right ; it did give the 1st Value :-) and not the last as the data is a Date

The records loads between 10 seconds and even up to 4 minutes (1 million records) ; the files are large; i display a text box at the bottom stating it's loading . . and I could use the lof() command to show how big the file is but not show how many records are there in it ; it would have been great of the cmd.ExecuteScalar would work than I could like try and simulate some fake bar moving . .

The site you listed discusses the use of background worker ; the link does not work for the worker :(


Let's first make sure there's no confusion re the ExecuteScalar.   That error message is not saying that the command that we should be using will not work.  It is saying that the value which is returned is a Date and you are trying to ascribe it to an Integer (which is what ProgressBar.Maximum is).  But that is because it looks like you're still using the wrong commandtext

Here's the code it should be, in one lump rather than odd lines scattered through various posts

        Dim cmd As New OleDbCommand
        cmd.Connection = conn
        cmd.CommandText = "SELECT COUNT(*) FROM " & file
            ToolStripProgressBar1.Maximum = cmd.ExecuteScalar ‘&#61663;--------------Crashes the proggy
       Catch ex As Exception
       End Try
       conn.Close() ` I have tried leaving this line

See if that works, and then we'll investigate pseudo hooks.  But if that doesn't work, the other bit would be wasted effort.

:-) works gives the correct number of records . .

OK.  I haven't tried this myself but if you want to give it a go, it might be as fast as the dataadapter and give you the hook you are looking for.  Rather than using a dataadapter to get the data from the csv file use a DataReader.  The coding is more complex, but the behind the scenes operations are the same: a dataadapter using .Fill is actually itself, behind the scenes, using a DataReader.

The additional complexity in the coding comes from the fact that (a) you need to create your own datatable rather than the .Fill command doing it for you and (b) you need to transfer the data into that table row by row rather than (as you see with a dataadapter) it all happening in one operation.

The point of doing it this way would be that you could increment the ProgressBar's .Value by one with each record read.

I've not got time actually to code an example right now.  And won't have for a few hours yet.  If you can work it out yourself from that general description then let me know.  Otherwise, I'll see if I can put something together when I get free.

Thanks, Roger . . It would be really nice if you could put something together when ever you have some time. .  

I just had a look at the DataReader and it looks daunting . .

Avatar of Sancler

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
*hehe* I feel like a kid with a new TOY ! ! !  -- Thanks maaaaaaaaaan ; much appreciated.