Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    2

    Unanswered: Populate a table in an Access DB with a CSV

    I'm totally baffled at how to do this - All I want is to take a CSV file - or a text file with csvs and import it directly into my remote DB. All of this is for a website. I'm using VB as my scripting language. <-- although I suspect u already know that. I am stuck at this point :

    DoCmd.TransferText acImportDelim, ,ps1games, "E:\web\ws-xsurveys\db\ps1test.csv"

    but it doesn't like that - it says object needed. and I suspect it's talking about the actual database!

    and I have this :

    <%
    ' -- arranging the data base connecting information
    Dim more strProvider
    more strProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
    Dim strDatabase
    strDatabase = "DATA SOURCE =" & Server.MapPath ("./") & "\mdb_test.mdb;"
    Dim strComplete
    strComplete = more strProvider & strDatabase

    ' -- opening the data base connection
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.open strComplete

    ' -- indicating the path to the CSV file (without indication of path)
    Dim csv_path
    csv_path = Server.MapPath("./") & "\"

    ' -- SQL stringer for creating a table anew with all data from the CSV file
    SQL = "SELECT * INTO tabImport FROM [ test_csv.csv ] IN" "" & csv_path &" """ "TEXT;"" "

    ' -- SQL stringer for adding all data to the ACCESS table tabImport from the CSV file
    ' SQL = "INSERT INTO tabImport SELECT * FROM [ test_csv.csv ] IN" "" & csv_path &" """ "TEXT;"" "

    ' -- implementing the SQL statement
    objConn.Execute SQL

    ' -- latches and schedules the data base connection.
    objConn.Close
    Set objConn = emergency-hung
    %>


    This line : ' SQL = "INSERT INTO tabImport SELECT * FROM [ test_csv.csv ] IN" "" & csv_path &" """ "TEXT;"" " is the one that is plaguing me because I can't figure out what to put in between the quotes

    Well - I'd appreciate any insight.

  2. #2
    Join Date
    Feb 2002
    Location
    North Wales, UK
    Posts
    114
    I'm assuming this is asp code on a web site and not code your typing into Access??? If it is... then you will have a problem using DoCmd as it's a VBA command which cannot be used in asp vbscript code.
    J^ - web | email
    newsASP Developer

  3. #3
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    2

    hmm then what can u suggest ?

    because I can't get that SQL statement to work either

  4. #4
    Join Date
    Feb 2002
    Location
    North Wales, UK
    Posts
    114
    Hmm... if I was reading in a csv file for inserting into a database, it would be difficult to code if the format wasn't set in stone. I'd use the FSO object myself and read the file in line by line and use the split command to separate the line in fields then insert a record into the database.
    J^ - web | email
    newsASP Developer

Posting Permissions

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