Export excel data to SQL server in ASP
-
Hi Friends, Kindly help me for this. I wanted to export of excel data to table in sql server. I have to compare the column name in excel and table then export that column then next column. Like this i wanted this export. Please help... Thanks in Advance, Regards,
-
Hi Friends, Kindly help me for this. I wanted to export of excel data to table in sql server. I have to compare the column name in excel and table then export that column then next column. Like this i wanted this export. Please help... Thanks in Advance, Regards,
' Path to excel file z="uploadasp/contact_data.xls;" Path = Server.MapPath(z) SQL_Query = "SELECT * FROM [Commercial$]" Driver ="{Microsoft Excel Driver (*.xls)}" DataSource = "DBQ=" & Path & ";" DataSource = DataSource & "DefaultDir=" & Path & ";" DataSource = DataSource & "Driver=" & Driver & ";" DataSource = DataSource & "FIL=excel 5.0;ReadOnly=1;" Set MyConn =Server.CreateObject("ADODB.Connection") MyConn.ConnectionTimeout = 15 MyConn.CommandTimeout = 30 MyConn.Open DataSource 'Set MyConn = Server.CreateObject("ADODB.Connection") 'Set MyConn = Server.CreateObject("ADODB.Recordset") 'strBaseFilePath ="uploadasp/contact_data.xls" 'MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ '"Data Source=" & Server.MapPath(strBaseFilePath) & ";" & _ '"Extended Properties=""Excel 8.0;""" 'RS.Open SQL_Query, MyConn, adOpenStatic 'Lets loop through the data and output it into a HTML table Set RS = MyConn.Execute(SQL_Query) IF RS.EOF THEN 'There is no data to show tot=0 ELSE 'Lets output the field names ' FOR EACH Field IN RS.Fields ' NEXT 'Now we will output each row of the data tot=0 tota=0 DO WHILE NOT RS.EOF if rs.fields(0)<>"" then Set rs4 = Server.CreateObject("ADODB.Recordset") rs4.open "select * from data where 1<>1",Con,1,3 rs4.addnew IF rs.Fields(0)<>"" THEN RS4("name")=rs.Fields(0) IF rs.Fields(1)<>"" THEN RS4("surname")=rs.Fields(1) IF rs.Fields(2)<>"" THEN RS4("primary_email")=rs.Fields(2) rs4.UPDATE end if RS.MoveNext LOOP END IF RS.Close Set RS = nothing MyConn.Close Set MyConn = nothing =================== Hope this code will help