🚀 go-pugleaf

This May be an option:
psuedo code

Open text file 
Open table 

add new record.
get line from file
put line in first field
get line from file
put line in second field

start over at the add record.


sorry about the sloppy responce. but you should get the idea.
if there are more than 2 line for each record just keep adding gets
until you complete the record

there may be a way to do your multi line import but I dont know how.


Reply to XXXXdudley@mail.auburn.net
Remove XXXX to reply

I had a similar task to perform. I couldn't find a way to import multi-line
records so I ended up writing some VB code to do it. This seems to work
quite well and runs at an acceptable speed.

Simon

John, you have two approaches here. You could write a small basic program to
read two lines, and concatenate them together in to one line, and then write
this line out to a new text file. At that point you could then use and setup
the import wizard (alone with a saved spec). This method is good, since much
of the work involved in an import is "lining up" the columns in the text
file to fields in a table. Access in the above case can then do most of the
dirty work for you.

The 2nd approach would involve reading each line, and use basic to "parse"
the code, and then add a record after reading two lines.

Anyway...to read a text file in basic you do the following:

   Dim OneLine As String
   Dim fref As Integer

   fref = FreeFile()
   Open "c:\mytext.txt" For Input As fref

   Do While Not (EOF(fref))
      Input #fref, OneLine
      rem do what ever you want.......example : dispaly the line we just
read
      MsgBox (OneLine)
   Loop

close fref

Either modify the above to read 2 lines, and then write out to a new file,
or use the code to write records to a mdb as below.  If you put the two
together you can do what you want.

Dim MyDb As Database
Dim MyTable As Recordset

Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDb.OpenRecordset("Table1")

   MyTable.AddNew                                 ' add a new record to
table
   MyTable![my field] = "some text"        ' assign values to a field
   MyTable.Update                                   ' write the record

Albert D. Kallal
Edmonton, Alberta Canada
kallal@msn.com

You can import without using the wizard using macros:
TransferText is for importing text
TransferSpreadsheet is for importing from Excel
TransferDatabase is for importing from another database.

The same commands can be used from a module if you can program.

To import only a certain number of lines from a file then you can do that
using code. Try using File Input/Output (I/O) and then Line Input.

john hayes wrote in message <3692698D.AE9@earthlink.net>...
>I need to import a text data file which has 2 lines per record, into
>Access.  I have tried the Import Wizard, but it does not seem to support
>multi-line records.  I see that you can save/load a format spec
>for a file, but I didn't see if it saves the spec in a file that
>I can manipulate (or create from scratch) - it seems to save the spec
>internally?  Also, I don't know the syntax requirements for such a
>spec file (not in the book).
>
>Can anyone tell me how to tell Access that it needs to load multi-line
>records from a text file?  BTW - editing the text file to make single-
>line records is not an option, since the data file is created that
>way by a canned vendor module and will have to be loaded periodically
>on-going.
>
>Also, is there a way to import without going through the Wizard?
>
>Thanks!
>
>John Hayes
>aikosys@earthlink.net
>

Hello John,
one other possibility consists in an import in a temporary table with
general spec's.
The second step: After this you can add this table with an query to the
correct table.
This query can consist with a condition for a character, which is
appearing in each first line with a relation to the correspond second
line. 
An other way of second step could be that create an relation of an
counter of the temporary table e.g. line x with the next line x+1
br
Karl gruber

Just to throw one more thing into the mix.  You could attach the text
file to an Access Database then deal with it via VBA.

							Mark

On Tue, 05 Jan 1999 11:35:41 -0800, john hayes <aikosys@earthlink.net>
wrote:

>I need to import a text data file which has 2 lines per record, into
>Access.  I have tried the Import Wizard, but it does not seem to support
>multi-line records.  I see that you can save/load a format spec 
>for a file, but I didn't see if it saves the spec in a file that
>I can manipulate (or create from scratch) - it seems to save the spec
>internally?  Also, I don't know the syntax requirements for such a
>spec file (not in the book).
>
>Can anyone tell me how to tell Access that it needs to load multi-line
>records from a text file?  BTW - editing the text file to make single-
>line records is not an option, since the data file is created that
>way by a canned vendor module and will have to be loaded periodically
>on-going.
>
>Also, is there a way to import without going through the Wizard?
>
>Thanks!
>
>John Hayes
>aikosys@earthlink.net
>

I have a routine that will import this.  You need to open the text
file and read it line by line, then append the line to your database.
Here's my basic function:

Function ImportFile()

Dim intImportFile As Integer
Dim strImportFile As String
Dim strTableName As String
Dim strInputLine As String
Dim dbs As Database
Dim rst As Recordset

strImportFile = "c:\temp\temp.txt"
strTableName = "TempTable"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)

intImportFile = FreeFile
Open strImportFile For Input As #intImportFile

Do Until EOF(intImportFile)
    Line Input #intImportFile, strInputLine
    rst.AddNew
    rst!Line1 = strInputLine
    Line Input #intImportFile, strInputLine
    rst!Line2 = strInputLine
    rst.Update
Loop

Close intImportFile
End Function


You need to add error checking when you're doing the Line Input
statements and test with your file to see if there are any extra lines
at the end.  Also, if there's a blank line between records, you need
to do an extra Line Input statement to get rid of that.

The table "TempTable" has two fields, "Line1" and "Line2".

If you have any questions, please respond.




>On Tue, 05 Jan 1999 11:35:41 -0800, john hayes <aikosys@earthlink.net>
>wrote:
>
>>I need to import a text data file which has 2 lines per record, into
>>Access.  I have tried the Import Wizard, but it does not seem to support
>>multi-line records.  I see that you can save/load a format spec 
>>for a file, but I didn't see if it saves the spec in a file that
>>I can manipulate (or create from scratch) - it seems to save the spec
>>internally?  Also, I don't know the syntax requirements for such a
>>spec file (not in the book).
>>
>>Can anyone tell me how to tell Access that it needs to load multi-line
>>records from a text file?  BTW - editing the text file to make single-
>>line records is not an option, since the data file is created that
>>way by a canned vendor module and will have to be loaded periodically
>>on-going.
>>
>>Also, is there a way to import without going through the Wizard?
>>
>>Thanks!
>>
>>John Hayes
>>aikosys@earthlink.net
>>
>

Richard L. Stone
Healthcare-ID
rstone@fenris-inc.com

Rick,

Is this Visual Basic code?  My client does not have VB - only Access.
Any idea how much VB would cost for a Win95 platform?  Funding is at a
premium for this project.... 

If possible, I would like to do this with some sort of Access macros
or something - but I am not too familiar with Access.

Thanks for the input, all!

John Hayes
aikosys@earthlink.net

Rick Stone wrote:
> 
> I have a routine that will import this.  You need to open the text
> file and read it line by line, then append the line to your database.
> Here's my basic function:
> 
> Function ImportFile()
> 
> Dim intImportFile As Integer
> Dim strImportFile As String
> Dim strTableName As String
> Dim strInputLine As String
> Dim dbs As Database
> Dim rst As Recordset
> 
> strImportFile = "c:\temp\temp.txt"
> strTableName = "TempTable"
> 
> Set dbs = CurrentDb
> Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)
> 
> intImportFile = FreeFile
> Open strImportFile For Input As #intImportFile
> 
> Do Until EOF(intImportFile)
>     Line Input #intImportFile, strInputLine
>     rst.AddNew
>     rst!Line1 = strInputLine
>     Line Input #intImportFile, strInputLine
>     rst!Line2 = strInputLine
>     rst.Update
> Loop
> 
> Close intImportFile
> End Function
> 
> You need to add error checking when you're doing the Line Input
> statements and test with your file to see if there are any extra lines
> at the end.  Also, if there's a blank line between records, you need
> to do an extra Line Input statement to get rid of that.
> 
> The table "TempTable" has two fields, "Line1" and "Line2".
> 
> If you have any questions, please respond.
> 
> >On Tue, 05 Jan 1999 11:35:41 -0800, john hayes <aikosys@earthlink.net>
> >wrote:
> >
> >>I need to import a text data file which has 2 lines per record, into
> >>Access.  I have tried the Import Wizard, but it does not seem to support
> >>multi-line records.  I see that you can save/load a format spec
> >>for a file, but I didn't see if it saves the spec in a file that
> >>I can manipulate (or create from scratch) - it seems to save the spec
> >>internally?  Also, I don't know the syntax requirements for such a
> >>spec file (not in the book).
> >>
> >>Can anyone tell me how to tell Access that it needs to load multi-line
> >>records from a text file?  BTW - editing the text file to make single-
> >>line records is not an option, since the data file is created that
> >>way by a canned vendor module and will have to be loaded periodically
> >>on-going.
> >>
> >>Also, is there a way to import without going through the Wizard?
> >>
> >>Thanks!
> >>
> >>John Hayes
> >>aikosys@earthlink.net
> >>
> >
> 
> Richard L. Stone
> Healthcare-ID
> rstone@fenris-inc.com

Hi John,

The code will work fine in Access.  This is because Access uses Visual Basic
for Applications (VBA) which is also the underlying lang. in VB.

HTH
--
Dev Ashish (Just my $.001)
---------------
The Access Web ( http://home.att.net/~dashish )
---------------

john hayes wrote in message <36950860.3D7A@earthlink.net>...
:Rick,
:
:Is this Visual Basic code?  My client does not have VB - only Access.
:Any idea how much VB would cost for a Win95 platform?  Funding is at a
:premium for this project....
:
:If possible, I would like to do this with some sort of Access macros
:or something - but I am not too familiar with Access.
:
:Thanks for the input, all!
:
:John Hayes
:aikosys@earthlink.net
:
:Rick Stone wrote:
:>
:> I have a routine that will import this.  You need to open the text
:> file and read it line by line, then append the line to your database.
:> Here's my basic function:
:>
:> Function ImportFile()
:>
:> Dim intImportFile As Integer
:> Dim strImportFile As String
:> Dim strTableName As String
:> Dim strInputLine As String
:> Dim dbs As Database
:> Dim rst As Recordset
:>
:> strImportFile = "c:\temp\temp.txt"
:> strTableName = "TempTable"
:>
:> Set dbs = CurrentDb
:> Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)
:>
:> intImportFile = FreeFile
:> Open strImportFile For Input As #intImportFile
:>
:> Do Until EOF(intImportFile)
:>     Line Input #intImportFile, strInputLine
:>     rst.AddNew
:>     rst!Line1 = strInputLine
:>     Line Input #intImportFile, strInputLine
:>     rst!Line2 = strInputLine
:>     rst.Update
:> Loop
:>
:> Close intImportFile
:> End Function
:>
:> You need to add error checking when you're doing the Line Input
:> statements and test with your file to see if there are any extra lines
:> at the end.  Also, if there's a blank line between records, you need
:> to do an extra Line Input statement to get rid of that.
:>
:> The table "TempTable" has two fields, "Line1" and "Line2".
:>
:> If you have any questions, please respond.
:>
:> >On Tue, 05 Jan 1999 11:35:41 -0800, john hayes <aikosys@earthlink.net>
:> >wrote:
:> >
:> >>I need to import a text data file which has 2 lines per record, into
:> >>Access.  I have tried the Import Wizard, but it does not seem to
support
:> >>multi-line records.  I see that you can save/load a format spec
:> >>for a file, but I didn't see if it saves the spec in a file that
:> >>I can manipulate (or create from scratch) - it seems to save the spec
:> >>internally?  Also, I don't know the syntax requirements for such a
:> >>spec file (not in the book).
:> >>
:> >>Can anyone tell me how to tell Access that it needs to load multi-line
:> >>records from a text file?  BTW - editing the text file to make single-
:> >>line records is not an option, since the data file is created that
:> >>way by a canned vendor module and will have to be loaded periodically
:> >>on-going.
:> >>
:> >>Also, is there a way to import without going through the Wizard?
:> >>
:> >>Thanks!
:> >>
:> >>John Hayes
:> >>aikosys@earthlink.net
:> >>
:> >
:>
:> Richard L. Stone
:> Healthcare-ID
:> rstone@fenris-inc.com
:

As people have said, put the code in a module in Access and it will
work just fine.  You will have to modify it to work with your
particular tables and files.  If you haven't worked with modules,
you'll have some learning to do.

I don't think you can do what you want with a macro.

On Thu, 07 Jan 1999 11:17:52 -0800, john hayes <aikosys@earthlink.net>
wrote:

>Rick,
>
>Is this Visual Basic code?  My client does not have VB - only Access.
>Any idea how much VB would cost for a Win95 platform?  Funding is at a
>premium for this project.... 
>
>If possible, I would like to do this with some sort of Access macros
>or something - but I am not too familiar with Access.
>
>Thanks for the input, all!
>
>John Hayes
>aikosys@earthlink.net
>
>Rick Stone wrote:
>> 
>> I have a routine that will import this.  You need to open the text
>> file and read it line by line, then append the line to your database.
>> Here's my basic function:
>> 
>> Function ImportFile()
>> 
>> Dim intImportFile As Integer
>> Dim strImportFile As String
>> Dim strTableName As String
>> Dim strInputLine As String
>> Dim dbs As Database
>> Dim rst As Recordset
>> 
>> strImportFile = "c:\temp\temp.txt"
>> strTableName = "TempTable"
>> 
>> Set dbs = CurrentDb
>> Set rst = dbs.OpenRecordset(strTableName, dbOpenDynaset)
>> 
>> intImportFile = FreeFile
>> Open strImportFile For Input As #intImportFile
>> 
>> Do Until EOF(intImportFile)
>>     Line Input #intImportFile, strInputLine
>>     rst.AddNew
>>     rst!Line1 = strInputLine
>>     Line Input #intImportFile, strInputLine
>>     rst!Line2 = strInputLine
>>     rst.Update
>> Loop
>> 
>> Close intImportFile
>> End Function
>> 
>> You need to add error checking when you're doing the Line Input
>> statements and test with your file to see if there are any extra lines
>> at the end.  Also, if there's a blank line between records, you need
>> to do an extra Line Input statement to get rid of that.
>> 
>> The table "TempTable" has two fields, "Line1" and "Line2".
>> 
>> If you have any questions, please respond.
>> 
>> >On Tue, 05 Jan 1999 11:35:41 -0800, john hayes <aikosys@earthlink.net>
>> >wrote:
>> >
>> >>I need to import a text data file which has 2 lines per record, into
>> >>Access.  I have tried the Import Wizard, but it does not seem to support
>> >>multi-line records.  I see that you can save/load a format spec
>> >>for a file, but I didn't see if it saves the spec in a file that
>> >>I can manipulate (or create from scratch) - it seems to save the spec
>> >>internally?  Also, I don't know the syntax requirements for such a
>> >>spec file (not in the book).
>> >>
>> >>Can anyone tell me how to tell Access that it needs to load multi-line
>> >>records from a text file?  BTW - editing the text file to make single-
>> >>line records is not an option, since the data file is created that
>> >>way by a canned vendor module and will have to be loaded periodically
>> >>on-going.
>> >>
>> >>Also, is there a way to import without going through the Wizard?
>> >>
>> >>Thanks!
>> >>
>> >>John Hayes
>> >>aikosys@earthlink.net
>> >>
>> >
>> 
>> Richard L. Stone
>> Healthcare-ID
>> rstone@fenris-inc.com
>

Richard L. Stone
Healthcare-ID
rstone@fenris-inc.com