Solved

split datatable

Posted on 2008-06-20
7
779 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:sevensnake77
  • 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:sevensnake77
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:sevensnake77
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 53

Accepted Solution

by:
Dhaest earned 500 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:sevensnake77
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:sevensnake77
ID: 21831584
I get an error expression is not a method
0
 
LVL 9

Author Comment

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


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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now