Import Text AND Add Field??

[Guest]

I use the code that follows (borrowed from a prior post) to import an
indeterminate number of text files (course evaluations) into a table for
further processing.

Unfortunately, the files' records contain no field that identifies the
course. Only the file name itself identifies the course.

So, is there a way to expand this procedure to insert and/or fill a field
with the filename as the value for every record in the file? Or is there some
other way to link the records to course names? Note I have no structural
control over the files themselves.

Second question, same process: How do I prompt the user to browse for the
folder containing the files?

Many thanks for your help.Function fimportAllFiles()
Dim strfile As String
Dim strPath As String
strPath = "DIRECTORY PATH"

ChDir strPath

'Find the firsttext file
'
strfile = Dir("*.txt")

'Loop through the string & import the files
'
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "Raw Data Import", "tblRawData",
strPath & "\" & strfile

strfile = Dir
Loop
End Function

 

[Larry Daugherty]

dim MyFile as string

Following each Dir execution, capture the filename to MyFile. I'll
leave the parsing to you.

Then, given that Excel is returning flat files to you anyway, I'd
make the data changes in Excel. Open the workbook whose name you just
received. Open sheet 1. Insert a new column A. In each non-blank
row insert the file's root name in column A. Close the workbook
saving changes.

Continue with your code as before.

If the above Excel stuff sounds challenging then I'd take a copy of
one of the target workbooks and record a macro in Excel which does the
bit of opening sheet 1, inserting new column A and inserting the
file's root name in each cell in column A. Stop recording the macro
and go open it for analysis and modification. Stay in the Excel
environment until you have the code generalized and it does everything
you want it to do. For your Excel programming questions, post into
one of their groups. Programming is the best bet for this.

Once you've got things perfected in Excel then look in help for
Automation.

HTH

 

[Guest]

First Question answered in modified code below.
Here is some code that will let you select a folder. Put it in a standard
module. Call it like this:

strDirectory = BrowseFolder("Select File Path")

The text you pass will be in the header of the dialog, so it can be anything
you like.
strDirectory will now contain the path you selected without the trailing \
For example, it would look like "E:\SomeDirectory\SomeSubDirectory". So you
would have to add the \ when you put a file name on the end of it.
strPathAndFile = strDirectory & "\" & "AFile.txt"

Option Compare Database
Option Explicit

'************** Code Start **************
'This code was originally written by Terry Kreft.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code courtesy of
'Terry Kreft

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long

Private Const BIF_RETURNONLYFSDIRS = &H1
Public Function BrowseFolder(szDialogTitle As String) As String
Dim X As Long, bi As BROWSEINFO, dwIList As Long
Dim szPath As String, wPos As Integer

With bi
.hOwner = hWndAccessApp
.lpszTitle = szDialogTitle
.ulFlags = BIF_RETURNONLYFSDIRS
End With

dwIList = SHBrowseForFolder(bi)
szPath = Space$(512)
X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath)

If X Then
wPos = InStr(szPath, Chr(0))
BrowseFolder = Left$(szPath, wPos - 1)
Else
BrowseFolder = vbNullString
End If
End Function

I use the code that follows (borrowed from a prior post) to import an
indeterminate number of text files (course evaluations) into a table for
further processing.

Unfortunately, the files' records contain no field that identifies the
course. Only the file name itself identifies the course.

So, is there a way to expand this procedure to insert and/or fill a field
with the filename as the value for every record in the file? Or is there some
other way to link the records to course names? Note I have no structural
control over the files themselves.

Second question, same process: How do I prompt the user to browse for the
folder containing the files?

Many thanks for your help.
Function fimportAllFiles()
Dim strfile As String
Dim strPath As String
strPath = BrowseFolder("Select Folder")

if len(strPath) = 0 Then 'User Canceled
Exit Sub
End If

'Find the firsttext file
'
strfile = Dir(strPath & "\*.txt")

'Loop through the string & import the files
'
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "Raw Data Import", "tblRawData",
strPath & "\" & strfile

Call AddFileName(strfile)

'You will need to write this Sub. Since I don't know the structure of your
table, I don't know if there is a field you can put the filename in or if you
will have to have another table with an additional field, but the essence of
it is you will need an update query that will use strfile that is passed to
it to populate the field where you want the file name.