Import csv file into database
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Import csv file into database
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
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
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Import csv file into database
Essentially you use the TransferText command which works fairly similarly to the TransferSpreadsheet command. See TransferText for the details of how to invoke it.
Wendell
You can't see the view if you don't climb the mountain!
You can't see the view if you don't climb the mountain!
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
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
If the .csv file doesn't have field names in its first line, change HasFieldNames:=True to HasFieldNames:=False.
- 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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
Worked like a charm!!!! Thanks so much. I never used the "advanced" button before. Pretty neat.
Thanks,
Leesha
Thanks,
Leesha
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
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
Thanks!
Leesha
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
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
Leesha
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
You can assemble the name in a loop. If you want help with that, please provide more detailed information.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
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
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
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
What do you want to do in the loop? Process all .csv files in a folder? Or ...?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
Sorry Hans, yes, my goal is to push the button and all of the .csv files would get pulled into tblCSVImportTemp.
Leesha
Leesha
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
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.
Please try to provide specific information.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
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
Sorry,
Leesha
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
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
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
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
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
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
Not to worry! I know the feeling AND I learn so much mistakes or not :-)
Thanks!
Leesha
Thanks!
Leesha
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
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
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
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Import csv file into database
If you look at the import specification (start the import manually, then click Advanced...), is the Date_DS field mentioned and specified correctly?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Import csv file into database
Hi Hans,
Yes I checked that and even created a new one to be sure.
Leesha
Yes I checked that and even created a new one to be sure.
Leesha