Solved

Converting a text file to .mdb format

Posted on 1997-06-12
4
316 Views
Last Modified: 2012-05-04
I am looking for a code sample and/or good explination about how to convert a delimited text file into a .mdb format by opening the text file using the dbengine and also opening the .mdb file with the dbengine, reading one record at a time from the text file and filling in the .mdb file.

I have tried several times to make the schema.ini file work but it is returning unexpected results every time even though I am vurtully copying the code from the help file.

The text file is being delimited with the pipe character "|" but another printable character can be used if that is causing the problem. Although I have tried several other characters too such as Tilde "~" and even the exclamation point "!" and get the exact same results.

I get 1 of 2 results...

1, The parse doesn't work and I get 1 field in each record that includes the delimiter character I am using instead of several fields in each record.

2, An error to the effect "filename" is an invalid file name. and this makes no sence to me.

Any help would be greatly appreciated.
0
Comment
Question by:schworak
  • 2
4 Comments
 
LVL 3

Expert Comment

by:dansari
Comment Utility
schworak:
It's much simpler and easier to just parse the file one line at a time (with Line Input #, or just Input #FileNo, field1, field2, ..., fieldn), and add records to the .mdb file with something like:

with rs
  .Edit
  .Fields!Field1 = field1
  .Fields!Field2 = Field2
  .Update
end with

I dabbled with using the schema.ini file last year to access a text file as a database - it's poorly documented (even Microsoft admitted to that when I got them on the phone), and in my opinion it doesn't give you any advantage over a simple method like that described above.

Hope this helps.

0
 
LVL 3

Author Comment

by:schworak
Comment Utility
Due to the ability for the file requirements to change, fields to be added/removed/resized it is required that the settings be in the schema.ini so a complete recompilation and update won't be required every time this happens.

I am making some headway on solving the problem myself and have found one key item, the schema file must be in the same directory as the text file that is being read for it to do anything.

Now that I know that (which was not documented by the way) I may be able to push forward.

If someone comes up with any additional info, I would appreciate it.
0
 
LVL 5

Accepted Solution

by:
y96andha earned 100 total points
Comment Utility
OK, this is a simple sample I just wrote:


------- F:\SCHEMA.INI --------
[Text ISAM]
Extensions=none,asc,csv,tab,txt
ColNameHeader=False
Format=TabDelimited
MaxScanRows=25
CharacterSet=OEM

[TESTDB.TXT]
ColNameHeader=True
Format=Delimited(|)
MaxScanRows=0
CharacterSet=OEM
Col1=Name char width 50
Col2=Job char width 9
Col3=Feel char width 9
Col4=Good char width 9
-------------------------------------------

------- F:\TESTDB.TXT--------
Name|Job|Feel|Good|
a|b|c|d|
e|f|g|h|
-------------------------------------------

------- Code in VB Form --------
Private Sub Command1_Click()
Dim db As Database, tdb As Database
Dim tb As Recordset, rs As Recordset
Dim fld As Field, fld2 As Field
Dim td As TableDef
DBEngine.IniPath = "f:\schema.ini"
Set db = OpenDatabase("F:\Program\Microsoft Visual Basic\dbgtst.mdb")
Set tdb = OpenDatabase("F:\", False, False, "Text;")
Set rs = tdb.OpenRecordset("TESTDB")
On Error Resume Next
db.TableDefs.Delete "test"
On Error GoTo 0
Set td = db.CreateTableDef("test")
For Each fld In rs.Fields
    Set fld2 = td.CreateField(fld.Name, fld.Type)
    td.Fields.Append fld2
Next fld
db.TableDefs.Append td
Set tb = db.OpenRecordset("test")
While Not rs.EOF
    tb.AddNew
    For Each fld In rs.Fields
        tb(fld.Name) = fld
    Next fld
    tb.Update
    rs.MoveNext
Wend
rs.Close
tdb.Close
db.Close
End Sub
-------------------------------------------


Notes:

I had previously created the database "F:\Program\Microsoft Visual Basic\dbgtst.mdb" with Access 95.

Just create a form, add a command button and paste the code into the code section.  You may need to change a couple of paths.

0
 
LVL 3

Author Comment

by:schworak
Comment Utility
I did descover that the SCHEMA.INI file must be in the same directory as the text file that is being read in. If it is not, it won't work and I can't figure out what setting to change to change this. But no matter. I have the answer I needed. Thanks!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now