Add File Name to Table when Importing Files


Hello. I have a database which allows a user to import multiple .csv files at one time and adds them to a table. The file name format is based on the date and time including seconds which eliminates the possibility of duplicates since one person creates these throughout the day. Each file is considered a "batch" and can contain anywhere from 1 to 50 (or more) line items. If I can capture the file name and import that into the table that will allow me to show how many "batches" have been submitted.

Is there code that is compatible with what I am currently doing that will also import the filename into the table?

Operating System: Windows XP Professional
Program: Microsoft Access 2003
Example of .csv File Name: AROPS20111104145057.csv (AROPSYYYYMMDDHHMMSS.csv)

Here is the code I am currently using and it works wonderfully:

Function ImportARData()
Dim fDialog As Office.FileDialog
Dim varFile As Variant

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = True

.Title = "Choose Files to Import"

.Filters.Clear
.Filters.Add "Text Files", "*.csv"

If .Show = True Then
For Each varFile In .SelectedItems

DoCmd.TransferText acImportDelim, "AROPSImport", "tbl_AROPSImport", varFile
Next
Else
MsgBox "You have cancelled the import."
End If
End With
End Function

Any help you can provide would be greatly appreciated. Thank you.


Sponsored Links:



I need to scan a directory and add file names in that directory to a table.

I was told that the Dir() command can scan for file names and you can use ADO/DAO code to insert them into your table. But, I need some guidance on excatly how to do this. Can anyone post some examples on how to do this?

Here is some specific information on what type of data I'm working on:
The file names are in a folder called Box 001
The files are PDF's with a bates number naming convention, such as
ACC 000001.pdf
ACC 000003.pdf
ACC 000005.pdf
ACC 000011.pdf
ACC 000012.pdf
ACC 000024.pdf

-Thanks guys for any help you can provide




Need some ideas before I proceed.

I have a series of text files that are downloaded each day. The naming convention is as follows with the middle of the file representing the date:

cs070908.txt
cs071008.txt

What I want to do is have a list box that in which column 1 is the file date extracted from the file name along with another column that says either "posted" or "pending"

Then I want to run code on the double click event of the row in the listbox. (already wrote code to append data from text file to table) in which it converts to posted after code run.

So, I am thinking on the "on open" event, write code to import the file names to a table for the list box datasource.

Anyone encountered this approach before or have sample code to get me started, ideas?

TIA

Steve




I've created a macro that does the following:

open a table
transfer an excel spreadsheet
show all records
save the table
close the table
open a report
stop macro

It works almost flawlessly but I have two small problems. The table has a column called "startdate". I want the macro to look at the last entry in the "startdate" column in the table and compare it to the first entry in the "startdate" column of the import file. If the two "startdate" columns match I want the macro to stop running and present an error similar to, "Start dates match-check to make sure you have the proper file" or something to that degree. If the "startdate" doesn't match then continue with the macro. I can't figure out how to tell the macro to compare the import file to the table.

I'm trying to keep it from importing the same excel file more than once. Each import file should be advanced by the next day of the last record of the table.

Any help?




Hi Have this function in access that allows me to propmt for a file name to be imported.

Function Import_File_Click()

On Error GoTo Err_Import_File_Click

Dim strFile_Path As String
Dim strTable As String

'Prompt user for file path
strFile_Path = InputBox("Please enter file path V")
'Prompt user for name of table to create for imported data
strTable = InputBox("Please enter name of new table")

'Import file, using inputted file path and table name
DoCmd.TransferText acImportDelim, , strTable, strFile_Path

Exit_Import_File_Click:
Exit Function

Err_Import_File_Click:
If Err.Number = 3011 Then
MsgBox strFile_Path & " is not a valid path, please try again", vbExclamation, "Invalid File Path"
Else
MsgBox Err.Description
End If
Resume Exit_Import_File_Click

End Function

Can I adapt it to do 3 extra things.

1. Can I add an Import Specification. Am having problems if the first line of what I am importing is numeric, the text values on the lines that follow error.

2. Can I add in a bit where the filename is file location is filled out. i.e. c:/temp/ then all I have to put is the filename i.e. myfile.csv

3. I am only importing to one table. Can the table field already be filled out. i.e. It imports everything to table MYTABLE

Any help would be appreciated




I am importing a large number of files and need to keep track of the files that I imported from a the folder in a table so I will know not to import it again. An option that is not available to me is to import the file from a folder and move or rename (other process need the file so I cant move or rename the files). Bellow is what I am using to import the files. In the if statement I'd like to add something that will update my list of files table with the fl.

Dim fs 'file system
Dim fl ' current import file
Dim fls ' import files
Dim fldr ' import folder

Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.getfolder("Parentsubfolderfolder")
Set fls = fldr.files

or Each fl In fls
If Right(fl.Name, 4) = ".csv" Then

'DoCmd.TransferText acImportDelim, "Import Specification", "Master Table", "Parentsubfolderfolder" + fl.Name, No


End If

Next fl




I've been through Ken's excellent list of Excel import VBA.

However, I'm not very good with VBA, and could use some help. I have a number of poorly formatted Excel files, with different layouts, etc. etc. etc., and I have everything I need to import it into Access and clean it up, but the one piece of the puzzle I'm missing is this: Ken's scripts allow importing into multiple tables where each table has the name of the origin worksheet, but does not give the ability to bring that worksheet name into the actual table.

What I need to be able to do is import every Excel sheet (with each sheet having anywhere from 1 to 10 columns), but rather than go into separate tables, I need everything to go into one table where, in addition to the data from the spreadsheet, each imported record also has a field showing the file name and the worksheet name.

Thanks for the help!!!




Hi everyone,
I'm another bloke on a steep learning curve to understand access as a system. I have this as my current problem:

A client is emailed monthly .csv files which contain sales data for each salesman in the company. I want to be able to automate an import this information to a database for later manipulation. The problem is that the information must go across many tables, and that many of the rows/columns in the .csv file are not relevant at all. I don't fully understand the concept of ranges, can I create and import them through vba? even if I can, many columns have to be ignored

I have implemented a file browser in a form, which passes a file name to a macro procedure, now how can I use vba to browse the file, and choose which columns from which rows to import? I gather I can use an ADO connection to create an append query but I have no idea how.

I've searched for what seems like a long time but only came up with an older .csv thread which (can't post link sorry) is for only one table and not for 2007 (different?)

Thanks for any help




Hi,

I have attached the screen shot of the error.Please help.

Thanks Attached Files Getting these errors when importing CSV file to access.doc (349.0 KB, 7 views) Reply With Quote 01-07-2013, 11:39 AM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,115 What field is the primary key? Why would the import records not have a primary key value?

Would have to examine your database and the import file. If you want to provide for analysis, follow instructions at bottom of my post.




I have a macro in which I'm using TransferText action to import a tab delimited file. It works great, but I need the File Name to point to a default UNC path that the user(s) set up and store in a table of default settings, i.e. tblDefault, field=NetworkPath.

Any ideas how to get the file name to read the tblDefault.NetworkPath

As always, this bulletin board is a wealth of information and a real life saver!!!!!




I would like to use a TransferSpreasheet (import) macro but I do not see a way to allow me to choose what file name to import from. Can someone give me some instruction?

Thanks a million!




I'm tying to get just the file name that get's selected in a dialogue box. I can get the path and the path and the file name but not just the file name.

Here what I have so far:

'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Allow user to make multiple selections in dialog box.
.AllowMultiSelect = False
'Set the title of the dialog box.
.Title = "Please select a scan model that this can be modeled after."
.Filters.Add "All Files", "*.*"
If .Show = True Then

oldname = fDialog.SelectedItems.item(1)
NewName = "Llfsp2IRISEUCRTM2007RTMSorageFolderScanMode ls"
retval = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(oldname, NewName, True)
Else
Exit Function
End If
End With




I have been using some code that allows a user to browse for a file in order to import it into Access. It works like a charm as long as the data to be inported comes from another access file. The module that allows the user to select a file is simply saving the path and filename selected as a variable. The import command for access looks like this below:


DoCmd.TransferDatabase acImport, "Microsoft Access",strPath, actable, "TableName", "NEWTableName"

The variable is inserted in the red portion. The problem is with Dbase. I need to be able to do the same thing with Dbase but its import syntax looks like this:

DoCmd.TransferDatabase acImport, "dBase 5.0",strPath, actable, "FileName.dbf", "NEWTableName"

As you can see in order to import dbase you have to know both the path/file name combination AND the the file name on it's own. I just don't know enough about coding in VBA yet to modify the code I'm using. I'm pasting that code in here. Any ideas or thoughts on how to do this are appreciated.


' Declarations
' (Copy them to the (declarations) section of a module.)
Public Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustomFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Public Const OFN_FILEMUSTEXIST = &H1000
Public Const OFN_HIDEREADONLY = &H4
Public Const OFN_PATHMUSTEXIST = &H800
Public Declare Function GetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (lpofn As OPENFILENAME) As Long




Public Function PromptFileName() As String
Dim filebox As OPENFILENAME ' open file dialog structure
Dim FName As String ' filename the user selected
Dim result As Long ' result of opening the dialog

' Configure how the dialog box will look
With filebox
' Size of the structure.
.lStructSize = Len(filebox)
' Handle to window opening the dialog.
.hwndOwner = 0 'Me.Hwnd
' Handle to calling instance (not needed).
.hInstance = 0
' File filters to make available: Access Databases and All Files
.lpstrFilter = "Access Databases (*.mdb)" & vbNullChar & "*.mdb" & _
vbNullChar & "All Files (*.*)" & vbNullChar & "*.*" & _
vbNullChar & vbNullChar
'.lpstrCustomFilter is ignored -- unused string
.nMaxCustomFilter = 0
' Default filter is the first one (Text Files, in this case).
.nFilterIndex = 1
' No default filename. Also make room for received
' path and filename of the user's selection.
.lpstrFile = Space(256) & vbNullChar
.nMaxFile = Len(.lpstrFile)
' Make room for filename of the user's selection.
.lpstrFileTitle = Space(256) & vbNullChar
.nMaxFileTitle = Len(.lpstrFileTitle)
' Initial directory is C:.
.lpstrInitialDir = "C:" & vbNullChar
' Title of file dialog.
.lpstrTitle = "Select a File" & vbNullChar
' The path and file must exist; hide the read-only box.
.flags = OFN_PATHMUSTEXIST Or OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY
' The rest of the options aren't needed.
.nFileOffset = 0
.nFileExtension = 0
'.lpstrDefExt is ignored -- unused string
.lCustData = 0
.lpfnHook = 0
'.lpTemplateName is ignored -- unused string
End With

' Display the dialog box.
result = GetOpenFileName(filebox)
If result 0 Then
' Remove null space from the file name.
FName = Left(filebox.lpstrFile, InStr(filebox.lpstrFile, vbNullChar) - 1)
'Debug.Print "The selected file: "; fname
End If

'return the string of the file name
PromptFileName = FName

End Function




Hi. I have a filename stored in a table. I was wondering if there was a way to look at this file name and store it in a different table but as an OLE object. Apaprently there is a way to do it which follows the logic of reading the record, doing something to the record (in this case making it an OLE object), and the storing the changed record.

Any help would be grealy appreciated.

Thank you!




I am importing an excel spreadsheet into an Access table using the following code:
Dim FiletoImport As String
Dim dbs As Database
Set dbs = CurrentDb
FiletoImport = "l:HR Current.xls"
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="(SE)_tbl_HR_Data_Raw", Filename:=FiletoImport, hasfieldnames:=True, spreadsheettype:=5

it works fine but I need error handler for when the file is not yet in the right location i.e. the person using the db has not saved the file in the correct location but still tries to run the import. Furthermore I would like to change the file name to not be proceeded by the letter "l" but by a wilde card character in order for the db to work on other people's pc who might not have named their drive "l".

Thanks in advance...




I am printing an invoice as a PDF. I need to set the PDF file name to 'Invoice # ' and the invoice number.
In the report On Open area I am using the following code:

Me.Caption = "Invoice # " & Me.Invonum

This doesn't work. (Not even when I remove Me.Invonum). I tried putting this into the On Activate area also, and it still didn't work.
How do I set the file name of the PDF?

Robert




Hi - I would like to output a ".snp" file to another drive and since I will be send a new file each week, I would like to have the file name change by date: e.g. "data_031105.snp" and then "data_031805.snp" the next week etc.

I have search the forums and cannot find any solutions; however, I apologize in advance if this question has been asked before and I missed it. Thanks for your help and enjoy the weekend.




Hi all,

after a little bit of advise, searched google but cannot find the answer,
I have a macro that exports a query to excel is it possible to change the file name to a text box on a form the query is also filtered off the text box

Many thanks




Hi;

I have a function that I created with a lot of help. It creates an array from files in a specified directory, dumps these filenames into an Access table, renames each file with the correct extension and then imports each file. After this I have some cleanup queries.


The original goal of this process is to rename and import files on a weekly basis through a form. File date and import date would be assessed at the time that the file is renamed and imported. This part works perfect.

Here is the problem, there are appx 250 files from the past year that I need imported, which is why I have the array. But I need the filename attached to the group of records, and the date that it was imported into the PC. Can this be done? I tried doing this one by one, but every time I mess up, I have to start from the beginning.


Please help.


Here’s is the code for the array.

Function M_ALL_LOANSA()

Dim SQL As String
Dim DirectoryFiles() ' Array that contains dir listing
Dim count

ffile = DIR("C:WINPATHRECEIVE-BILLE3*.REC")
Do While ffile ""
If ffile "" Then
ReDim Preserve DirectoryFiles(count)
DirectoryFiles(count) = ffile
count = count + 1

End If

ffile = DIR()

Loop
DoCmd.SetWarnings False
For Each itm In DirectoryFiles

TMP1 = Split(itm, ".")

SQL = "INSERT INTO FILENAMES (FILENAME) VALUES ('" & TMP1(0) & "')"
DoCmd.RunSQL SQL

FileCopy "c:winpathreceive-bill" & itm, "C:WINPATHreceive-bill" & TMP1(0) & ".txt"

DoCmd.TransferText acImportFixed, "hesc-down Import Specification", "ALL-LOANS", "C:WINPATHreceive-bill" & TMP1(0) & ".txt", False, ""

'DoCmd.OpenQuery "Q-EMPTY OUT FILENAMES", acViewNormal, acEdit
'DoCmd.Close acQuery, "Q-EMPTY OUT FILENAMES"

' CLEAN ALL LOANS

DoCmd.OpenQuery "Q-CLEAN ALL LOANS", acViewNormal, acEdit
DoCmd.Close acQuery, "Q-CLEAN ALL LOANS"

' APPEND TO ALL LOANS FORMATED
DoCmd.OpenQuery "Q-ALL LOANS APPEND TABLE", acViewNormal, acEdit
DoCmd.Close acQuery, "Q-ALL LOANS APPEND TABLE"
DoCmd.OpenQuery "Q-ALL LOANS EMPTY OUT TABLE", acViewNormal, acEdit
DoCmd.Close acQuery, "Q-ALL LOANS EMPTY OUT TABLE"

Next
M_ALL_LOANS_Exit:
Exit Function
M_ALL_LOANS_Err:
MsgBox Error$
Resume M_ALL_LOANS_Exit

End Function




hi.. I used this module to save the file directory in new created table. This code i got from searching in the google. so the problem is.... how to save the folder name to in that table.

there is a few folder in (CurrentProject.Path & "Kml Shape File") :
1. lotname
2. place
3. Hotel



	Code:
	Private Function FilesAndDetails()
On Error GoTo Err_FilesAndDetails

    Dim rs As Recordset
    Dim vDir As Variant
    Dim FilewPath As String
    
    FilewPath = (CurrentProject.Path & "Kml Shape File") 'sPath must end with a back slash, sPath = "C:Windows"
    
    CurrentDb.Execute "Delete tTempFiles.* from tFiles;"
    
    Set rs = CurrentDb.OpenRecordset("tFiles")
    
    vDir = Dir(FilewPath & "*.*")
    
    Do Until vDir = ""
        rs.AddNew
        rs!FilePathName = FilewPath & vDir
        rs!FilePath = FilewPath
        rs!FileFolde = ??????????????
        rs!FileName = vDir
        rs!ModifiedDate = FileDateTime(FilewPath & vDir)
        rs!FileSize = FileLen(FilewPath & vDir)
        rs.Update
        vDir = Dir
    Loop
    
    rs.Close
    Set rs = Nothing

    DoCmd.Requery

Exit_FilesAndDetails:
    Exit Function
    
Err_FilesAndDetails:
    MsgBox Err.Number & " - " & Err.description
    Resume Exit_FilesAndDetails

what should i put at ?????????? (red colour) this part in this code.