Importing .txt file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a .txt which has been exported as a .csv from an external source.
What i need to do is to import this into SQL2000 (into a table) but I need to
do special things on the data:

1. I need to look for the first three chars and import rows into separate
tables. E.g. if the first three chars begin with CCC, then this row goes
into the CCC_table, if it is TTT then into the TTT_table etc...
2. Once I have my tables built up, I need to do add more columns onto the
table, e.g. AAA, BBB, CCC and do a find and replace on the data. E.g. in
table CCC_table, if the 2nd row contains a AAA, then I need to output this to
the AAA field.

Please can someone help me on this. I am new to VB.net and Visual
Studio.net as a whole, but would appreciate some pointers.

Thanks,

skc
 
I don't understand - please can you elaborate.

Cor Ligthert said:
Skc,

This is an often asked answer and filled with tons of answers in these
newsgroups.
The best newsgroups for this kind of questions are the next time.

microsoft.public.dotnet.framework.adonet
microsoft.public.dotnet.languages.vb

However, we have as well a sample for this on our website.

http://www.windowsformsdatagridhelp.com/default.aspx?ID=1b644f6b-aa01-49f6-bc1f-212f9e0de193

The other way around is as well on our site.

I hope this helps

Cor
Click to expand...
 
D>I don't understand - please can you elaborate.What is it that you don't understand, did you try the sample?

In that way you get a datatable wherein you can handle all the columns
separately.

Cor
 
Skc,

With your file than of course. Than you get a datatable.

If you got that, reply than, than we take the other problem.

Cor
 
This is my code... I have errors on it...help!

Option Strict Off
Option Explicit On
Imports VB = Microsoft.VisualBasic
Friend Class Form1
Inherits System.Windows.Forms.Form
#Region "Windows Form Designer generated code "
Public Sub New()
MyBase.New()
If m_vb6FormDefInstance Is Nothing Then
If m_InitializingDefInstance Then
m_vb6FormDefInstance = Me
Else
Try
'For the start-up form, the first instance created is the default
instance.
If
System.Reflection.Assembly.GetExecutingAssembly.EntryPoint.DeclaringType Is
Me.GetType Then
m_vb6FormDefInstance = Me
End If
Catch
End Try
End If
End If
'This call is required by the Windows Form Designer.
InitializeComponent()
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal Disposing As Boolean)
If Disposing Then
If Not components Is Nothing Then
components.Dispose()
End If
End If
MyBase.Dispose(Disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
Public ToolTip1 As System.Windows.Forms.ToolTip
Public WithEvents cboDelimiter As System.Windows.Forms.ComboBox
Public WithEvents txtTable As System.Windows.Forms.TextBox
Public WithEvents txtDatabaseFile As System.Windows.Forms.TextBox
Public WithEvents cmdImport As System.Windows.Forms.Button
Public WithEvents txtTextFile As System.Windows.Forms.TextBox
Public WithEvents _Label2_1 As System.Windows.Forms.Label
Public WithEvents _Label1_2 As System.Windows.Forms.Label
Public WithEvents _Label1_1 As System.Windows.Forms.Label
Public WithEvents _Label1_0 As System.Windows.Forms.Label
Public WithEvents Label1 As
Microsoft.VisualBasic.Compatibility.VB6.LabelArray
Public WithEvents Label2 As
Microsoft.VisualBasic.Compatibility.VB6.LabelArray
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Public WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents Label3 As System.Windows.Forms.Label
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.components = New System.ComponentModel.Container()
Me.ToolTip1 = New System.Windows.Forms.ToolTip(Me.components)
Me.cboDelimiter = New System.Windows.Forms.ComboBox()
Me.txtTable = New System.Windows.Forms.TextBox()
Me.txtDatabaseFile = New System.Windows.Forms.TextBox()
Me.cmdImport = New System.Windows.Forms.Button()
Me.txtTextFile = New System.Windows.Forms.TextBox()
Me._Label2_1 = New System.Windows.Forms.Label()
Me._Label1_2 = New System.Windows.Forms.Label()
Me._Label1_1 = New System.Windows.Forms.Label()
Me._Label1_0 = New System.Windows.Forms.Label()
Me.Label1 = New
Microsoft.VisualBasic.Compatibility.VB6.LabelArray(Me.components)
Me.Label2 = New
Microsoft.VisualBasic.Compatibility.VB6.LabelArray(Me.components)
Me.Button1 = New System.Windows.Forms.Button()
Me.Label3 = New System.Windows.Forms.Label()
CType(Me.Label1, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.Label2, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'cboDelimiter
'
Me.cboDelimiter.BackColor = System.Drawing.SystemColors.Window
Me.cboDelimiter.Cursor = System.Windows.Forms.Cursors.Default
Me.cboDelimiter.ForeColor = System.Drawing.SystemColors.WindowText
Me.cboDelimiter.Items.AddRange(New Object() {"*", ";", ",", "<tab>",
"<space>"})
Me.cboDelimiter.Location = New System.Drawing.Point(120, 144)
Me.cboDelimiter.Name = "cboDelimiter"
Me.cboDelimiter.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.cboDelimiter.Size = New System.Drawing.Size(48, 21)
Me.cboDelimiter.TabIndex = 8
Me.cboDelimiter.Text = "*"
'
'txtTable
'
Me.txtTable.AcceptsReturn = True
Me.txtTable.AutoSize = False
Me.txtTable.BackColor = System.Drawing.SystemColors.Window
Me.txtTable.Cursor = System.Windows.Forms.Cursors.IBeam
Me.txtTable.ForeColor = System.Drawing.SystemColors.WindowText
Me.txtTable.Location = New System.Drawing.Point(120, 120)
Me.txtTable.MaxLength = 0
Me.txtTable.Name = "txtTable"
Me.txtTable.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.txtTable.Size = New System.Drawing.Size(176, 19)
Me.txtTable.TabIndex = 5
Me.txtTable.Text = "DataValues"
'
'txtDatabaseFile
'
Me.txtDatabaseFile.AcceptsReturn = True
Me.txtDatabaseFile.AutoSize = False
Me.txtDatabaseFile.BackColor = System.Drawing.SystemColors.Window
Me.txtDatabaseFile.Cursor = System.Windows.Forms.Cursors.IBeam
Me.txtDatabaseFile.ForeColor = System.Drawing.SystemColors.WindowText
Me.txtDatabaseFile.Location = New System.Drawing.Point(120, 96)
Me.txtDatabaseFile.MaxLength = 0
Me.txtDatabaseFile.Name = "txtDatabaseFile"
Me.txtDatabaseFile.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.txtDatabaseFile.Size = New System.Drawing.Size(176, 19)
Me.txtDatabaseFile.TabIndex = 3
Me.txtDatabaseFile.Text = "C:\Temp\test.mdb"
'
'cmdImport
'
Me.cmdImport.BackColor = System.Drawing.SystemColors.Control
Me.cmdImport.Cursor = System.Windows.Forms.Cursors.Default
Me.cmdImport.ForeColor = System.Drawing.SystemColors.ControlText
Me.cmdImport.Location = New System.Drawing.Point(120, 184)
Me.cmdImport.Name = "cmdImport"
Me.cmdImport.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.cmdImport.Size = New System.Drawing.Size(81, 33)
Me.cmdImport.TabIndex = 2
Me.cmdImport.Text = "Import"
'
'txtTextFile
'
Me.txtTextFile.AcceptsReturn = True
Me.txtTextFile.AutoSize = False
Me.txtTextFile.BackColor = System.Drawing.SystemColors.Window
Me.txtTextFile.Cursor = System.Windows.Forms.Cursors.IBeam
Me.txtTextFile.ForeColor = System.Drawing.SystemColors.WindowText
Me.txtTextFile.Location = New System.Drawing.Point(120, 72)
Me.txtTextFile.MaxLength = 0
Me.txtTextFile.Name = "txtTextFile"
Me.txtTextFile.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.txtTextFile.Size = New System.Drawing.Size(176, 19)
Me.txtTextFile.TabIndex = 0
Me.txtTextFile.Text = "C:\Temp\test.txt"
'
'_Label2_1
'
Me._Label2_1.BackColor = System.Drawing.SystemColors.Control
Me._Label2_1.Cursor = System.Windows.Forms.Cursors.Default
Me._Label2_1.ForeColor = System.Drawing.SystemColors.ControlText
Me.Label2.SetIndex(Me._Label2_1, CType(1, Short))
Me._Label2_1.Location = New System.Drawing.Point(16, 144)
Me._Label2_1.Name = "_Label2_1"
Me._Label2_1.RightToLeft = System.Windows.Forms.RightToLeft.No
Me._Label2_1.Size = New System.Drawing.Size(65, 17)
Me._Label2_1.TabIndex = 7
Me._Label2_1.Text = "Delimiter"
'
'_Label1_2
'
Me._Label1_2.BackColor = System.Drawing.SystemColors.Control
Me._Label1_2.Cursor = System.Windows.Forms.Cursors.Default
Me._Label1_2.ForeColor = System.Drawing.SystemColors.ControlText
Me.Label1.SetIndex(Me._Label1_2, CType(2, Short))
Me._Label1_2.Location = New System.Drawing.Point(16, 120)
Me._Label1_2.Name = "_Label1_2"
Me._Label1_2.RightToLeft = System.Windows.Forms.RightToLeft.No
Me._Label1_2.Size = New System.Drawing.Size(73, 17)
Me._Label1_2.TabIndex = 6
Me._Label1_2.Text = "Table"
'
'_Label1_1
'
Me._Label1_1.BackColor = System.Drawing.SystemColors.Control
Me._Label1_1.Cursor = System.Windows.Forms.Cursors.Default
Me._Label1_1.ForeColor = System.Drawing.SystemColors.ControlText
Me.Label1.SetIndex(Me._Label1_1, CType(1, Short))
Me._Label1_1.Location = New System.Drawing.Point(16, 96)
Me._Label1_1.Name = "_Label1_1"
Me._Label1_1.RightToLeft = System.Windows.Forms.RightToLeft.No
Me._Label1_1.Size = New System.Drawing.Size(96, 17)
Me._Label1_1.TabIndex = 4
Me._Label1_1.Text = "Database File"
'
'_Label1_0
'
Me._Label1_0.BackColor = System.Drawing.SystemColors.Control
Me._Label1_0.Cursor = System.Windows.Forms.Cursors.Default
Me._Label1_0.ForeColor = System.Drawing.SystemColors.ControlText
Me.Label1.SetIndex(Me._Label1_0, CType(0, Short))
Me._Label1_0.Location = New System.Drawing.Point(16, 72)
Me._Label1_0.Name = "_Label1_0"
Me._Label1_0.RightToLeft = System.Windows.Forms.RightToLeft.No
Me._Label1_0.Size = New System.Drawing.Size(65, 17)
Me._Label1_0.TabIndex = 1
Me._Label1_0.Text = "Text File"
'
'Button1
'
Me.Button1.BackColor = System.Drawing.SystemColors.Control
Me.Button1.Cursor = System.Windows.Forms.Cursors.Default
Me.Button1.ForeColor = System.Drawing.SystemColors.ControlText
Me.Button1.Location = New System.Drawing.Point(264, 184)
Me.Button1.Name = "Button1"
Me.Button1.RightToLeft = System.Windows.Forms.RightToLeft.No
Me.Button1.Size = New System.Drawing.Size(81, 33)
Me.Button1.TabIndex = 9
Me.Button1.Text = "Sort out data"
'
'Label3
'
Me.Label3.Font = New System.Drawing.Font("Microsoft Sans Serif",
15.75!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point,
CType(0, Byte))
Me.Label3.Location = New System.Drawing.Point(8, 16)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(328, 32)
Me.Label3.TabIndex = 10
Me.Label3.Text = "Import Program from .txt to .mdb"
'
'Form1
'
Me.AcceptButton = Me.cmdImport
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(584, 294)
Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.Label3,
Me.Button1, Me.cboDelimiter, Me.txtTable, Me.txtDatabaseFile, Me.cmdImport,
Me.txtTextFile, Me._Label2_1, Me._Label1_2, Me._Label1_1, Me._Label1_0})
Me.Location = New System.Drawing.Point(76, 101)
Me.Name = "Form1"
Me.StartPosition = System.Windows.Forms.FormStartPosition.Manual
Me.Text = "Form1"
CType(Me.Label1, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.Label2, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)

End Sub
#End Region
#Region "Upgrade Support "
Private Shared m_vb6FormDefInstance As Form1
Private Shared m_InitializingDefInstance As Boolean
Public Shared Property DefInstance() As Form1
Get
If m_vb6FormDefInstance Is Nothing OrElse m_vb6FormDefInstance.IsDisposed
Then
m_InitializingDefInstance = True
m_vb6FormDefInstance = New Form1()
m_InitializingDefInstance = False
End If
DefInstance = m_vb6FormDefInstance
End Get
Set
m_vb6FormDefInstance = Value
End Set
End Property
#End Region

Private Sub cmdImport_Click(ByVal eventSender As System.Object, ByVal
eventArgs As System.EventArgs) Handles cmdImport.Click
Dim delimiter As String
Dim contents As String
Dim lines() As String
Dim fields() As String
Dim wks As DAO.Workspace
Dim db As DAO.Database
Dim fnum As Short
Dim line_num As Short
Dim field_num As Short
Dim sql_statement As String
Dim num_records As Integer

delimiter = cboDelimiter.Text
If delimiter = "<space>" Then delimiter = " "
If delimiter = "<tab>" Then delimiter = vbTab

' Grab the file's contents.
fnum = FreeFile()
On Error GoTo NoTextFile
FileOpen(fnum, txtTextFile.Text, OpenMode.Input)
contents = InputString(fnum, LOF(fnum))
FileClose(fnum)

' Split the contents into lines.
lines = Split(contents, vbCrLf)

' Open the database.
On Error GoTo NoDatabase
wks = DAODBEngine_definst.Workspaces(0)
db = wks.OpenDatabase(txtDatabaseFile.Text)
On Error GoTo 0

' Process the lines and create records.
For line_num = LBound(lines) To UBound(lines)
' Read a text line.
If Len(lines(line_num)) > 0 Then
' Build an INSERT statement.
sql_statement = "INSERT INTO " & txtTable.Text & " VALUES ("

fields = Split(lines(line_num), delimiter)
For field_num = LBound(fields) To UBound(fields)
' Add the field to the statement.
sql_statement = sql_statement & "'" & fields(field_num)
& "', "
Next field_num

' Remove the last comma.
sql_statement = VB.Left(sql_statement, Len(sql_statement) -
2) & ")"

' Insert the record.
On Error GoTo SQLError
db.Execute(sql_statement)
On Error GoTo 0
num_records = num_records + 1
End If
Next line_num

' Close the database.
db.Close()
wks.Close()
MsgBox("Inserted " & VB6.Format(num_records) & " records")
Exit Sub

NoTextFile:
MsgBox("Error opening text file.")
Exit Sub

NoDatabase:
MsgBox("Error opening database.")
FileClose(fnum)
Exit Sub

SQLError:
MsgBox("Error executing SQL statement '" & sql_statement & "'")
FileClose(fnum)
db.Close()
wks.Close()
Exit Sub
End Sub
Private Sub Form1_Load(ByVal eventSender As System.Object, ByVal eventArgs
As System.EventArgs) Handles MyBase.Load
' Enter default file and database names.
txtTextFile.Text = VB6.GetPath & "\testdata.txt"
txtDatabaseFile.Text = VB6.GetPath & "\testdata.mdb"
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim file As String = VB6.GetPath & "\testdata.txt"
Dim path As String = VB6.GetPath & "\Test1\"
Dim ds As New DataSet()
Try
Dim f As System.IO.File
If f.Exists(path & file) Then
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Dim conn As New OleDb.OleDbConnection(ConStr)
Dim da As New OleDb.OleDbDataAdapter("Select * from " &
file, conn)
da.Fill(ds, "TextFile")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
DataGrid1.DataSource = ds.Tables(0)
End Sub
End Class
 
Cor,

I have created a new button and have started my own subroutine:

Private Sub Button1_Click(ByVal oTable As DataTable, ByVal oConn As
System.EventArgs) Handles Button1.Click
Dim oRow As DataRow
For Each oRow In oTable.Rows()

Next

End Sub

in here I want to pick up the first cell of the .csv and step through the
rows one-by-one and dump the cells to variables and then drop in in the rows
later.
 
I have created a new button and have started my own subroutine:
Private Sub Button1_Click(ByVal oTable As DataTable, ByVal oConn As
System.EventArgs) Handles Button1.Click
Dim oRow As DataRow
For Each oRow In oTable.Rows()

Next

End Sub

in here I want to pick up the first cell of the .csv and step through the
rows one-by-one and dump the cells to variables and then drop in in the
rows
later.
Click to expand...


As far as I can see, you are on the right track,

And please clean up your code first before you sent it to the newsgroups.

I assume that your program can at least be 70% smaller.

Not that I investigated it.

Cor
 
¤ I have a .txt which has been exported as a .csv from an external source.
¤ What i need to do is to import this into SQL2000 (into a table) but I need to
¤ do special things on the data:
¤
¤ 1. I need to look for the first three chars and import rows into separate
¤ tables. E.g. if the first three chars begin with CCC, then this row goes
¤ into the CCC_table, if it is TTT then into the TTT_table etc...
¤ 2. Once I have my tables built up, I need to do add more columns onto the
¤ table, e.g. AAA, BBB, CCC and do a find and replace on the data. E.g. in
¤ table CCC_table, if the 2nd row contains a AAA, then I need to output this to
¤ the AAA field.
¤
¤ Please can someone help me on this. I am new to VB.net and Visual
¤ Studio.net as a whole, but would appreciate some pointers.

You can probably do most of this using just SQL. I don't know whether you're working with existing
tables or are creating new tables on the fly but something like the following should work (F1 is the
first column of the text file):

Dim TextConnection As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "E:\My
Documents\TextFiles" & ";" & _
"Extended
Properties=""Text;HDR=NO;""")

TextConnection.Open()

'New table
Dim TextCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [CCC_Table] IN ''
[ODBC;Driver={SQL Server};Server=(local);Database=Northwind;Trusted_Connection=yes] FROM
[FileNamet#csv] WHERE Left(F1,3) = 'CCC'", TextConnection)

TextCommand.ExecuteNonQuery()
TextConnection.Close()

As for question #2, you can add columns using ALTER TABLE:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_aa-az_3ied.asp?frame=true

....and use an UPDATE query to populate these new columns:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ua-uz_82n9.asp?frame=true


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Paul,

Based on what I copies/pasted a few days ago, do you think you can email me
the code I need to have in order for me to do this please? I am at a total
deadend. My email address is: (e-mail address removed)

Thanks,

skc
 
Paul,

The other problem I am facing is that the .TXT file I am importing contains
delimiatation by """, so I have "AAA", "222" but I only need the AAA and 222
values without the speech marks!!

Please help.

skc
 
Back
Top