Solved

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

Posted on 2007-03-19
18
453 Views
Last Modified: 2009-07-29
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

'and

    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)

        ds.Tables.Clear()
        DataGrid1.DataSource = Nothing
        Dim ticks As Long = Now.Ticks
        da.Fill(ds)
        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

[trial.txt]
ColNameHeader=False
Format=TabDelimited
CharacterSet=ANSI

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.

0
Comment
Question by:BASit Bulbulia
  • 8
  • 6
  • 4
18 Comments
 
LVL 16

Expert Comment

by:AlexNek
ID: 18751624
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.
0
 

Author Comment

by:BASit Bulbulia
ID: 18751784
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 . . .
0
 
LVL 16

Expert Comment

by:AlexNek
ID: 18751992
0
 

Author Comment

by:BASit Bulbulia
ID: 18752494
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
0
 
LVL 16

Expert Comment

by:AlexNek
ID: 18752589
Progress of what?
0
 
LVL 16

Expert Comment

by:AlexNek
ID: 18752600
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18753281
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
0
 

Author Comment

by:BASit Bulbulia
ID: 18755625
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


0
 

Author Comment

by:BASit Bulbulia
ID: 18755783
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
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 34

Expert Comment

by:Sancler
ID: 18756394
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
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18756962
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
0
 

Author Comment

by:BASit Bulbulia
ID: 18757066
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

0
 
LVL 34

Expert Comment

by:Sancler
ID: 18757175
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
0
 

Author Comment

by:BASit Bulbulia
ID: 18757270
:-) works gives the correct number of records . .

BASit
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18757488
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
0
 

Author Comment

by:BASit Bulbulia
ID: 18759040
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
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 18760478
Here's a demo.  One form, one DataGridView - called DataGrid1 - one Button, one ProgressBar, this code

Imports System.Data.OleDb
Public Class Form1

    Private dt As New DataTable("TestTable")
    Private file As String = "trial.txt" 'hard coded.  You will need to change

    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 OleDbConnection(cn)

        'use dataadapter (with a select statement that will return no records) to create table
        Dim da As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " & file & " WHERE 1 = 2", conn)
        da.Fill(dt)

        'get a count of the fields/columns
        Dim NumFields As Integer = dt.Columns.Count - 1

        'create a command and open the connection for it
        Dim cmd As New OleDbCommand
        cmd.Connection = conn
        conn.Open()

        'get a count of the records for the progress bar max
        cmd.CommandText = "SELECT COUNT(*) FROM " & file
        ProgressBar1.Maximum = cmd.ExecuteScalar
        Dim RecsDone As Integer = 0

        'change the command text actually to bring over the records
        cmd.CommandText = "SELECT * FROM " & file
        '... and run it
        Dim reader As OleDbDataReader = cmd.ExecuteReader
        While reader.Read
            'create a new row for the table
            Dim dr As DataRow = dt.NewRow
            'copy each value into it
            For i As Integer = 0 To NumFields
                dr(i) = reader(i)
            Next
            'save it
            dt.Rows.Add(dr)
            'update the count of records read
            RecsDone += 1
            'periodically - see Note
            If RecsDone Mod 200 = 0 Then
                '... adjust the progress bar
                ProgressBar1.Value = RecsDone
            End If
        End While

        'tidy up
        reader.Close()
        conn.Close()

        'bind the grid to the table
        DataGrid1.DataSource = dt

    End Sub

End Class

As I surmised, the process of updating the progress bar itself adds to the overall time for the datatransfer.  On my tests, with the setting of 200 for how often the ProgressBar was updated, the increase was ~50% on a straight dataadapter transfer.  The majority of that seems to come from using the datareader rather than the dataadapter, and the rest from the actual updating of the ProgressBar.  The latter can be fine-tuned by changing the figure of 200 in

            If RecsDone Mod 200 = 0 Then

A higher figure means less updates so less extra time, a lower figure more updates so more extra time.

Roger
0
 

Author Comment

by:BASit Bulbulia
ID: 18762843
*hehe* I feel like a kid with a new TOY ! ! !  -- Thanks maaaaaaaaaan ; much appreciated.

BASit
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

13 Experts available now in Live!

Get 1:1 Help Now