Import dan mengolah text document bag.3

Codeng untuk form proses :

Sebelumnya tambahkan reference office dan excelnya yang versi 11

Imports System.IO
Imports System.Data
Imports System.Data.OleDb

#Region "declare"
Dim br As DataRow
Dim strsql As String
Dim mykonek As New acces.datacon
Dim insert As New acces.datainsrt
Dim urut, no, lev, master, itemmaster, desc, Qty, uom, bkolom, tkolom, purchase, labor, machine, over, extra, total As String
#End Region

memecah kemudian menyimpan ke acces--------------

#Region "proses2"
Sub proses2()
Try
objdatatable.Clear()
Dim strsql As String
strsql = "SELECT urut,Left([item],4) AS lev, Mid([item],9,14) AS itemmaster, Mid([item],33,17) AS [desc],Mid([item],19,4) AS Qty,Mid([item],24,3) AS UOM,Mid([item],50,1) AS bkolom,Mid([item],52,1) AS tkolom,Mid([item],54,12) AS purchae,Mid([item],66,13) AS labour,Mid([item],79,13) AS machine,Mid([item],92,17) AS over,Mid([item],105,13) AS extra,Mid([item],118,14) AS total from [cost]"
' 0 1 2 3 4 5 6 7 8 9 10 11 12 13
'backup
'strsql = "SELECT Left([item],4) AS lev, Mid([item],9,14) AS itemmaster, Mid([item],33,17) AS [desc],Mid([item],21,23) AS Qty,Mid([item],24,32) AS UOM,Mid([item],51,53) AS bkolom,Mid([item],53,55) AS tkolom,Mid([item],56,68) AS purchae,Mid([item],69,81) AS labour,Mid([item],82,94) AS machine,Mid([item],95,107) AS over,Mid([item],108,120) AS extra,Mid([item],123,133) AS total from [cost]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "cost")
objdatatable = objdatset.Tables("cost")
'DataGrid1.DataSource = objdatatable
mykonek.close()
Catch ex As Exception
MsgBox("kenapa ya tidak Konek" & ex.Message & vbCrLf)
End Try
Try
Dim lev, itemmaster, desc, Qty, uom, bkolom, tkolom, purchase, labor, machine, over, extra, total As String
Dim i, jum As Integer
jum = objdatatable.Rows.Count - 1
For i = 0 To objdatatable.Rows.Count - 1
With objdatatable.Rows(i)
urut = Convert.ToString(.Item(0).ToString.Trim())
lev = .Item(1).ToString.Trim
itemmaster = .Item(2).ToString
desc = .Item(3).ToString()
bkolom = .Item(6).ToString()
tkolom = .Item(7).ToString()
purchase = .Item(8).ToString()
labor = .Item(9).ToString()
machine = .Item(10).ToString()
over = .Item(11).ToString()
extra = .Item(12).ToString()
total = .Item(13).ToString()
End With
'i = i + 1
With objdatatable.Rows(i + 1)
Qty = .Item(4).ToString()
uom = .Item(5).ToString()
End With
'insert.proses2(urut, lev, itemmaster, desc, bkolom, Qty, uom, tkolom, purchase, labor, machine, over, extra, total)
insert.proses2(urut, lev, itemmaster, desc, bkolom, Qty, uom, tkolom, purchase, labor, machine, over, extra, total)
Label5.Text = "Langkah 1 sukses"
ProgressBar1.Maximum = objdatatable.Rows.Count - 1
If i <= ProgressBar1.Maximum Then
ProgressBar1.Value = i
ElseIf ProgressBar1.Maximum >= objdatatable.Rows.Count - 1 Then
ProgressBar1.Dispose()
End If
Next i
Catch ex As Exception
MsgBox("gagal konek ke database" & ex.Message & vbCrLf)
End Try
End Sub
#End Region 'langkah kedua

hapus yang tidak perlu--------------------------

#Region "hapus"
Sub hapus()
Try
'---- 0 ** 3044 901 Acco Batc Cost Leve Mult Requ S I
Dim hapus1, hapus2, hapus3, hapus4, hapus5, hapus6, hapus7, hapus8, hapus9, hapus10, hapus11 As String
hapus1 = "----"
hapus2 = "0 **"
hapus3 = "3044"
hapus4 = "901"
hapus5 = "Acco"
hapus6 = "Batc"
hapus7 = "Cost"
hapus8 = "Leve"
hapus9 = "Mult"
hapus10 = "Requ"
hapus11 = "S I"
strsql = "delete from [backcoast] where ([level])='" & hapus1 & "' or ([level])= '" & hapus2 & "' or ([level])= '" & hapus3 & "' or ([level])= '" & hapus4 & "' or ([level])= '" & hapus5 & "' or ([level])= '" & hapus6 & "' or ([level])= '" & hapus7 & "' or ([level])= '" & hapus8 & "' or ([level])= '" & hapus9 & "' or ([level])= '" & hapus10 & "' or ([level])= '" & hapus11 & "' "
'strsql = "delete from [backcoast] where ([level])='" & hapus1 & "' or ([level])= '" & hapus2 & "' or ([level])= '" & hapus7 & "' "
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objcom.ExecuteNonQuery()
'DataGrid1.DataSource = objdatatable
mykonek.close()
Label5.Text = "langkah 2 sukses"
Catch ex As Exception
MsgBox("tidak dapat menghapus" & ex.Message & vbCrLf)
End Try
End Sub
#End Region 'langkah ketiga

Insert kolom baru dengan menggunakan formula------------------------

#Region "Proses3"
Sub masterdanno()
Try
objdatatable.Clear()
Dim strsql, nos, nos1, rmaster As String
Dim i, nom As Integer
strsql = "SELECT * from [backcoast]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "backcoast")
objdatatable = objdatset.Tables("backcoast")
For i = 0 To objdatatable.Rows.Count - 1
With objdatatable.Rows(i)
urut = .Item(0).ToString
nos = .Item(1).ToString.Trim
nos1 = CInt(.Item(1).Replace(".", ""))
no = (IIf(nos1 = 0, nos1, no + 1))
rmaster = .Item(2).ToString()
nom = no.ToString
lev = .Item(1).Replace(".", "")
If nos1 = 1 Then
master = .Item(2).ToString()
ElseIf nos1 = 2 Then
master = rmaster.Insert(0, "_")
ElseIf nos1 = 3 Then
master = rmaster.Insert(0, "__")
ElseIf nos1 = 4 Then
master = rmaster.Insert(0, "___")
ElseIf nos1 = 5 Then
master = rmaster.Insert(0, "____")
Else
master = .Item(2).ToString()
End If
itemmaster = .Item(2).ToString
desc = .Item(3).ToString()
Qty = .Item(5).ToString()
uom = .Item(6).ToString()
bkolom = .Item(4).ToString()
tkolom = .Item(7).ToString()
purchase = .Item(8).ToString()
labor = .Item(9).ToString()
machine = .Item(10).ToString()
over = .Item(11).ToString()
extra = .Item(12).ToString()
total = .Item(13).ToString()
End With
insert.proses3(urut, nom, lev, master, itemmaster, desc, bkolom, Qty, uom, tkolom, purchase, labor, machine, over, extra, total)
Label5.Text = "Total record #" & objdatatable.Rows.Count - 1
ProgressBar1.Maximum = objdatatable.Rows.Count - 1
If i <= ProgressBar1.Maximum Then
ProgressBar1.Value = i
ElseIf ProgressBar1.Maximum >= objdatatable.Rows.Count - 1 Then
ProgressBar1.Dispose()
End If
Next i
mykonek.close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
#End Region 'langkah keempat

menampilkan hasil----------------------------

#Region "viewexport"
Sub viewexport()
objdatatable.Clear()
strsql = "SELECT * from [export] ORDER BY [urut]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "export")
objdatatable = objdatset.Tables("export")
DataGrid1.DataSource = objdatatable
mykonek.close()
End Sub
#End Region 'langkah kelima

Button proses diklik------------------------------

Private Sub btnproses_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnproses.Click
proses2()
hapus()
masterdanno()
viewexport()
End Sub

Button close klik------------------------------

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Close()
End Sub