?
Solved

split datatable

Posted on 2008-06-20
7
Medium Priority
?
796 Views
Last Modified: 2013-11-06
Yes I like to split a table within my column I have one column that i like to split 64 oz or 32 floz I like to have the
64 in column and floz or oz in another column. Now i only have one column
that the header reads sizemeasureid. this should not be a problem for you experts.

this is in vb
ScreenHunter-03-Jun.-20-09.48.gif
0
Comment
Question by:Seven price
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 21831288
Can you show how you get your data. Perhaps it's easier to split it on sql.
0
 
LVL 9

Author Comment

by:Seven price
ID: 21831339
I will post the code all the code up there so you can have a full understanding.

for the most part the datatable info is coming from excel sheet. What is happing is the format of the excel sheet had alot of null spaces so the reader was attached to have all the info fill in null spaces so it can be imported into sql database. Thanks again

For rowCt = 0 To i

            row1 = exDataTable.NewRow()
            row1("UPC") = exData(rowCt, 0)
            row1("Order") = exData(rowCt, 1)
            row1("Description") = exData(rowCt, 2)
            row1("PK") = exData(rowCt, 3)
            row1("SIZE") = exData(rowCt, 4)
            row1("SALE QTY") = exData(rowCt, 5)
            row1("SALE COST") = exData(rowCt, 6)
            row1("CASE COST") = exData(rowCt, 7)
            row1("DEAL") = exData(rowCt, 8)
            row1("DEAL COST") = exData(rowCt, 9)
            row1("UNIT QTY") = exData(rowCt, 10)
            row1("UNIT COST") = exData(rowCt, 11)
            row1("RETAIL") = exData(rowCt, 12)
            row1("PAL") = exData(rowCt, 13)
            row1("TIER") = exData(rowCt, 14)
            row1("TRIP PTS.") = exData(rowCt, 15)

            ' Be sure to add the new row to the DataRowCollection.
            exDataTable.Rows.Add(row1)

        Next rowCt

        'Dim row As DataRow
        'Dim myDataSet As DataSet
        Dim connS As New SqlConnection
        connS.ConnectionString = Application("A1SConnString")
        Dim cmd As New SqlCommand("spA1SInsertExcel", connS)
        For Each row1 In DS.Tables(0).Rows
           
            Dim UPC As String = row1("UPC").ToString()
            Dim Description As String = row1("Description").ToString()
            cmd.Parameters.Add("@Upc", SqlDbType.VarChar, 50).Value = UPC
            cmd.Parameters.Add("@Description", SqlDbType.VarChar, 75).Value = Description
            ' cmd.Parameters.Add("@SizeUnit", SqlDbType.VarChar, 75).Value = SizeUnit

            connS.Open()
            cmd.ExecuteNonQuery()

            '    ' Bind data to DataTable
            MyCommand = New System.Data.OleDb.OleDbDataAdapter(sqlStr, MyConnection)
            MyCommand.TableMappings.Add("DS", "Sales Batch")
        Next
        DataGrid1.DataSource = exDataTable
        DataGrid1.DataBind()
  Private DS As System.Data.DataSet
    Private exData(1000, 15) As String
    Private toUPC(1000)
    Private fromKCtoUPC(1000) 'KC = Krasdale Code
    Protected WithEvents Button2 As System.Web.UI.WebControls.Button
    Protected WithEvents DataGrid2 As System.Web.UI.WebControls.DataGrid
    Private exDataTable As System.Data.DataTable
#Region " Web Form Designer Generated Code "
 
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
 
    End Sub
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
 
    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object
 
    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub
 
#End Region
 
 
    Public Sub loadT1()
 
        Dim DS As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim sqlStr As String = "SELECT * FROM [100120080404$]", extendedString As String
 
        extendedString = "\Excel 8.0;HDR=Yes;IMEX=1\"";"";"
        MyConnection = New System.Data.OleDb.OleDbConnection( _
              "provider=Microsoft.Jet.OLEDB.4.0; " & _
              "data source=C:\Excel\Grocery.XLS; " & _
              "Extended Properties=Excel 8.0;")
 
        Dim command As New OleDbCommand(sqlStr, MyConnection)
        MyConnection.Open()
 
        Dim myReader As OleDbDataReader = command.ExecuteReader()
        '*************************Code begins here****************************************************
        Dim i As Integer, j As Integer, makeChange As Boolean
        i = 0 : j = 0
        While myReader.Read() 'Populate exData array with DataReader
 
            fromKCtoUPC(1000) = myReader(0) 'Read in Krasdale Code and UPC
 
            Dim l As Integer = Len(myReader(0).ToString())
            If l = 6 Then
                For j = 0 To 15
                    exData(i, j) = myReader(j).ToString()
                    If j >= 15 Then
                        i = i + 1
                    End If
                Next
 
            Else
                toUPC(i - 1) = myReader(0) 'Collect UPC
            End If
        End While
        '*************************Array is Loaded with data form 100120080404***************************
        'Begin fill in omitted areas
        '***********************************************************************************************
        'Check if these indices are populated
        Dim q As Integer, r As Integer, y As Integer
        Dim start As Integer = 0, start1 As Integer = 0
        For q = 0 To i
 
            For r = 0 To (1 - 1)
                'Condition 1
                If exData(q, 3) <> "" And exData(q, 4) <> "" And exData(q, 5) <> "" And exData(q, 6) = "" _
                And exData(q, 7) <> "" And exData(q, 8) <> "" And exData(q, 9) <> "" And exData(q, 10) <> "" _
                And exData(q, 11) = "" And exData(q, 12) <> "" Then
                    Dim a As String, b As String : a = exData(q, 5) : b = exData(q, 10)
                    For y = start To q
                        Dim x As String, z As String : x = exData(y, 4)
                        If exData(y, 3) = "" And exData(y, 4) = "" And exData(y, 5) = "" And exData(y, 6) = "" _
                        And exData(y, 7) = "" And exData(y, 8) = "" And exData(y, 9) = "" And exData(y, 10) = "" _
                        And exData(y, 11) = "" And exData(y, 12) = "" Then
 
                            exData(y, 3) = exData(q, 3)
                            exData(y, 4) = exData(q, 4)
                            exData(y, 5) = SplitFront(a)
                            exData(y, 6) = SplitBack(a)
                            exData(y, 7) = exData(q, 7)
                            exData(y, 8) = exData(q, 8)
                            exData(y, 9) = exData(q, 9)
                            exData(y, 10) = SplitFront(b)
                            exData(y, 11) = SplitBack(b)
                            exData(y, 12) = exData(q, 12)
                        End If
                    Next y
                    start = q
                    'Condition 2
                ElseIf exData(q, 3) <> "" And exData(q, 4) <> "" And exData(q, 5) = "" And exData(q, 6) = "" _
                And exData(q, 7) <> "" And exData(q, 8) <> "" And exData(q, 9) <> "" And exData(q, 10) <> "" _
                And exData(q, 11) = "" And exData(q, 12) = "" Then
                    Dim a As String, b As String : a = exData(q, 5) : b = exData(q, 10)
                    For y = start To q
                        If exData(y, 3) = "" And (exData(y, 4) = "" Or exData(y, 4) <> "") And exData(y, 5) = "" _
                        And exData(y, 7) = "" And exData(y, 8) = "" And exData(y, 9) = "" _
                        And exData(y, 10) = "" And exData(y, 12) = "" And exData(y, 6) = "" _
                        And exData(y, 11) = "" Then
                            exData(y, 3) = exData(q, 3)
                            If exData(y, 4) = "" Then
                                exData(y, 4) = exData(q, 4)
                            End If
                            exData(y, 5) = SplitFront(a)
                            exData(y, 6) = SplitBack(a)
                            exData(y, 7) = exData(q, 7)
                            exData(y, 8) = exData(q, 8)
                            exData(y, 9) = exData(q, 9)
                            exData(y, 10) = SplitFront(b)
                            exData(y, 11) = SplitBack(b)
                            exData(y, 12) = exData(q, 12)
                        End If
                    Next y
                    start = q
                End If
            Next
        Next
        '***********************************************************************************************
        'Go over data second time and fill in the blanks
        '***********************************************************************************************
        Dim count As Integer
        For q = 0 To i
            If exData(q, 5) = "" And exData(q, 6) = "" Then
                If count = 0 Then
                    start = q
                End If
                count = count + 1
            Else
                Dim a As String, b As String : a = exData(q, 5) : b = exData(q, 12)
                For y = start To q
                    If exData(y, 5) = "" And exData(y, 6) = "" And exData(y, 12) = "" Then
                        exData(y, 5) = SplitFront(a)
                        exData(y, 6) = SplitBack(a)
                        exData(y, 12) = b
                    End If
                Next y
            End If
        Next q
        '***********************************************************************************************
        'Go over data third time and fill Sale Qty and Sale Cost where applicable
        '***********************************************************************************************
        For q = 0 To i
            Dim a As String, b As String : a = exData(q, 5)
            If exData(q, 5) <> "" Then
                Dim l = InStr(exData(q, 5), "/")
                If l > 0 Then
                    exData(q, 5) = SplitFront(a)
                    exData(q, 6) = SplitBack(a)
                End If
            End If
        Next q
 
        For q = 0 To i
            Dim a As String : a = exData(q, 10)
            If exData(q, 10) <> "" Then
                Dim l = InStr(exData(q, 10), "/")
                If l > 0 Then
                    exData(q, 10) = SplitFront(a)
                    exData(q, 11) = SplitBack(a)
                End If
            End If
        Next q
        '**********************************************************************************************
        'Go over data and change Krasdale Code to UPC
        '**********************************************************************************************
        For q = 0 To i
            Try
                exData(q, 0) = toUPC(q) 'Update from Krasdale Code to UPC 
            Catch
            End Try
        Next q
 
        For q = 0 To i
            Dim l As Integer = Len(exData(q, 0))
            If l = 6 Then
                exData(q, 0) = fromKCtoUPC(q + 1)
            End If
        Next q
        '***********************************************************************************************
        myReader.Close() 'Close connection string
        '*****************************Add Rows to DataTable**********************************
        Dim rowCt As Integer, row1 As DataRow
        row1 = exDataTable.NewRow
 
        For rowCt = 0 To i
 
            row1 = exDataTable.NewRow()
            row1("UPC") = exData(rowCt, 0)
            row1("Order") = exData(rowCt, 1)
            row1("Description") = exData(rowCt, 2)
            row1("PK") = exData(rowCt, 3)
            row1("SIZE") = exData(rowCt, 4)
            row1("SALE QTY") = exData(rowCt, 5)
            row1("SALE COST") = exData(rowCt, 6)
            row1("CASE COST") = exData(rowCt, 7)
            row1("DEAL") = exData(rowCt, 8)
            row1("DEAL COST") = exData(rowCt, 9)
            row1("UNIT QTY") = exData(rowCt, 10)
            row1("UNIT COST") = exData(rowCt, 11)
            row1("RETAIL") = exData(rowCt, 12)
            row1("PAL") = exData(rowCt, 13)
            row1("TIER") = exData(rowCt, 14)
            row1("TRIP PTS.") = exData(rowCt, 15)
 
            ' Be sure to add the new row to the DataRowCollection. 
            exDataTable.Rows.Add(row1)
 
        Next rowCt
 
        'Dim row As DataRow
        'Dim myDataSet As DataSet
        Dim connS As New SqlConnection
        connS.ConnectionString = Application("A1SConnString")
        Dim cmd As New SqlCommand("spA1SInsertExcel", connS)
        For Each row1 In DS.Tables(0).Rows
           
            Dim UPC As String = row1("UPC").ToString()
            Dim Description As String = row1("Description").ToString()
            cmd.Parameters.Add("@Upc", SqlDbType.VarChar, 50).Value = UPC
            cmd.Parameters.Add("@Description", SqlDbType.VarChar, 75).Value = Description
            ' cmd.Parameters.Add("@SizeUnit", SqlDbType.VarChar, 75).Value = SizeUnit
 
            connS.Open()
            cmd.ExecuteNonQuery()
 
            '    ' Bind data to DataTable
            MyCommand = New System.Data.OleDb.OleDbDataAdapter(sqlStr, MyConnection)
            MyCommand.TableMappings.Add("DS", "Sales Batch")
        Next
        DataGrid1.DataSource = exDataTable
        DataGrid1.DataBind()
        ' Next
        'Close the connection
        MyConnection.Close()
        connS.Close()
    End Sub
 
 
    '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click, Button2.Click
 
 
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & Server.MapPath("/Store/temp/041001/Grocery.XLS") & ";" & _
             "Extended Properties=Excel 8.0;"
        Dim conn As New OleDbConnection(strConn)
        conn.Open()
        Dim OledbImportRecommend As New OleDbDataAdapter("SELECT UPC, Description FROM [Sheet1$]", conn)
        Dim myDataSet = New DataSet
 
        OledbImportRecommend.Fill(myDataSet, "Dataset1")
        DataGrid2.DataSource = myDataSet.Tables(0).DefaultView
        DataGrid2.DataBind()
 
        '##################################################################
 
        ' ****** Here saving to the Recommend table from datagrid
 
        Dim row As DataRow
 
        Dim ConnRecommendNew As New SqlConnection(Application("A1SConnString"))
        ConnRecommendNew.Open()
        For Each row In myDataSet.Tables(0).Rows
 
            Response.Write(myDataSet)
 
            Dim UPC As String = row("UPC").ToString()
 
            If UPC = UPC Then
                Response.Write(myDataSet)
            End If
            Dim Description As String = row("Description").ToString()
            'Try
 
            Dim ImportRecommend As New SqlCommand
            ImportRecommend = ConnRecommendNew.CreateCommand
            '   to use this Impoort REcoomene Command Typae 
 
 
            ImportRecommend.CommandType = CommandType.Text
            ImportRecommend.CommandText = "Insert into SalesItems(Upc,Description) Values (@Upc,@Description)"
            ImportRecommend.Parameters.Add("@Upc", SqlDbType.VarChar, 50).Value = UPC
            ImportRecommend.Parameters.Add("@Description", SqlDbType.VarChar, 50).Value = Description
 
 
            ImportRecommend.ExecuteNonQuery()
            ImportRecommend.Dispose()
 
        Next
        ConnRecommendNew.Close()
 
        conn.Close()
    End Sub
 
 
 
    '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
 
    'Auxilary Functions
    Private Function SplitFront(ByVal s As String) As String
        Dim l = InStr(s, "/")
        If l > 0 Then
            SplitFront = Mid(s, 1, l - 1)
        Else
            SplitFront = s
        End If
    End Function
    Private Function SplitBack(ByVal s As String) As String
        Dim l = InStr(s, "/")
        If l > 0 Then
            SplitBack = Mid(s, (l + 1), (Len(s) - 1))
        Else
            SplitBack = s
        End If
    End Function
 
    'EVENT HANDLERS
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        loadT1()
    End Sub
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        'exDataTable = New System.Data.DataTable
        ''Create 16 columns with string as their type
        'exDataTable.Columns.Add("UPC", String.Empty.GetType())
        'exDataTable.Columns.Add("Order", String.Empty.GetType())
        'exDataTable.Columns.Add("Description", String.Empty.GetType())
        'exDataTable.Columns.Add("PK", String.Empty.GetType())
        'exDataTable.Columns.Add("SIZE", String.Empty.GetType())
        'exDataTable.Columns.Add("Sale QTY", String.Empty.GetType())
        'exDataTable.Columns.Add("Sale Cost", String.Empty.GetType())
        'exDataTable.Columns.Add("Case Cost", String.Empty.GetType())
        'exDataTable.Columns.Add("Deal", String.Empty.GetType())
        'exDataTable.Columns.Add("Deal Cost", String.Empty.GetType())
        'exDataTable.Columns.Add("Unit QTY", String.Empty.GetType())
        'exDataTable.Columns.Add("Unit Cost", String.Empty.GetType())
        'exDataTable.Columns.Add("Retail", String.Empty.GetType())
        'exDataTable.Columns.Add("PAL", String.Empty.GetType())
        'exDataTable.Columns.Add("Tier", String.Empty.GetType())
        'exDataTable.Columns.Add("Trip Pts.", String.Empty.GetType())
 
    End Sub

Open in new window

0
 
LVL 9

Author Comment

by:Seven price
ID: 21831378
row1("SIZE") = exData(rowCt, 4)

is actually the sizemeasureID because msql will read the columns to do the insert. if you need the xls sheet
to see the results not a problem also. But as you see the sheet did not have size or sizemeasureid but the database have size and size measureid so in order to insert i have to split these 2 up.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 53

Accepted Solution

by:
Dhaest earned 1500 total points
ID: 21831441
You can easily split your string into 2 parts:
Example
dim myString as string = "64 oz"
dim mySplit() as string = myString.Split(" ")

mySplit(0) will contain "64"
mySplit(1) will contain "oz"
0
 
LVL 9

Author Comment

by:Seven price
ID: 21831530
Ok but what if it is not 64 in the next row how will this work dynamically
0
 
LVL 9

Author Comment

by:Seven price
ID: 21831584
I get an error expression is not a method
0
 
LVL 9

Author Comment

by:Seven price
ID: 21831638
ImportRecommend.Parameters("@SizeMeasureID").Value = mySplit(1)


An SqlParameter with ParameterName '@SizeMeasureID' is not contained by this SqlParameterCollection
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

649 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