Create DataTable from Text or ListBox W/Comma Delimited 1

[Woofer2]

I am trying to create a DataTable with Columns and Rows from existing "Comma delimited Text/String" in either a TextBox or ListBox, which ever is faster.

Like passing the information from an Array or Collection, not looping through all Columns and Lines.

Thanks,

 

[sunaj]

It is not clear what you are trying to achive (not to me at least). Do you simply want to load the file into a textbox or do you want to parse the file into a system.Data.DataTable and then display the data?

Loading a file into a textbox:
---------------------------------------------------------
Dim SR As New IO.StreamReader("c:\YourFile.csv&quot
TextBox1.Text = SR.ReadToEnd
SR.Close()
---------------------------------------------------------

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[SqueakinSweep]

In theory this is not too difficult, but it raises a few questions. How do you know what datatype to make each columns. I can only assume you would have to make each column string by default, and make each columns a fixed length. Can you post a sample file, and your thoughts after reading the above.


Sweep
...if it works dont mess with it

 

[Woofer2]

Sweep, thanks for bringing up the issue of datatype.

I have stored the data (in a text file) as 1 record per row and each data (Column) being separated by commas like most people would do. I StreamRead this file into a TextBox and use the Split method looping through for each row and column. The Textfile format is not set in stone yet. I am in the designing/planning stage of this program and searching for all (best) options. If you are suggesting the possibility of copping the values of an Array of Integers or Strings to a Column in the DataTable.
I like that idea. It gives more flexibility.

Each column will have a fixed Datatype, Maxlength and will always have value.
X = Alphanumeric
C# = Column Name
Format for Integer “#####0”

C1 C2 C3 C4 C5 C6 C7
dd/mm/yyyy,XXX,XXX,#####0,#####0,#####0,#####0

A simple sample of the TextFile would be like:
C1,C2,C3
10/31/2003,A,001
11/1/2003,B,002
11/2/2003,C,003
11/3/2003,D,004
11/4/2003,E,005

Whats your thoughts now?

Thanks again,

 

[SqueakinSweep]

Woofer

Im not 100% sure what you are after, but the following code should give you a very good start

'DataTypes
Dim type_int As Type = Type.GetType("System.Int32")
Dim type_str As Type = Type.GetType("System.String")
Dim type_bool As Type = Type.GetType("System.Boolean")
Dim type_numeric As Type = Type.GetType("System.Double")

Dim dt As New DataTable
Dim sr As StreamReader = New StreamReader("c:\test.txt")

'Read Line 1
Dim st As String = sr.ReadLine

'Build the Datatable with the column info from Line.1
For Each sCol As String In st.Split(","c)
    dt.Columns.Add(sCol, type_str)
Next

'Populate Table - note that all DataTypes are assumed string Type
While sr.Peek > 0
    st = sr.ReadLine
    Dim scol As Object() = st.Split(","c)
    dt.Rows.Add(scol)
End While

sr.Close()

Sweep
...if it works dont mess with it

 

[SqueakinSweep]

Now further improved

'DataTypes
Dim type_int As Type = Type.GetType("System.Int32")
Dim type_str As Type = Type.GetType("System.String")
Dim type_bool As Type = Type.GetType("System.Boolean")
Dim type_numeric As Type = Type.GetType("System.Double")
Dim type_date As Type = Type.GetType("System.DateTime")

Dim dt As New DataTable

'Handling Column for Date-Types
Dim dcol As New DataColumn
dcol.DataType = type_date
dcol.DefaultValue = CDate("01/01/1900")

Dim sr As StreamReader = New StreamReader("c:\test.txt")

'Read Line 1
Dim st As String = sr.ReadLine

'Build the Datatable with the column info from Line.1
For Each sCol As String In st.Split(","c)
    Dim sType As String = UCase(Mid(sCol, 1, 1))
    Select Case sType
       Case "S" 'String
          dt.Columns.Add(sCol, type_str)
       Case "N" 'Numeric Double
          dt.Columns.Add(sCol, type_numeric)
       Case "I" 'Integer
          dt.Columns.Add(sCol, type_int)
       Case "D" 'Date
           dcol.ColumnName = scol
           dt.Columns.Add(dcol)
       Case "B" 'Boolean/Bit (0/1)
           dt.Columns.Add(sCol, type_bool)
    End Select
Next

'Populate the Data  
While sr.Peek > 0
    st = sr.ReadLine
    Dim scol As Object() = st.Split(","c)
    dt.Rows.Add(scol)
End While

sr.Close()

And here is the sample Text File I imported (TEST.TXT)
S1,I2,N3,D4
Smith,1,22.2,1/1/1900
Jones,2,3.56,31/11/1978
Bloggs,3000,5000,01/08/2000




Sweep
...if it works dont mess with it

 

[Woofer2]

How many STARS can I give?
Works like a champ!

Extreme test on 1,900 lines and 12 columns = 22,800 values…..
22 sec. - Looping through each column.add, row.add, w/ delimiter (old code).
vs.
1 sec. – (Now)

The reason why I wanted a code like that was SPEED.
Fewer loops equal faster program.
Wanted to make sure no matter how many lines required Speed is there.
AND NOW IT IS!

Thanks again,