Imports System
Imports System.Drawing
Imports System.Collections
Imports System.ComponentModel
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.IO
Public Class imex
'Initialisierung Global
Dim gbMain As System.Windows.Forms.GroupBox
'Dim txtCSVFolderPath As System.Windows.Forms.TextBox
'Dim btnOpenFldrBwsr As System.Windows.Forms.Button
'Dim fbdCSVFolder As System.Windows.Forms.FolderBrowserDialog
'Dim txtCSVFilePath As System.Windows.Forms.TextBox
'Dim btnOpenFileDlg As System.Windows.Forms.Button
'Dim openFileDialogCSVFilePath As System.Windows.Forms.OpenFileDialog
'Dim btnImport As System.Windows.Forms.Button
'Dim dGridCSVdata As System.Windows.Forms.DataGrid
Dim strCSVFile = ""
Dim gbMainUploadData As System.Windows.Forms.GroupBox
'Dim btnUpload As System.Windows.Forms.Button
Dim obj_oledb_da As System.Data.Odbc.OdbcDataAdapter
'Dim label1 As System.Windows.Forms.Label
'Dim cmbFormats As System.Windows.Forms.ComboBox
'Dim label2 As System.Windows.Forms.Label
'Dim txtDelimiter As System.Windows.Forms.TextBox
Dim bolColName = True
'Dim cbColNameHeader As System.Windows.Forms.CheckBox
Dim strFormat = "CSVDelimited"
'####Dim szSrcLine As String
'Dim lblFolderPath As System.Windows.Forms.Label
'Dim lblFilePath As System.Windows.Forms.Label
' Initialisierung Global
'###########################################
'###########################################
Private Sub OpenFileDialogCSVFilePath_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialogCSVFilePath.FileOk
End Sub
Private Sub fbdCSVFolder_HelpRequest(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles fbdCSVFolder.HelpRequest
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
cmbFormats.SelectedIndex = 0
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
End Try
End Sub
Private Sub btnOpenFldrBwsr_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenFldrBwsr.Click
' Ein Klick auf dieser Schaltfläche, öffnet sich das FolderBrowserDialog wo der Benutzer den Pfad des Ordners auswählen kann
' csv-Dateien
Try
If fbdCSVFolder.ShowDialog() = DialogResult.OK Then
txtCSVFolderPath.Text = fbdCSVFolder.SelectedPath.Trim()
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
End Try
End Sub
Private Sub btnOpenFileDlg_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenFileDlg.Click
' Ein Klick auf dieser Schaltfläche, öffnet sich das OpenFileDialog wo der Benutzer die CSV-Datei auswählen kann.
Try
OpenFileDialogCSVFilePath.InitialDirectory = txtCSVFolderPath.Text.Trim()
If OpenFileDialogCSVFilePath.ShowDialog() = DialogResult.OK Then
txtCSVFilePath.Text = OpenFileDialogCSVFilePath.FileName.Trim()
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
End Try
End Sub
Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click
Try
If txtCSVFolderPath.Text = "" Then
MessageBox.Show("Der Pfad des Ordners TextBox darf nicht leer sein.", "Warnung")
Return
ElseIf txtCSVFilePath.Text = "" Then
MessageBox.Show("Das File Path TextBox darf nicht leer sein.", "Warnung")
Return
ElseIf txtDelimiter.Text = "" Then
MessageBox.Show("Sie können nicht festlegen ("") als Trennzeichen.", "Warnung")
Return
Else
Dim intLengthOfFileName As Integer = txtCSVFilePath.Text.Trim().Length
Dim intLastIndex As Integer = txtCSVFilePath.Text.Trim().LastIndexOf("\") ' Trimmen ?????
strCSVFile = txtCSVFilePath.Text.Trim().Substring(intLastIndex, intLengthOfFileName - intLastIndex)
Format() 'Aufruf Format Unterprogramm
strCSVFile = strCSVFile.Remove(0, 1).Trim() ' #### Remove
writeSchema() 'Aufruf writeSchema ini schreiben
ConnectCSV(strCSVFile) 'Aufruf DB Connect
btnUpload.Enabled = True
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
End Try
End Sub
Private Function ReadConFile() As String
'/* Diese Funktion baut eine dynamische Verbindung auf*/
'Dim s2 As String = "", szSrcLine As String
Dim s2 As String = " ", szSrcLine As String
'Dim fsInput As New FileStream("C:\conString.txt", FileMode.Open, FileAccess.Read)
Dim fsInput As New FileStream("C:\conString.txt", FileMode.Open, FileAccess.Read)
Dim srInput As New StreamReader(fsInput)
While (InlineAssignHelper(szSrcLine, srInput.ReadLine())) IsNot Nothing
s2 = s2 & szSrcLine
End While
srInput.Close()
fsInput.Close()
Return s2
End Function
Public Function ConnectCSV(ByVal filetable As String) As DataSet
Dim ds As New DataSet()
Try
' Sie können ein connected absetzten oder mit DSN connection string setzten
' Erstellen eines connection string als unten, wenn Sie weniger DSN-Verbindung verwenden. Die DBQ Attribut legt den Pfad des Verzeichnisses fest, die die CSV-Dateien enthält
Dim strConnString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & txtCSVFolderPath.Text.Trim() & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
Dim sql_select As String
Dim conn As System.Data.Odbc.OdbcConnection
'Erstellen Verbindung zur CSV-Datei
conn = New System.Data.Odbc.OdbcConnection(strConnString.Trim())
' Zum Herstellen einer Verbindung mit DSN, verwenden Sie folgende Zeile
'conn = New System.Data.Odbc.OdbcConnection(DSN() = "MyDSN")
' Oeffne die Verbindung
conn.Open()
' Holen der Einträge records aus dem CSV
sql_select = "select * from [" & filetable & "]"
obj_oledb_da = New System.Data.Odbc.OdbcDataAdapter(sql_select, conn)
' Fülle dataset mit dem records aus dem CSV file
obj_oledb_da.Fill(ds, "Stocks")
' Set the datagrid properties
dGridCSVdata.DataSource = ds
dGridCSVdata.DataMember = "Stocks"
' Schliesse die Verbindung zu dem CSV file
conn.Close()
Catch e As Exception
' Fehler
MessageBox.Show(e.Message)
End Try
Return ds
End Function
Private Sub Format()
' Das .csv file format ist spezifizert, das file ist abgegrenzt, oder als
' Komma,Tab oder als selbstdefinierter Einstellung möglich...
Try
If cmbFormats.SelectedIndex = 0 Then
strFormat = "CSVDelimited"
'MsgBox("strFormat()")
' MsgBox("CSVDelimited()")
ElseIf cmbFormats.SelectedIndex = 1 Then
strFormat = "TabDelimited"
'MsgBox("strFormat()")
' MsgBox("TabDelimited(" & txtDelimiter.Text() & ")")
Else
strFormat = "Delimited(" & txtDelimiter.Text.Trim() & ")" '###löscht von und nach der Zeichenfolge die Leerzeichen
' strFormat = "Delimited(" & txtDelimiter.Text.Trim() & ")"
' Trim löscht Leerzeichen nach der Zeichenkette, txtDelimiter.Text Eingabefenster...
'MsgBox("(" & Trim(strTrim) & ")")
' MsgBox("Delimited(" & txtDelimiter.Text.Trim() & ")")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
End Try
End Sub
Private Sub writeSchema()
'*Schema.ini File (Text File Driver)
'Wenn ein Text-Treiber verwendet wird, ist das Format in der Text-Datei mit einem bestimmten
'Schema-Information-Datei enthalten. Die Schema-Information-Datei, die immer hier genannt wird heisst Schema.ini
'und steht im gleichen Verzeichnis wie die Text Datenquelle dies stellt die IISAM
'Informationen zu Verfügung. Über das allgemeine Format der Datei, die Spalte Namen und Datentyp
'Informationen und eine Reihe anderer Daten Merkmale zu Verfügung gestellt*/
'http://www.der-softwareentwickler-blog.de/2008/12/04/tipps-zur-schemaini-datei-von-csv-files/
'http://msdn.microsoft.com/en-us/library/ms709353.aspx
'http://www.vbarchiv.net/tipps/details.php?id=2217
'http://www.datanorm.de/versionen_datanorm5.aspx
'http://de.wikipedia.org/wiki/Datanorm
'"V" - Vorlaufsatz
'"K" - Kundennummern-Kontrollsatz
'"S" - Haupt- oder Warengruppensatz
'"R" - Rabattsatz
'"A" - Hauptsatz 1
'"B" - Hauptsatz 2 (nur Datanorm bis V.4)
'"C" - Leistungs-Satz
'"D" - Dimensionssatz
'"T" - Langtextsatz
'"E" - Ersetzungstextsatz (Datanorm V.4)
'"E" - Dateiendesatz (Datanorm V.5)
'"J" - Artikel-Set-Satz
'"P" - Preisänderungssatz
'"Z" - Staffelpreiszu- und -abschlagssatz
'"G" - Grafikanbindungssatz (nur Datanorm V.5)
Try
Dim fsOutput As New FileStream(txtCSVFolderPath.Text + "\schema.ini", FileMode.Create, FileAccess.Write)
Dim srOutput As New StreamWriter(fsOutput)
Dim s1 As String, s2 As String, s3 As String, s4 As String, s5 As String
s1 = "[" & strCSVFile & "]"
s2 = "ColNameHeader=" & bolColName.ToString()
s3 = "Format=" & strFormat
s4 = "MaxScanRows=10" 'z.B. 25 Reihen Tabelle
s5 = "CharacterSet=OEM"
srOutput.WriteLine(s1.ToString() & ControlChars.Lf & s2.ToString() & ControlChars.Lf & s3.ToString() & ControlChars.Lf & s4.ToString() & ControlChars.Lf & s5.ToString())
srOutput.Close()
fsOutput.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
End Try
End Sub
Private Sub cbColNameHeader_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbColNameHeader.CheckedChanged
Try
If cbColNameHeader.Checked = False Then
bolColName = False
Else
bolColName = True
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
End Try
End Sub
Private Sub txtCSVFolderPath_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtCSVFolderPath.TextChanged
End Sub
Private Sub txtCSVFilePath_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtCSVFilePath.TextChanged
End Sub
Private Sub txtDelimiter_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtDelimiter.TextChanged
End Sub
Private Sub dGridCSVdata_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dGridCSVdata.CellContentClick
End Sub
Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click
End Sub
Private Sub lblFolderPath_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lblFolderPath.Click
End Sub
Private Sub lblFilePath_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lblFilePath.Click
End Sub
Private Sub gbMain_Enter(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Private Sub dGridCSVdata_Navigate(ByVal sender As Object, ByVal ne As NavigateEventArgs)
End Sub
Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click
End Sub
Private Sub Label2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label2.Click
End Sub
Private Function InlineAssignHelper(ByVal szSrcLine As String, ByVal p2 As String) As Object
Throw New NotImplementedException
End Function
Private Sub cmbFormats_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbFormats.SelectedIndexChanged
Try
If cmbFormats.SelectedIndex = 2 Then
txtDelimiter.Enabled = True
txtDelimiter.BackColor = Color.FromName("Info")
Else
txtDelimiter.Enabled = False
txtDelimiter.BackColor = Color.FromName("InactiveBorder")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
End Try
End Sub
Private Function DSN() As String
Throw New NotImplementedException
End Function
Private Sub Label3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label3.Click
'#### Test ausgabe
Label3.Text = txtCSVFilePath.Text
End Sub
End Class
|
|
|
|
|
|
|
|
|
|
|
|
