Witam,
stanąłem przed problemem importu plików Excel do SQL.
Problem polega na tym, że plik nie ma ściśle określonej struktury - ważne jest aby plik zawierał kolumny o określonych nazwach.
Import do bazy zawiera warunki:
- Wystepowanie rekordu
- Status z rekordu
Np. Jeśli rekord istnieje, to sprawdzam, czy jego status jest taki sam jak aktualny status w bazie, jeśli nie to dodaję nowy status do tabeli statusów.
Temat obecnie rozwiązałem następująco (bez SQL).
Private Sub import()
Try
SQLCmd.Connection = SQLConn
SQLConn.Open()
Catch ex As SqlException
MsgBox(ex.Message)
SQLConn.Close()
End Try
If Not importfilepath = "" Then
import_status.Text = "IMPORTOWANIE.........................."
Dim csvPath As String = importfilepath
csvPath = importfilepath
Dim rowc = 0
Dim line = ""
Dim lineset = ""
Dim cellnr As Integer = 0
Dim cellupdatenumber As Integer = 0
Dim columns() As String = {"ID zlecenia", "Projekt", "Numer zlecenia", "Typ zlecenia", "Status", "Konieczność rekrutacji", "Wykonawca", "Wystawiający", "Akceptujący", "Przełożony akceptującego", "Data utworzenia", "Data akceptacji / odrzucenia", "Data wpłynięcia", "Data wykonania", "Data rozliczenia", "Kwota zlecenia", "Identyfikator zewnętrzny klienta", "Opis"}
Dim dbidcolumns As Integer = 1
Dim type As Integer = -1
Dim updatevalue As String = ""
Dim updatecount As Integer = 0
Dim insertcount As Integer = 0
Dim woupdate As Integer = 0
Dim tablename As String = "PromotionsTEST"
Dim diffcounter As Integer = 0
Dim GUID As String = ""
Dim projid As String = ""
Dim signature As String = ""
Dim status As String = ""
Dim executor As String = ""
Dim acceptdate As String = ""
Dim importdate As String = ""
Dim creationdate As String = ""
Dim executiondate As String = ""
Dim closeddate As String = ""
Dim value As String = "0"
Dim hce As String = ""
Dim comm As String = ""
Dim author As String = ""
Dim typepromotion As String = ""
Dim recruitment As String = ""
Dim acceptor As String = ""
Dim acceptor_boss As String = ""
Dim starttime As Date = Now
Dim endtime As Date
Dim goalvalue As Integer = 0
Dim progressbar As Integer = 0
Dim finalcmdstring As String = ""
Dim worksheetname = "test"
Dim oApp As New Excel.Application
Dim oWBa As Excel.Workbook = oApp.Workbooks.Open(importfilepath)
Dim oWS As Excel.Worksheet
Dim lRow
Dim columnsid(columns.Length) As Integer
Dim colmax = 0
Dim cell As String = ""
Try
oWS = DirectCast(oWBa.Worksheets(worksheetname),
Excel.Worksheet)
Catch e As Exception
import_status.Text = ("Brak arkusza o nazwie: " & worksheetname)
Exit Sub
End Try
Try
oApp.Visible = False
With oWS
'~~> Check if there is any data in the sheet
If oApp.WorksheetFunction.CountA(.Cells) <> 0 Then
lRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
LookAt:=Excel.XlLookAt.xlPart, _
LookIn:=Excel.XlFindLookIn.xlFormulas, _
SearchOrder:=Excel.XlSearchOrder.xlByRows, _
SearchDirection:=Excel.XlSearchDirection.xlPrevious, _
MatchCase:=False).Row
Else
lRow = 1
End If
goalvalue = lRow - 1
import_status.Text = import_status.Text & vbNewLine & "Kolumny poprawne"
For i = 1 To 40
For j = 0 To columns.Length - 1
If Equals((oWS.Cells(1, i).Value), columns(j)) Then
columnsid(j) = i
colmax = i
End If
Next j
Next i
For k = 0 To columns.Length - 1
If columnsid(k) = Nothing Then
import_status.Text = ("Nie znaleziono kolumny : " & columns(k) & " W Arkuszu: " & worksheetname)
Exit Sub
End If
Next k
For x = 2 To lRow
progressbar += 1
progressset(progressbar, goalvalue)
''For z = 0 To columns.Length - 1
cell = (oWS.Cells(x, columnsid(0)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
GUID = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(1)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
If cell = "NPGŻ SP" Then
projid = "'" & 1 & "'"
Else
projid = "'" & cell & "'"
End If
cell = (oWS.Cells(x, columnsid(2)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
signature = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(3)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
typepromotion = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(4)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
status = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(5)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
recruitment = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(6)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
author = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(7)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
acceptor = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(8)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
acceptor_boss = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(9)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
executor = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(10)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
Try
Dim tempdate As Date = cell
cell = tempdate.ToString("yyyyMMdd HH:mm:ss")
cell = "'" & cell & "'"
Catch err As Exception
cell = "NULL"
End Try
creationdate = cell
cell = (oWS.Cells(x, columnsid(11)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
Try
Dim tempdate As Date = cell
cell = tempdate.ToString("yyyyMMdd HH:mm:ss")
cell = "'" & cell & "'"
Catch err As Exception
MsgBox("Creation date " & err.Message)
cell = "NULL"
End Try
acceptdate = cell
cell = (oWS.Cells(x, columnsid(12)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
Try
Dim tempdate As Date = cell
cell = tempdate.ToString("yyyyMMdd HH:mm:ss")
cell = "'" & cell & "'"
Catch err As Exception
cell = "NULL"
End Try
executiondate = cell
cell = (oWS.Cells(x, columnsid(13)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
Try
Dim tempdate As Date = cell
cell = tempdate.ToString("yyyyMMdd HH:mm:ss")
cell = "'" & cell & "'"
Catch err As Exception
cell = "NULL"
End Try
closeddate = cell
cell = (oWS.Cells(x, columnsid(14)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
cell = cell.Replace(",", ".")
value = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(15)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
hce = "'" & cell & "'"
cell = (oWS.Cells(x, columnsid(16)).Value)
cell = cell.Replace(vbCrLf, "").Replace(vbCr, "").Replace(vbLf, "").Replace("""", "")
comm = "'" & cell & "'"
insertcount += 1
Next x
End With
endtime = Now
import_status.Text = "Zaimportowano import: " & importfilepath & vbNewLine & "Zaktualizowano dane tabeli: " & tablename & vbNewLine & "Nowe rekordy:" & insertcount & vbNewLine & "Zaktualizowane rekordy:" & updatecount & vbNewLine & "Zaimportowane rekordy, bez modyfikacji: " & woupdate & vbNewLine & "Start: " & starttime & " Koniec: " & endtime
importfilepath = ""
SQL2.Logg("Import", "Zaimportowano Dane", "Raport z Rozliczeniowej", import_status.Text, "-3")
System.Runtime.InteropServices.Marshal.ReleaseComObject(oApp)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWS)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWBa)
SQLConn.Close()
Catch ex As Exception
import_status.Text = "BŁĄD W IMPORCIE" & vbNewLine & "Treść: " & ex.Message
System.Runtime.InteropServices.Marshal.ReleaseComObject(oApp)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWS)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWBa)
SQLConn.Close()
End Try
Else
import_status.Text = "Wybierz plik importowy"
End If
End Sub
Kod wykonuje się +/- 20 min.
Kolejny pomysł to wrzucenie całego arkusza do datatable i później wybieranie komórek z datatable.
Co o tym sądzicie?
Jak rozwiązać import do SQL? Rekord po rekordzie - zapytanie i na jego podstawie odpowiedni import?
Z góry dzięki za wskazówki.