Could we import an Excel File into SQL Server 2000?

[giandeo]

Hello everybody

I am trying to import an excel file for days into MS SQL Server 2000, unfortunately, I cannot see a ray of hope till now.

I have surfed the WEB for hours, but again no concrete solution.

CAN ANY ONE COME TO MY RESCURE PLEASE.....


Actually, I am solving a problem for a student who wants to import an Excel file called ExamResults into SQL Server 2000.

The file ExamResults consists of sheet 1 with the following header info:
IndexNo
Surname
Name
Standard
Subject
Marks

I have created a database name 'Results' in SQL Server 2000 and wish to import that file therein.

Please help....

[teddarr]

I think you can do this using SQL Server Management Studio in SQL Server 2005, I don't know about 2000.

For 2005, right click on the target database, hover over Tasks, then select import data...

This will start the Import Export Wizard.

You could also write a custom program centered around the excel com object. I wrote a program like that a few years ago and it has helped me to learn many fundamentals that I use on a daily basis now. It has evolved as my skill and experience has evolved. Try it.

[giandeo]

I think you can do this using SQL Server Management Studio in SQL Server 2005, I don't know about 2000.

For 2005, right click on the target database, hover over Tasks, then select import data...

This will start the Import Export Wizard.

You could also write a custom program centered around the excel com object. I wrote a program like that a few years ago and it has helped me to learn many fundamentals that I use on a daily basis now. It has evolved as my skill and experience has evolved. Try it.

Hello Sir,
Thank you so so much for your intervention and suggestions

I have tried to import the Excel file using the DTS Import Export Wizard and it works.

But, I do not want to run the wizard times and again to import the Excel file. I wish to write a script using ASP codes to do the task.

Could anyone help me please.

[teddarr]

I'll try to walk you through the process and let you code it from there.

Create a new folder in your directory. Right on the C:drive for simplicity.

In the code behind page use the System.IO.Direc tory class to look in that folder and find the file name of the excel file you want to enter.

ex.
[code=cpp]
//-Attempt to process each discovered file-------------------
bool fileFound = false;
foreach (string filename in Directory.GetFi les("C:\\newFol der))
{
fileFound = true;


}
[/code]
Next, we'll convert the excel file to something we can use...an ArrayList.

[teddarr]

OK, the first step in converting the excel file to an ArrayList is to convert the excel file to a comma delimited string.

I'm going to give you this method in C#. It was passed on to me so I now pass it to you.

Moderators This is the only large amount of code I will post. I'm gonna let this person write the program themselves.

first add this to the header of the file and add a reference to Microsoft Excel 12.0:


using Excel = Microsoft.Offic e.Interop.Excel ;

private string ConvertExcelToCommaDelimited(string filename)
 {     //Excel objects             
      Excel.Workbook book;             
      Excel.Sheets sheets;
      //Start up an Excel instance
      Excel.Application ExcelObj = new Excel.Application();
      ExcelObj.DisplayAlerts = false;
      ExcelObj.Visible = false; 

      //-Process the desired contents------------------------------             
      try             
      {                 
            book = ExcelObj.Workbooks.Open(filename, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, true, false);
      } 
      catch 
      {
                 book = null;             
      }            
 
      if (book == null)            
       {                 
            //MessageBox.Show("Could not open the workbook (.xls file) in file '" + filename + "'!");                 
            return String.Empty;             
      }             

       //-Ensure sheet exists (avoid hanging if not found)
       sheets = book.Worksheets;             
       if (sheets.Count != 1)             
      {                 
            //MessageBox.Show("Excel file '" + filename + "' should contain only 1 sheet!");                 
      return String.Empty;            
       }             

      //-Get a temporary work file name
      string tempFileName = Path.GetTempFileName();  
      //-Save Excel data in comma delimited format 
      // (a single sheet file is expected)   
      book.SaveAs(tempFileName, 
            Excel.XlFileFormat.xlTextWindows,
            Type.Missing,    
            Type.Missing,     
            Type.Missing,   
            Type.Missing,  
            Excel.XlSaveAsAccessMode.xlNoChange,  
            Type.Missing, 
            Type.Missing,  
            Type.Missing,    
            Type.Missing,         
            Type.Missing);    
      //-Housekeep Excel       
      ReleaseCOMReference(sheets); 
      if (book != null)
      {  
               try 
               {    
                   book.Application.Quit();    
               }               
              catch                
             {		
                  //	Fail silently in case Excel is shutdown   
             }		
            //external to this application.
            ReleaseCOMReference(book); 
            book = null;             
      }             
       //-Return the name of the file that was created--------------             
      return tempFileName;         
 }
//end ConvertExcelToTabDelimited()

Let me know when your up to here.

[teddarr]

Next, in that first method, create a string variable. Call it something like "tempfile".

make tempfile equal to the method call to the method I just gave you with the filename of your excel file as the argument.

[Frinavale]

Hello Sir,
Thank you so so much for your intervention and suggestions

I have tried to import the Excel file using the DTS Import Export Wizard and it works.

But, I do not want to run the wizard times and again to import the Excel file. I wish to write a script using ASP codes to do the task.

Could anyone help me please.

Are you using ASP.NET to implement your solution?

[Curtis Rutland]

teddarr, it looks like you are giving a C# solution...whic h would be fine if this were ASP.NET, but currently it is in Classic ASP, so this code may not be much help.

To OP:
As Frinny has asked, are you using Classic ASP or ASP.NET (does your file end in .asp or .aspx)?

If it is .NET, we can move it to that forum and you will get more help. If not, we'll leave it here for the ASP experts.

EDIT:

Also, if you don't want to use any scripting, you can write a DTS package that won't require you to step through the wizard each time. This package will reside on the SQL Server itself. You will just find the package and click "Execute." Let me know if this is an option. If it is, we'll move this to the SQL forum, and I can help you out there.

[NeoPa]

I'm at home now so don't have access to my SQL Servers, but isn't there a special tool within SQL Server 2000 servers to handle importing and exporting data from / to various formats. I forget the name for now, but if you dig through the SQL administration tool you should see it.

I remember it was new in 2000. Unfortunately I haven't done much SQL work recently. I set it up so well all those years ago I hardly ever have to touch it :D (That's my excuse and it sounds much better that way)

[giandeo]

teddarr, it looks like you are giving a C# solution...whic h would be fine if this were ASP.NET, but currently it is in Classic ASP, so this code may not be much help.

To OP:
As Frinny has asked, are you using Classic ASP or ASP.NET (does your file end in .asp or .aspx)?

If it is .NET, we can move it to that forum and you will get more help. If not, we'll leave it here for the ASP experts.

EDIT:

Also, if you don't want to use any scripting, you can write a DTS package that won't require you to step through the wizard each time. This package will reside on the SQL Server itself. You will just find the package and click "Execute." Let me know if this is an option. If it is, we'll move this to the SQL forum, and I can help you out there.

Sir
I am using classic/pure ASP. I am writing my scripts in Dreamweaver MX 2004. My file ends with .asp

Frankly speaking, I am not an expert in programming but I always struggle to learn and help students in my locality.

I heartfully thank all of you for your valuable help.

As stated earlier, I know how to import the excel file into SQL Server 2000 using the DTS import Export wizard.

I want to automate the process through a script. That is, the table in SQL Server will be dropped and created again each time a user click a button to import data from the excel file.

I have surfed the net and got some bits and pieces of codes which can read data from the excel file and display the result on the browser. I do not know how to write the data to a table in SQL Server.

Here are the codes:

<%@ 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=c:\ExcelAdo\Test.xls;" & _
"Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With

strQuery = "SELECT * FROM [Sheet1$] ORDER BY sName"
With adoRs
Set .ActiveConnection = adoCn
.Open strQuery
End With
%>

<html>

<body>
<table cellspacing="0" 
style="border:1px solid silver;">


<%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>
</tr>
<% adoRs.MoveNext 
wend%>


</table>
<% 
adoRs.Close
set adoRs=nothing
adoCn.Close
set adoCn=nothing
%>
</body>
</html>

Please help....

[NeoPa]

As stated earlier, I know how to import the excel file into SQL Server 2000 using the DTS import Export wizard.

Sorry. I missed that in your earlier post (#3). Please ignore my post.

[teddarr]

I guess I assumed ASP.NET was being used, my bad.

Let me know if you are interested in finishing this code.

In reality this project works best as a console app or WinForm app. I have adapted it in my own asp.net projects.

It is very handy for my sideline gig where I need to process a ton of excel spreadsheets.

[DrBunchman]

Hi Giandeo,

Have you ever handled inserting data into a database from your ASP pages before? Check out this tutorial written by Jared which goes through the basics.

What you need to do is insert each row to the database as you go through the loop insead of printing the content of the excel sheet to the browser,

Have a look at the turorial and see what you come up with. If you run into any problems give me a shout.

Dr B

[mldisibio]

I am just going to throw one idea in here...are you wanting to do this in ASP because it provides you a scripting environment?

Using ASP, you need to get the Excel content to the web server, and the the web server content into SQL Server.

If the web server is not strictly required, you can do the exact same process using Windows Scripting Host.

This lets you write a script (JScript or VBScript, same as classic ASP), and also lets your user interact with the native Excel COM libraries, OR with the native Sql Server DTS COM libraries, as some of the above examples are suggesting.

Is that a possibility for you? You will probably find several examples on the net about exporting an Excel file using Windows Scripting.

- Mike

[giandeo]

Hi Giandeo,

Have you ever handled inserting data into a database from your ASP pages before? Check out this tutorial written by Jared which goes through the basics.

What you need to do is insert each row to the database as you go through the loop insead of printing the content of the excel sheet to the browser,

Have a look at the turorial and see what you come up with. If you run into any problems give me a shout.

Dr B

Hello Dr B
Thank you so much for your valuable suggestions. I have gone through the document you suggested for inserting data in a database.

I am getting the following errors when I run the script.

Error Type:
ADODB.Recordset (0x800A0CB3)
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Could you please locate the errors and give me your advice.

Here are the codes:

<%
Dim adocon, adorst
Const adUseClient = 3
Const adClipString = 2
Const strColDelim="</td><td>"
Const strRowDelim="</td></tr><tr><td>"

Set adocon = Server.CreateObject("ADODB.Connection")
With adocon
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = _
"Data Source=c:\ExcelAdo\Test.xls;" & _
"Extended Properties=Excel 8.0;"
.CursorLocation = adUseClient
.Open
End With


'display data on browser
dim strQuery
set adorst=server.createobject("ADODB.Recordset")
strQuery="SELECT*FROM [Sheet1$] order by sname"
adorst.open strQuery, adocon



dim conn, rs
set conn=server.createobject("ADODB.connection")
conn.open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=profiling; Integrated Security=SSPI;"
set rs=server.createobject("ADODB.recordset")
rs.open"SELECT * FROM paxlist",conn

while not adorst.eof
rs.addnew
for each x in adorst
rs(x) = adorst(x)
next
rs.update
adorst.movenext
wend



%>