Solved

read fixed width text  into a database, appending the file name to the begining of each line of text.

Posted on 2004-08-04
26
4,567 Views
Last Modified: 2008-02-20
I have over 40,000 files that are notes for customers accounts,  each files name is the name of the account.
 for example:  " N47600.txt"  i need to exstract the file name  and  append that to each line of data contained in each file as they are read into an access database (ms access 2000).the file contents are as follows:

NAA       09-08-2003 14:38 53                                                
NAE2      11-20-2003 17:49 68                                                
NAM3      12-11-2003 08:20 53                                                
NAA4      12-19-2003 15:36 65                                                
NAA5      12-30-2003 08:04 32                                                
MA        01-13-2004 16:57 65  old RD 1 BOX 156                              
MA        01-13-2004 16:57 65  new 2774 HWY ROUTE 20                          
$$        01-13-2004 17:01 65  Order #84891  

The width is  as follows data element 1 is 10 char wide, the second data element is starting at position 11 is 11 wide, the next  bit is the time and started at position 21 and is 6 wide, and the next field starts at 28 and  is 3 wide, the last field is  a note starting a 31 and  could ne upto 255 wide.

Main question: can I read a file name the  take the file name and append to each line of data as it is read into a database  and continuing though the entire folder containg these files and  have a singe database with all the contents of the files  the final data would look like the following:

N47600 NAA       09-08-2003 14:38 53                                                
N47600 NAE2      11-20-2003 17:49 68                                                
N47600 NAM3      12-11-2003 08:20 53                                                
N47600 NAA4      12-19-2003 15:36 65                                                
N47600 NAA5      12-30-2003 08:04 32                                                
N47600 MA        01-13-2004 16:57 65  old RD 1 BOX 156                              
N47600 MA        01-13-2004 16:57 65  new 2774 HWY ROUTE 20                          
N47600 $$        01-13-2004 17:01 65  Order #84891  

ect,.. with  the next file read the data would  continue.

 I apreciate any help you can provide. Thank you !


0
Comment
Question by:danlooper
  • 9
  • 7
  • 5
  • +3
26 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 11716234
have a look at import specifications, u can use them to import fixed width files

the command in VBA then is to use

DoCmd.TransferText acImportFixed,"specname","tablename","filename","trueiffieldnames"

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11716265
here is some old saved comments of mine, it refers to csv, but same applies to txt files


An import/export specification contains information, such as file format, date order, or number formats, that Microsoft Access uses to import or export a fixed-width or delimited text file. An import/export specification is stored with the default name: Filename_ImportSpec or Filename_ExportSpec in the database that you import to or export from.

You create an import/export specification by using the Import Text Wizard or by using the Export Text Wizard. On the first screen of either wizard, click Advanced, define the specification, and then click Save As. Click the Specs button to select the import/export specification the next time you use either wizard.

Use an import/export specification when you want to repeatedly import to the same table, export to the same file, or automate the import or export process.

Alternatively, you can use a Schema.ini file in a Visual Basic program to provide even more control over data in the text file, such as specifying special currency formats or handling floating point data types. A schema.ini file is a text file containing entries that override default text driver settings in the Windows Registry. You store a schema.ini in the same folder as the imported or exported text file, and it must always be named schema.ini.




Say I have a table with two fields

ID (AutoNumber)
Desc (String)

Have a spreadsheet with one column titled Description
I inserted a blank column at the start and titled it ID
(this matches table)
Saved this as a CSV file

In MSAccess, go to Get External Data/Import, select my CSV spreadsheet,
Selected Delimited, clicked on Next the selected Comma
At this point I clicked on Advanced
Since my CSV file has two fields, its auto calls it Field1, and Field2
I renamed it to be my fields in the DB file (ID, Txt)
then I selected Skip on ID (first field)
clicked on SaveAs and called it XXXX


now that is my specification saved


I then aborted this import (but you can carry on if you choose to do so)


How I loaded it in code is as follows

    DoCmd.TransferText acImportDelim, "XXXX", "<tableName>", "C:\Imp.csv", True

where C:\Imp.csv is the name of my csvfile
True specifies whether csv file has heading or not

Public Sub SpecS()
    DoCmd.TransferText acImportDelim, "XXXX", "Import", "C:\Imp.csv", True
End Sub
0
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 11716575
Docmd.Transfertext will not append the file name into a field in a table

try:

Create table tblImport
strFile string 25
strdataelement string 10
strsecondelement string 11 wide
etc..

Public sub ImportFile(pstrFolder as string)
'will import all .TXT files in folder
dim strFile as string

strFile = dir(pstrFolder & "*.txt")
do while len(strFile) >0
     TransferFile pstrFolder , strfile
     strfile = dir()
loop
end sub

private sub TransFerFile (pstrFolder as string, pstrFile as string)
'import contents of file into table
dim intFile as integer
dim strLine as string
dim recInput as dao.recordset

set recInput = currentdb.openrecordset("tblInput",dbopendynaset)
intfile = freefile
open pstrfolder & pstrfile for input as intFile
Do While Not EOF(intFile)   ' Loop until end of file.
   Input #intFile, strLine' Read data into single variable.
   with recInput
    .addnew
   !StrFile = left(pstrfile,instr(1,pstrfile,".txt")-1)
   !strDataElem = mid(strline,1,10)
  !strDatElem2 = mid(strline,11,11)
...etc
   .update
end with
Loop
Close #intFile   ' Close file
recInput.close
end sub

Richard


0
 
LVL 44

Expert Comment

by:GRayL
ID: 11716751
Assume all the files are in one directory and are all txt. None have a fieldname header. You have generated a table with all the correct fields including the fldname as the last field. Add this subroutine to your DB module

Private Sub ImportFiles()
dim fname as string
set mydb = currentdb
fname = dir(drive:\mypath\firstfilenametoimport.txt)
do while fname <> ""
  docmd.transfertext acImportFixed,,"MyBigTable","drive:\mypath\" & fname,no
  fname=left(fname,instr(fname,".txt")-1)
  docmd.runSQL "Update MyBigTable set fldname= '" & fname & "' Where fname is  null;"
  dir
loop
End Sub

In the immediate pane of the VB Editor (Ctl-G) type:
ImportFiles
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11716774
Sorry. Change:

docmd.runSQL "Update MyBigTable set fldname= '" & fname & "' Where fname is  null;"

To:

docmd.runSQL "Update MyBigTable set fldname= '" & fname & "' Where fldname is  null;"

0
 

Author Comment

by:danlooper
ID: 11717463
I have looked over the informaiton provided there is a ton of knowledge out there,  Thank you,  I have looked over GRayl's entry  it make sense but i still have  a problem.

received an error when i ran the code :error as follows,

Compile error
sub or function not defined

I entered the code as follows ( i do not do very much VBA stuff at all)

Private Sub ImportFiles()
Dim fname As String
Set mydb = CurrentDb
fname = Dir("C:\FILE\N10007.txt")
Do While fname <> ""
  DoCmd.TransferText acImportFixed, , "MyBigTable", "drive:\mypath\" & fname, no
  fname = Left(fname, InStr(fname, ".txt") - 1)
  DoCmd.RunSQL "Update MyBigTable set fldname= '" & fname & "' Where fldname is  null;"
  Dir
Loop
End Sub

These files are on "C:\" in folder names "file"there are 40,000 + files in this folder, and the routing will need to read all files names and data into a single database. ie.. N47600.txt, N10007.txt, N23456.txt, N10765.txt ect...

My question do need to create the databse MyBigTable first , or should I import one manually first the use this to update or appende to the table ?

did I enter the directory info right as well. ?

Thank you.

 
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11717578
try this
you need to add to your references
Microsoft DAO x.x Object Library     >>set to the highest allowable position by clicking the UP arrow
Microsoft Office xx Object Library


Private Sub cmdImport_Click()
Dim strFileName As String, sTableName As String, strPath As String
Dim i As Integer, db As DAO.Database
Dim fs As Object
Set fs = Application.FileSearch
With fs
    .LookIn = "C:\TestText"
    .fileName = "*.txt"
    If .Execute(SortBy:=msoSortbyFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
strPath = .FoundFiles(i)
strFileName = Dir(strPath)
sTableName = Left$([strFileName], InStr(1, [strFileName], ".") - 1)

DoCmd.TransferText acImportFixed, "savedimportspec", "" & sTableName & "", strPath, False

     '  Debug.Print .FoundFiles(i)
     '  Debug.Print sTableName
 Set db = CurrentDb
 Dim uSql As String
    'alter imported table adding the column NameOfFile
        db.Execute ("Alter Table " & sTableName & " Add Column NameOfFile Text(25)"), dbFailOnError
    'update imported table
        db.Execute ("Update " & sTableName & " set NameOfFile= '" & sTableName & "'")
    'append imported table to ImpTextTable
        uSql = "INSERT INTO ImpTextTable ( LastName, FirstName, RecID, SSN, [Date], NameOfFile ) "
        uSql = uSql & "SELECT " & sTableName & ".LastName, " & sTableName & ".FirstName, " & sTableName & ".RecID, " & sTableName & ".SSN, " & sTableName & ".Date, " & sTableName & ".NameOfFile "
        uSql = uSql & "FROM " & sTableName & ""
        db.Execute uSql, dbFailOnError
    'delete imported table
        db.TableDefs.Delete "" & sTableName & ""

        Next i
    Else
        MsgBox "There were no files found."
    End If
End With

End Sub
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11717605
also you need to create the table where you are going to dump all the records with corresponding field names.



change the field names as suited

        uSql = "INSERT INTO ImpTextTable ( LastName, FirstName, RecID, SSN, [Date], NameOfFile ) "
        uSql = uSql & "SELECT " & sTableName & ".LastName, " & sTableName & ".FirstName, " & sTableName & ".RecID, " & sTableName & ".SSN, " & sTableName & ".Date, " & sTableName & ".NameOfFile "

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11717727
the correction to GRayL's code is the Dir statement to read the next fiel

where u have the line

  Dir

it should be

fname = Dir
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11717749
u should still look into specifications though, u want to ensure the data imported is correct
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11718202
thanks rocki
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 11718282
no probs, Im off home now, so since u got him/her going, u might as well polish it off

have a good day

0
 

Author Comment

by:danlooper
ID: 11718919
Where do I find the reference at in MS access 2000 or is this in VB6.0

you need to add to your references
Microsoft DAO x.x Object Library     >>set to the highest allowable position by clicking the UP arrow
Microsoft Office xx Object Library

thank you for your help !


Dan

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11719057
From your VB Editor window

Tools>References

scroll down and look for the ref youwant
0
 

Author Comment

by:danlooper
ID: 11719068
thankyou will try now.

Dan
0
 

Author Comment

by:danlooper
ID: 11720329
Received an error after runnig the code

run-tim error 3625

The Text file specification 'savedimportspec'does not exist. you cannot import, export, or link using the specification.

code modified as followed:

.LookIn = "C:\FILE"

uSql = "INSERT INTO ImpTextTable ( result, date, time, salesman, notes, NameOfFile ) "
        uSql = uSql & "SELECT " & sTableName & ".result, " & sTableName & ".date, " & sTableName & ".time, " & sTableName & ".salesman, " & sTableName & ".notes, " & sTableName & ".NameOfFile "

It does count the number of files just fine then has a OK msg box then the error was presented.

any suggestions ?
0
 

Author Comment

by:danlooper
ID: 11720739
the Debug  is bringup this line of code as the error point


DoCmd.TransferText acImportFixed, "savedimportspec", "" & sTableName & "", strPath, False

Dan
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11721107
first;
the error is coming from this

DoCmd.TransferText acImportFixed, "savedimportspec", "" & sTableName & "", strPath, False
--------------------------------------------^^^^^^^^^^

the savedimportspec is the import specification i created when i export the first file by

File>Get External Data>Import
on the File of Types
select TextFiles
Select any of the text file you want to import and click import
the Import Text Wizard window will open
on the First page select Fixed Width click Next
check if the break lines are not mixing data from fields adjust as necessary by double clicking click Next when done
Select In a New Table Click Next
Rename Fields with correct field names, change data type accordingly , select another field and  click Next when done
Select No Primary Keys Click Next
****Very Important***
on this Window where it is asking the name of table, just Click Advanced
Click Saved As
Give the Specification a name  *** this will be the name you will use here
--------------------------------------------vvvvvvvvvvvvvv
DoCmd.TransferText acImportFixed, "savedimportspec", "" & sTableName & "", strPath, False


0
 

Author Comment

by:danlooper
ID: 11725953
Note  to capricorn1

Now I didnt know that I could do that,  saving a import specification to be called again, that is awsome!

I did what you suggested and the  system responded with an error msg.

" db.Execute uSql, dbFailOnError"

 when I moved the mouse over it  the dbfailonerror=128

this is a great help!
 Thanks

Dan
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11726421
copy and paste the whole code you are using for this event.
0
 

Author Comment

by:danlooper
ID: 11726792
Option Compare Database
Private Sub Command0_Click()
Dim strFileName As String, sTableName As String, strPath As String
Dim i As Integer, db As DAO.Database
Dim fs As Object
Set fs = Application.FileSearch
With fs
    .LookIn = "C:\FILE"
    .fileName = "*.txt"
    If .Execute(SortBy:=msoSortbyFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
strPath = .FoundFiles(i)
strFileName = Dir(strPath)
sTableName = Left$([strFileName], InStr(1, [strFileName], ".") - 1)

DoCmd.TransferText acImportFixed, "savedimportspec", "" & sTableName & "", strPath, False

     '  Debug.Print .FoundFiles(i)
     '  Debug.Print sTableName
 Set db = CurrentDb
 Dim uSql As String
    'alter imported table adding the column NameOfFile
        db.Execute ("Alter Table " & sTableName & " Add Column NameOfFile Text(25)"), dbFailOnError
    'update imported table
        db.Execute ("Update " & sTableName & " set NameOfFile= '" & sTableName & "'")
    'append imported table to ImpTextTable
        uSql = "INSERT INTO ImpTextTable ( results, date, time, salesman, notes, NameOfFile ) "
        uSql = uSql & "SELECT " & sTableName & ".results, " & sTableName & ".date, " & sTableName & ".time, " & sTableName & ".salesman, " & sTableName & ".notes, " & sTableName & ".NameOfFile "
        uSql = uSql & "FROM " & sTableName & ""
        db.Execute uSql, dbFailOnError
    'delete imported table
        db.TableDefs.Delete "" & sTableName & ""

        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
End Sub
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 11727080

is the ImpTextTable the name of your table?  if not change to name of your table

also avoid using reserved words as name of your fields  you have date, time.


       uSql = "INSERT INTO ImpTextTable ( results, [date], [time], salesman, notes, NameOfFile ) "
        uSql = uSql & "SELECT results, [date], [time], salesman, notes, NameOfFile "
        uSql = uSql & "FROM " & sTableName & ""
        db.Execute uSql, dbFailOnError
0
 

Author Comment

by:danlooper
ID: 11727322
Good point didn't think about reserve words.

I renamed date to rdate and time to rtime,

I verified the destination table as the ImpTextTable and it is setup with the same fields and  data types as the import specification.

Worked 100% , Thank you so very much, You ROCK !!!!

Dan



0
 

Author Comment

by:danlooper
ID: 11727782
I have 1 additional question ,

I was testing with 10 files and works awsome,

when i pulled the files over to  import into the datebase i received an erro , but i think i have the solution.

The program first part told me there was 56994 records to import,  I then click ok a but shortly after that receive an error, " run-time error 6    Overflow"

 the debug pointed to this line of code

"For i = 1 To .FoundFiles.Count"

looking at the variable defined above "Dim i As Integer"

I think  that we have exceeded the limit of the Integer and need to use a long or some other data type.

I would apprciate your feedback on this  final question?

Thank

Dan
 
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11727835
try

dim i as long
0
 
LVL 1

Expert Comment

by:weeb0
ID: 12137339
I would like to know what is in the savedimportspec ... is it a string, a file?  I would like to do this in VB6.

thanx
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 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