By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,649 Members | 1,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,649 IT Pros & Developers. It's quick & easy.

Importing text files whose lines are longer than 80-chars?

P: n/a
MLH
I'm using A97 import data wizard to import text file N2 a
table. The text file is a DIR listing produced by running

dir jdc*.* /s c:\JDCs.txt

The wizard is chopping the lines off at the leftmost 80
chars. Dunno why - I don't know how to configure it to
grab longer lines than that. I made the TARGET table
field a memo field. That didn't help. Ideas anyone?
Nov 20 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
try reading your text file this way

Sub LineInputThing()
Dim s As String, RS as DAO.RecordSet
Set RS = CurrentDB.OpenRecordset("yourTbl")
Open "C:\1A\JDCs.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, s
RS.AddNew
RS(0) = s
RS.Update
Loop
Close #1
RS.Close

End Sub

This is real straight forward. Copy this code into a Standard Code
Module (not a form code module - to to the modules tab and select New
Module). Then change the name of 'yourTbl' to the actual name of your
table (don't forget the "yourTbl" double qoutes around the table name.
Then press the F5 key and your table will be populated very quickly.

Line Input reads the entire line for each line in your text file. You
can find more information on Line Input, Open File in the Access Help
files.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 20 '07 #2

P: n/a
MLH
Rich that's a hundred times better! The text
importation wizard leaves a LOT to be desired.
Your procedure brought everything in without
any of the CRLF's in the text lines. The wizard
MOST ALWAYS skips over some of them and
that means there's a great deal more work to
do after importing a text file to a table.

Thanks a bunch!
Nov 20 '07 #3

P: n/a

Why are you using a text file for a directory listing?

You can get at everything a DIR list can produce (and more!) through
VBA. No need to "jump" to a cmd line to create a list.
On Tue, 20 Nov 2007 15:31:28 -0500, MLH <CR**@NorthState.netwrote:
>I'm using A97 import data wizard to import text file N2 a
table. The text file is a DIR listing produced by running

dir jdc*.* /s c:\JDCs.txt

The wizard is chopping the lines off at the leftmost 80
chars. Dunno why - I don't know how to configure it to
grab longer lines than that. I made the TARGET table
field a memo field. That didn't help. Ideas anyone?
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!
Nov 21 '07 #4

P: n/a
MLH
Excellent question - what I want is to populate a text field
in a table with full path 'n filename spec for files beginning
with JDC in a given branch. It really is a lot of work. When
I run dir jdc*.* /s c:\JDCs.txt - I get a lot
of extra junk in the JDCs.txt glob that has to be parsed out.
But I just don't know how to instruct Access to go about
doing this on its own. I would LOVE to see something
along that line, already written - tried 'n true.

Thx Chuck.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Tue, 20 Nov 2007 18:40:08 -0600, Chuck Grimsby
<c.*******@worldnet.att.net.invalidwrote:
>
Why are you using a text file for a directory listing?

You can get at everything a DIR list can produce (and more!) through
VBA. No need to "jump" to a cmd line to create a list.
On Tue, 20 Nov 2007 15:31:28 -0500, MLH <CR**@NorthState.netwrote:
>>I'm using A97 import data wizard to import text file N2 a
table. The text file is a DIR listing produced by running

dir jdc*.* /s c:\JDCs.txt

The wizard is chopping the lines off at the leftmost 80
chars. Dunno why - I don't know how to configure it to
grab longer lines than that. I made the TARGET table
field a memo field. That didn't help. Ideas anyone?

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!
Dec 17 '07 #5

P: n/a

Assuming your table is named "Table1" and the field in that table you
want to fill out is named "Field1", you would simply do something like

Public Function FillTable1() As Long
Const strPath As String = "c:\some path\"
Const strExt As String = "jdc"
Dim strFN As String
Dim lngReturn As Long
Dim strSQL As String
Dim myDB As DAO.Database

Set myDB = CurrentDb
' Clear out any existing Entries:
myDB.Execute "DELETE * FROM TABLE1", dbFailOnError
' Start reading the disk:
strFN = Dir$(strPath & "*." & strExt)
While strFN <""
' Stuff the entries into Table1:
strSQL = "INSERT INTO Table1 " & _
"(Field1) VALUES ('" & _
Replace(strPath & strFN, "'", "''") & "')"
myDB.Execute strSQL, dbFailOnError
lngReturn = lngReturn + myDB.RecordsAffected
strFN = Dir$
Wend
Set myDB = Nothing
FillTable1 = lngReturn
End Function

If the function returns 0, no entries were made.

On Mon, 17 Dec 2007 10:46:44 -0500, MLH <CR**@NorthState.netwrote:
>Excellent question - what I want is to populate a text field
in a table with full path 'n filename spec for files beginning
with JDC in a given branch. It really is a lot of work. When
I run dir jdc*.* /s c:\JDCs.txt - I get a lot
of extra junk in the JDCs.txt glob that has to be parsed out.
But I just don't know how to instruct Access to go about
doing this on its own. I would LOVE to see something
along that line, already written - tried 'n true.

Thx Chuck.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Tue, 20 Nov 2007 18:40:08 -0600, Chuck Grimsby
<c.*******@worldnet.att.net.invalidwrote:
>>
Why are you using a text file for a directory listing?

You can get at everything a DIR list can produce (and more!) through
VBA. No need to "jump" to a cmd line to create a list.
On Tue, 20 Nov 2007 15:31:28 -0500, MLH <CR**@NorthState.netwrote:
>>>I'm using A97 import data wizard to import text file N2 a
table. The text file is a DIR listing produced by running

dir jdc*.* /s c:\JDCs.txt

The wizard is chopping the lines off at the leftmost 80
chars. Dunno why - I don't know how to configure it to
grab longer lines than that. I made the TARGET table
field a memo field. That didn't help. Ideas anyone?

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!
---
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!
Dec 19 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.