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 :
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;"
strDatabase = "DATA SOURCE =" & Server.MapPath ("./") & "\mdb_test.mdb;"
strComplete = more strProvider & strDatabase
' -- opening the data base connection
Set objConn = Server.CreateObject("ADODB.Connection")
' -- indicating the path to the CSV file (without indication of 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
' -- latches and schedules the data base connection.
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
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.
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.