?
Solved

text (.txt) file to access(.mdb) file converter

Posted on 2003-03-18
9
Medium Priority
?
362 Views
Last Modified: 2006-11-17
I like to create a new Access database [lager.mdb] with a table name [Internet] from a user selected txt file using VB this code will be running on a PC without Access installed, I'm new into VB & MDB can someone give me some help?

If possible I also like to be able start the ascii to mdb convertion with a commandline parameter.
Maybe a VBS "Visual Basic Script" program is a better solution, you tell me :)


This is how my txt looks like.

Artnr     Benam     Annanben     Kortnamn     Artgrp     Enhet     Dokument     Anm1     Pris1     ilager
beg-AC-10     Insida/Utsida Lår     SABA     SABA     Styrketräningsutrustning     st          Ben     8 500,00     1,0000
beg-AC-11     Motionscykel MPK     MPK     MPK     Motionscykel     st          Kondition     8 800,00     1,0000
beg-AC-16     Bicepsmaskin-75 kg     World Class     World Class     Styrketräningsutrustning     st          Armar     8 000,00     1,0000
beg-AC-26     Trappmaskin Alpine-450          Alpine     Trappmaskin     st          Kondition     17 500,00     1,0000
beg-AC-30     Hacklift     Akema     Akema     Rehab-utrustning     st          Ben     4 500,00     1,0000
beg-AC-31     Löpband Challenger          Challenger     Löpband     st          Kondition     22 000,00     1,0000
beg-AC-34     Roddyna     Casall     Casall     Styrketräningsutrustning     st               800,00     1,0000
beg-AC-36     Hiss-50 kg     SABA     SABA     Rehab-utrustning     st          Rygg     2 500,00     1,0000




This is the code I have today but it's missing the txt import stuff...

      Dim cat As ADOX.Catalog
      'Dim tbl As ADOX.Table
      Dim con As ADODB.Connection
      Dim db_name As String
      Dim strSQL As String

      With OpenFileDialog1 ' Ask for new file location.
          .Title = "Select the file you like to Import"
          .Filter = "All(*.*)|*.*"
          .FilterIndex = 1
          .ShowDialog()
          If Len(.FileName) > 0 Then

              db_name = "lager.mdb"

              ' ---------------------------------------------------------------
              ' Delete the database if it exists.
              ' ---------------------------------------------------------------
              On Error Resume Next
              If Dir(db_name) <> "" Then Kill(db_name)
              On Error GoTo 0

              ' ---------------------------------------------------------------
              ' Create the new database.
              ' ---------------------------------------------------------------
              cat = New ADOX.Catalog()
              cat.Create( _
                  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & db_name & ";")

              ' ---------------------------------------------------------------
              ' Connect to the database.
              ' ---------------------------------------------------------------
              con = cat.ActiveConnection

              ' ---------------------------------------------------------------
              ' Connect to the txt file.
              ' ---------------------------------------------------------------
              ' ???

              ' ---------------------------------------------------------------
              ' Create Table [Internet] and import the data from the txt file.
              ' Field names are in the first row in the txt file.
              ' There are two empty rows in the end of the txt file.
              ' ---------------------------------------------------------------
              ' ???

              ' ---------------------------------------------------------------
              ' Create the primary key [Nr] and make it autonr.
              ' ---------------------------------------------------------------
              ' ???

              ' ---------------------------------------------------------------
              ' Create the Index's.
              '
              ' Artnr = No dup.
              ' Benam = dup. OK
              ' Kortnamn = dup. OK
              ' Artgrp = dup. OK
              ' Anm1 = dup. OK
              ' ---------------------------------------------------------------
              ' ???
             
              ' ---------------------------------------------------------------
              ' Close the database connection.
              ' ---------------------------------------------------------------
              con.Close()
              con = Nothing
              'tbl = Nothing
              cat = Nothing

              MsgBox("Import Done.", vbInformation)
              Else
                  'MsgBox("Cancel!", vbCritical)
                  Exit Sub
              End If

      End With
0
Comment
Question by:PeOd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 8158700
Have you tried playing around with the TransferText method?  
0
 
LVL 28

Expert Comment

by:TextReport
ID: 8161182
As PsychoDazey says you can try the TransferText option with a saved Import/Export Filter alternatively you can use the Open command to open the text file and loop through the file yourself.

But the answeer to your actul question is YES

RunCommand acCmdImport

and the user then has to find the file etc.

Cheers, Andrew
0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 8161405
Here is the syntax for the TransferText method I referred to:
DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename][, codepage]

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PeOd
ID: 8165386
This convertion util is executed on a PC without MS Access installed, So I don't think that I can use the TransferText method.
0
 
LVL 1

Accepted Solution

by:
AceDefabul earned 2000 total points
ID: 8166333
The following code should provide you with the missing parts. As for command line parameters, look up the Command function provided by Visual Basic.

 ' ---------------------------------------------------------------
      ' Connect to the txt file.
      ' ---------------------------------------------------------------
       
       Dim F1 As Long
       F1 = FreeFile
       Dim sThisLine As String
       
       'open the txt file
       'substitute the correct name for Import.txt"
       Open "Import.txt" For Input As #F1
       'get the column names from first line of Import file
       If Not EOF(F1) Then
         Line Input #F1, sThisLine
       End If
       
           
      ' ---------------------------------------------------------------
      ' Create Table [Internet] and import the data from the txt file.
      ' Field names are in the first row in the txt file.
      ' There are two empty rows in the end of the txt file.
      ' ---------------------------------------------------------------
      Dim vColumnNames As Variant
      Dim i As Integer
     
      'split the data fields using 5 space characters as the delimiter
      'I tried vbTab, it didn't work with cutting and pasting the example
      'data from your message.
     
      vColumnNames = Split(sThisLine, "     ")
      Set tbl = New ADOX.Table
      'add table to database
      tbl.Name = "Internet"
      cat.Tables.Append tbl
     
      'add the columns to the table
      For i = 0 To UBound(vColumnNames)
        'add column (name, data type, length)
        tbl.Columns.Append vColumnNames(i), adVarWChar, 65
      Next
             
      ' ---------------------------------------------------------------
      ' Create the primary key [Nr] and make it autonr.
      ' ---------------------------------------------------------------
     
      Dim col As ADOX.Column
      Set col = New ADOX.Column
      With col
        .Name = "Nr"
        .Type = adInteger
      End With
      Set col.ParentCatalog = cat
      col.Properties("Autoincrement") = True
      tbl.Columns.Append col
     
      Set idx = New ADOX.Index
      With idx
        .PrimaryKey = True
        .Name = "PrimaryKey"
        .IndexNulls = adIndexNullsDisallow
        .Unique = True
        .Columns.Append "Nr"
      End With
      tbl.Indexes.Append idx
      Set idx = Nothing
      ' ---------------------------------------------------------------
      ' Create the Index's.
      '
      ' Artnr = No dup.
      ' Benam = dup. OK
      ' Kortnamn = dup. OK
      ' Artgrp = dup. OK
      ' Anm1 = dup. OK
      ' ---------------------------------------------------------------
      Set idx = New ADOX.Index
      With idx
        .Name = "idxArtner"
        .Unique = True
        .Columns.Append "Artnr"
      End With
      tbl.Indexes.Append idx
     
      Set idx = New ADOX.Index
      With idx
        .Name = "idxBenam"
       ' .Unique = False
        .Columns.Append "Benam"
      End With
      tbl.Indexes.Append idx
                   
      Set idx = New ADOX.Index
      With idx
        .Name = "idxKortamn"
        '.Unique = False
        .Columns.Append "Kortnamn"
      End With
      tbl.Indexes.Append idx
     
      Set idx = New ADOX.Index
      With idx
        .Name = "idxArtgrp"
        '.Unique = False
        .Columns.Append "artgrp"
      End With
      tbl.Indexes.Append idx
     
      Set idx = New ADOX.Index
      With idx
        .Name = "idxAnm1"
        '.Unique = False
        .Columns.Append "Anm1"
      End With
      tbl.Indexes.Append idx
     
      Set idx = Nothing
     
      ' ---------------------------------------------------------------
      ' import the data from the txt file.
      ' There are two empty rows in the end of the txt file.
      ' ----------------------------------------------------------
      Dim vDataItems As Variant
      Dim rsTable As ADODB.Recordset
      Set rsTable = New ADODB.Recordset
      rsTable.Open "Internet", con, adOpenDynamic, adLockOptimistic
     
      While Not EOF(F1)
        Line Input #F1, sThisLine
        If Len(sThisLine) Then
          vDataItems = Split(sThisLine, "     ")
          rsTable.AddNew
          For i = 0 To UBound(vDataItems)
            rsTable(i) = vDataItems(i)
          Next
          rsTable.Update
        End If
      Wend
     
      rsTable.Close
      Set rsTable = Nothing
      Close #F1
     
0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 8166398
Could you use the runtime version of access?
0
 
LVL 28

Expert Comment

by:TextReport
ID: 8168011
So why did you post the question in the access area if you are not actually using Access.

What are you running, if it is Access Runtime then TransferText will work. If VB rather that Access then you should post the question in the VB forum.

Cheers, Andrew
0
 
LVL 2

Expert Comment

by:HobsonT
ID: 8705497
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area:
Accept AceDefabul 's answer
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
Trevor
EE Cleanup Volunteer
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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