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

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.

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 , ,

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\DisabledExtension
s

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 . . .

do you know this way?
http://vbcity.com/forums/faq.asp?fid=30&cat=Registry

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
            'MsgBox(regnew)
            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?

http://www.vbdotnetheaven.com/UploadFile/mahesh/ProgressBar04262005051817AM/ProgressBar.aspx

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"
        conn.Open()
        myProgressBar.Maximum = cmd.ExecuteScalar
        conn.Close()

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

http://msdn2.microsoft.com/en-us/library/kxs7kbfe.aspx

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

Roger

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

        Timer1.Start()
        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

        Application.DoEvents()

        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
        conn.Open()
        ToolStripProgressBar1.Maximum = cmd.ExecuteScalar ‘&#61663;--------------Crashes the proggy
        MsgBox(ToolStripProgressBar1.Maximum)
        conn.Close() ` I have tried leaving this line

        Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + file, conn)
        ds.Tables.Clear()
        DataGridView1.DataSource = Nothing
        Dim ticks As Long = Now.Ticks
        da.Fill(ds)
        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 "
        'MsgBox(c)
        Timer1.Stop()

    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
        '' INSERT TABLE FOR XML

        crvListViewInfo2.Show()

        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
        DSX.Tables.Add("DataGridInfo")
        '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
                DSX.Tables(0).Rows.Add(row)
                rr = rr + 1
            End If
        Next

        'Set Report Source To the Database
        ListRpt.SetDataSource(DSX)

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

        'DISPOSE
        DSX.Dispose()
        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 :(

BASit

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

        Try
            cmd.CommandText = "SELECT * FROM " & file
        Catch ex As Exception
            Debug.WriteLine(ex.ToString)
        End Try

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

http://www.thescripts.com/forum/thread595561.html

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?

Roger

Sorry, I put the Try Catch in the wrong place

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

Roger

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 pos.net.TestSpeedCsv.Button1_Click(Object 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 :(

Thanks.
BASit

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
        conn.Open()
        Try
            ToolStripProgressBar1.Maximum = cmd.ExecuteScalar ‘&#61663;--------------Crashes the proggy
            MsgBox(ToolStripProgressBar1.Maximum)
       Catch ex As Exception
            Debug.WriteLine(ex.ToString)
       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.

Roger

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

BASit

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.

Roger

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 . .

BASit

*hehe* I feel like a kid with a new TOY ! ! !  -- Thanks maaaaaaaaaan ; much appreciated.

BASit