Import Data 1

[TheRiver]

Currently I have a procedure where by I have serveral text files or csv files stored in a folder on my drive. Each month I have to import these into existing tables within Access using import specs.

Does anyone know any ways of streamlining this procedure?

 

[p27br]

What is your procedure ?
Are you using Docmd.TransferText ?

Here is an example :

Sub ImportCSV()

Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = "mypath"
.FileName = "*.csv"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportDelim, "My Import Specification", "tablename", .FoundFiles(i)
Next i
End If
End With
End Sub

 

[TheRiver]

I dont use any procedure at the moment I manually load the data into the tables.

What is the i as integer?

and do I do this code within a module?

 

[BNPMike]

I would look at Macros. These are dead easy to use. It'll only take you half an hour to understand them and then 5 mins to automate your loads. The macro action you need is TransferText, I think.

The VBA solution is more sophisticated but I always think that if you want to be a Visual Basic programmer then why use Access?

 

[TheRiver]

Thats great I worked it out I did the following:

Added the following within a module:
Function ImportTXT()

Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = "K:\WL CMDS 2003-2004\Dudley Group of Hospitals NHS Trust\Inpatient Waiting List CMDS"
.FileName = "08 IWL Nov (QEE).txt"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL 04 RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With
End Function

And set up a macro to run it. It works great, thanks for your help

 

[TheRiver]

I also tried using the macro too, and that worked great too. Both solutions offered worked a treat.

 

[p27br]

glad I could help.
the integer is just a counter variable that is used by the for loop to run through the array of files found by the search.
For example the search returns 3 files :

.foundfiles(1) = file1
.foundfiles(2) = file2
.foundfiles(3) = file3

the loop will execute three times with i having as value 1 then 2 then 3.

 

[TheRiver]

Can you help again...

Function ImportCSV()

Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = "K:\WL CMDS (Current Financial Year)\Dudley Group of Hospitals NHS Trust\Inpatient Waiting List CMDS"
.FileName = "08 IWL Nov (QEE).txt"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL 04 RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With


End Function


I did this and it works great, but what I would like is to be able to have the option to choose different filename each time I run the module. Is this possible?

 

[p27br]

Hi
what you did is search for a specific file by assigning a specific value to the .FileName property. If you look at the sample code I posted, I used the asterisk symbol as a wild card, which will result in returning all files with a .csv extension.
You should try *.txt to return all Text files.

 

[TheRiver]

that is great and it did work pulling all files across and is useful to know.

But ideally each month a new file is added to the folder, and I want to only import the latest file added not all files. the names of the files in the folder are different each month.

 

[TheRiver]

Also what if I wanted to select just specific two files from this folder what would I do?

 

[p27br]

just add this line of code :

.LastModified = msoLastModifiedThisMonth

Generally it is best not to use constants in the code. If you add a parameter to the function, it will accept any number of files :

Sub ImportCSV(ByVal strFilePath as string, ByVal strFiles as String)

Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = strFilePath
.FileName = strFiles
.LastModified = msoLastModifiedThisMonth
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL 04 RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With
end sub

then call the Sub like so :

ImportCSV "C:\Windows", "autoexec.bat;config.sys"

Another remark : it's better to avoid spaces in table names. This can cause problems in queries and saves you the use of brackets. Try this notation : tblWL_CMDS_(RNA00)

 

[TheRiver]

You've kind of gone of my head now.

 

[TheRiver]

Can we go back to this bit?

'Import data from folder
With Application.FileSearch
.NewSearch
.LookIn = "K:\WL CMDS (Current Financial Year)\Good Hope Hospital NHS Trust\Inpatient Waiting List CMDS"
'Below File needs to be updated each month with the latest filename
.FileName = "08 IWL Nov (5MG).txt"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportDelim, "IPWL 05 RJH Import", "WL CMDS (RJH00)", .FoundFiles(i)
Next i
End If
End With

I got it this far fine

what if I wanted to import the following files aswell

08 IWL Nov (5MJ).txt
08 IWL Nov (5MG).txt to the import spec can this be done with out copy and pasting the above code?

 

[p27br]

try .FileName = "08 IWL Nov (5M*).txt"

good luck.
i'm leaving for today

 

[TheRiver]

Thanks for your help so far, would never have got this far without it.

But I tried that and it didnt work.

Error message of Invalid procedure call or arguement.

 

[p27br]

Hi

could you paste the code you have now ?
also, how are you calling the procedure ?

 

[TheRiver]

Function CSV()

Dim i As Integer

'Import data from folder
With Application.FileSearch
.NewSearch
.LookIn = "K:\WL CMDS (Current Financial Year)\Dudley Group of Hospitals NHS Trust\Inpatient Waiting List CMDS"
'Below File needs to be updated each month with the latest filename
.FileName = "08 IWL Nov (5M*).txt"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL 04 RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With

End Function


This is the whole procedure, what do you mean by calling the procedure? I run it using a macro.

 

[TheRiver]

Anyone got any ideas with the above post still got problems?

 

[p27br]

do this :

create a new form. Add a command button on the form.
Name : cmdImportCSV

add two text boxes :
Names txtPath and txtFiles

Open the properties window for this button.
On the OnClick event of the Event Tab select Event Procedure. Then click on the ... icon to the right.
That will open the form module and create the event procedure that should look like this after adding the procedure call :

Private Sub cmdImportCSV_Click()

ImportCSV me!txtPath, me!txtFiles
End Sub

and the procedure should be like this :

Sub ImportCSV(ByVal strFilePath as string, ByVal strFiles as String)

Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = strFilePath
.FileName = strFiles
.LastModified = msoLastModifiedThisMonth
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
DoCmd.TransferText acImportFixed, "IPWL 04 RNA Import", "WL CMDS (RNA00)", .FoundFiles(i)
Next i
End If
End With
end sub

hope you can make this work