Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB.NET 1.0 > VB.NET 2002/2003 Basics
| Search | Today's Posts | Mark Forums Read
VB.NET 2002/2003 Basics For coders who are new to Visual Basic, working in .NET versions 2002 or 2003 (1.0 and 1.1).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET 2002/2003 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 28th, 2004, 12:50 PM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default Loading a text file into a datagrid

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


  #2 (permalink)  
Old September 28th, 2004, 01:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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
  #3 (permalink)  
Old September 29th, 2004, 04:49 AM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


  #4 (permalink)  
Old September 29th, 2004, 09:48 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Does it work?
  #5 (permalink)  
Old September 29th, 2004, 12:47 PM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #6 (permalink)  
Old September 29th, 2004, 02:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

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

Brian
  #7 (permalink)  
Old September 29th, 2004, 03:38 PM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


  #8 (permalink)  
Old October 4th, 2004, 09:27 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Harrisburg, PA, USA.
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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
  #9 (permalink)  
Old October 21st, 2004, 04:06 PM
Registered User
 
Join Date: Oct 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


  #10 (permalink)  
Old October 22nd, 2004, 03:31 AM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
loading a html file abaso.jadhav01 ASP.NET 2.0 Basics 0 October 30th, 2006 01:46 PM
File Down Loading kvingupta Beginning VB 6 0 March 8th, 2006 07:33 AM
Loading huge text files zoostar Java GUI 0 June 13th, 2005 04:08 PM
Error loading file bmains VS.NET 2002/2003 1 September 2nd, 2004 12:52 PM
Loading a dropdown from a datagrid colum brenclarke VB.NET 2002/2003 Basics 1 May 30th, 2004 08:43 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.