importing csv into access db via asp

[rob]

Hi all,

I've got a csv file which I will need to import into an access db via asp.
The csv file will be imported every day of each month so there will be
duplicates which will not need to be imported. Any sample scripts pointing
me in the right way would be great.

Many thanks,
Rob

 

[Michael Wendell]

Rob,

I spent today working on importing a csv/xls group of files and this may
help you:

<%
' create and open the connection to the database
Set cn = Server.CreateObject("ADODB.Connection")
' create and open the recordset
cn.open "ODBC;DBQ=c:\items.xls;Driver={Microsoft Excel Driver (*.xls)}"
set rs=server.createobject("ADODB.recordset")
rs.activeconnection=cn
rs.source="select * from ITEMS"
'NOTE: you will want to create a named range of all data cells in your
CSV file
'by highlighting them all, and typing a name in the cell name box in
upper left
'of excel sheet...then select from it as you would in sql...
rs.open
%>

<TABLE Border=1 CellPadding=3>
<TR>
<% For col = 0 To RS.Fields.Count-2 %>
<TH><% = RS.Fields(col).name %></TH>
<% Next %>
</TR>
<% Do While Not RS.eof %>
<TR>
<% For col = 0 To RS.Fields.Count-2 %>
<TD><% = RS.Fields(col).value %></TD>
//here you can do whatever you need, as in write out to database...I am
writing to arrays then building a new CSV file

<% Next %>
</TR>
<% RS.MoveNext %>
<% Loop %>
</TABLE>

<%
rs.close
cn.close
set rs = nothing
set cn = nothing
%>


Hope this helps some...

Mike

 

[Bullschmidt]

<<
I've got a csv file which I will need to import into an access db via
asp.
The csv file will be imported every day of each month so there will be
duplicates which will not need to be imported. Any sample scripts
pointing
me in the right way would be great.
You can connect to a csv file (which can be opened in Excel and which an
Excel file can be converted into) in good form just as you can to a
regular database.

And you can have two recordsets open at the same time.

So I'd suggest going through this recordset one row at a time and within
this loop add a new record to the "real" database's recordset.

And for help connecting to a text file using the Jet OLE DB provider:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#O
LEDBProviderForMicrosoftJetText

And based on the above link realize that the actual filename does NOT go
in the connection string - rather it goes in the SQL statement
(definitely a little tricky).

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...