File Import Questions...

[Guest]

I have the following vb.net form that allows you to select a .txt file,
specify a .mdb and it happily imports. However, I have the following
questions:

1. My .txt contains data delimeted with "," - e.g. "AAA","BBB" and this is
appearing in my .mdb with the speech marks. How do I modify my code to get
rid of this?
2. I want to store certain patterns as variables. E.g. if my .txt contains:
"AAA", "1","2","22222"
"BBB","2","3","43231"
"CCC","3","4","33323"
then I want to assign a variable to location 1, i.e. picking up all the
"AAA" and "BBB" etc..., and do an CASE statement going through the .txt line
by line. E.g CASE Left(A,3)="AAA" Then, no_of_cars=B (i.e. the 1), doors=C
(i.e. 2), mileage=D (i.e. 22222).

How do I build this in please?

thanks

skc
---------------------------------
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

End Class

 

[Cor Ligthert [MVP]]

Skc,

I am sure I answered this question for the first part (the ADONET part) in
the newsgroup General. I saw no reply, what is wrong with that answer?

Cor

 

[Guest]

Cor,

I am really new to this. I have no idea what you mean't by your post.
Please can you give me a step-by-step answer.

Thanks.

skc

 

[Cor Ligthert [MVP]]

Cor,

I am really new to this. I have no idea what you mean't by your post.
Please can you give me a step-by-step answer.

Answered in the General newsgroup

Cor

 

[Guest]

I need help on this. I have already posted in the General Newsgroup.

 

[Cor Ligthert [MVP]]

I need help on this. I have already posted in the General Newsgroup.

:

Do you mean that there where no answers.

I did it first and after that Paul Clement did it. You asked in a way to
Paul to make a complete program for you based on some code you had sent in
and in my opinion completly does not match the problem.

Don't expect that from a newsgroup. However, you never know.

Just my thought,

Cor