Thread: Importing csv to correct database

Hello, first timer, got a simple one

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("Filepathfolder") Then
        Set oFolder = oFs.GetFolder("Filepathfolder")
            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)
                            With wsp
                            Debug.Print oFile
                                DoCmd.TransferText acImportDelim, CSVimport, cleanname, oFile, True
                                .Close
                            End With
            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

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

Just out of curiosity, why are you doing this? I can't think of a reason you'd want 52 separate databases instead of one with 52 tables, or even just leaving them as csv's, but everyone does have their own business needs. Maybe this is unnecessary?

I can't find a way to transfer text to a database being manipulated by another database. Maybe there is a way, but I can't find it. However, if you really do want to accomplish this, there are options. I'd suggest one of the following:
1) Use basically the same code you have now, but after each csv is imported, transfer it to the new database, close that database, and delete the uploaded table. You could use a constant to specify the table name.
2) Create a "template" database. For each csv, open the template database. The template database should have an OnOpen event that will upload the csv and save the database with the csv's file name. Then you can close the database. You will need a way to pass the csv's name as a parameter to the template database.