Import multiples csv files in one table

%3CLINGO-SUB%20id%3D%22lingo-sub-1114911%22%20slang%3D%22en-US%22%3EImport%20multiples%20csv%20files%20in%20one%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1114911%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3EI%20have%20a%20code%20where%20we%20can%20import%20many%20csv%20files%20in%20one%20table%2C%20this%20works%20fine%2C%20but%20the%20user%20need%20to%20drop%20the%20files%20in%20a%20specific%20folder.%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20adapting%20the%20code%20below%20with%20a%20dialog%20function%20to%20allow%20the%20user%20to%20looking%20the%20Csv's%20files%20in%20any%20folder%20and%20then%20selected%20one%20or%20many%20at%20the%20same%20time%20to%20import%20in%20one%20table%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20helping%3C%2FP%3E%3CP%3ECarlos%3C%2FP%3E%3CP%3E------My%20Code------Access%20365---------------%3C%2FP%3E%3CP%3EPrivate%20Sub%20Command74_Click()%3C%2FP%3E%3CP%3EDim%20stLinkCriteria%20As%20String%3C%2FP%3E%3CP%3EConst%20strPath%20As%20String%20%3D%20%22C%3A%5C%22%20'Directory%20Path%3C%2FP%3E%3CP%3E%26nbsp%3BDim%20strFile%20As%20String%20'Filename%3C%2FP%3E%3CP%3E%26nbsp%3BDim%20strFileList()%20As%20String%20'File%20Array%3C%2FP%3E%3CP%3E%26nbsp%3BDim%20intFile%20As%20Integer%20'File%20Number%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoCmd.SetWarnings%20False%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B'Loop%20through%20the%20folder%20%26amp%3B%20build%20file%20list%3C%2FP%3E%3CP%3EstrFile%20%3D%20Dir(strPath%20%26amp%3B%20%22*.csv%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'''''''%20This%20line%20does%20not%20working%3C%2FP%3E%3CP%3E'''''''strFile%20%3D%20Application.GetOpenFileName(%2C%20strPath%20%26amp%3B%20%22*.csv%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BWhile%20strFile%20%26lt%3B%26gt%3B%20%22%22%3C%2FP%3E%3CP%3E%26nbsp%3B'add%20files%20to%20the%20list%3C%2FP%3E%3CP%3E%26nbsp%3BintFile%20%3D%20intFile%20%2B%201%3C%2FP%3E%3CP%3E%26nbsp%3BReDim%20Preserve%20strFileList(1%20To%20intFile)%3C%2FP%3E%3CP%3E%26nbsp%3BstrFileList(intFile)%20%3D%20strFile%3C%2FP%3E%3CP%3E%26nbsp%3BstrFile%20%3D%20Dir()%3C%2FP%3E%3CP%3E%26nbsp%3BWend%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B'see%20if%20any%20files%20were%20found%3C%2FP%3E%3CP%3E%26nbsp%3BIf%20intFile%20%3D%200%20Then%3C%2FP%3E%3CP%3E%26nbsp%3BMsgBox%20%22No%20files%20found%22%3C%2FP%3E%3CP%3E%26nbsp%3BExit%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3BEnd%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B'cycle%20through%20the%20list%20of%20files%20%26amp%3B%20import%20to%20Access%3C%2FP%3E%3CP%3E%26nbsp%3B'creating%20a%20new%20table%20called%20MyTable%3C%2FP%3E%3CP%3E%26nbsp%3BFor%20intFile%20%3D%201%20To%20UBound(strFileList)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'''''''Delete%20old%20data%3C%2FP%3E%3CP%3EDoCmd.RunSQL%20(%22DELETE%20*%20FROM%20my%20table%22)%3C%2FP%3E%3CP%3E'''''''Import%20fresh%20data%3C%2FP%3E%3CP%3EDoCmd.TransferText%20acImportDelim%2C%20%22mySpec%20%26nbsp%3BImport%20Specification%22%2C%20%22MyTable%22%2C%20strPath%20%26amp%3B%20strFileList(intFile)%2C%200%3C%2FP%3E%3CP%3ENext%3C%2FP%3E%3CP%3EMsgBox%20UBound(strFileList)%20%26amp%3B%20%22%20Files%20were%20Imported%22%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1114911%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

Hi everyone,

I have a code where we can import many csv files in one table, this works fine, but the user need to drop the files in a specific folder.

Is there a way to adapting the code below with a dialog function to allow the user to looking the Csv's files in any folder and then selected one or many at the same time to import in one table?

 

Thanks for helping

Carlos

------My Code------Access 365---------------

Private Sub Command74_Click()

Dim stLinkCriteria As String

Const strPath As String = "C:\" 'Directory Path

 Dim strFile As String 'Filename

 Dim strFileList() As String 'File Array

 Dim intFile As Integer 'File Number

 

DoCmd.SetWarnings False

 

 'Loop through the folder & build file list

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

 

''''''' This line does not working

'''''''strFile = Application.GetOpenFileName(, strPath & "*.csv")

 

 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 Sub

 End If

 

 'cycle through the list of files & import to Access

 'creating a new table called MyTable

 For intFile = 1 To UBound(strFileList)

 

'''''''Delete old data

DoCmd.RunSQL ("DELETE * FROM my table")

'''''''Import fresh data

DoCmd.TransferText acImportDelim, "mySpec  Import Specification", "MyTable", strPath & strFileList(intFile), 0

Next

MsgBox UBound(strFileList) & " Files were Imported"

End Sub

0 Replies