Basic problem - CSV to new Database/Table

[ClimoC]

Afternoon all

I have very comprehensive Excel vba skills, but am learning Access the wrong way (or the right way but haven't gotten my head around the object structures yet)

I have lots of csv files I have created from an export. They are stats and figures from individual weeks, numbered 1-52

What I'm trying to do is the 'ImportTextWizard' in Access 2010, to create Database copies of the csv's so I can delete the csv's. There are a lot more than 52 because it goes over several years.

So far, in a module sitting in 'Database4.accdb' I have a module with the following code in it:

Public Function ImportAll()
Dim oFs As New FileSystemObject, oFolder As Object, oFile As Object
Dim cleanname As String
Dim wsp As DAO.Database

If oFs.FolderExists("Filepath") Then
        Set oFolder = oFs.GetFolder("Filepath")
            For Each oFile In oFolder.Files
                    cleanname = Left(oFile.Name, Len(oFile.Name) - (Len(oFile.Name) - InStr(1, oFile.Name, ".", vbTextCompare)) - 1)
                        Set wsp = DBEngine.CreateDatabase(oFolder.Path & "\" & cleanname & ".accdb", dbLangGeneral)
                            Application.OpenCurrentDatabase oFile.Path & "\" & cleanname & ".accdb"

                            Debug.Print oFile
                                DoCmd.TransferText acImportDelim, CSVimport, cleanname, oFile, True
        
            Next
End If
        
End Function

This does everything, but in the wrong way.

Using this code, what I get is 52 .accdb's in the Filepath directory, which are empty of tables, and I get 52 tables added to the Database4.accdb

In Excel I can specify which workbook/worksheet I'm dealing with, or, if all else fails, fall back on '.Activate' (yuck)

How can I get each Database to have a table of the same name, and one each? End result from "Week1.csv" should be to have one "Week1.accdb" with a table inside called "Week1"

NB: My specs 'CSVImport' are simply to be Delimited, with the 1st row as headers. Though I wonder if because the CSVImport was 'recorded'/'saved' when Database4.accdb was open, means that that is the database the specs say to write the table to?

Just a bit confused. I get the feeling I'm a parameter or 2 or a single line of code off this working...

All help appreciated, and I acknowledge the code may look amateur...because it is!

Thanks
C

 

[ClimoC]

I should also mention, the 'OpenCurrentDatabase' line was just something I tried, akin to the 'Worksheet.activate' method. It wasn't in there before when it worked. Previously, to get to the end results I have been experiencing, I had a 'With wsp' around the 'DoCmd.TransferText'

Thanks

 

[jackd]

ClimoC,
I saw your post and tried a few things to attempt what you were trying to do.
I don't have a lot of experience with Excel, but have worked with Access. I have Acc2003 , not 2010, and have no experience with 2007 or 2010 directly.

Your code is using a CreateDatabase and that part is working.
However, I don't believe there is any option in the TransferText that allows you to transfer(import in your case) to a different database. I think, based on my tests, it only allows you to import into the database your code resides in.

An option for you perhaps, open a database, lets call it MyCurrentDB.
Import all of the text(csv) files into MyCurrentDB.

Try your current code, or adjust as needed

DoCmd.TransferText acImportDelim, CSVimport, cleanname, oFile, True

Since you have created 52 empty databases which do have or could have some naming convention with a sequential suffix in the name, you could have a routine to
DoCmd.CopyObject to a destination database

The CopyObject method carries out the CopyObject action in Visual Basic.

expression.CopyObject(DestinationDatabase, NewName, SourceObjectType, SourceObjectName)

You could set this up in a Loop such that (conceptually)

-- For I = 1 to 52 (or whatever you max is)
-- DoCmd.CopyObject destinationDBName & I, tablename & I, acTable, tablename & I
--Next I

So if your databases were named Database1 thru Database52, and your tables were named Table1 thru Table 52. I think the above approach would work.

Post back and let us know.
Good luck

 

[ClimoC]

Thanks Jack - That's exactly what I ended up doing! Your post at least confirms my rationale which makes me feel a bit better.

So, still a bit scrappy, but here is the working code

Public Function ImportAll()
Dim oFs As New FileSystemObject, oFolder As Object, oFile As Object
Dim cleanname As String
Dim wsp As DAO.Database
Dim myDB As TableDef
Dim time1 As Long
Dim time2 As Long

time1 = CLng(Now())
If oFs.FolderExists(["FilefolderPath"]) Then
        Set oFolder = oFs.GetFolder(["FilefolderPath"])
        For Each oFile In oFolder.Files
                cleanname = Left(oFile.Name, Len(oFile.Name) - (Len(oFile.Name) - InStr(1, oFile.Name, ".", vbTextCompare)) - 1)
                Set wsp = DBEngine.CreateDatabase(oFolder.Path & "\" & cleanname & ".accdb", dbLangGeneral)
                wsp.Connect = False
                wsp.Close
                Debug.Print oFile
                
                DoCmd.TransferText acImportDelim, CSVimport, cleanname, oFile, True
                    
                Set mytd = CurrentDb.TableDefs
                    Debug.Print CurrentDb.TableDefs.Count
                    
                        For j = 0 To (mytd.Count - 1)

                            If mytd(j).Name = cleanname Then
                                                        Debug.Print mytd(j).Name
                                InTable = mytd(j).Name
                                OutTable = mytd(j).Name
                                OutDB = oFolder.Path & "\" & cleanname & ".accdb"
                                DoCmd.CopyObject OutDB, InTable, acTable, OutTable
                            End If
                        Next
                                    
        Next
End If
time2 = CLng(Now())
On Error Resume Next
Debug.Print CDate(time2 - time1)
Debug.Print time2 - time1
Err.Clear
On Error GoTo 0

End Function

So what this does, is create a database for each csv file in a folder, of the same name, then opens and copies the matching tables from the 'Home' DB (the one with this script module in it) to the appropriate db.

It also provided me at the end with a DB that had all 52 tables in it (the aforementioned 'Home' db we ran this script from), which I have saved as an overall repository.

Obviously, to anyone who in the future may read this post and use it for their own, you need to replace ["FilefolderPath"] with a legitimate Folder Directory path where the .csv files to start from and end result databases reside. In addition to this, the parameter 'CSVImport' is an Access Import specification. To anyone who doesn't know what this is, Google "Access Import Specifications Text" or words to that effect. It's just settings like "First row contains headers", "delimited" (comma separated), etc.

I also for timing purposes put in a time elapsed calculator to measure it, but it didn't work. 52 csv's turned into 52 accdb's, each about 7000-8000 records of 40-50 columns, took around 20 to 30 seconds each.