We help IT Professionals succeed at work.

MOVING EXCEL RECORDS TO SQL SERVER

Evolutionmarketplace
on
275 Views
Last Modified: 2008-02-01
Hi experts,

In my ASP the user must upload a catalog in excel file, and the problems I have are two:

1) I don't know if the user is uploading the excel template I provide (cannot be any excel file). How can I check if the excel file is the right one?

2) I am updating record to records, and the server crash if the user upload a catalog with 10,000 items. I know I can upload the catalog using INSERT INTO - SELECT *, but in the same database (in this case, I have the SQL Server connection and the EXCEL connection). Is it possible to upload the 10,000 items in one time?

Best regards, and thanks!!

Pablo (sorry for my english

Comment
Watch Question

Brad Dobyns, CSMScrumMaster

Commented:
Are you wanting to do this in Classic ASP or .NET? It may be easier in .NET.

Brad
Top Expert 2004

Commented:
Big problem...users are involved!  So you'll have a lot of ID10T errors.  

DTS is the best way to get data from Excel....I'd like to hear the answer for ASP though.
Hello Brad. I am using classic ASP.

I use the following string:

stringsql="insert into catalog (Product_Code , Description, brand, Umedida, Price, Branchid, UPC, companyid, lastupdate, notes) select [item code], description, brand, [unit of measure], price, 4334, [upc code], 3321, 20040206, notes from  [EXCEL 8.0 ; HDR=NO; IMEX=2 DATABASE=" & destinationpath & field.filename & "]"

But the following error appears:

Invalid object name 'EXCEL 8.0 ; HDR=NO; IMEX=2 DATABASE=\\192.168.10.3\temp\2004\02\06\1196\EVMcatalog20.xls'.

I have an area defined as ITEMSLIST on the excel file.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Hi Low8566,

I checked the file extension... the problem is I have a file that must be filled, and the server cannot accept any XLS file. What I have to check is if the excel file than the user is uploading is the right file.

I put a "mark" on a cell, but I don't know how to read it.
Michel SakrRounded IT Consultant / Projects manager / Cloud Consultant / IT Planning
CERTIFIED EXPERT

Commented:
Hi Evolutionmarketplace,
http://www.idude.net/Excel/articles/usingexceldatasources.pdf
http://www.asp101.com/samples/xl_data.asp

Cheers!
Michel SAKR
MVP,MCDBA,MCAD,MCSA
Michel SakrRounded IT Consultant / Projects manager / Cloud Consultant / IT Planning
CERTIFIED EXPERT

Commented:
Evolutionmarketplace,
Dts is an ultimate solution and fast enough..about users you will not have problems since a file is used once..but on heavy web usage you might encounter problem..the best way is to create a background compiled application that will load files from a set queue in the db..
Why your server won't accept excel?for security?
Hi Silvers5!

Basically what I need is to insert the excel records into the SQL Server with an INSERT INTO string.

I am using the following:

stringsql="insert into catalog (Product_Code , Description, brand, Umedida, Price, Branchid, UPC, companyid, lastupdate, notes) select [item code], description, brand, [unit of measure], price, 4334, [upc code], 3321, 20040206, notes from  [EXCEL 8.0 ; HDR=NO; IMEX=2 DATABASE=" & destinationpath & field.filename & "]"

But the following error appears:

Invalid object name 'EXCEL 8.0 ; HDR=NO; IMEX=2 DATABASE=\\192.168.10.3\temp\2004\02\06\1196\EVMcatalog20.xls'.

Salute!

Pablo.

Commented:
How about checking the date that the file was created, if the user must use a template file that file I am sure you provide. check the create date of the file being uploaded. The Other option would be to read a hidden cell on the sheet1$ for a specific value. If thats your problem, reading that cell let me know, I am assuming if you have gotten this far you know how to read the cell.

<%
'***************** DateCreated
Set fso = CreateObject("Scripting.FileSystemObject")
Set folderObject = fso.GetFolder(Server.MapPath("\fso\"))

xDC = folderObject.DateCreated

if xDC = "11/01/01 12:41:55" then
   ' process file
else
   ' error msg
end if

Set folderObject = Nothing
Set fso = Nothing
%>

Commented:
Here is an example of reading excel and putting the values into an sql db, Hope this helps.
<!--#include file="../dbConn.inc" -->
<%
' ***************************************************************************
' Created by: Lou on Dec 31, 2003
' Purpose, Read an Excel Spread Sheet that is located on a server and export
'          the data to a database.
' Last Modified on: Dec 31, 2003
'****************************************************************************

Dim xlCn, dbCn
Dim xlRs, dbRs
Dim sDate, eDate


Set xlCn = CreateObject("ADODB.Connection")  'Excel Connection
Set dbCn  = CreateObject("ADODB.Connection") 'SQL DB Connection

Set xlRs = CreateObject("ADODB.Recordset") 'Excel RecordSet
Set dbRs  = CreateObject("ADODB.Recordset") ' SQL RecordSet

With xlCn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & Server.MapPath(".") & "/schedule.xls;" & _
            "Extended Properties=Excel 8.0;"
      .CursorLocation = 3
      .Open
End With

dbCn.open connStrDeskmate

With xlRs
      Set .ActiveConnection = xlCn
      .Open "SELECT * FROM [Sheet1$] ORDER BY [payroll ID]" 'Select * from the Excel SpreedSheet on Sheet1.
End With

dbRs.open "Select * from tblShifts",dbCn,3,3
%>

<html>
<head>
<title>File Upload and Data Transfer</title>
</head>
<body>
<%

if len(xlRs.fields.item("Start")) < 5 AND len(xlRs.fields.item("End")) < 5 then
   dbRs.addnew
   dbrs.fields.item("shiftStartDate")                              = session("sDate")
   dbrs.fields.item("shiftEndDate")                                = session("eDate")
   dbrs.fields.item("empPayID")                                       = xlRs.fields.item("Payroll ID")
   dbrs.fields.item("shiftBound")                                       = xlRs.fields.item("Inbound (I) / Outbound (O)")
   dbrs.fields.item("shiftStartTime")             = xlRs.fields.item("Start")                                          
   dbrs.fields.item("shiftEndTime")             = xlRs.fields.item("End")
   dbrs.fields.item("shiftSun")                   = xlRs.fields.item("Sunday")
   dbrs.fields.item("shiftSun")                   = xlRs.fields.item("Monday")
   dbrs.fields.item("shiftSun")                   = xlRs.fields.item("Tuesday")
   dbrs.fields.item("shiftSun")                   = xlRs.fields.item("Wednesday")
   dbrs.fields.item("shiftSun")                   = xlRs.fields.item("Thursday")
   dbrs.fields.item("shiftSun")                   = xlRs.fields.item("Friday")
   dbrs.fields.item("shiftSun")                   = xlRs.fields.item("Saturday")
xlRs.movenext
loop

      
      Set xlCn = nothing
      Set xlRs = nothing
      Set dbCn = nothing
      Set dbRs = nothing  
%>

</body>
</html>

Hello Lu8566,

I had make something like that, but the update is record to records, and I need to implement an update method that updates the database in one time.

The point is that if I have a guy uploading 50,000 records, the record-to-record method crash the server.

Regards,

Pablo.
Brad Dobyns, CSMScrumMaster

Commented:
Sorry I've been away...

Perhaps this will help you and give you some answers:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303814

Brad
Brad Dobyns, CSMScrumMaster

Commented:
This may also give you some answers:
http://techsupt.winbatch.com/TS/T000001033012F19.html

Sounds like what you are doing is not being done by a lot of people. I can relate. Many of the applications we are doing in my office are somewha groundbreaking. Good Luck!

Brad
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.