[AccessD] Import Fixed Length Data

This is a script I wrote to write values out from one table in Acc

DoCmd.RunSQL "DELETE tblDocumentsStdApp.txtFiles FROM
tblDocumentsStdApp;  ", 0
Set txtfile = Nothing
Set objFSO = CreateObject("scripting.filesystemobject")
This routine may help.  We have an application that users submit to us
with various files - word, excel,....  I read the file names from the
drive, write those names to a .txt file and then import each one into a
table to store in the database.


strsource = "C:\_DEIMS_SQL\StdApp"
strDest = strsource
filepath = strDest & "\filenames.txt"
Set txtfile = objFSO.OpenTextFile(filepath, 2, True, False)
Set objFolder = objFSO.GetFolder(strsource)
Set colFiles = objFolder.Files

For Each objFile In colFiles
If objFile.Name <> "filenames.txt" Then
    If InStr(objFile.Name, ".ldb") = 0 Then
        strFile = objFile.Name
        strSourcePath = strsource & "\" & strFile
        strDestPath = strDest & "\" & strFile
        objFSO.CopyFile strSourcePath, strDestPath
        txtfile.WriteLine (strFile)
        End If
  End If
Next
Dim db As dao.Database
Set db = CurrentDb
Dim rs As Recordset
Dim varStr As String
Set txtfile = Nothing

Set rs = db.OpenRecordset("tblDocumentsStdApp", dbOpenDynaset)
Set txtfile = objFSO.OpenTextFile(filepath, 1, True, False)
While txtfile.AtEndOfStream = False
    varStr = txtfile.ReadLine()
    rs.AddNew
    rs!txtFiles = varStr
    rs.Update
Wend

DoCmd.SetWarnings True 


Karen S. Long
Programmer Analyst
EG&G Technical Services, Inc.
Pittsburgh, PA
Phone: 412-386-6649
Email: cyx5 at cdc.gov


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos
Sent: Monday, April 24, 2006 12:33 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Import Fixed Length Data

You need to write your own import reading the text file with regular VB
I/O statements. Former list regular Seth Galitzer has a demo posted in
the downloads section at his website under FILE IO in VB

http://puma.agron.ksu.edu/~sgsax/

Gary



On 4/24/06, Reuben Cummings <reuben at gfconsultants.com> wrote:
> I have a LONG text file that I need to extract into an Access table.
>
> Every record is five lines so the Access import system will not work.

> The field descriptions are also part of the data which is no big deal.

> I can import the field descriptions and then delete those fields
later.
>
> What I am after is...Does anyone have a neat fixed-length import 
> system that will handle multiple lines per record?
>
> If not, how do I jump to the next line after I finish with one?
>
> Can I read one entire line into a string and then manipulate that?  Or

> analyze it for certain data in order to handle the entire line
differently?
>
> Thanks.
>
> Reuben Cummings
> GFC, LLC
> 812.523.1017
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


--
Gary Kjos
garykjos at gmail.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com