import .CSV

I need to import .csv files into and existing table in Access.  How would i do that?
vbnetcoderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
from ribbon
Get external Data > (Import group) Text File
Locate the .csv file and click open
select the Import the source..
click OK

follow the wizard...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
Can i save this import to reuse it latter?
Rey Obrero (Capricorn1)Commented:
yes, in the last window after the import, check the box to save the import steps
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

PatHartmanCommented:
There are two wizards in play when you do the import.  Rey gave you instructions for newer one of them.   I prefer the original option since it gives you more options and you don't have to redo it if you move the source file.

On the last form of the dialog, press the Advanced button.  Save the Import/Export and give it a short name (no spaces or special characters).

Then you can use TransferText in code to automate the import/export.  Just refer to the name of the spec you saved.
vbnetcoderAuthor Commented:
Do you have some code that you can post one how to use the TransferText?  I have a file dialog that I want the user to select the file and then do the import based on the file selected.
PatHartmanCommented:
Just reference the form field where the file name was placed.

If you simply start typing - TransferText, you will get intellisense which will help you with the arguments.  I don't like appending directly to permanent tables so I link first and then run an append query where I can do some validation and cleanup.  Me.txtFileName is the name of the control that holds the name of the file the user selected with the file dialog.
Private Sub cmdMARemit_Click()
Dim db as DAO.Database
Dim qd as DAO.Querydef

    On Error GoTo ErrorProc

    db.TableDefs.Delete "Link_MARemit"
    DoCmd.TransferText acLinkDelim, "MA_Remit", "Link_MARemit", Me.txtFileName, True
    Me.txtMARemitName = Me.txtFileName
    Me.txtMARemitDate = Now()
    Set qd = db.QueryDefs!qAppendMARemit
    qd.Execute
    
ExitProc:
    On Error GoTo 0
    Exit Sub

ErrorProc:

    Select Case Err.Number
        Case 91, 3265, 3011
            Resume Next
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdMARemit_Click of Form_frmMain"
    End Select
End Sub

Open in new window

Rey Obrero (Capricorn1)Commented:
@vbnetcoder,

- what version of Access are you using? 2007 or above?

- are the .csv files have the same format, but different names ?
vbnetcoderAuthor Commented:
i am using 2013.  I will have .csv files in different format but i plan on creating a different import for each.
vbnetcoderAuthor Commented:
What is the point of these lines: 9 & 10

 Me.txtMARemitName = Me.txtFileName
    Me.txtMARemitDate = Now()
Rey Obrero (Capricorn1)Commented:
- first you need to create an import specification for each different format

To create the Import Specification
   1) Click on external data> text file which then opens another window called "Get external data - Text file"
   2) Use radio button to select "Import the source data into a new table in the current database"
   3) specify the source of the file using the browse procedure then click OK
   4) Choose radio button to select delimited format and then click next
   5) this window allows you to choose delimiter and text qualifier and if first row contains names - click next
   6) This window allows you to type the name of the field in the Field Name column, choose data type, and if you want field indexed - also can choose to skip field - then click next
   7) This window allows you to add primary key or designate field as such - click next
   8) Click on ADVANCED button
   9. in the import specification window
   type the name of the field in the Field Name column
   (here you can use the field names of the destination table, specify data type,
   check the box Skip if you do not want to import the column)
   10 click save as, give the specification a name <-- this is the specification name that you will use in the command line below


 DoCmd.TransferText acImportDelim, "ImportSpecificationName", "TableName", "C:\folderName\myText.txt" , False


using the selected file from your browse codes
private sub cmdImportCSV_Click()

dim strFileName as string

strFileName= fSelectFileCSV()

 DoCmd.TransferText acImportDelim, "ImportSpecificationName", "TableName",strFileName, False 

end sub

Open in new window

PatHartmanCommented:
Lines 9 & 10 are part of my import logging.  The form is bound and this populates the file name and date/time it was imported.
vbnetcoderAuthor Commented:
ty
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.