Hi
Being new to VB.Net I am struggling with the following.

I want to load the contents of a text file into a datagrid (by browsing to the text file at runtime using a file dialog box).

I have been trying with System.Data.OleDB, though have read that using System.IO namespace is also possible. Any help or guidance appreciated.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim conn As New OleDb.OleDbConnection
    Dim strFileName As String

    With fileDialog
      .Title = "Import CSV file"
      .InitialDirectory = "C:\"
      .Filter = "Sage (*.xls;*.csv;*.txt)|*.xls;*.csv;*.txt|All files (*.*)|*.*"

      .ShowDialog()
      strFileName = .FileName
    End With

    Try
      conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source=C:\;" _
        & "Extended Properties=""text;" _
        & "HDR=No;" _
        & "FMT=Delimited"""

      conn.Open()

'Will pass the filename here when I get it working
      Dim adap As New OleDb.OleDbDataAdapter("select * from txtFile.txt", conn)


      Dim dstSage As New DataSet

      'adap.Fill(dstSage, "batch")

      dg.DataSource = adap.Fill(dstSage, "batch")
      dg.Refresh()

    Catch ex As Exception
      MsgBox(ex.Message)

    Finally
      conn.Close()
    End Try
  End Sub

Hey,

Connect to the file using the Extended properties as the Microsoft Excel 9.0 Library, and provide the path to the file at the data source level. You should be able to do that using the System.Data.OleDb objects.

http://www.c-sharpcorner.com/Code/20...essExcelDb.asp
http://support.microsoft.com/default...en-us%3B311731

Brian

Brian

Brian thanks for the pointers, have come up with this from the link(s) given

    Dim strFileName As String
    Dim strFilePath As String
    Dim sSlash As Single

    Try
      With fileDialog
        .Title = "Import Sage CSV file"
        .InitialDirectory = "D:\Accounts"
        .Filter = "File (*.xls;*.csv;*.txt)|*.xls;*.csv;*.txt|All files (*.*)|*.*"

        .ShowDialog()

        sSlash = InStrRev(.FileName, "\")
        strFilePath = Mid(.FileName, 1, sSlash)
        strFileName = Mid(.FileName, sSlash + 1, Len(.FileName))
      End With


      Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                  & "Data Source=" & strFilePath & ";" _
                  & "Extended Properties=""text;HDR=NO;FMT=Delimited"""

      Dim conn As New OleDb.OleDbConnection(strConnectionString)
      conn.Open() ' Open connection with the database.

      Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM [" & strFileName & "]", conn) ' Create new OleDbCommand to return data from worksheet.
      'Dim objCmdSelect As New OleDb.OleDbCommand("Select * from [Sheet1$]", conn) ' Create new OleDbCommand to return data from worksheet.

      ' Create new OleDbDataAdapter that is used to build a DataSet based on the preceding SQL SELECT statement.
      Dim objAdapter1 As New OleDb.OleDbDataAdapter
      objAdapter1.SelectCommand = objCmdSelect 'Pass the Select command to the adapter.
      Dim objDataset1 As New DataSet 'Create new DataSet to hold information from the worksheet.
      objAdapter1.Fill(objDataset1, "XLData") 'Fill the DataSet with the information from the file.


     dgImported.DataSource = objDataset1.Tables(0).DefaultView 'Build a table from the original data.

      conn.Close() 'Clean up objects.

    Catch ex As Exception
      MsgBox(ex.Message).ToString()

    End Try

Does it work?

Brian
Yes it does work. What I am trying to do now is to pass the datagrid into SQL Server 2K either as a temp table in tempdB or a permanent table to manlipulate the text (perform some aggregate calculations).

Maybe in hindsight, I should be looking for ways to pass the csv table straight into SQL Server.

My overall aim (besides completing my first .Net project) is to produce three different Excel files from the specified csv.

Regards
Dan

Hey,

Why .NET for that... why not just use DTS to input the CSV, and output the excel files. Will be easier.

Brian

Yes I like the idea using DTS, this I could do no problem with DTS. But my only snag here would be

  - I would need a front end for the user to browse to the file(already coded)
  - then I would need to pass the filepath and name into a DTS package.

I presume that I could pass the filename into a SProc that would call the DTS package with DTSRUN command??.. then make my files

If this is the case then my only dark area is how would I pass a string to SProc. The connection bit is okay

 SqlConn.ConnectionString = "workstation id=MAIN;" _
            & "packet size=4096;" _
            & "integrated security=SSPI;" _
            & "data source=main;" _
            & "persist security info=False;" _
            & "initial catalog=tempdb"

        SqlConn.Open()

...it's the bit after using the command objects that is new to me

command type = sProc and parameters, a few lines of code would be great here

Regards
Dan

Hey,

You could use Global Parameters to store the values, yes use dtsrun to run (using xp_cmdshell in a proc if need be); using a dynamic properties task to modify any needed values. The dynamic properties task is the key.

Brian

Daniel, I just wanted to let you know I appreciate your post. I have been looking for a way to load a Datagrid with a CSV file for quite a while and had tried several different things. I finally found your post with Google and it works great!
Now, if I can only create a new datatable from the loaded CSV file and export that to a new Access Database! I am getting there thanks to your post.
james

James, glad the post is helping you, though you should be thanking Brian.