Macro To Importing Several CSV Files into Access

[Lucas in London]

Hi,

I have about 25 CSV files located in a folder that I want to open in access as separate tables within one database. All files are identical in the sense that they all contain only two fields (Coolum 1 date field, column 2 data), the top rows contains column headers. The only thing that is different between the files in the data content and so the length of fields (number of rows).

Opening/importing each file is taking a while and was wondering if I could import all the files using some kind of macro? I'm thinking this should be easy given that I have to go through identical steps to import each file:

1) Get external Data - Import File and select my file
2) Select Comma Delimited in the next window
3) Click first row Contains field names in next window
4) Click import into a new table in next window
5) Do not assign a Primary key

I am completely new to macros for Access – do not even now how to record! So any help will be much appreciated.

Thanks,

Lucas

 

[Andrew Fergus]

Hi Lucas

I provided some VBA code to do exactly that in this thread:
http://www.mrexcel.com/board2/viewtopic.php?t=236546

You may want to have a read of that thread to see if you can apply my solution to your situation. If you get stuck, just post another question in this thread.

Andrew

 

[Lucas in London]

Andrew,

Thanks for the suggestion. I followed all the steps you outlined and when I run the macro I get the following Run-time error(31519): "You cannot Import this File" which takes place at the point where it says error on in the code below.

Private Sub Command4_Click()

Dim Counter As Integer

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\bulsarah\Files\trading\" 'change this to your actual directory
.SearchSubFolders = False 'set to True if you want to search subfolders too
.FileType = msoFileTypeAllFiles 'get all files in the directory

If .Execute() > 0 Then 'files found
For Counter = 1 To .FoundFiles.Count 'loop through files
.FileName = .FoundFiles(Counter) 'set / get the file name
'Change the "ImportFile" part in the line below if you are using a different table name
DoCmd.TransferText , , "ImportFile", .FileName, False 'import file ERROR
DoEvents 'don't take over all of the PC resources
Next Counter
MsgBox "Import complete.", vbInformation, "Done"
Else 'files not found
MsgBox "There were no files found.", vbCritical, "Error"
End If
End With

When you say all files will be imported into one table - does that mean in separate columns or rows, and how would the data be aligned? If I'm going to import all files into one table that I need the data arranged like this:

Say I have two CSV Files with two colomns, rows vary. Column one is a date field and col 2 is a value feild. In my table I only need to importnat the date feild once (as my primary key) and then from all subsquent files import only the second coloum from each file as the dates are the same. The name of each colum would equal the name of the file:

Example

File 1 called X:

Col 1 Value

10/06/06 22
11/06/06 45

File 2 called y:

Col 1 Value

10/06/06 3546
11/06/06 4491

So after importing the raw CSV files, my Access table would look like this:

Date X Y

10/06/06 22 3546
11/06/06 45 4491

If I cannot arrange the data like this then it maybe easier to import each files as separate tables and then I can run a query joining all the tables using the date feild/(primary key) to get the consolidated data (which was my orginal plan).

Hope this makes sense.

Thanks,

Lucas

 

[Andrew Fergus]

Hi Lucas

I can't recreate that error and a quick search on the internet shows this:

You cannot import this file.You cannot import a text file unless it has one of these extensions: |.

which is not very helpful. If I create a csv file using either Excel or Notepad then my code works fine. How are the csv files you are using created? Are you able to send me a copy of one to test? (my e-mail address is in my profile)

This import routine is designed to import the data like this:
10/06/06 22
11/06/06 45
10/06/06 3546
11/06/06 4491

This is 'denormalised' data which could (probably) be rearranged into the format you described using a query or two.

Does the routine import any data or does it fail on the first pass? Alos, does the import table already have data in it or the fields already set up? Access will create the 'importfile' table if it doesn't already exist when you run my code.

Andrew

 

[Lucas in London]

Hi Andrew,

I re-ran the macro, but this time deleting the table I created manually and now this imports fine. But the data is not aligned in terms of how I need it. You mentioned that I could potentially run a query to get it into the right format. But, how I would identify which data set belongs to which file?

I need the second feild in all the files placed in a separate column aligned using the date field. And I need the name of the coloum to reflect the name of the file so I can identify which field relates to what data.

I've emailed you a couple examples of the raw files I am using.

Thanks,

Lucas

 

[Andrew Fergus]

Hi Lucas

Use this for your import routine - but read the comments throughout the code to see what changes you need to make.

Private Sub Command4_Click()

'Enable the following references in the
'  VBA screen under menu option Tools > References:
'      Microsoft Office x.0 Object Library
'            (where x is your version number, e.g. 10, 11, 12 etc)

'Note : This process assumes there is an import table already created
'  with 3 fields called F1, F2 and F3 of the following types:
'  F1 = Date, F2 = Number (not integer), F3 = Text

'Change the line below to the actual name of your
'  import table in your database
Const MyImportTable As String = "ImportFile"
'Change the line below to the name of the
'  directory that contains the import files
Const MyImportFileDir As String = "C:\ImportFiles"

Dim Counter As Integer, tmpName As String

On Error GoTo ErrorHandler

DoCmd.SetWarnings (False)
DoCmd.Hourglass (True)
'Remove the line below if you don't want to clear the import table
' when you run this process
DoCmd.RunSQL "DELETE " & MyImportTable & ".* FROM " & MyImportTable & ";"

With Application.FileSearch
    .NewSearch
    .LookIn = MyImportFileDir
    .SearchSubFolders = False 'set to True if you want to search subfolders too
    .FileType = msoFileTypeAllFiles 'get all files in the directory
    If .Execute() > 0 Then 'files found
        For Counter = 1 To .FoundFiles.Count 'loop through files
            .FileName = .FoundFiles(Counter) 'set / get the file name
            DoCmd.TransferText , , MyImportTable, .FileName, False 'import file
            tmpName = Mid$(.FileName, Len(.LookIn) + 2)
            tmpName = Left$(tmpName, Len(tmpName) - 4)
            'add the source file name to the import table
            DoCmd.RunSQL "UPDATE " & MyImportTable & _
                    " SET " & MyImportTable & ".F3 = '" & tmpName & _
                    "' WHERE ((" & MyImportTable & ".F3) Is Null);"
        Next Counter
        'Remove the blank records created during the import
        '  due to the file headers (these may appear as import errors)
        DoCmd.RunSQL "DELETE " & MyImportTable & _
                    ".* FROM " & MyImportTable & _
                    " WHERE ((" & MyImportTable & ".F1) Is Null);"
        MsgBox "Import complete.", vbInformation, "Done"
    Else 'files not found
        MsgBox "There were no files found.", vbCritical, "Error"
    End If
End With

DoCmd.Hourglass (False)
DoCmd.SetWarnings (True)

ExitHere:
    Exit Sub

ErrorHandler:
    DoCmd.Hourglass (False)
    DoCmd.SetWarnings (True)
    MsgBox Err.Description, vbCritical, "Error# " & Err.Number
    GoTo ExitHere

End Sub

Once you have imported the data, use this query to view the imported data in the format you described:

TRANSFORM Sum(ImportFile.F2) AS [Value]
SELECT ImportFile.F1 AS [Date]
FROM ImportFile
GROUP BY ImportFile.F1
ORDER BY ImportFile.F1, ImportFile.F3
PIVOT ImportFile.F3;

The code for the query above is the SQL for the query - you can get into the SQL screen when designing your query by clicking on View > SQL View.

HTH, Andrew

 

[Lucas in London]

Hi Andrew,

Thanks very much for the code, I've tested it out and overall it works fine. I did notice when you import the data however that it only imports a whole number and not the decimal points even though as you suggested I set the second field value to number in the table design view.

Any ideas what this might be?

Thanks,

Lucas

--------------------------------------
Private Sub Command4_Click()

'Enable the following references in the
' VBA screen under menu option Tools > References:
' Microsoft Office x.0 Object Library
' (where x is your version number, e.g. 10, 11, 12 etc)

'Note : This process assumes there is an import table already created
' with 3 fields called F1, F2 and F3 of the following types:
' F1 = Date, F2 = Number (not integer), F3 = Text

'Change the line below to the actual name of your
' import table in your database
Const MyImportTable As String = "ImportFile"
'Change the line below to the name of the
' directory that contains the import files
Const MyImportFileDir As String = "C:\ImportFiles"

Dim Counter As Integer, tmpName As String

 

[Andrew Fergus]

Hi Lucas

When I tested your import data my routine imported the decimal places. In the design of the import table, I set the data type for F2 to Number (Double). If you set it to Integer it will cutt off the decimals. I didn't do anything else differently.

Andrew

 

[Lucas in London]

Hi Andrew,

I had mine set to long integer instead of double. This works now perfectly, thanks.

Lucas

 

[Lucas in London]

Hi Fergus,

You provided a great solution to my previous post so I thought I'd run this by you.

Do you know how easy it would be to adapt the code you provided to import excel worksheets with multiple data columns? Basically, I need to import the first sheet of three separate workbooks, but each sheet contains several columns of data. Can these all be imported into one table? If not, automated importing in three tables would be also be fine.

And then once imported, I'd like to run a similar SQL query to group all the variables by date to get a consolidated data file that I can use.

Compared to the previously, I am still trying to do the same as before, consolidate each file/varaibles by date field, but there are three main differences:

1) Excel files instead of CSV, but I can easily save as CSV if need be
2) Multiple columns of data instead of just one (dates in column A as before)
3) Because each excel sheet contains differing numbers of populated columns/variables, it may not be practical to import into one table as before, I don't know. But if the all the data can be consolidated using a SQL query then importing into separate tables is fine.

If you can suggest a solution, it would be a major relief, as right now, with my limited knowledge, I'm really struggling with the complexity of the task

Many Thanks,

Lucas