Import csv file into database

[Leesha]

Hi,

I need to be able import .csv files from C:\Import into Access tables named tblImportTemp. The number of files per month may vary and the names may not always be the same. What would this code look like and is it possible? In the past I've import excel files via code but wasn't sure if the same could be done with csv files.

Thanks!
Leesha

[Wendell]

Essentially you use the TransferText command which works fairly similarly to the TransferSpreadsheet command. See TransferText for the details of how to invoke it.

[HansV]

You need to create an import specification; you need to do this only once:
- Select File | Get External Data Import...
- Select Text Files (*.txt, *.csv, *.tab, *.asc) from the File Type dropdown.
- Select a suitable .csv file and click Open.
- Follow the instructions and click Next > until you're on the last step of the Import Text Wizard.
- Instead of clicking Finish, click Advanced...
- Click Save As...
- You can accept the name proposed by Access or provide a new name. Write down the name that you use.
- Click OK twice to return to the Import Text Wizard.
- You can cancel the wizard (or click Finish if you wish to make sure that all settings are OK).

The code you need to import a ,csv file is

Code: Select all

   Dim strFileName As String
    Dim strTableName As String
    Dim strSpecName As String
    strFileName = "..." ' specify the file name here
    strTableName = "..." ' specify the table name here
    strSpecName = "..." ' use the name that you gave the specification
    DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:=strSpecName, _
        TableName:=strTableName, FileName:=strFileName, HasFieldNames:=True

If the .csv file doesn't have field names in its first line, change HasFieldNames:=True to HasFieldNames:=False.

[Leesha]

Worked like a charm!!!! Thanks so much. I never used the "advanced" button before. Pretty neat.

Thanks,
Leesha

[Leesha]

This is probably a dumb question, but where do the specifications get saved? Is there anything special I will need to do when I upload this to another computer?
Thanks!
Leesha

[HansV]

Import and export specifications are saved in the database itself (in two hidden system tables), so you don't need to do anything when you send the database to another computer.

[Leesha]

Well that certainly works for me!! One less thing to have to remember. Since there will more than one csv file / month and the names may vary, is it possible to do this as a loop the code and somehow set then name of the csv to not be a specific name?

Leesha

[HansV]

You can assemble the name in a loop. If you want help with that, please provide more detailed information.

[Leesha]

This is what my code looks like now. I'm assuming that I add "loop" at the end and I need to replace the actual strFileName with something that doesn't actually name the file??

Dim strFileName As String
Dim strTableName As String
Dim strSpecName As String
strFileName = "TestCSV.csv" ' specify the file name here
strTableName = "tblCSVImportTemp" ' specify the table name here
strSpecName = "CSVSpecs" ' use the name that you gave the specification

DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:=strSpecName, _
TableName:=strTableName, FileName:=strFileName, HasFieldNames:=True

[HansV]

What do you want to do in the loop? Process all .csv files in a folder? Or ...?

[Leesha]

Sorry Hans, yes, my goal is to push the button and all of the .csv files would get pulled into tblCSVImportTemp.
Leesha

[HansV]

What is "all of the .csv files"? All files in a specific folder? If so, can we hard-code that folder? Or should the code prompt the user to select a folder? Or...?

Please try to provide specific information.

[Leesha]

It would be all of the .csv files that are in folder C:\Import. It just dawned on me that you didn't have that info as its in the specifications!!

Sorry,
Leesha

[HansV]

You could use code like this:

Code: Select all

    Dim strFileName As String
    Dim strTableName As String
    Dim strSpecName As String
    Dim strPath As String
    ' Path, including backslash at the end
    strPath = "C:\Import\"
    strFileName = "TestCSV.csv"
    strTableName = "tblCSVImportTemp"
    strSpecName = "CSVSpecs"
    ' Path, including backslash at the end
    strPath = "C:\Import\"
    ' Get the name of the first .csv file
    strFileName = Dir(strPath & "*.csv")
    ' Loop through the files
    Do While strFileName <> ""
        ' Import .csv file
        DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:=strSpecName, _
            TableName:=strPath & strTableName, FileName:=strFileName, HasFieldNames:=True
        ' Get next filename
        strFileName = Dir
    Loop

[Leesha]

Hi Hans,

I get an error stating that the "Microsoft Jet Database engine could not find the object "AuntieAnn.csv. Make sure the object exists and the you spell the name and path correctly. The debug goes to
DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:=strSpecName, _
TableName:=strPath & strTableName, FileName:=strFileName, HasFieldNames:=True

I tried changing strFileName = "TestCSV.csv" to strFileName = "*.csv" but that didn't work. The file that it is naming is the first one in the folder. If I delete the file then it names the second one in the folder and so on. I wasn't sure if it was related to the specifications that I set earlier using the wizard. I didn't see a way to show the "hidden system tables" you told me about.

Leesha

[HansV]

Oh sorry, I got distracted by something else while writing the code (seems to happen frequently lately...). I mixed up some things.

Code: Select all

    Dim strFileName As String
    Dim strTableName As String
    Dim strSpecName As String
    Dim strPath As String
    ' Path, including backslash at the end
    strPath = "C:\Import\"
    strTableName = "tblCSVImportTemp"
    strSpecName = "CSVSpecs"
    ' Path, including backslash at the end
    strPath = "C:\Import\"
    ' Get the name of the first .csv file
    strFileName = Dir(strPath & "*.csv")
    ' Loop through the files
    Do While strFileName <> ""
        ' Import .csv file
        DoCmd.TransferText TransferType:=acImportDelim, SpecificationName:=strSpecName, _
            TableName:=strTableName, FileName:=strPath & strFileName, HasFieldNames:=True
        ' Get next filename
        strFileName = Dir
    Loop

[Leesha]

Not to worry! I know the feeling AND I learn so much mistakes or not :-)

Thanks!
Leesha

[Leesha]

Hi,

I'm trying to import a different CSV file than the one that was originally used in the question. I changed the CSVSpecs file and all seems to be working well with the exception of the fact that the Date_DS data doesn't import. I've verified that the field in the table is set as a date field. I'm not sure where else to look.

Thanks,
Leesha

[HansV]

If you look at the import specification (start the import manually, then click Advanced...), is the Date_DS field mentioned and specified correctly?

[Leesha]

Hi Hans,
Yes I checked that and even created a new one to be sure.
Leesha