Link to home
Start Free TrialLog in
Avatar of Seven price
Seven priceFlag for United States of America

asked on

split datatable

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
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

Can you show how you get your data. Perhaps it's easier to split it on sql.
Avatar of Seven price

ASKER

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

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.
ASKER CERTIFIED SOLUTION
Avatar of Dirk Haest
Dirk Haest
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok but what if it is not 64 in the next row how will this work dynamically
I get an error expression is not a method
ImportRecommend.Parameters("@SizeMeasureID").Value = mySplit(1)


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