Solved: Import Multiple Text Files (1600!)

[John_Mc]

Hi all,

I'm not sure what comes before newbie level.....but......

I have never used access, but have used VBA in Excel.

I need to import 1600 Excel Comma Seperate Value files. Each has the same column structure (Columns A to AC), but can vary in the number of records (max 500rows).

I did write something in Excel to combine them all, but it uses up the 65000 rows too quickly and therefore still leaves me with loads large files to import.

I have the files saved in C:\Jenny (no sub-directories or anything).

Can someone please tell me how I can import this many into a new table?

I'm hoping someone has a 'copy and paste' set of code I can just paste into a module and then run from a form (where i put a button, behind which the code sits (is that the way it works in Access?).....fingers crossed.

Many thanks for any advice,
John

[mdmackillop]

Hi John,
I'm no access expert, but give this a try. It assumes you have an appropriate table design to suit the files for import.
I would advise copying a few csv files to a separate folder for testing.
Regards
MD


Option Compare Database
Sub DoImport()
Dim MyPath As String, MyName As String, MyTable As String

'Table where files are to be appended
MyTable = "Test"
MyPath = "C:\Jenny\*.csv" ' Set the path.

MyName = Dir(MyPath) ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
DoCmd.TransferText acImportDelim, "", MyTable, MyName, False, ""
MyName = Dir ' Get next entry.
Loop

End Sub

[John_Mc]

Hi mdmackillop,

Thanks for your advice, you're a life saver - especially since contracts are up for renewel soon(!) :thumb


I had to change the code slightly as it gave me a 'could not find file' message. I changed it from:


DoCmd.TransferText acImportDelim, "", MyTable, MyName, False, ""


to

DoCmd.TransferText acImportDelim, "", MyTable, "c:\Jenny\" & MyName, False, ""


For anyone else trying to do the same thing, I'll explain my process below (note: this is how i did it and I am a definite newbie to Access. As i couldn't find a guide on the web when i searched (at leaset one i could understand, I thought i'd try to explain a bit here in case anyone wants to do the same).

Create a new table, called MyTable (no space)
Go into Design view and enter the field names to correspond to each heading in the csv file you want to import.
Select the relevant data type for each field (which matches the data type in the csv file - otherwise access will exclude any that don't match)
Save the table
Next, create a form. Click design view.
If the toolbox isn't visiible, view/toolbars/toolbox.
On the toolbox, select the command button and draw a button on the form.
Next, right click on the button and choose build event, then choose code from the dialog box.
The VBA code window opens. Add the above code (just the bit below the sub Import... and above the end sub).

You'll see in the code window Access has already put a sub....end sub in there,so don't duplicate it.

Save, close the vba window.
Now to run the macro, open the form in normal view and press the button.

If you have set the data types correctly, everything will import, otherwise it create a log file which tells you which field datatypes did not match.

One word here, if you have header files, access will reject them, which isn't a problem, except that it gives you that error message and creates a log file.

So, i got some excel code (below) and altered it to delete the header row for each file in the c: directory and then resave it (i'm sure vba gurus could programme the original access code to do this, but i couldn't).

i got this from a website, but in my haste yesterday forgot to note the coder, so apolgies for not naming the original source



Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
Unload Me
'Fill in the path\folder where the files are
'on your machine
MyPath = "c:\Jenny"
'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If
'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.csv")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If
On Error GoTo CleanUp
Set basebook = ThisWorkbook
'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
Rows("1:1").Delete
ActiveWorkbook.Save
ActiveWorkbook.Close False
On Error Resume Next
Next Fnum
End If



I ran this code in Excel, got rid of the headers and then the upload worked fine.

It is worth testing on a few files, otherwise you could potentially get 1000's of error messages (as you get one for each file that you try to import in Access).

Also, my directory is called c:\Jenny. Which a folder called Jenny on my C drive. You will need to change this line of code to whatever the folder is called on your drive.

Hope this helps ,

Cheers,
John Mc

[Norie]

John

To deal with the headers check help.


Syntax

DoCmd.TransferText [transfertype][, specificationname], tablename, filename[, hasfieldnames][, HTMLtablename][, codepage][code]


hasfieldnames Use True (?1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default (False) is assumed.

[mdmackillop]

Hi John
Glad it worked out.
Regards
MD