Solved

Import text file into an access dbase

Posted on 2001-08-27
31
355 Views
Last Modified: 2008-03-17
i have a import set in a textfile looking like this:

Avd; Number; Date; Terminal; ID; Name
31; 1; 03.08.01; 1; 100; Martin
31; 1; 08.08.01; 1; 100; Martin
31; 1; 10.08.01; 1; 100; Martin
31; 1; 10.08.01; 1; 100; Martin
31; 1; 15.08.01; 1; 100; Martin


is it possible to automaticly insert these data into an existing access table containing the same field names??

I know i can open the text file and loop trough the lines but if i add a field to the table and textfile my source code wont handle that, any ideas??????
0
Comment
Question by:haggmar
  • 11
  • 6
  • 4
  • +8
31 Comments
 
LVL 1

Expert Comment

by:jendrix
ID: 6428202
Hello haggmar,

there are Import-Wizards in Access. In Access97 go to "File" -> "Get external Data" ->"Import"

A FileOpen Dialog appears, select your file. Be sure to select the proper file extension (e.g. "textfiles")

Then the import Wizard pops up.
Select RadioButton "Delimited..."
then Button Next.
Then select "Semicolon" as the delimiter
Check "First Row Contains Field Names"
You have the choice of creating a new Table or append the data to an existing one..

You then have several Options, which fields you want, if you want to have a primary key, etc.

good luck!

Friedhelm

0
 

Author Comment

by:haggmar
ID: 6428229
I know about the import wizard in access and i use them with success, but i want to do the same operation in visual basic..
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 6428347
You actually have a CSV file with a semicolon as a separator. Try the following

This code will import into a table in Access

Dim appAccess As New Access.Application
'***********************************************************************
'************* Open Access database and transfer file to DB ************
'Must select Access 8 Object Library for Access 97 &
'Access 9 Library for Access 2000 in Project|References
'Open Database DB Path Exclusive
appAccess.OpenCurrentDatabase "Path\Some..MDB", True

' Transfer text file to database
appAccess.DoCmd.TransferText acImportDelim, , "Tablename", "Path\somefile.csv", 1

' Close
appAccess.CloseCurrentDatabase

' Set object to Nothing
Set appAccess = Nothing



0
 

Author Comment

by:haggmar
ID: 6428391
I think i must have access installed on my machine to run your code iboutchkine but i liked it anyway, do you have any other code where i dont need access installed on the computer??
0
 
LVL 27

Expert Comment

by:planocz
ID: 6428396
does your text file have line feeds of carriage return?
0
 

Author Comment

by:haggmar
ID: 6428442
nope

i have some code here which automaticly import the fieldnames and the data into a datagrid, since it is possible to do this automaticly in a datagrid there should also be possible to automaticly insert the data into a access table whitout using the datagrid??

Private Sub cmdReadTXT_Click()
      Dim obj As Read_Files.CReadFile
      Set obj = New Read_Files.CReadFile

      Set dgData.DataSource = obj.Read_Text_File
      Set obj = Nothing
End Sub


Public Function Read_Text_File() As ADODB.Recordset

      Dim rs As ADODB.Recordset
      Set rs = New ADODB.Recordset
      Dim conn As ADODB.Connection
      Set conn = New ADODB.Connection
      conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
                  "DBQ=c:\test;", "", ""

      rs.Open "select * from [test#txt]", conn, adOpenStatic, _
                  adLockReadOnly, adCmdText
      Set Read_Text_File = rs
      Set rs = Nothing
      Set conn = Nothing
End Function
0
 
LVL 27

Expert Comment

by:planocz
ID: 6428459
sorry had a type-o

does your text file have line feeds or carriage return?
0
 
LVL 27

Expert Comment

by:planocz
ID: 6428461
or is it a flat file?
0
 

Author Comment

by:haggmar
ID: 6428495
not shure what that is but here is the code exporting the data, maybe you can tell:


Private Function QueryExport(FileName As String, Query As String) As Boolean
    Dim hfileExport As Integer
    Dim Table As Recordset
    Dim Rows As Integer
    Dim FieldName As Field
    Dim FileFound As Boolean
   
    Set Table = proposdb(0).OpenRecordset(Query)
   
    Rows = Table.RecordCount
    If Rows > 0 Then
        proPosWorkspace.BeginTrans
   
        FileFound = (Dir(FileName) > vbNullString)
        hfileExport = FreeFile
        Open FileName For Append As hfileExport
   
        If Not FileFound Then
            For Each FieldName In Table.Fields
                Print #hfileExport, "; " & FieldName.Name;
            Next FieldName
            Print #hfileExport,
        End If
   
        Do
            For Each FieldName In Table.Fields
                Print #hfileExport, "; " & FieldName.Value;
            Next FieldName
            Print #hfileExport,
            Table.MoveNext
        Loop Until Table.EOF
   
        Close hfileExport
   
        proPosWorkspace.CommitTrans
        QueryExport = True
    Else
        QueryExport = True
    End If

End Function
0
 
LVL 27

Expert Comment

by:planocz
ID: 6428869
Hi haggmar,
Here is some code that might help you with working
in text files - transfer to a database.
This is just quick and dirty, but I think you will be able
to step through it.
The basic format will work on all kinds of text files.
parsing off from a line feed or carriage return.

Public Sub OpenRecSet(sSql As String)
   Set RS = New ADODB.Recordset
   RS.Open sSql, conDataBase, adOpenDynamic, adLockOptimistic, adCmdText
End Sub

Public Sub ImportTextFiles()
  Dim sRecord         As String
  Dim sDate           As String
  Dim sAvd            As String
  Dim sTerm           As String
  Dim sID             As String
  Dim sParse          As String
  Dim sBuffer         As String
  Dim iReadfile       As Integer
  Dim bFirstLine      As Boolean
 
  Screen.MousePointer = vbHourglass

  DoEvents
 
  sSql = "SELECT * FROM TextData"
  OpenRecSet (sSql)
 
  conDataBase.BeginTrans
  bFirstLine = True
 
  iReadfile = FreeFile
  Open sCurrentPath & "TextFile.txt" For Input As #iReadfile
  Do Until EOF(iReadfile)
     '******** read file into a buffer string ******************
     Select Case LOF(iReadfile) - Seek(iReadfile)
       Case 0
              If sParse = "" Then
                Exit Do
              End If
              If InStr(sParse, vbLf) = 0 Then
                Exit Do
              End If
      'This size is for a 1.44 Disc
      Case Is >= 142000
               sBuffer = Input(LOF(iReadfile) - Seek(iReadfile) + 1, iReadfile)
               sParse = sParse & sBuffer
      Case Is < 142000
              On Error Resume Next
              sBuffer = Input(LOF(iReadfile) - Seek(iReadfile) + 1, iReadfile)
              sParse = sParse & sBuffer
   End Select
    '*********** read string *******************************
   While InStr(sParse, vbCr) Or Len(sParse) > 4
       sRecord = Left(sParse, InStr(sParse, vbLf) - 1)
       sParse = Mid(sParse, InStr(sParse, vbLf) + 1)
      If bFirstLine Then
         If InStr(sRecord, "Avd") = 1 Then
            sParse = Mid(sParse, InStr(sParse, vbLf) + 1)
            bFirstLine = False
         End If
      Else
          If Len(sParse) = 2 Then Exit Do
            sAvd = Val(Mid(sRecord, 1, 10))
            sRecord = Mid(sRecord, InStr(sRecord, ";") + 1)
            sNumber = Trim(Mid(sRecord, 1, 2))
            sRecord = Mid(sRecord, InStr(sRecord, ";") + 1)
            sDate = Trim(Mid(sRecord, 1, 9))
            sDate = Left(sDate, 2) & "/" & Mid(sDate, 4, 2) & "/" & Right(sDate, 2)
            sRecord = Mid(sRecord, InStr(sRecord, ";") + 1)
            sTerm = Trim(Mid(sRecord, 1, 2))
            sRecord = Mid(sRecord, InStr(sRecord, ";") + 1)
            sID = Trim(Mid(sRecord, 1, 4))
            sRecord = Mid(sRecord, InStr(sRecord, ";") + 1)
            sName = Left(sRecord, Len(sRecord) - 1)
            sRecord = Left(sRecord, InStr(sRecord, vbLf))
                       
            RS.AddNew
            RS("Avd") = Val(sAvd)
            RS("Number") = Val(sNumber)
            RS("Date") = sDate
            RS("Terminal") = sTerm
            RS("id") = sID
            RS("Name") = sName
            RS.Update
      End If
    Wend
    Loop
 
  MsgBox "Finished Text Transfer  "
 
  conDataBase.CommitTrans
  Set RS = Nothing
  Close #iReadfile
     
  Screen.MousePointer = vbArrow
End Sub
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6428882
haggmar Question History:

Questions Asked 14
Last 10 Grades Given A A B B B B B B B B  
Question Grading Record 10 Answers Graded / 10 Answers Received

FOUR Open Questions dating back to APRIL of this year. haggmar, PLEASE maintain YOUR other Open Questions with a comment as to YOUR present status.. BEFORE.. proceeding here. Thank YOU.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6428908
If YOU can change the semi-colons in your data to commas.. this will do the trick:

1. Start a New Standard.Exe Project.
2. Add a DataGrid (DataGrid1) to Form1.
3. Set a reference (VB Menu -> Project -> References) to "Microsoft ActiveX Data Objects 2.1 Library"
4. Copy/Paste the folowwing into the Form1 code window.
5. Change strFilePath to the folder containing the CSV.
6. Change strFileName to an existing CSV file.
7. Press F5 to run. The datagrid will be filled with the CSV data
NOTE: The first line of the CSV will be used as Column Headings.

<----- Code Begin ----->

Option Explicit

Private m_cn As ADODB.Connection

Private Sub Form_Load()
 
   Dim strFilePath As String
   strFilePath = "c:\tempwork"   ' <-- CHANGE ME
   
   Dim strFileName As String
   strFileName = "test.CSV"    ' <-- CHANGE ME
   
   Set m_cn = New ADODB.Connection
   With m_cn
      .CursorLocation = adUseClient
      .ConnectionString = _
         "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
         "Initial Catalog=" & strFilePath & ";"
      .Open
   End With
   
   Set DataGrid1.DataSource = m_cn.Execute("SELECT * FROM " & strFileName)

End Sub

<----- Code End ----->



0
 

Author Comment

by:haggmar
ID: 6429133
wsh2 :
have deleted the open questions...
i think you misunderstood me, i know how to fill csv data in the datagrid, but i want the data into an access table
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6429366
A great big Thank YOU for tending to YOUR Questions.. <smile>.

Ok.. what you want to do.. can definitely be done.. (BUT, it has been a long time since I have done it.. <groan>). As soon as I get a chance, I will go back and review. Until then, check the SQL "INTO" clause in conjunction with an External database file.. and you will most certainly come to one solution.. <smile>.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6429368
Sorry.. that is "SELECT... INTO"
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6429404
hearing...
0
 
LVL 3

Expert Comment

by:Hornet241
ID: 6429473
Try this

ff = freefile
open filename for input as #ff
line input #ff,tmpTitle
Title = replace(tmpTitle, ";", ",")
do while not EOF(ff)
    line input #ff, tmpdata
    inData = replace(tmpData, ";", "', '"
    rs.open "Insert into tbname (" & Title & ") values " & _
            "('" & inData & "')",dbconn
loop
close all
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6429510
It is like riding a Bicycle.. <smile>.. hehehehehhe.. <pedal> <pedal>.. <KKKEEEEEEERRRRAAAASSSSHHHHH!>.

<----- Code Begin ----->

Option Explicit

Private m_cnCSV As ADODB.Connection

Private Sub Form_Load()
 
   Dim strFileCSV As String
   strFileCSV = "MYFILE.CSV"    ' <-- CHANGE ME
   Dim strPathCSV As String
   strPathCSV = "x:\MYFOLDER"   ' <-- CHANGE ME
   Dim strFileMDB As String
   strFileMDB = "x:\MYFOLDER\MYFILE.MDB"   ' <-- CHANGE ME
   
'  Create Connection
   Set m_cnCSV = New ADODB.Connection
   With m_cnCSV
      .CursorLocation = adUseClient
      .ConnectionString = _
         "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
         "Initial Catalog=" & strPathCSV & ";"
      .Open
   End With
 
'  Create Table
   Dim strCSV As String
   strCSV _
      = "SELECT * " _
      & "  INTO TEST_CSV_TABLE IN '" & strFileMDB & "' " _
      & "  FROM " & strFileCSV
   m_cnCSV.Execute (strCSV)
   m_cnCSV.Close

End Sub


0
 
LVL 3

Expert Comment

by:nzjonboy
ID: 6430001
<ping>
0
 

Author Comment

by:haggmar
ID: 6431038
wsh2: I like your code but my db is password protected, how will the string be then??
0
 

Author Comment

by:haggmar
ID: 6431234
wsh2: you will get my points if you can show me how to do this with a password protected db :)
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6431886
haggmar, that's not part of primary question.
0
 
LVL 3

Expert Comment

by:modder
ID: 6432655
haggmar,

Your question was answered. You should now award the points.

Your question about passport protection is a new question, really. You can open a new question about that, or, perhaps, offer to increase the points for further information.

Regards

modder
Community Support
0
 

Author Comment

by:haggmar
ID: 6435525
100 points for giving answer about the password protected DB
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6438142
haggmar, please, post a new question and grade this one.
;
Cheers
0
 
LVL 14

Accepted Solution

by:
wsh2 earned 100 total points
ID: 6439152
Sorry for the delay.. have been spending my time filling trash cans with paper.. <sheesh> and <lol>.

=========================================================
First, do me a favor and give this a try:

Change:

  '  Transfer Excel Spreadsheet Into An Access Table
  Dim strSQL As String
  strSQL _
     = "SELECT * " _
     & "  INTO " & strAccessTable & " " _
     & "    IN " & """" & strAccessPath & """ " _
     & "  FROM [" & strExcelSheet & "] "
  m_cnExcel.Execute strSQL
 
To:

  '  Transfer Excel Spreadsheet Into An Access Table
  Dim strSQL As String
  strSQL _
     = "SELECT * " _
     & "  INTO " & strAccessTable & " " _
     & "    IN " & """" & strAccessPath & """ " _
     & "  FROM [" & strExcelSheet & "] " _
     & "  WITH OWNERACCESS OPTION "
  m_cnExcel.Execute strSQL

I've never used the "WITH OWNERACCESS OPTION" clause, but it is SUPPOSED to allow YOU to do exactly
what you want.

=========================================================
Option 2.. is to switch things around. Instead of making the initial connection to the Excel Spreadsheet,
we are going to reverse things, connect to the MDB (where you can specify an UserId/Password) and then
pull the Excel Spreadsheet into it. This one WILL work.

1. Start a New Standard.Exe project.
2. Add a Reference (VB Menu -> Project -> References) to "Microsoft ActiveX Data Objects 2.5 Object
Library"
3. Add a DataGrid (VB Menu -> Project -> Components) "Microsoft DataGrid Control" to Form1.
4. Copy / Paste the following into the Form1 code window.
5. Change the strXXX_Path, strXXX_Table and strUserXXXX variables to fit your environment.
6. Press F5 to run. The Excel Spreadsheet will be OVERLAYED into the database and then displayed in
the datagrid.

*  NOTE: This is set up to use Excel v8.0 Spreadsheets

<----- Code Begin ----->

Option Explicit

Private m_cnMDB As ADODB.Connection
Private m_rsMDB As ADODB.Recordset

Private Sub Form_Load()
 
  Dim strSQL As String
  Dim strMDB_Path As String: strMDB_Path = "x:\myFolder\myFile.MDB" ' <- CHANGE ME
  Dim strMDB_Table As String: strMDB_Table = "MyNewTable" ' <- CHANGE ME
  Dim strXLS_Path As String: strXLS_Path = "x:\myFolder\myFile.XLS" ' <- CHANGE ME
  Dim strXLS_Table As String: strXLS_Table = "Sheet1$" ' <- CHANGE ME
  Dim strUserName As String: strUserName = "admin" ' <- CHANGE ME
  Dim strUserPass As String: strUserPass = "" ' <- CHANGE ME
'  Existence Tests
  If Dir(strMDB_Path) = "" Then
     MsgBox "FILE " & LCase(strMDB_Path) & " NOT FOUND!!!"
     Exit Sub
  End If
  If Dir(strXLS_Path) = "" Then
     MsgBox "FILE " & LCase(strXLS_Path) & " NOT FOUND!!!"
     Exit Sub
  End If
 
'  Establish Connection
  Set m_cnMDB = New ADODB.Connection
  With m_cnMDB
     .ConnectionString = strMDB_Path
     .CursorLocation = adUseClient
     .Provider = "Microsoft.Jet.OLEDB.4.0"
     .Open , strUserName, strUserPass
  End With
 
'  If Table Exists, Delete it
  On Error Resume Next
  strSQL = "DROP TABLE [" & strMDB_Table & "] "
  m_cnMDB.Execute strSQL
  On Error GoTo 0

'  Transfer Excel Spreadsheet Into An Access Table
  strSQL _
     = "SELECT * INTO [" & strMDB_Table & "] " _
     & "  FROM [" & strXLS_Table & "] " _
     & "    IN """ & strXLS_Path & """ ""Excel 8.0;""; "
  m_cnMDB.Execute strSQL
 
'  Display Access Table
  Set m_rsMDB = New ADODB.Recordset
  strSQL = "SELECT * FROM [" & strMDB_Table & "] "
  Set m_rsMDB = m_cnMDB.Execute(strSQL)
  Set DataGrid1.DataSource = m_rsMDB
 
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

'  Disconnect DataGrid
  Set DataGrid1.DataSource = Nothing
 
'  Destroy Recordset
  If Not m_rsMDB Is Nothing Then
     With m_rsMDB
        If .State = adStateOpen Then .Close
        Set m_rsMDB = Nothing
     End With
  End If

'  Destroy Connection
  If Not m_cnMDB Is Nothing Then
     With m_cnMDB
        If .State = adStateOpen Then .Close
        Set m_cnMDB = Nothing
     End With
  End If

End Sub

<----- Code End ----->
0
 

Author Comment

by:haggmar
ID: 6439525
wsh2: remember I use a csv, txtfile for importing into the table, not an excel spreadsheet, i have modified your code here, not mutch left before working with a csv file i hope?? I tried changing the Excel 8.0 text to Microsoft Text Driver, but that didnt work, err message cant find installable isam

Option Explicit

Private dBase As New ADODB.Connection
Private m_rsMDB As ADODB.Recordset

Private Sub Form_Load()

Dim strSQL As String
Dim strTable As String: strTable = "TestX" ' <-  Dim strTxtPath As String: strTxtPath = "c:\pos\test.txt"
 Dim strTxtName As String: strTxtName = "test.txt" ' <- CHANGE ME

 With dBase
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=c:\pos\db.mdb" & _
    ";Jet OLEDB:Database Password=1234"
    .Open
 End With

 On Error Resume Next
 strSQL = "DROP TABLE [" & strTable & "] "
 dBase.Execute strSQL
 On Error GoTo 0


 strSQL _
    = "SELECT * INTO [" & strTable & "] " _
    & "  FROM [" & strTxtName & "] " _
    & "    IN """ & strTxtPath & """ ""Excel 8.0;""; "
 dBase.Execute strSQL
End Sub
0
 
LVL 7

Expert Comment

by:q2eddie
ID: 6440653
Hi, haggmar.

NOTE: Do not select this as the answer.  I'm trying to help out.

#Try This

>but that didnt work, err message cant find installable
>isam

1. Registering your text ISAM driver

If you are using Win9x, then ...
a. Open a DOS box.
b. Go to windows\system
c. dir mstext*.dll
You will probably get two files: mstext35.dll and mstext40.dll.

d. type regsvr32 mstext35.dll
I had to do this on my WinNT PC recently to get my text/csv code to work.  If this doesn't work, then register the mstext40.dll instead.

If you are using WinNT, then ...
same thing but go to c:\winnt\system32 instead.

2. Writing the SQL Statement

' Transfer CSV Into An Access Table
strSQL _
   = "SELECT * INTO [" & strTable & "] " _
   & "  FROM " & strTxtName

where ...
strTable = "<name of your table in the mdb>"
strTxtName = "[Text;DATABASE=c:\pos\;DriverID=27;].[test.txt]"

Bye. -e2
0
 

Author Comment

by:haggmar
ID: 6469873
Thank you wsh2, the answer you gave is close enough, I will give another 100 points for them telling me how to change this code to import txtfile instead of excel file

 SQLQuery = "SELECT * INTO [" & strMDB_Table & "] " & "FROM [" & FilNavn & "] " _
    & "IN """ & strXLS_Path & """ ""Excel 8.0;""; "
0
 

Author Comment

by:haggmar
ID: 6469877
thanks for the answer close enough, I will give another 100 points for then telling me how to convert this code to import txtfiles instead of excel spreadsheet


 SQLQuery = "SELECT * INTO [" & strMDB_Table & "] " & "FROM [" & FilNavn & "] " _
    & "IN """ & strXLS_Path & """ ""Excel 8.0;""; "
0
 

Expert Comment

by:Dabuynu_Gom_Dere
ID: 7591180
planocz should have gotten the points.. he is not bullshiting i checked his code, and it real.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

757 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

18 Experts available now in Live!

Get 1:1 Help Now