VBA Importing same spec different filename

[ECEK]

I have several files in my folder that have the same structure but are named after relevant employees.

I have a list of the employees in a table.

Is there a way, using the same import spec, to import the files based on my table or will I have to import each file separately?

I hope I've explained this OK ?

 

[ECEK]

I found this for Excel which would be ideal if I could get some help adapting it to Access. I haven't tried this code but the principle is the same - import all xls (csv in my case so Id use Transfer Text instead)) files from a specific folder.

Sub ImportfromPath(path As String, intoTable As String, hasHeader As Boolean)

Dim fileName As String

'Loop through the folder & import each file
fileName = Dir(path & "\*.xls")
While fileName <> ""
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, intoTable, path & fileName, hasHeader
   'check whether there are any more files to import
    fileName = Dir()
Wend

End Sub

 

[arnelgp]

copy the following code in a Standard Module.
the code can be run on any Specification file
whether for Importing or Exporting.
Just make sure that the New filename that you
will use, is the same structure as you have in
the Original spec.

No need for you to use the DoCmd.Transfer... etc.
the code will do the importing/exporting for you just
call the Sub.

To Call:

RunSpecOnOtherFile "the import export spec name here", "full path and filename to import/export"

Or

Call RunSpecOnOtherFile("the import export spec name here", "full path and filename to import/export")

'''''''''''''''''''''''''''
'
' arnelgp
'
'
Public Sub RunSpecOnOtherFile(ByVal SpecName As String, _
                                    ByVal strPathAndFileToRun As String)
    Dim objSpec As ImportExportSpecification
    Dim strXML As String
    Dim intStartPos As Integer, intEndPos As Integer
    
    Set objSpec = CurrentProject.ImportExportSpecifications.Item(SpecName)
    ' get the xml string
    strXML = objSpec.xml
    Debug.Print strXML
    intStartPos = InStr(1, strXML, "Path")
    intStartPos = InStr(intStartPos, strXML, "=")
    intEndPos = InStr(intStartPos, strXML, "xmlns") - 1
    ' insert the new filename between intstartpos and intendpos
    strXML = Left(strXML, intStartPos) & " " & _
            """" & strPathAndFileToRun & """" & _
            Mid(strXML, intEndPos)
    Debug.Print strXML
    ' replace the xml of the spec to the new strXML
    objSpec.xml = strXML
    ' run the specification
    objSpec.Execute
    ' close it
    Set objSpec = Nothing
End Sub

 

[ECEK]

Thanks Arnel
Will this work for csv files?

 

[ECEK]

I have decided that I just need to import all csv files from a folder into a pre-defined table.

 

[arnelgp]

If the original spec is used on csv before, it will.
Like i said same structure as before but on differe t file.

 

[ECEK]

I found this which works to a degree.
They create their own table.
I need them into the same table. How would I do this?

Function DoImport()

 Dim strPathFile As String
 Dim strFile As String
 Dim strPath As String
 Dim strTable As String
 Dim blnHasFieldNames As Boolean

 ' Change this next line to True if the first row in CSV worksheet
 ' has field names
 blnHasFieldNames = True

 ' Replace C:\Documents\ with the real path to the folder that
 ' contains the CSV files
 strPath = "S:\Access\DATA\ACL\IO DATA EXPORT\Adviser Data\"

 ' Replace tablename with the real name of the table into which
 ' the data are to be imported

 strFile = Dir(strPath & "*.csv")


 Do While Len(strFile) > 0
       strTable = Left(strFile, Len(strFile) - 4)
       strPathFile = strPath & strFile
       DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames


 ' Uncomment out the next code step if you want to delete the
 ' EXCEL file after it's been imported
 '       Kill strPathFile

       strFile = Dir()

 Loop


End Function

 

[Gasman]

Call arnelgp's code in the do while loop?

 

[ECEK]

Gasman? I don't understand

 

[ECEK]

OK so I've found this but one of the fields is creating an error. The field is a ShortText field but the data still errors. Is there a way to format the import.

Private Sub Command0_Click()



Dim InputDir, ImportFile As String, tblName As String

InputDir = "S:\Access\DATA\ACL\IO DATA EXPORT\Adviser Data\"

ImportFile = Dir(InputDir & "*.csv")

Do While Len(ImportFile) > 0
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1)) 'This is to import each file into single tables.
tblName = "tblAdviserData" 'This is to import all files into one table.

DoCmd.TransferText acImportDelim, , tblName, InputDir & ImportFile, True

ImportFile = Dir
Loop


End Sub

 

[Gasman]

Gasman? I don't understand

You get each file in the folder in that Do While loop and you pass the spec name and the filename to arnelgp's function.

 

[ECEK]

So I saved my specification (AdviserImportSpecification) and inserted it into my TransferText like so:

Private Sub Command0_Click()



Dim InputDir, ImportFile As String, tblName As String

InputDir = "S:\Access\DATA\ACL\IO DATA EXPORT\Adviser Data\"

ImportFile = Dir(InputDir & "*.csv")

Do While Len(ImportFile) > 0
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1)) 'This is to import each file into single tables.
tblName = "tblAdviserData" 'This is to import all files into one table.

DoCmd.TransferText acImportDelim, AdviserImportSpecification, tblName, InputDir & ImportFile, True

ImportFile = Dir
Loop


End Sub

However.
My table (tblAdviserData) has a field called Plan Number which is a mix of text or numbers so I have formatted the field to Short Text.

ImportErrors occur where there is a number in the csv file !!!!!!!!!

It's fine if I manually import the data.

What can I do to correct this.

 

[Gasman]

I would have thought that the spec would need to be enclosed with " ?

EG
DoCmd.TransferText acExportDelim, "Standard Output", _ "External Report", "C:\Txtfiles\April.doc"

from https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-transfertext-method-access

 

[ECEK]

Gasman: That was the answer !! Yayyyyyy
Thanks to you all.
This was driving me mad. Ridiculous how complex this can be.

 

[ECEK]

For the record: This is the generic solution.

'Import your data manually remembering to select advanced and to save the specification
'Then subsitute the folder where the cvs files are
'The Name of the Access Table (Unless you want individual tables)
'And the name of you saved specification

Private Sub Command0_Click()

Dim InputDir, ImportFile As String, tblName As String

InputDir = "C:\My folder\"

ImportFile = Dir(InputDir & "*.csv")

Do While Len(ImportFile) > 0
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1)) 'This is to import each file into single tables.

tblName = "MyTable" 'This is the Access table to import all files into.

'don't do what I did and assume that saving the import proceedure is the same ans pressing advanced and saving a specification.
'Just replace MySavedSpecification with your spec name
DoCmd.TransferText acImportDelim, "MySavedSpecification", tblName, InputDir & ImportFile, True

ImportFile = Dir
Loop


End Sub

 

[Gasman]

Gasman: That was the answer !! Yayyyyyy
Thanks to you all.
This was driving me mad. Ridiculous how complex this can be.

Glad to know you got it working.
Logic says you should mark your duplicate thread as Solved as well?
Perhaps put a link to the original thread in case anyone has the same problem and for some reason does not find the original thread.?