Seven price
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
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
Can you show how you get your data. Perhaps it's easier to split it on sql.
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("spA1SInsertExc el", connS)
For Each row1 In DS.Tables(0).Rows
Dim UPC As String = row1("UPC").ToString()
Dim Description As String = row1("Description").ToStri ng()
cmd.Parameters.Add("@Upc", SqlDbType.VarChar, 50).Value = UPC
cmd.Parameters.Add("@Descr iption", SqlDbType.VarChar, 75).Value = Description
' cmd.Parameters.Add("@SizeU nit", SqlDbType.VarChar, 75).Value = SizeUnit
connS.Open()
cmd.ExecuteNonQuery()
' ' Bind data to DataTable
MyCommand = New System.Data.OleDb.OleDbDat aAdapter(s qlStr, MyConnection)
MyCommand.TableMappings.Ad d("DS", "Sales Batch")
Next
DataGrid1.DataSource = exDataTable
DataGrid1.DataBind()
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("spA1SInsertExc
For Each row1 In DS.Tables(0).Rows
Dim UPC As String = row1("UPC").ToString()
Dim Description As String = row1("Description").ToStri
cmd.Parameters.Add("@Upc",
cmd.Parameters.Add("@Descr
' cmd.Parameters.Add("@SizeU
connS.Open()
cmd.ExecuteNonQuery()
' ' Bind data to DataTable
MyCommand = New System.Data.OleDb.OleDbDat
MyCommand.TableMappings.Ad
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
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok but what if it is not 64 in the next row how will this work dynamically
ASKER
I get an error expression is not a method
ASKER
ImportRecommend.Parameters ("@SizeMea sureID").V alue = mySplit(1)
An SqlParameter with ParameterName '@SizeMeasureID' is not contained by this SqlParameterCollection
An SqlParameter with ParameterName '@SizeMeasureID' is not contained by this SqlParameterCollection