SteveS
Posts: 41
|
| Posted: 03/25/2004, 6:31 AM |
|
Hi,
Having checked Google and numerous other sites I post my question here in the hope of assistance!
My Codecharge application (based on Access and ASP) needs to be able to import data from the csv file to an Access table. The columns in the csv file will match the Access table.
I need the asp to put a new table row in Access for each row in the CSV (as an append).
I'm sure there must be lots of apps that import from csv. Can anyone help with an example please?
Thank you, Steve.
|
 |
 |
Marcus
Posts: 49
|
| Posted: 03/25/2004, 9:10 AM |
|
In MS-Access use "File-Get External Data-Import" to import data from .csv text file.
|
|
|
|
David Payer
|
| Posted: 03/25/2004, 9:40 AM |
|
Marcus, I think he wanted to do this on his web application via ASP, not on
his personal machine with an Access DB but on a website using an access DB.
David P.
"Marcus" <Marcus@forum.codecharge> wrote in message
news:64063129e97382@news.codecharge.com...
> In MS-Access use "File-Get External Data-Import" to import data from .csv
text file.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
DonB
|
| Posted: 03/25/2004, 11:12 AM |
|
You will have to do a file upload or copy/paste into a textarea. Then plow
through it line by line and construct INSERT statements for each row.
I create a csv upload very easily with an app that used SQL Server database.
The MS-SQL Data Transformation Services is for just this sort of need. I
simply wrote data to a table (into a memo field), which triggered DTS to
execute a "package" (in their terminology - think of it as essentially a
stored procedure). So it was very easy to implement. But I realize you
don't have that with Access. - just making a comment.
--
DonB
http://www.gotodon.com/ccbth
"David Payer" <david.payer-no-spam-Thanks!@ia-omni.com> wrote in message
news:c3v5ib$b1f$1@news.codecharge.com...
> Marcus, I think he wanted to do this on his web application via ASP, not
on
> his personal machine with an Access DB but on a website using an access
DB.
>
> David P.
>
> "Marcus" <Marcus@forum.codecharge> wrote in message
>news:64063129e97382@news.codecharge.com...
> > In MS-Access use "File-Get External Data-Import" to import data from
..csv
> text file.
> > ---------------------------------------
> > Sent from YesSoftware forum
> > http://forums.codecharge.com/
> >
>
>
|
|
|
|
SteveS
Posts: 41
|
| Posted: 03/25/2004, 11:21 AM |
|
Hi,
Thanks for the info. The solution seems rather complex - any other solutions?
I have read various articles suggesting that the whole csv file could be read to memory and then INSERTed into the access table with a for/next loop?
Suggestions most welcome as this is my last problem before my app is completed.
Thanks & regards,
Steve.
|
|
|
|
BlinkyBill
Posts: 86
|
| Posted: 03/25/2004, 7:21 PM |
|
Quote SteveS:
Hi,
Thanks for the info. The solution seems rather complex - any other solutions?
I have read various articles suggesting that the whole csv file could be read to memory and then INSERTed into the access table with a for/next loop?
Just use ADO.
Step 1. Upload File (Kind of obvious)
Step 2. Create ADO Recordset from csv (important, text ODBC drivers need to installed on SERVER), look up msdn.microsoft.com kb on this.
Step3. Create a connection the Access DB
While NOT rs.EOF
conn.execute insert statement
rs.MoveNext
End
Kind of simplistic bu it will work.
Alt Step3, sexier, but I haven't tested this method.
Disconnect the RS from connection then connect it to the Access connection and save. I don't know if this method will work as I haven't played with disconnected ADO recordsets in 1.5 years. But the First method will definately work!
|
|
|
|
donsafar
Posts: 90
|
| Posted: 03/25/2004, 8:12 PM |
|
Below is a code snippet that I found on Planet source code. I had to modify it to get it to work, but I tested it and it does work. Make sure the csv file has as the first line the field names that match the access table. <%
'Making a connection with the database
set objConn= Server.CreateObject("ADODB.Connection")
strDBPath = Server.MapPath("./mdb_test.mdb")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"
' Path of the CSV file
Dim csv_path
csv_path = Server.MapPath("./") & "\"
'Use the next 7 lines of code to delete a table then insert records
'Make sure 1st line of csv file has field names that match access database
'On Error Resume Next ' IGNORE error if tabimport does not exist
'SQL = "DROP TABLE tabImport"
'objConn.Execute SQL
'On Error GoTo 0 ' turn off the ignoring of errors!
' SQL String for inserting CSV data into the Database
'SQL = "SELECT * INTO tabImport FROM [test_csv.csv] IN """ & csv_path & """ ""TEXT;"""
' SQL String for inserting CSV data into the Database
SQL = "INSERT INTO tabImport SELECT * FROM [test_csv2.csv] IN """ & csv_path & """ ""TEXT;"""
'Executing the SQL Statement
objConn.Execute SQL
'Closing down the connection
objConn.Close
Set objConn = Nothing
Response.write "finished the import"
%>
Hope this helps!
_________________
Don Safar
|
|
|
|
SteveS
Posts: 41
|
| Posted: 03/29/2004, 3:54 AM |
|
Don, thanks for your code. I have added it to a standard Codechare page.
The table that needs the csv data is called Pupil_Names, therefore I have changed the tabImport to Pupil_Names. Unfortunatley I get the following error:
Microsoft VBScript compilation error '800a03fd'
Expected 'Case'
/barit/import.asp, line 95
SELECT * INTO Pupil_Names FROM [import.csv] IN """ & csv_path & """ ""TEXT;""
Any ideas? Thanks.
Regards, Steve.
E-mail:steve@spstsl.co.uk
|
|
|
|
SteveS
Posts: 41
|
| Posted: 03/29/2004, 4:40 AM |
|
Please ignore last message. I've now found the orignal code from Planet which worked and has helped me customise my code for what I require.
Thanks all for your support.
Regards, Steve.
|
|
|
|
Norris
|
| Posted: 04/16/2004, 10:07 PM |
|
Hi donsafar & SteveS, can you post the link to Planet Source Code? I've been trying to do the same thing too......
|
|
|
|
SteveS
Posts: 41
|
| Posted: 04/17/2004, 6:22 AM |
|
Hi Norris,
Please send me your e-mail address and I'll be happy to send the code over. Worked well with a few tweaks!
All the best.
SteveS.
steve@spstsl.co.uk
|
|
|
|
Koby
|
| Posted: 04/23/2004, 4:07 AM |
|
Hi all,
I used the code posted by Don Safar and it works fine when I read from a text file which is seperated with ';' ! But I have file formates where the seperater is a pipe, how can I change this code to tell the program to use a different seperater!
Thanks in advance!
Regards
Koby
|
|
|
|
