Gareth
|
| Posted: 09/27/2005, 6:20 PM |
|
How to import data from MS Excel to SQL database by ASP.
For example, already added a long list of contact people (name, phone, email, address...) in an excel file, how can I just upload the excel file to transfer the data to a table on the SQL server by ASP code?
Any help will be most appreciated!
Many thanks,
Gareth
|
|
|
 |
donb
Posts: 52
|
| Posted: 09/28/2005, 5:36 AM |
|
SOLUTION 1 Upload the file with the Upload control, open it with the ODBC driver for Excel files.
SOLUTION 2 Upload the file after it's saved in CSV format, then open it, read it, and use the Split() function in Asp to break the data apart line by line so it can be written to the database.
_________________
http://www.gotodon.com/ccbth |
 |
|
gareth
|
| Posted: 09/29/2005, 5:21 PM |
|
Hi Donb,
Thank you for your message.
Do you think it is possible to upload an excel document and then add all the records automatically to a pre-designed table with the same fields in the SQL server?
Many thanks.
|
|
|
|
Frode
|
| Posted: 10/11/2005, 3:40 AM |
|
Like this?!:
1. upload your excel file
2. make the connection to the file
Set fso = Server.CreateObject("Scripting.FileSystemObject")
psFilePath = "file.xls"
sPath = server.MapPath("\")
psFilePath = fso.BuildPath(sPath,psFilePath)
'Response.Write psFilePath
' Response.End
Set objConn = Server.CreateObject("ADODB.Connection")
strCnxn = "DRIVER=Driver do Microsoft Excel(*.xls);UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;ReadOnly=1;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;FIL=excel 8.0;DriverId=790;DBQ=" & psFilePath & ""
objConn.Open strCnxn
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn
objRS.CursorType = 3 'Static cursor.
objRS.LockType = 2 'Pessimistic Lock.
3. you geting out the cells you want, here its Cell1_name. (todo this you have to define the area in excel file (Excel: Insert->Name->Define)
sql = "Select " & periode & " from Cell1_name"
objRS.Source = sql
objRS.Open
objRS.MoveFirst
If objRS.Fields.Item(0).Value <> "" Then
lev_prosent = round((objRS.Fields.Item(0).Value * 100),1)
Else
lev_prosent = 0
End if
objRS.Close
4. next is to put it into sql database:
if update = "true" Then
' ====
' UPDATE
set g_conn = open_db()
set objRegExp = New RegExp
objRegExp.Pattern = ","
objRegExp.Global = true
Cell1_name= round(cell1_name,1)
set rs = Server.CreateObject("ADODB.Command")
set rs.ActiveConnection = a_conn
rs.CommandText = stSQL
rs.CommandType = 1
rs.Execute intNoOfRecords
'rs.close
5. Close the connection to the sql...
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
|
|
|
|
Frode
|
| Posted: 10/12/2005, 1:56 AM |
|
Or simply take a look at this:
http://support.microsoft.com/kb/q195951/
|
|
|
|
rohit
|
| Posted: 10/14/2005, 2:55 AM |
|
Quote Gareth:
How to import data from MS Excel to SQL database by ASP.
For example, already added a long list of contact people (name, phone, email, address...) in an excel file, how can I just upload the excel file to transfer the data to a table on the SQL server by ASP code?
Any help will be most appreciated!
Many thanks,
Gareth
|
|
|
|
frode
|
| Posted: 10/21/2005, 5:01 AM |
|
' =====
'Making variable from Excel
sql = "Select " & periode & " from area_in excel" 'make an area on insert->name->define in excel
objRS.Source = sql
objRS.Open
If objRS.Fields.Item(0).Value <> "" Then
variable1 = (objRS.Fields.Item(0).Value)
Else
variable1= 0
End if
objRS.Close
' ====================
' INSERT
set rs = server.CreateObject ("ADODB.Recordset")
set rs.ActiveConnection = a_conn
sql = "SELECT * FROM db_name"
rs.Open sql, ,1 , 2 , 1
rs.AddNew
rs("table1_sql") = variable1' variable from excel
rs("table1_sql") = variable2
rs.Update
rs.close
|
|
|
|
suman
|
| Posted: 10/21/2005, 5:45 AM |
|
hi,
how to write the url to connect the sal server
|
|
|
|
chese_girl
|
| Posted: 12/07/2005, 5:12 PM |
|
Quote rohit:
Quote Gareth:
How to import data from MS Excel to SQL database by ASP.
For example, already added a long list of contact people (name, phone, email, address...) in an excel file, how can I just upload the excel file to transfer the data to a table on the SQL server by ASP code?
Any help will be most appreciated!
Many thanks,
Gareth
the code that u guys gave me, cannot work...pls help
|
|
|
|
ah_kin19
|
| Posted: 01/04/2006, 11:08 PM |
|
i understand that if we wanna extract data from excel, we need to define the range ( Insert -->Name-->Define)
my question is how to extract only 2nd row, 7th row and 10th row of the data in excel ? i try highlight the rows specified and name the range, but it just doesn't work
pls help me
|
|
|
|
sankar
|
| Posted: 01/16/2006, 3:41 AM |
|
i urgrntly need the Codes to upload ecxel sheet to SQL Server using JSP
|
|
|
|
Walter Kempees
|
| Posted: 01/16/2006, 5:51 AM |
|
then I would suggest posting this in the Java part of this forum.
"sankar" <sankar@forum.codecharge> schreef in bericht
news:643cb866f4285b@news.codecharge.com...
>i urgrntly need the Codes to upload ecxel sheet to SQL Server using JSP
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Pedro Alves
|
| Posted: 01/25/2006, 4:22 AM |
|
Hi everybody!
If it is just necessary to specify the path of the excel file, how can I make the import of the data to an SQL server database using ASP code?
Please report the solution as soon as possible! It is very URGENT!
Thank you very much! 
|
|
|
|
rk
|
| Posted: 01/26/2006, 8:56 PM |
|
HI
how to import sql to excel sheet...
|
|
|
|
sanjith
|
| Posted: 01/27/2006, 10:45 PM |
|
Sir,
I would like to write data in the excel file (in a particular cell say c1,c2,c3..c8) from my asp program. Could you please help me to do this.
Thanks
Sanjith
|
|
|
|
satya
|
| Posted: 01/30/2006, 3:57 AM |
|
Below is the code to export Data to Excel through ASP...
Write this code in some asp file and invoke that asp file...
Code.....
<%@ Language=VBScript %>
<% Response.ContentType = "application/vnd.ms-excel" %>
<%
Dim strName
strName = Date()
strName = Replace(strName, "/", "") ' "/" Your local dateseparator?
strName = strName & ".xls"
Response.AddHeader "Content-Disposition", "attachment;filename=" & strName
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<table>
<tr>
<td valign=top><% Response.Write "satya1" %></td>
<td valign=top>satya2</td>
</tr>
</table>
</BODY>
</HTML>
|
|
|
|
satya
|
| Posted: 01/30/2006, 4:09 AM |
|
How to import data from excel sheet by use of asp.. I have a tricky problem here..
I have to import data from the excel and those data will be inserted into multiple tables.. data in the excel is in various fields scattered but in fixed cells.. In this case I can't use the Frode solution where it is directly attaching the excel..
Any body can help me on this...
|
|
|
|
satya
|
| Posted: 01/30/2006, 4:21 AM |
|
Quote satya:
How to import data from excel sheet by use of asp.. I have a tricky problem here..
I have to import data from the excel and those data will be inserted into multiple tables.. data in the excel is in various fields scattered but in fixed cells.. In this case I can't use the Frode solution where it is directly attaching the excel..
Also I don't want to make any changes in Excel like.. to create named ranges
Any body can help me on this...
|
|
|
|
Norbert
|
| Posted: 01/30/2006, 8:52 AM |
|
You can also try to use DTS from MS SQL.
1. Create DTS, referring to the file stored on the server
2. Check if manual import works fine
3. Write stored procedure that will start this DTS
4. Upload the file to the server
5. Change its name to the one defined in DTS
6. Execute stored procedure (p.3)
7. Check for errors
I use that technique to upload large amount of data stored in excel into MS SQL. If you decided to go that way please make sure that you taught people to always use correct column names, as DTS will refer literally to them, so if Worksheet name's changes or column name ... or whatever will deviate from definition, script will fail.
Best regards
Norbert
|
|
|
|
Arnie
|
| Posted: 01/30/2006, 7:37 PM |
|
Anybody knows how to upload an excel file from the client, instead from the server, I have multiple users who needs to upload an excel file from their PC to SQL server.
|
|
|
|
satya
|
| Posted: 02/01/2006, 4:34 AM |
|
Code goes here to upload an excel from client machine to browser...
there are a lot of commented code ignore them, because i was doing a R&D on it... But here is one issue if your user's excel is open and user tries to upload then this code will give error because ado will fail to estable the connection.. How to handel that I don't know anybody's help will be appriciated....
---------------------------------------------------------------------
<%@ Language=VBScript %>
<% Option Explicit%>
<%
Dim adoCn
Dim adoRs
Dim adoFld
Dim strQuery
Dim strResults
Const adUseClient = 3
Const adClipString = 2
Const strColDelim = " </td><td>"
Const strRowDelim = "</td></tr><tr><td>"
Set adoCn = CreateObject("ADODB.Connection")
Set adoRs = CreateObject("ADODB.Recordset")
With adoCn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = _
"Data Source=d:\ExcelAdo\Test.xls;" & _
"Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With
strQuery = "SELECT * FROM [Sheet1$] ORDER BY Name"
With adoRs
Set .ActiveConnection = adoCn
.Open strQuery
End With
%>
<html>
<head>
<!--<title>Still on the Island</title>
<style>
body {font:10pt tahoma;}
table {font:10pt tahoma;}
</style>-->
</head>
<body>
<!--<h1>Who's Still on the Island?</h1>-->
<table cellspacing="0"
style="border:1px solid silver;">
<!--<tr>
<td rowspan="<%'= adoRs.RecordCount + 2 %>">
<img src="island.gif">
</td>
</tr>-->
<tr>
<%
For Each adoFld in adoRs.Fields
Response.Write "<td style=""border-bottom:" & _
"1px solid silver;""><b>" & adoFld.Name & _
"</b></td>"
Next
%>
</tr>
<tr>
<td>
<%
strResults = adoRs.GetString(adClipString, _
adoRs.RecordCount, strColDelim, _
strRowDelim)
strResults = Left( strResults, _
InStrRev(strResults,"<tr><td>") - 1)
Response.Write strResults
%>
</tr>
<tr>
<td>Just a Delimiter</td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<%
' another wey to roam arround the record set..
if not adoRs is nothing then
adoRs.MoveFirst
'Response.Write "satya"%>
<td>First Value</td>
<td><%=adoRs.Fields(1).Value%></td>
<td><%=adoRs.Fields(0).Value%></td>
<td></td>
</tr>
<%adoRs.MoveNext%>
<tr>
<td>Second Value</td>
<td><%=adoRs.Fields(1).Value%></td>
<td><%=adoRs.Fields(0).Value%></td>
<td></td>
</tr>
<%while not adoRs.eof%>
<tr>
<td><%=adoRs.Fields(0).Value%></td>
<td><%=adoRs.Fields(1).Value%></td>
<td><%=adoRs.Fields(2).Value%></td>
<td><%=adoRs.Fields(3).Value%></td>
<td>2</td>
</tr>
<% adoRs.MoveNext
wend%>
<%end if%>
</table>
<%
adoRs.Close
set adoRs=nothing
adoCn.Close
set adoCn=nothing
%>
</body>
</html>
<!--<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<P> </P>
</BODY>
</HTML> -->
|
|
|
|
satya1
|
| Posted: 02/01/2006, 4:59 AM |
|
donb
can you help me how to go with your 2nd solution.. Because importing data from excel with ado comes with a lot of constrain, though it is easy..
can anybody help me on this solution.. my user will be updating data in excel so how to convert it to csv through asp and how to read from it ...
SOLUTION 2 Upload the file after it's saved in CSV format, then open it, read it, and use the Split() function in Asp to break the data apart line by line so it can be written to the database.
|
|
|
|
Pradip shaw
|
| Posted: 02/16/2006, 3:50 AM |
|
Quote Gareth:
How to import data from MS Excel to SQL database by ASP.
For example, already added a long list of contact people (name, phone, email, address...) in an excel file, how can I just upload the excel file to transfer the data to a table on the SQL server by ASP code?
Any help will be most appreciated!
Many thanks,
Gareth
|
|
|
|
gary
Posts: 2
|
| Posted: 03/01/2006, 6:54 PM |
|
I need help for this too. Anyone can help?
|
|
|
|
