Question How to edit saved imports...

[MSherfey]

I've searched this forum and others and haven't found an answer. Please forgive me if I missed a post.

Every quarter I have to import about 30 large files into my database. I've been renaming the files to a standard, running a saved import, and then repeating. This is a complete pain.

Without recreating the same import 30 different times, is there a way to copy the import 30 times and just rename the source file? I would then run a macro to do the complete import for all files.

Where do you edit an import after it is created (other than name and file locations) and can you copy an import?

 

[Kiwiman]

Howzit

I can't remember where I got this from - I have done some of it but the part where it stores the file names and then loops through importing each one was someone else. Can't say I fully understand it - but it works for me.

This searches a particular directory for a particular file type, then imports all files into my database. After the import, the source file is copied to another location (so it will not be imported in future imports) and then the source file is deleted \ killed.

I import upto 15 files a month using this method. It does work, but would be interested in learning alternatives.

All files imported are in exactly the same format.

I hope this will help. Sorry will be logging off very shortly, but will be back first thing in the am.

Private Function IMPORTDATA()

Dim strTable As String
Dim strFilePathName As String, sPath As String, dPath As String
Dim strImport As String
Dim strSFolder As String
Dim strDFolder As String
Dim strFile As String, strFileList() As String, FileName As String
Dim intFile As Integer

'NAME THE VARIABLES / STRINGS
strTable = "tblHCStaging"                           ' table to import data to
strImport = "HCImport"                              ' import spec to use

strSFolder = "Integrated\"                          ' Folder containing files to import
strDFolder = "DB Uploaded\"                         ' Folder to hold imported files

sPath = "C:\Test\"                                  ' Set Default folder

dPath = sPath & strSFolder & "\"                    ' Full destination path of folder contining files to imported

' The folder that contains the files to upload and the extension I am looking for
  strFile = Dir(dPath & "*.csv")
  
 ' Loop through folder storing file names found to strFileList
 While strFile <> ""
     'add files to the list
     intFile = intFile + 1
     ReDim Preserve strFileList(1 To intFile)
     strFileList(intFile) = strFile
      strFile = Dir()
  Wend
  
  'see if any files were found
  If intFile = 0 Then
    MsgBox "No files found"
    Exit Function
  End If


' For each file found, import into the database, copy file to new location and delete the file from source location
 For intFile = 1 To UBound(strFileList)
    FileName = strFileList(intFile)

    strFilePathName = dPath & FileName
    
    'Import the file
    DoCmd.TransferText acImportDelim, strImport, strTable, strFilePathName, True
    
    ' Copy the file to a new location so future imports do not pick it up
    FileCopy strFilePathName, sPath & strDFolder & "\" & FileName
    
    ' Delete the file in the source location
    Kill strFilePathName



 Next intFile



End Function

 

[MSherfey]

So, what it looks like is you can't edit a saved import, but you can create an import script?

 

[Kiwiman]

Howzit

I would never say can't (i have learnt from guys in this forum that almost everything is possible) - I just don't know how to do this.

I assume from below the data in each file is the same as you are only renaming each file and running a saved import - so why would you want 30 different import specifications?

Every quarter I have to import about 30 large files into my database. I've been renaming the files to a standard, running a saved import, and then repeating. This is a complete pain.

If your files are in different locations, you can (I would) insert a step, to copy each file into an import location first then use my solution to import them all in at once.

My solution assumes all files are in the same locaiton and in exactly the same format \ file type.

If you do want 30 differnt imports, you can import a file manually (Rt click in the database window then import), select file type and file to import. The import wizard will come up - select Advanced, select specs and select the import spec you want. You can then modify this if you wish and then save as a new one using the save as button.

 

[dallton]

OK, after searching this site and other to find a way to change the path I stumbled across this and for me it worked.

1. Click on External Data in the ribbon
2. Click on Saved Exports icon under the Export section.
3. Under the Manage Data Tasks window switch to the Saved Imports tab.
4. Select the Saved Import that you want to change and then click on the path, edit the path. Click away from the selected Saved Import.

This worked for me and much easier than writting a bunch of code for something where I simply need to move the files.