Importing Text File

[hendafe]

I have a problem of importing a text file into Access. The text file
will be imported using a delimiter, say ( , ) for example. This should
be imported into the database. An example is:

John, is, a, boy
Ada, is, a, girl

I have tried using the DoCmd.TransferT ext but I am not getting results.

[Bob Alston]

hendafe wrote:

I have a problem of importing a text file into Access. The text file
will be imported using a delimiter, say ( , ) for example. This should
be imported into the database. An example is:

John, is, a, boy
Ada, is, a, girl

I have tried using the DoCmd.TransferT ext but I am not getting results.

If you use the Access File - Get External Data - Import command, you can
specify what delimiter, if any is to be used. Look closely when you do
this.

Bob

[Ian Hinson]

"hendafe" <he*****@yahoo. com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.com.. .

I have a problem of importing a text file into Access. The text file
will be imported using a delimiter, say ( , ) for example.
Just to be pedantic, ( , ) is a "separator" not a delimiter.
A delimiter, if used, is usually double-quotes surrounding text.
Delimiters are needed around text fields so that commas can be included in
the text. eg
.... , "24 John Street, Bay Village", ...
This should be imported into the database.

What did that mean? Do you want the commas to be imported into the database
too?
If so, TransferText may not be the way to go.

[hendafe]

I have tried using the Get External Data - Import command in the File
menu, and it is working perfectly well.
The problem, these text files are frequently added into a particular
folder, more than one.
I want to be able to loop through this folder and import the whole of
them into a table.
This should be done through a form, by clicking a command button.
Any help with that

[Bob Alston]

Ian Hinson wrote:

"hendafe" <he*****@yahoo. com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.com.. .

I have a problem of importing a text file into Access. The text file
will be imported using a delimiter, say ( , ) for example.

Just to be pedantic, ( , ) is a "separator" not a delimiter.
A delimiter, if used, is usually double-quotes surrounding text.
Delimiters are needed around text fields so that commas can be included in
the text. eg
... , "24 John Street, Bay Village", ...

This should be imported into the database.

What did that mean? Do you want the commas to be imported into the database
too?
If so, TransferText may not be the way to go.

Pedantic or not, the Access Import Text Wizard states:
"Delimited - Characters such as comma or tab separate each field".

Consider this definition I found through google,
de·lim·it·er (d-lm-tr)
n. Computer Science
A character or sequence of characters marking the beginning or end of a
unit of data.

Bob

[hendafe]

Thanks.
The commas are not going to be imported into the database.
I am supposed to use a command button in a form to import
these files. The command button is supposed to collect these
different text files in the folder and transfer these text into a table
in the Database.
Any idea.

[hendafe]

I have tried using the Get External Data - Import command in the File
menu, and it is working perfectly well.
The problem, these text files are frequently added into a particular
folder, more than one.
I want to be able to loop through this folder and import the whole of
them into a table.
This should be done through a form, by clicking a command button.
Any help with that

[hendafe]

I have tried using the Get External Data - Import command in the File
menu, and it is working perfectly well.
The problem, these text files are frequently added into a particular
folder, more than one.
I want to be able to loop through this folder and import the whole of
them into a table.
This should be done through a form, by clicking a command button.
Any help with that

[Dean]

hendafe wrote:

Thanks.
The commas are not going to be imported into the database.
I am supposed to use a command button in a form to import
these files. The command button is supposed to collect these
different text files in the folder and transfer these text into a table
in the Database.
Any ideas.

Yes, when you imported via MS-Access's build import function, Access
save a spec. Run the process again and click on advance which should
be through the left on any of the forms. Your DoCmd.transfer will need
to use than spec as well.

As for as running a routine to process multiple files via one button,
you'll need use VBA.
Here is a sample project I did for a client:
Function IndexPC()
Set fs = Application.Fil eSearch
LoadCount = 0
PathStr(0) = DLookup("[Input Dir]", "ImportSettings ", "[ID]
=0")
PathStr(1) = DLookup("[Archive Dir]", "ImportSettings ", "[ID]
=0")
PathStr(3) = DLookup("[Archive File]", "ImportSettings ", "[ID]
=0")
PathStr(4) = DLookup("[Purge File]", "ImportSettings ", "[ID]
=0")
CurrentDb.Execu te "Empty Files_New tbl"
With fs
For Counter = 1 To 1
.LookIn = PathStr(0)
.SearchSubFolde rs = False

.FileName = "*.PYX" 'Could be anything
If .Execute <> 0 Then
DoCmd.Echo True, "There were " & .FoundFiles.Cou nt & _
" file(s) found."

DoCmd.OpenForm ("LoadFiles" )
DoCmd.RepaintOb ject acForm, "LoadFiles"
For i = 1 To .FoundFiles.Cou nt
'If Forms![LoadFiles]![File Name] Is Not Null Then
DoCmd.GoToRecor d acForm, "LoadFiles" , acNewRec
Forms![LoadFiles]![File Path Name] = .FoundFiles(i)
Forms![LoadFiles]![File Name] = Dir(.FoundFiles (i))
Forms![LoadFiles]![File Size] =
FileLen(Forms![LoadFiles]![File Path Name])
Forms![LoadFiles]![File Date/Time] =
FileDateTime(Fo rms![LoadFiles]![File Path Name])
'
NeedOLE = "No"
Age = DateDiff("d", Forms![LoadFiles]![File Date/Time],
Date)
If DCount("[File Path Name]", "Files_Load ed", "[File Path
Name]='" & Forms![LoadFiles]![File Path Name] & "'") < 1 Then NeedOLE =
"YES" 'prevent reloading if already in main table
If Age > 30000 Then Call DeleteFile
If NeedOLE = "YES" Then Call LoadOLE
Next i
Else
' MsgBox "There were no files found."
End If
Next Counter
End With
DoCmd.SetWarnin gs False
DoCmd.OpenQuery "Append New Files"
CurrentDb.Execu te "Empty Files_New tbl"
DoCmd.Close acForm, "LoadFiles" , acSaveNo
If PathStr(3) = "-1" Then Call CopyAllFiles(Pa thStr(0),
PathStr(1), "*.*")

MsgBox "Loaded " & LoadCount & " Files."

End Function

[Dean]

Here is another sample:

Function IndexPC()
Set FS = Application.Fil eSearch
DoCmd.SetWarnin gs False
CurrentDb.Execu te "Empty New files tbl"
DoCmd.Close acForm, "LoadFiles"

With FS
For Counter = 1 To 1
DaysAge = DLookup("[Delete Days]", "FilePath", "[ID] = " &
Counter)
.LookIn = DLookup("[FilePath]", "FilePath", "[ID] = " & Counter)
'.LookIn = "%temp%"

.SearchSubFolde rs = True
.FileName = "*.*"
.Execute msoSortBySize, msoSortOrderDes cending
i = .FoundFiles.Cou nt
Beep
If i > 0 Then
DoCmd.Echo True, "There were " & .FoundFiles.Cou nt & " file(s)
found."

DoCmd.OpenForm "LoadFiles" , acDesign
DoCmd.OpenForm "LoadFiles" , acNormal

For i = 1 To .FoundFiles.Cou nt
'If Forms![LoadFiles]![File Name] Is Not Null Then
DoCmd.Echo True, .FoundFiles.Cou nt - i
DoCmd.GoToRecor d acForm, "LoadFiles" , acNewRec
Forms![LoadFiles]![File Path Name] = .FoundFiles(i)
Forms![LoadFiles]![File Name] = Dir(.FoundFiles (i))

Forms![LoadFiles]![File Size] =
FileLen(Forms![LoadFiles]![File Path Name])
Forms![LoadFiles]![File Date/Time] =
FileDateTime(Fo rms![LoadFiles]![File Path Name])
' Forms![LoadFiles]![Owner] = DLookup("[Owner]", "File
Locations", "[Order]=" & Counter)
Forms![LoadFiles]![Folder] = Left$(.FoundFil es(i),
Len(.FoundFiles (i)) - Len(Dir(.FoundF iles(i))))
Forms![LoadFiles]![Projection] =
FileDateTime(Fo rms![LoadFiles]![File Path Name]) + DaysAge
Age = DateDiff("d", Forms![LoadFiles]![File Date/Time],
Date)

If Age > DaysAge Then Call DeleteFile
'Forms![LoadFiles]![Folder] =
Forms![LoadFiles]![Folder] & " -->" &
FileLen(Forms![LoadFiles]![Folder])
' RmDir Forms![LoadFiles]![Folder]
Next i
Else
MsgBox "There were no files found."
End If
Next Counter
End With
DoCmd.OpenQuery "Append New Files"
End Function

Sub DeleteFile()
Forms![LoadFiles]![Delete] = "Yes"
Kill Forms![LoadFiles]![File Path Name]
' DoCmd.RunComman d acCmdDeleteReco rd
End Sub