Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excel/ Access 2007.
    Hello.
    I've created a procedure that imports a csv file. I'm then using DAO to create a new table (TableDef)
    and copy the (csv) data into this new table. Is there an easier way to do this, without me having to
    loop through all the rows? Similarly, would there be an easier way if I were appending the records to
    an existing table? Thanks, Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could run an SQL statement:

    Dim strSQL As String
    Dim strNewTable As String
    Dim strCSVTable As String
    strNewTable = "tblNew"
    strCSVTable = "tblImport"
    strSQL = "SELECT * INTO [" & strNewTable & "] FROM [" & strCSVTable * "]"
    CurrentDb.Execute strSQL, dbFailOnError

    To append to an existing table, use

    strSQL = "INSERT INTO [" & strNewTable & "] SELECT * FROM [" & strCSVTable * "]"

    You can specify columns instead of *.


  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You could run an SQL statement:

    Dim strSQL As String
    Dim strNewTable As String
    Dim strCSVTable As String
    strNewTable = "tblNew"
    strCSVTable = "tblImport"
    strSQL = "SELECT * INTO [" & strNewTable & "] FROM [" & strCSVTable * "]"
    CurrentDb.Execute strSQL, dbFailOnError

    To append to an existing table, use

    strSQL = "INSERT INTO [" & strNewTable & "] SELECT * FROM [" & strCSVTable * "]"

    You can specify columns instead of *.
    Thank you. Is tblImport an Excel range name? Andy.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I had assumed from your description that you had already imported the CSV file into an Access table named tblImport, and wanted to transfer its records to a new or existing table.


  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    I had assumed from your description that you had already imported the CSV file into an Access table named tblImport, and wanted to transfer its records to a new or existing table.
    No, sorry. The csv data is currently in an Excel worksheet. I now need, from Excel, to either create a new
    Access table or append to an existing one. Andy.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use DoCmd.TransferSpreadsheet in Access to import from an Excel workbook, or DoCmd.TransferText to import directly from a .csv file.
    If you want, you can use Automation to do this from Excel.

    Alternatively, you could use ADO - it can run SQL on an Excel worksheet or .csv file.

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You could use DoCmd.TransferSpreadsheet in Access to import from an Excel workbook, or DoCmd.TransferText to import directly from a .csv file.
    If you want, you can use Automation to do this from Excel.

    Alternatively, you could use ADO - it can run SQL on an Excel worksheet or .csv file.
    Thank you very much. Andy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •