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.TransferText 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.TransferText 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.googlegr oups.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.googlegr oups.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.FileSearch
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.Execute "Empty Files_New tbl"
With fs
For Counter = 1 To 1
.LookIn = PathStr(0)
.SearchSubFolders = False

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

DoCmd.OpenForm ("LoadFiles")
DoCmd.RepaintObject acForm, "LoadFiles"
For i = 1 To .FoundFiles.Count
'If Forms![LoadFiles]![File Name] Is Not Null Then
DoCmd.GoToRecord 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(Forms![LoadFiles]![File Path Name])
'
NeedOLE = "No"
Age = DateDiff("d", Forms![LoadFiles]![File Date/Time],
Date)
If DCount("[File Path Name]", "Files_Loaded", "[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.SetWarnings False
DoCmd.OpenQuery "Append New Files"
CurrentDb.Execute "Empty Files_New tbl"
DoCmd.Close acForm, "LoadFiles", acSaveNo
If PathStr(3) = "-1" Then Call CopyAllFiles(PathStr(0),
PathStr(1), "*.*")

MsgBox "Loaded " & LoadCount & " Files."

End Function

[Dean]

Here is another sample:

Function IndexPC()
Set FS = Application.FileSearch
DoCmd.SetWarnings False
CurrentDb.Execute "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%"

.SearchSubFolders = True
.FileName = "*.*"
.Execute msoSortBySize, msoSortOrderDescending
i = .FoundFiles.Count
Beep
If i > 0 Then
DoCmd.Echo True, "There were " & .FoundFiles.Count & " file(s)
found."

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

For i = 1 To .FoundFiles.Count
'If Forms![LoadFiles]![File Name] Is Not Null Then
DoCmd.Echo True, .FoundFiles.Count - i
DoCmd.GoToRecord 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(Forms![LoadFiles]![File Path Name])
' Forms![LoadFiles]![Owner] = DLookup("[Owner]", "File
Locations", "[Order]=" & Counter)
Forms![LoadFiles]![Folder] = Left$(.FoundFiles(i),
Len(.FoundFiles(i)) - Len(Dir(.FoundFiles(i))))
Forms![LoadFiles]![Projection] =
FileDateTime(Forms![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.RunCommand acCmdDeleteRecord
End Sub

[-]

Check out this download ...
http://amazecreations.com/datafast/d...owseImport.zip

Not sure where the Browse code came from. Have been using it for years
and probably got it from the MVP site, Dev Ashish.

--
"hendafe" <he*****@yahoo.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

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.TransferText but I am not getting results.