need help with text file migration to access with vb...

hello,

i have a small project which i wanna do with vb. i have a text file with some data. it is in this format...

0010 CHOLERA D/T VIB CHOLERAE
0011 CHOLERA D/T VIB EL TOR
00320 LOCAL SALMONELLA INF NOS
00321 SALMONELLA MENINGITIS
00322 SALMONELLA PNEUMONIA
00323 SALMONELLA ARTHRITIS
00324 SALMONELLA OSTEOMYELITIS
00329 LOCAL SALMONELLA INF NEC
0038 SALMONELLA INFECTION NEC
0039 SALMONELLA INFECTION NOS
0040 SHIGELLA DYSENTERIAE
00581 FOOD POISN D/T V. VULNIF
00589 BACT FOOD POISONING NEC
0059 FOOD POISONING NOS

... and so on.

it is saved as a text file in notepad. i wanna export or transer this into ms-access. the table there has two fields... the first field contains the numbers or codes above and the second field has all the rest (description).
the catch here is, that i need to introduce a decimal point after the first three characters in the first field. AND, THE FIRST FIELD SHOULD BE OF TYPE "TEXT" AND NOT "NUMBER".

how do i go about this. I am able to export the entire line into one field in access... but am not able to split the data. also, am not sure how to include the decimal point in a text!

any help would be greatly appreciated. thanks in advance...

A good thing to do if your trying to split things in a text file, is separate them all in the same place.

Example:

It looks like your numbers have a max of 5 chars. So when inputting into the text file make sure your words start at position 7

then you could do something like this

Code:

---

dim i as integer
dim refer(5000)
dim names(5000)
dim var(5000)  '5000 will be the number of items that can be stored in the array

i = 0

open "yourtxt.txt" for input as #1
do until EOF(1)
    line input #1, var(i)
    i = i + 1
loop
close #1

For j = 0 to i - 1
    refer(j) = left$(var(j),5)
    names(j) = mid$(var(j),7,25)
Next j

'then do put a decimal in the number
For p = 0 to i - 1
    refer(p) = left$(refer(p),3) & "." & mid$(var(p),4,2)
    refer(p) = trim(refer(p))  ' to get rid of spaces
    names(p) = trim(names(p))
Next p

---

Now for the database part.

There are a few different ways to open a database, I recommend using ADODB.

You will have to add the reference ... something like MS ActiveX Data Objects 2.5 library

Code:

---

Dim conn as New ADODB.Connection
Dim rs as New ADODB.RecordSet
Dim mySQL as string

Set conn = New ADODB.Connection
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=myDB.mdb;"
   
mySQL = "select * from myTable order by ID"
   
Set rs = New ADODB.Recordset
rs.Open mySQL, conn, adOpenDynamic, adLockOptimistic

For o = 0 to i - 1
    With rs
          .Addnew
          .Fields("Number") = refer(j)
          .Fields("Word") = names(j)
          .Update
      End With
Next o

---

Now my text file usage is a little rusty, and I'm not sitting where I can test this first, so if you run into any problems. Let me know

Good Luck :D

** EDIT **

Ok my first post had a couple errors in it. I went and tested it out, and this will return exactly what you would like.

Actually, I think a better method of splitting the line would be:

Code:

---

Dim sCode() As String
Dim sDesc() As String
Dim sLine As String
Dim lArraySize As Long

lArraySize = 0
Open TEXTFILE For Input As #1
Do Until EOF(1)
    ReDim Preserve sCode(0 To lArraySize) As String
    ReDim Preserve sDesc(0 To lArraySize) As String
    Line Input #1, sLine
    sCode(lArraySize) = Left(sLine, InStr(1, sLine, " ") - 1)
    sCode(lArraySize) = Left(sCode(lArraySize), 3) & "." & Right(sCode(lArraySize), Len(sCode(lArraySize)) - 3)
    sDesc(lArraySize) = Right(sLine, Len(sLine) - InStr(1, sLine, " "))
    lArraySize = lArraySize + 1
Loop

---

This code is more variable length friendly. That way he could have a long code (like 0054654) and still get 005.4654 out and have the description correct.

You could also opt to keep the arrays a static length and just remove the ReDim Preserve lines.

Then, add an ADO reference: Project -> References... -> Microsoft ActiveX Data Objects 2.X Library. Go with the newest one you have.

In the same sub, do:

Code:

---

Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Dim lCount As Long

Set adoConnection = New ADODB.Connection
adoConnection.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=myDB.mdb;"
adoConnection.Open
Set adoRecordset = New ADODB.Recordset
adoRecordset.Open "SELECT * FROM myTable", adoConnection, adOpenDynamic, adLockBatchOptimistic

For lCount = 0 To lArraySize
    With adoRecordset
        .AddNew
        .Fields(0).Value = sCode(lCount)
        .Fields(1).Value = sDesc(lCount)
        .UpdateBatch
    End With
Next

'If you're finished with the connection, do:
adoRecordset.Close
Set adoRecordset = Nothing
adoConnection.Close
Set adoConnection = Nothing

---

thanx for ur reply, buddy! i really appreciate ur prompt reply. thanx a lot although i guess chaostheeternal's reply is closest to what i want. i really appreciate ur time and effort though

thanks chaos! it works. it actually writes into the access database. only, the program ends with an error...

after writing into the database, it goes to the error handler and says "subscript out of range". trying to figure out what that means altho im not sure.

Also, is there a funtion or something like that to change case of the text before it writes into the database. say, the text "0038 SALMONELLA INFECTION NEC" should be populated in access as 0038 Salmonella Infection Nec"

thanx once again for ur help. i really appreciate it!

Ah, I know why. For the For Loop, change:

Code:

---

For lCount = 0 To lArraySize

---

To:

Code:

---

For lCount = 0 To lArraySize - 1

---

And you can have it proper.

Code:

---

sOutput = StrConv(sInput, vbProperCase)

---

sInput is the input string, and sOutput is the output string. That will output a string taken in with the first letter of each word capitalized, like:

Code:

---

"HELLO WORLD" -> "Hello World"
"vISUAL bASIC" -> "Visual Basic"
"CoDe gUrU" -> "Code Guru"
Etc.

---

thanx once again, chaos boss! it worked. thanx a lot!!!