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?
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 ***
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!
Incase you care (probably not but what the hey) here is how you would do
it in VB.Net
Private Sub ImportTextFile()
Dim da As OleDbDataAdapter, ds As DataSet
Dim conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\;
Extended Properties=Text;"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From JBSC.txt"
da.Fill(ds, "tbl1")
dgrv1.DataSource = ds.Tables("tbl1")
End Sub
This may not be any simpler than the VBA code, but it is way faster (for
large text files) because .Net has practically eliminated the need for
data looping (in addition to eliminating repetitive I/O in loops).
Everything happens in one shot with .Net. The VB.Net datagridview is
like a subform in Access but 100 times more functionality.
Rich
*** Sent via Developersdex http://www.developersdex.com ***
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
>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!
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
>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
>
>
>>
>>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
>>
>>>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!
Anxious to try this out. But first, I must test a suitable
code snippet to recreate in Access 97 the functionality
of the Replace FN you used...
Replace(strPath & strFN, "'", "''")
Any better suggestions there, pop 'em in here. Meanwhile,
Lyle Fairfield offered a substitute for the Replace function.
I'll experiment with that. But let me ask you - what are you
trying to replace with what?
Are you replacing a ' with two of them? I'm not sure what
the syntax is implying there.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Tue, 18 Dec 2007 18:59:34 -0600, Chuck Grimsby
>
>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
>
>>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
>>
>>
>>>
>>>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
>>>
>>>>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!
On Dec 18, 11:15 pm, MLH
> Lyle Fairfield offered a substitute for the Replace function.
> I'll experiment with that.
Holy Mary, Mother of God, pray for us sinners, now and at the hour of
our death. Amen.
Sure. The code below was originally written by Joe Foster in one of
the VB newsgroups. I "fixed" it for Access 97, and I tend to still
use it in my databases, even when the "Replace" function is available.
(So as not to confuse people, I almost always replace the "JoeReplace"
function with "Replace", so people aren't confused. (Joe's function
seems to be faster....)
The point of doing the replace is to make sure that the SQL string
that the code I posted before is a valid SQL string. If there is a
single quote in the file name or path, the SQL string wouldn't be
valid, and there would be an error. The Replace function (or the
JoeReplace function I'm posting here) will change any single quote in
the path & filename combination to have 2 single quotes, and thus
resolve the issue.
Example:
?JoeReplace("C:\Someone's path\That is SQL ugly", "'","''") =
C:\Someone''s path\That is SQL ugly
------------------- CUT HERE ---------------------------------
Public Function JoeReplace(ByVal Expression As String, _
ByVal Find As String, _
ByVal Repl As String, _
Optional ByVal Start As Long = 1, _
Optional ByVal Count As Long = -1 _
) As String
If Start < 1 Or Count < -1 Then
Err.Raise 5
ElseIf Start > Len(Expression) Then
' nothing to do!
Exit Function
ElseIf Len(Find) < 1 Or Count = 0 Then
If Start <> 1 Then JoeReplace = Mid$(Expression, Start) _
Else JoeReplace = Expression
Exit Function
End If
Dim p As Long, q As Long, rlen As Long
p = Start
Do
q = InStr(p, Expression, Find) ', jCompare)
If q = 0 Then Exit Do
If rlen + q - p + Len(Repl) > Len(JoeReplace) Then
JoeReplace = JoeReplace & JoeReplace & Space$(q - p + Len(Repl))
End If
If p < q Then _
Mid$(JoeReplace, rlen + 1) = _
Mid$(Expression, p, q - p)
If Len(Repl) Then Mid$(JoeReplace, rlen + q - p + 1) = Repl
Count = Count - 1
rlen = rlen + q - p + Len(Repl)
p = q + Len(Find)
Loop Until Count = 0
JoeReplace = Left$(JoeReplace, rlen) & Mid$(Expression, p)
End Function
On Tue, 18 Dec 2007 23:15:25 -0500, MLH
>Anxious to try this out. But first, I must test a suitable
>code snippet to recreate in Access 97 the functionality
>of the Replace FN you used...
>
>Replace(strPath & strFN, "'", "''")
>
>Any better suggestions there, pop 'em in here. Meanwhile,
>Lyle Fairfield offered a substitute for the Replace function.
>I'll experiment with that. But let me ask you - what are you
>trying to replace with what?
>
>Are you replacing a ' with two of them? I'm not sure what
>the syntax is implying there.
>
>
>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
>
>On Tue, 18 Dec 2007 18:59:34 -0600, Chuck Grimsby
>
>
>>
>>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
>>
>>>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
>>>
>>>
>>>>
>>>>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
>>>>
>>>>>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!
---
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!