possible to upload excel file into sql server?

[st111]

I was thinking of having a web page for uploading excel files to a db in sql server.

is this possible? if so, where is a good place to start (ie reference, textbook, etc)?

Thanks!

EDIT: I would also like to append any uploads to the table where the files are uploaded, so that the table keeps growing.

[lmf232s]

well let me ask you this. Will you have column headers in the .xls page?
Will the headers always be the same?

Or will this just be a random .xls workbook that has random data.

[st111]

well let me ask you this. Will you have column headers in the .xls page?
Will the headers always be the same?

Or will this just be a random .xls workbook that has random data.

Sorry, I should have been more clear...

The .xls file WILL have col. headings. Each .xls file that is to be uploaded will be the same format, just diff. files for diff. months.

[lmf232s]

ok, here is what i did.

I use aspupload.

First i let the user select the file to upload, then i upload the file and set a variable = to the path of the .xls file.

FileName = "Your File Path"

Then i call my Sub which does just this.

Opens the excel sheet and gets all the data
Delete all data from a temp table
Write the excel data to a temp table
If no errors then i take the temp table data and write it to the live table.

You will need 2 connections
1 for the excel sheet conneciton
1 for your Database table connections.

Here is the connection string for the excel sheet

strExConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
set oExConn = Server.CreateObject("ADODB.Connection")
oExConn.Open strExConn
set oEx1 = Server.CreateObject("ADODB.recordset")

HEre is a web site that lists all the conneciton types, we are using a ole db conneciton for this one
http://www.connectionstrings.com/

Then you need 1 connection that you use to connect to your Database.

Then this is basically the sub i call

SQL = "SELECT * FROM [sheet1$]"
set oEx1 = oExConn.Execute(SQL)
If not oEx1.EOF then

'delete temp table data
SQL = "TRUNCATE TABLE TempData"
set oRS1 = oConn.Execute(SQL)

'When you get the data from the excel sheet, loop it
Do WHile not oEx1.EOF
sqlInsert = "Instert into temptable " & _
"VALUES ('" & Trim(oEx1.Fields(0)) & "')"
set oRS1 = oConn.Execute(SQL) 'this is my Datbase connection

oEx1.MoveNext
Loop

'we are done writing the data to the temp table, now lets copy it to the real table
SQL = "INSERT INTO REALTABLE " & _
"SELECT * FROM TEMPTABLE "
set oRS1 = oConn.Execute(SQL)

ELSE
' WE HAVE NO DATA
ENd if



When itirating the data returned from the excel sheet, you get the values like this

oEx1.Fields(0)
oEx1.Fields(1)
oEx1.Fields(2) etc.


Now if you follow this example there are some things to keep in mind.
You need to make sure that the that your temp table data types match your live table if you do the kind of insert that i did.

This is a pretty long post and i might not of been clear on some things. Let me know how it goes and ill help you.

Thats it

[st111]

WOW!

Thanks a lot! I will get to this tomorrow and let you know how I make out.

[st111]

I have used an uploading system before, i assume I can use it? It uses the FileSystem, and I upload to a folder (created on-the-fly if it isn't already there).

if i use this, do i need a temp table, or can i upload the actual .xls file to a folder?

[lmf232s]

ok i may of jumped the gun.

Are you just trying to upload the file to the server?
In that case there are many upload programs out there and thats really
straight forward.


Or (this is the 1 i thought you were talking about)
Do you want to take the contents of an excel sheet and write the values to a database?
This is the 1 i thought i was posting for.
In this case what i do is upload the file to the server (so i can manuplate it from the server) and then i take its contents and writes it to a table.

The only reason i use a temp table is so that i can varify that the entire excel sheet data gets writen to the table and does not error out.

Lets say i write the data straight to the live table and it errors out half way through. Now i have written half the records to the live table, and now i need to upload the other half. So i guess you would have to delete records from the excel sheet and do it again to get the rest of the records or something else like that because if you try to run this again on the excel sheet with out getting rid of the records that were uploaded then now you have duplicate records.




Explain again what it is your after as im afraid i may of jumped the gun and gave you a solution that is not the solution.

Even if you just want to upload the file and write the file path to a DB, thats alot easier then this.

Any way let me know.

[st111]

I do want to write the excel values to a database in sql server. i was just trying to understand the temp table part. instead of a temp table could i upload to a folder?

i am not sure how to do the temp table part.

i understand about making sure all the records get written w/ no errors, that's a good idea. i am just not sure how to begin since i have never done this before, aqnd my asp experience is limited.

thanks!

[lmf232s]

instead of a temp table could i upload to a folder?
Well you will be uploading the file to a folder. Let me explain.
1). The first thing that needs to happen, is that you need to upload the file to the server. You can have this file uploaded anywhere you want on the server.

In your code to upload, you can have the path to the location you want the file uploaded to, hardcoded. So for the sake of this example, lets say it was uploaded to this location
C:\inetpub\wwwroot\_Files\MyTest.xls

Get it, got it, good........

2). Next we need to open the excel file like its a Database table.

This is where the connection string comes into play. In your connection string you need to give it the path of the file you just uploaded. So in your code you need to pass the connection string this string or use a variable to set it. But you will set the path to the file as
C:\inetpub\wwwroot\_Files\MyTest.xls

3). Now that you have your excel connection string, lets select all the data from the excel sheet like we would do with a normal table in a database.

SELECT * FROM [sheet1$]

Think of this as creating a database connection and selecting all the data in the Table only this is an excel sheet.

4). Now that you have your recordset of data, you now need to loop that data and write this data to a Temp Database table (which is nothing more than a copy of the real Database table) or to your Live Table that you will use to display the data which i have been calling a Live Table when a user requests it.

Temp Table is nothing more then a table in the database. I call it a Temp table because that is the purpose it servers. It will temporally hold the data until i write it to the Live Table.

Live Table is just what im calling the table that i will read when a user requests this information.

Temp Table Name = T_ExcelData
Live Table Name = ExcelDate

Again my reason for this is this. Say your excel sheet has 10,000 records. As we are writing the data to the live table it breaks and only put in say 3,635 records into the real database. There are still about 6,500 records that did not make it to the database. So since you were writing this straight to the Live Table you will now have to delete those 3,635 recrods from the excel sheet and attempt to do this process all over again.

Where as if you were just writing these records to a temp table and it broke like it did above, then since its a temp table and i always delete all this data before i attempt to write the records, i can just rerun the script and attempt to upload all the records again becuase it will overwright everything that was there. Now that it made it to the Temp table it easy to write it to the live table.

* Both the Temp table and Live Table have the same datatypes and the same column names. They are mirror images of their selfs. This allows for an easy copy from 1 table to the other with a statement like this

INSERT INTO LIVETABLE
SELECT * FROM TEMPTABLE

Get it, got it, good.

Hope that helps explain what a temp table really is, nothing more than an exact copy of the table you want the data to sit in. Temp table is nothing more than a name that i am calling it because it will temporally hold data until im dont with it.

[st111]

that clears things up, thanks!

so i don't really need to upload to a folder before uploading to a temp table, correct?

maybe have the uploading to a temp table (in sql server i assume) then when that completes successfully i display a screen to the user where it says that the data has been validated and is ready to be inserted into the master table. then have a submit button for the user to click when they want it to finsih (temp data to master data).

does that make sense?

EDIT: the uploaded data will need to be appended to the master table, this would not be a problem, correct?

[lmf232s]

so i don't really need to upload to a folder before uploading to a temp table, correct?

No, you do need to upload the file to the server, so that you can connect to the file. If the file is located on your C: drive on your machine, then the server will not be able to connect to the file. So YES you do need to upload the file.

maybe have the uploading to a temp table (in sql server i assume) then when that completes successfully i display a screen to the user where it says that the data has been validated and is ready to be inserted into the master table. then have a submit button for the user to click when they want it to finsih (temp data to master data).

Yes this is not a bad idea you could do it like this. This way you upload it to a temp table and then notify the user that its ready to insert into the new table. I see no problem with that. They example i provided, just does it all at the same time. But there is no reason why you could not do it like this.

EDIT: the uploaded data will need to be appended to the master table, this would not be a problem, correct?

No this is not a problem. With this statement
INSERT INTO LIVE TABLE
SELECT * FROM TEMP TABLE
This will just copy all the data from the temp table and add it to the Live Table. This data will appened to the master table, thus not overwriting anything and just adding these records to the rest of them.

Good luck

[st111]

OK, i'm running into unforseen problems with my uploading.

sooo, i tried to go from scratch using this:

http://www.asp101.com/articles/jacob/scriptupload.asp

i have no clue if i can just copy&paste the code and it will work (ie no extra programming on my part, such as a stored proc).

EDIT: this is what i get:
File Name:upload.aspFile Size:4862File Type:text/aspFile Name:upload.aspFile Size:4862File Type:text/asp

but the file isn't uploaded. in fact, the filename is wrong..

any ideas?

[st111]

OK, now it displays the proper file attributes(ie name, size) but it doen't save the file.

[lmf232s]

Here is a sample page that i use to upload a file.

You will see that when you have selected a file and click the upload button, it will call the ProcessForm which will loop and upload all your files.

After the file is uploaded it will call a sub SaveFile which is a sub i created that just writes the filepath and file name to my database.

See if this helps.

This also assumes that you have the _upload.asp file located in a folder called Include.
Change this line
<!--#INCLUDE virtual="Include/_upload.asp"-->
To point to where ever you have the file located.


<%option explicit

Dim ResultHTML
Dim Form: Set Form = New ASPForm
%>
<!--#INCLUDE virtual="Include/_upload.asp"-->
<%
'Do not upload data greater than 1MB.
Server.ScriptTimeout = 1000
Form.SizeLimit = &H100000

Const fsCompletted = 0

If Form.State = fsCompletted Then 'Completted
ResultHTML = ProcessForm
ElseIf Form.State > 10 then
Const fsSizeLimit = &HD
Select case Form.State
case fsSizeLimit: ResultHTML = "<br><Font Color=red>Source form size (" & Form.TotalBytes & "B) exceeds form limit (" & Form.SizeLimit & "B)</Font><br>"
case else ResultHTML = "<br><Font Color=red>Some form error.</Font><br>"
end Select
End If

if request.QueryString("Action") = "Cancel" then
ResultHTML = "<br><b>Upload was cancelled</b>"
end if

Function ProcessForm
Dim objNewMail
Dim File
Dim FileName

For Each File In Form.Files.Items
'If source file is specified.
If Len(File.FileName) > 0 Then
MyFile = File.FileName

HTML = HTML & "<br>&nbsp;" & File.Name & ": <b>" & File.FileName & ", " & File.Length \ 1024 & "kB</b>"

'FILE LOCATION TO SAVE THE FILE TO (THIS IS LOCATION ON THE SERVER)
FileName = "\\data2\UploadFiles\" & File.FileName
File.SaveAs FileName

'THIS IS HOW I SAVE THE INFORMATION TO THE DATABASE.
'i call a sub and pass it the filepath and the filename.
SaveFile FileName, MyFile

End If
Next
End Function

Public sub SaveFile(FilePath, FileName)
'save the file path and the id of the record.
Dim strConnection
Dim objConn
Dim objRS
Dim SQL

strConnection = "Provider=sqloledb;Data Source=?;Initial Catalog=?;USER ID=?;PASSWORD=?;"
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnection
Set objRS = Server.CreateObject("ADODB.Recordset")

SQL = "INSERT INTO UPLOAD " & _
"VALUES ('" & FileName & "', '" & FilePath & "') "
set objRS = oConn.Execute(SQL)

set objRS = Nothing
set objConn = Nothing
End Sub
%>
<HTML>
<HEAD>
<title>UpLoad Files Example</title>
</HEAD>
<BODY>

<form method="post" ENCTYPE="multipart/form-data" id=form1 name=form1>

<table class=tablebody width=100%>
<tr>
<td>
<table width=100%>
<tr>
<td class=tablehead>Add Attachments</td>
</tr>
</table>

<table class=clearbody>
<tr>
<td><b>Attachment 1 :</b><input type="file" name="File 1" size=70></td>
</tr>
<tr>
<td colspan=2 align=Center>
<input Type=button name=SubmitButton Value="UpLoadFile" onclick="document.form1.submit()" style='width=150'>
</td>
</tr>
</table>
</td>
</tr>
</table>

</BODY>
</HTML>

[st111]

have u encountered this error, even tho the file is in the right location:

The include file 'Include/_upload.asp' was not found.

[lmf232s]

Do you have the file in an include folder?
The way this example is set up the location of the file is here

domainName.com/Include/_upload.asp

I have to go to a meeting, so ill be gone for about an hour.
<!--#INCLUDE virtual="_upload.asp"-->
Make sure that is the name of the file.
You could always move that file to the same folder as the page you are executing
and change the path to

[st111]

Do you have the file in an include folder?
The way this example is set up the location of the file is here

domainName.com/Include/_upload.asp

I have to go to a meeting, so ill be gone for about an hour.
<!--#INCLUDE virtual="_upload.asp"-->
Make sure that is the name of the file.
You could always move that file to the same folder as the page you are executing
and change the path to

OK, it seems to be able to find the include now (no clue what was happening before).

but now it says that ASPForm is undefined. When i dim it, the error says Microsoft VBScript runtime (0x800A01FA)
Class not defined: 'aspform'

Maybe i should try and get this code to work, it is displaying the file properties, just not saving for some reason:


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<!-- #include file="upload/upload.asp" -->
<!-- #include file="Includes/connection.asp" -->
<title>Untitled</title>


</head>

<body>

<%
Dim MyUploader
Set MyUploader = New FileUploader
%>

<%
MyUploader.Upload()
%>
<%
Dim File
For Each File In MyUploader.Files.Items
File.SaveToDisk "C:\ST\test"
Next

%>
<%
' Dim File
For Each File In MyUploader.Files.Items
Response.Write "File Name:" & File.FileName
Response.Write "<br>File Size:" & File.FileSize
Response.Write "<br>File Type:" & File.ContentType
Next
%>

<%
Response.Write "<br><br>File Name:" & MyUploader.Files("file1").FileName
Response.Write "<br>File Size:" & MyUploader.Files("file1").FileSize
Response.Write "<br>File Type:" & MyUploader.Files("file1").ContentType
%>

</body>
</html>


thanks for all the time u r spending to help me!!!!

EDIT: I tried another uploading thing i found on the net (http://www.freeaspupload.net/freeaspupload/download.asp)and it has a part that tells u if the folder doesn't exist. well, it says that the folder i want to upload to doesn't exist! could this be a server issue, and the person in charge of the server needs to change some permission or something?

thanks!

[lmf232s]

what ever folder you are trying to upload the file to needs to exists or youll get an error.

If you want to create the folder or check to see if it exists then you should be able to use FSO to see if the folder exists, if not then use FSO to create the directory.

You should not have to set any permission to do this on server unless IUSER_APP (the user that the web pages excute under, might be a different name) does not have permissions to do this.

Do you have access to your web site on the server?
C:\inetput\wwwroot ?? i think, that is if it was left at its default location.
Anyway on IIS there should be a folder called _Files.
You could try to upload the files to this folder.

[st111]

what ever folder you are trying to upload the file to needs to exists or youll get an error.

If you want to create the folder or check to see if it exists then you should be able to use FSO to see if the folder exists, if not then use FSO to create the directory.

You should not have to set any permission to do this on server unless IUSER_APP (the user that the web pages excute under, might be a different name) does not have permissions to do this.

Do you have access to your web site on the server?
C:\inetput\wwwroot ?? i think, that is if it was left at its default location.
Anyway on IIS there should be a folder called _Files.
You could try to upload the files to this folder.

something wierd is going on, i'll have to look into it on monday.

the server is in another city, so i have to discuss with the guy on mon.

thanks for the help so far!!!

[st111]

ok, here is what i did.

I use aspupload.

First i let the user select the file to upload, then i upload the file and set a variable = to the path of the .xls file.

FileName = "Your File Path"

Then i call my Sub which does just this.

Opens the excel sheet and gets all the data
Delete all data from a temp table
Write the excel data to a temp table
If no errors then i take the temp table data and write it to the live table.

You will need 2 connections
1 for the excel sheet conneciton
1 for your Database table connections.

Here is the connection string for the excel sheet

strExConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
set oExConn = Server.CreateObject("ADODB.Connection")
oExConn.Open strExConn
set oEx1 = Server.CreateObject("ADODB.recordset")

HEre is a web site that lists all the conneciton types, we are using a ole db conneciton for this one
http://www.connectionstrings.com/

Then you need 1 connection that you use to connect to your Database.

Then this is basically the sub i call

SQL = "SELECT * FROM [sheet1$]"
set oEx1 = oExConn.Execute(SQL)
If not oEx1.EOF then

'delete temp table data
SQL = "TRUNCATE TABLE TempData"
set oRS1 = oConn.Execute(SQL)

'When you get the data from the excel sheet, loop it
Do WHile not oEx1.EOF
sqlInsert = "Instert into temptable " & _
"VALUES ('" & Trim(oEx1.Fields(0)) & "')"
set oRS1 = oConn.Execute(SQL) 'this is my Datbase connection

oEx1.MoveNext
Loop

'we are done writing the data to the temp table, now lets copy it to the real table
SQL = "INSERT INTO REALTABLE " & _
"SELECT * FROM TEMPTABLE "
set oRS1 = oConn.Execute(SQL)

ELSE
' WE HAVE NO DATA
ENd if



When itirating the data returned from the excel sheet, you get the values like this

oEx1.Fields(0)
oEx1.Fields(1)
oEx1.Fields(2) etc.


Now if you follow this example there are some things to keep in mind.
You need to make sure that the that your temp table data types match your live table if you do the kind of insert that i did.

This is a pretty long post and i might not of been clear on some things. Let me know how it goes and ill help you.

Thats it


OK, i FINALLY have the file uploading to a folder on the server. Now when u say u 'call the sub' (above), is there another Submit button the user clicks to trigger it? Or is it just part of the process (upload to a folder, truncate and write to temp table, then append to db table all after one 'Upload' click?)

EDIT: i get a permission denied error after about 3MB of uploading (large file). i do not get the error when the file is smaller. this is the line it craps out on:

Set oFS = Server.CreateObject("Scripting.FileSystemObject")
If Not oFS.FolderExists(sPath) Then Exit Sub

THIS LINE >>> Set oFile = oFS.CreateTextFile(sPath & FileName, True)

For nIndex = 1 to LenB(FileData)
oFile.Write Chr(AscB(MidB(FileData,nIndex,1)))
Next

oFile.Close


Why would this happen?

EDIT2: OK this is real wierd. i got the permission denied error, but when i go to the folder and hity refresh, i can see that the file size is increasing, so it appears to still be uploading!!! v. wierd, i'll find out what's up...

[lmf232s]

Well i know with my upload script it is limited to 1MB uploads. Anything over a 1MB upload, i think you have to pay for that script. If this is the problem look around and you might be able to find something that will upload larger files. For what i am using it for, i do not need to allow users to upload large files so 1MB is just fine.

Look to see what size of a file your upload script will accept.

[st111]

Well i know with my upload script it is limited to 1MB uploads. Anything over a 1MB upload, i think you have to pay for that script. If this is the problem look around and you might be able to find something that will upload larger files. For what i am using it for, i do not need to allow users to upload large files so 1MB is just fine.

Look to see what size of a file your upload script will accept.

No limit in my script...sems to be kinda working, i just uploaded a file that was larger than 7MB..

BUT...the uploading is extrememly slow! it took almost 20min to upload the 7+MB file!
do u know if this is a normal performance thing? does it hav to do with the set-up here? i am a contract worker and just started here recently, so i do not know how they have everything set-up..

[st111]

I get this error:
Object required: 'oConn'


and this is where the error occurs:

set oRS1 = oConn.Execute(SQL)

The code block it is in is:

SQL = "SELECT * FROM [sheet1$]"
set oEx1 = oExConn.Execute(SQL)
If not oEx1.EOF then

'delete temp table data
SQL = "TRUNCATE TABLE TempData"
set oRS1 = oConn.Execute(SQL)

'When you get the data from the excel sheet, loop it
Do WHile not oEx1.EOF
sqlInsert = "Instert into temptable " & _
"VALUES ('" & Trim(oEx1.Fields(0)) & "')"
set oRS1 = oConn.Execute(SQL) 'this is my Datbase connection

oEx1.MoveNext
Loop

'we are done writing the data to the temp table, now lets copy it to the real table
SQL = "INSERT INTO REALTABLE " & _
"SELECT * FROM TEMPTABLE "
set oRS1 = oConn.Execute(SQL)

ELSE
' WE HAVE NO DATA
ENd if

[lmf232s]

BUT...the uploading is extrememly slow! it took almost 20min to upload the 7+MB file!

Ya this is probablly correct. You have to remember you are uploading the file over the web. Its not the same as coping a file from 1 directory or 1 network to another. Attempt to attache a 10MB file to outlook and see how long it takes.

Im not saying that this is correct but i would say its expected.


The error you are getting is because you do not have a connection string set up yet.
You should have your excel connection set up but now you need 1 to the Database.
You could do something like this.

Public Sub Open_DB()
strConnection = "Provider=sqloledb;Data Source=?;Initial Catalog=?;USER ID=?;PASSWORD=?;"
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnection
Set objRS = Server.CreateObject("ADODB.Recordset")
End Sub

Public Sub Close_DB()
set objRS = Nothing
set oConn = Nothing
End Sub


'To open the connection
Open_DB()



'To Close the Connection
Close_DB()


If you do it this way wrap the open and close around the block of code that will be writing to the Database.

[st111]

I have a connection as an include, so it's there.

maybe it can't find the table TempTable?

when i comment out the truncate part, i get the same error the next time oConn is used. happens like that all the way down to when writing to the RealTable.

i just created a table w/ the col. headers the RealTable uses (and the excel file).

EDIT: i inserted a few records in the temp database, then went to query analyzer and the truncate command worked..just not on my page yet...

[lmf232s]

youll see in my code that oConn is the name of my connection.

Since you already have this defined in an include file, all you need to do is replace
oConn with the name of your connection string.

oConn just referes to my connection string.
exp: oConn.Open strConnection


Just replace oConn with your connection string.

[st111]

youll see in my code that oConn is the name of my connection.

Since you already have this defined in an include file, all you need to do is replace
oConn with the name of your connection string.

oConn just referes to my connection string.
exp: oConn.Open strConnection


Just replace oConn with your connection string.

OK, i feel like an idiot, thx!

but not working 100% yet...thanks so far, u've been a lifesaver!

BUT.....for this part:
"VALUES ('" & Trim(oEx1.Fields(0)) & "')"

Should it be:
"VALUES ('" & Trim(oEx1.Fields(0)) & Trim(oEx1.Fields(1)) & Trim(oEx1.Fields(2)) & Trim(oEx1.Fields(3)) & "')"

I don't get why we don't list the other columns (ie Fields(1).....)

Also, i assume the data types have to match (between excel and sql server table)? does excel have varchar data types? is there default we can use in sql to accept all datatypes?

[lmf232s]

You can do the insert statement 2 ways and a couple of others but ill show you these 2 to explin the question.

TABLE VIEW
KEY_ID - AUTONUMBER
F_NAME - varchar
L_NMAE - varchar
CITY - varchar
STATE - varchar

INSERT INTO TABLENAME
VALUES (VALUE1, VALUE2, VALUE3, VALUE3)


INSERT INTO TABLENAME
(FIELD1, FIELD2, FIELD3, FIELD4)
VALUES (VALUE1, VALUE2, VALUE3, VALUE4)

oK the explination.
In the 1st query, they way its set up is that value1 will be placed into the 1st column in the database, value2 will be placed into the second column, etc, until your done. When doing the update this way i am placing the values into the database in the correct order that they appear in the Database. So for the 1st query value1 = F_name, value2 = L_NAME, VALUE3 = CITY, and VALUE4 = STATE. These are the columns that these values will be placed in.

But in the 2nd query we are telling the statement what values we want in each column. So lets say that we say
FIELD1 = F_NAME
FIELD2 = L_NAME
FIELD3 = CITY
FIELD4 = STATE

Then when we add the values to the query string we want to make sure that value1 is a value for F_NAME becuase thats the value we are telling the query to insert into COLUMN FIELD1 which is F_NAME.


In my example, i used the first query but thats because the table was built from the excel table. Meaning that the first column of the excel sheet is actually the value that needs to be inserted into the first column in the database. If my excel sheet did not match my table (because i either built the table first or i did not pay attention to the excel sheet when i built the table) then i would of used query 1 to specify what values should be writin to each column.

Make sense????

All in all in the 1st query the values will be written to the table starting with the first column and go until its done but in the second query i explicitly tell the query what value belongs to what column name.

[lmf232s]

sorry i did not read the entire post befor posting

You are correct you want ot include all the other Fields(?) but i did not want to type them out. So if you have 15 columns of data then you should make the statement to accept
Fields(1) - Fields(15)

I just did not want to type everything out.
This is what my real query looks like


sqlInsert = "INSERT INTO TempSplitComm " & _
"VALUES ('" & Trim(oEx1.Fields(0)) & "', '" & Trim(oEx1.Fields(1)) & "', '" & Trim(oEx1.Fields(2)) & "', " & _
" '" & Trim(oEx1.Fields(3)) & "', '" & Trim(oEx1.Fields(4)) & "', " & _
" '" & Trim(oEx1.Fields(5)) & "', '" & Trim(oEx1.Fields(6)) & "', " & _
" '" & Trim(oEx1.Fields(7)) & "', '" & ccDBL(Trim(oEx1.Fields(8))) & "', " & _
" '" & ccDBL(Trim(oEx1.Fields(9))) & "', '" & ccDBL(Trim(oEx1.Fields(10))) & "', " & _
" '" & ccDBL(Trim(oEx1.Fields(11))) & "', '" & ccDBL(Trim(oEx1.Fields(12))) & "', " & _
" '" & ccDBL(Trim(oEx1.Fields(13))) & "', '" & ccDBL(Trim(oEx1.Fields(14))) & "', " & _
" '" & Date() & "', '0') "

[lmf232s]

oh and on the data type thing
If the database expects a varchar then you dont need to do anything. By default asp are all stings but if you need to insert a number or something you might want to conver it to a number of a double

Exp

cInt(value)
cDbl(value)

[st111]

can u spot a problem here? each section seems to work (ie truncate and inserting values from temptable into realtable), but i can't send the excel values to the temptable.

EDIT: I found the error!





sqlInsert = "Insert into TempTable " & _
"VALUES ('" & Trim(oEx1.Fields(0)) & "', '" & Trim(oEx1.Fields(1)) & "', '" & Trim(oEx1.Fields(2)) & "', '" & Trim(oEx1.Fields(3)) & "')"

set oRS1 = con.Execute(SQL) 'this is THE ERROR! Needs to be sqlInsert


So now the only prob i have is that one col is not being inserted....

[lmf232s]

yes i know why you are not writing the values to the temptable.
What is the name of the query you are trying to execute when adding the data to the Temp Table??

And what command are you trying to execute when you try to add the data to the Temp table??

If you cant find it or see it let me know and ill tell you but you should be able to figure it out. Its a nameing problem.

Let me know if you find it. If not ill tell you.

[st111]

yes i know why you are not writing the values to the temptable.
What is the name of the query you are trying to execute when adding the data to the Temp Table??

And what command are you trying to execute when you try to add the data to the Temp table??

If you cant find it or see it let me know and ill tell you but you should be able to figure it out. Its a nameing problem.

Let me know if you find it. If not ill tell you.

I found it, and edited my last post (i think u replied before my edit went through).

sooooo...seems to be working now, testing with actuall data to make sure (the actual files are about 8MB).

i'll let u know how it goes.

THANKS FOR ALL YOUR HELP!!

You have really helped me out BIG TIME!!! i can't thank u enough! :D :D :D

[lmf232s]

ok you edited the post while i was typing.

What i would do to try to solve the problem of the field not writing to the database is:
I would make sure that i am reading in a value.
So in the Do Loop I would do something like this to see what the values are

Response.write oEx1.Fields(0) & "<BR>"
Response.write oEx1.Fields(1) & "<BR>"
Response.write oEx1.Fields(2) & "<BR>"
Response.write oEx1.Fields(3) & "<BR><BR>"

This will write the values to the screen and help you to see if you have a problem here with 1 of these values.

If all the values are being written correctly then you might want to look into the insert statment where you explicitly tell each column what value to write to it.

INSERT INTO TEMPTABLE
(FIELD1, FIELD2, ETC)
VALUES (VALUE1, VALUE2, ETC)

[lmf232s]

No Problem, Im glad you were able to get it working.

Ive been in your position before where people on these boards have helped me to see daylight again.

I hope that this is a working solution to your problem and most of all i hope you have learned something from this.

The only reason i was able to help you with this is that i had done the same thing 2 weeks ago and this was my solution to the problem. There are most likely several ways to accomplish this task but this idea was my brain child so enjoy.

[st111]

ok you edited the post while i was typing.

What i would do to try to solve the problem of the field not writing to the database is:
I would make sure that i am reading in a value.
So in the Do Loop I would do something like this to see what the values are

Response.write oEx1.Fields(0) & "<BR>"
Response.write oEx1.Fields(1) & "<BR>"
Response.write oEx1.Fields(2) & "<BR>"
Response.write oEx1.Fields(3) & "<BR><BR>"

This will write the values to the screen and help you to see if you have a problem here with 1 of these values.

If all the values are being written correctly then you might want to look into the insert statment where you explicitly tell each column what value to write to it.

INSERT INTO TEMPTABLE
(FIELD1, FIELD2, ETC)
VALUES (VALUE1, VALUE2, ETC)

Thanks, but, and here is evidence of my idiocy, i made a mistake with the column data types..and for some reason there was a 'hidden' col of nulls that increased the # to 5 col instead of 4...sheesh! :o

[st111]

No Problem, Im glad you were able to get it working.

Ive been in your position before where people on these boards have helped me to see daylight again.

I hope that this is a working solution to your problem and most of all i hope you have learned something from this.

The only reason i was able to help you with this is that i had done the same thing 2 weeks ago and this was my solution to the problem. There are most likely several ways to accomplish this task but this idea was my brain child so enjoy.

Yes, i am VERY grateful for your help, and i did learn a lot!!!

it's nice to know there are people who take time to help others!

[lmf232s]

Its my way of repaying the message boards.

Im always glad to help. :)

Later

[st111]

Its my way of repaying the message boards.

Im always glad to help. :)

Later

Thanks, I have one final question:

The # of fields in the table is large, can i loop through the fields in this part. It seems to be in a loop, but i've also specifid the fields (isn't that what it is doing?).

[code]
Do WHile not oEx1.EOF

sqlInsert = "Insert into TempTable " & _
"VALUES ('" & Trim(oEx1.Fields(0)) & "', '" & Trim(oEx1.Fields(1)) & "', '" & Trim(oEx1.Fields(2)) & "', '" & Trim(oEx1.Fields(3)) & "')"

set oRS1 = con.Execute(SQLinsert) 'this is my Datbase connection

oEx1.MoveNext
Loop
[\code]

[lmf232s]

Well im not quite sure what your asking for here.
We are actually looping the record set and not the fields.


What do you have like 50 columns in the excel sheet that your tring to write to the DB
and you want to loop the fields and build the sql statement??

[st111]

Well im not quite sure what your asking for here.
We are actually looping the record set and not the fields.


What do you have like 50 columns in the excel sheet that your tring to write to the DB
and you want to loop the fields and build the sql statement??

there are 66 columns, is this a problem? for some reason this morn i got an error about # of specified col doesn't match, something like that...

i thought it was ok yest, so i'm trying to refresh myself. still that error is wierd, no?

[lmf232s]

it sounds like you may of referenced a column that was non existance.
Just debug it. Do a bunch of
Response.write "Column1 = " & oEx1.Fields(0) & "<BR>"
Response.write "Column2 = " & oEx1.Fields(1) & "<BR>"
Response.write "Column3 = " & oEx1.Fields(2) & "<BR>"
etc.
See what happends, maybe youll find that you mistyped a number or something.
Or maybe youll find that you forgot to add a column to the DB.

Just sounds like its a little over site on something. Nothing major

[st111]

it sounds like you may of referenced a column that was non existance.
Just debug it. Do a bunch of
Response.write "Column1 = " & oEx1.Fields(0) & "<BR>"
Response.write "Column2 = " & oEx1.Fields(1) & "<BR>"
Response.write "Column3 = " & oEx1.Fields(2) & "<BR>"
etc.
See what happends, maybe youll find that you mistyped a number or something.
Or maybe youll find that you forgot to add a column to the DB.

Just sounds like its a little over site on something. Nothing major

So it's not like having 66 cloumns will affect it? i had the values insert properly yest., so i find it weird that it errors now. so the 'oEx1.Fields(0)' part in the code doesn't have to go to 65?

i'll do some de-bugging, thanks!

[lmf232s]

no if you have 65 columns that you want to put in the db you will need
oEx1.Fields(0) - oEx1.Fields(65) so that you can add all the columns.

If its going to be the same number of fields everytime, you could loop the fields to create the query so you dont have to type it.

SQL = "INSERT INTO TABLE " & _
"VALUES ("
For i = 0 to 65
SQL = SQL & " '" & oEx1.Fields(i) & "' "
Next


But no 65 columns should not be a problem.

[st111]

no if you have 65 columns that you want to put in the db you will need
oEx1.Fields(0) - oEx1.Fields(65) so that you can add all the columns.

If its going to be the same number of fields everytime, you could loop the fields to create the query so you dont have to type it.

SQL = "INSERT INTO TABLE " & _
"VALUES ("
For i = 0 to 65
SQL = SQL & " '" & oEx1.Fields(i) & "' "
Next


But no 65 columns should not be a problem.

THANKS!!!

I am also working on a solution for this line:

SQL = "SELECT * FROM [sheet1$]"


The name of the sheet changes, so it doesn't like sheet1$..

[st111]

in order to avoid the sheet naming prob, i tried this:



Dim strXLA
Dim objXLA
Set objXLA = CreateObject("Excel.Application")
objXLA.Visible = False
objXLA.Workbooks.Open(oExl)
strXLA = objXLA.ActiveSheet.Name
objXLA.Quit
Set objXLA = Nothing
WScript.Echo strXLA



But the line,

Set objXLA = CreateObject("Excel.Application")

returns this error:

Microsoft VBScript runtime (0x800A01AD)
ActiveX component can't create object: 'Excel.Application'


I can't find the reason, any ideas????

[lmf232s]

this method might require excel to be on the server.
I would assume that excel is not on the server.

Here at my place of business we will not load office app's onto the server. Just not needed but in order to do some things office app's need to be on the server.

Youll have to see if they have excel on the server and see if they are willing to load it. Else i dont think this method will work.

[st111]

this method might require excel to be on the server.
I would assume that excel is not on the server.

Here at my place of business we will not load office app's onto the server. Just not needed but in order to do some things office app's need to be on the server.

Youll have to see if they have excel on the server and see if they are willing to load it. Else i dont think this method will work.

THANKS!!!

If they do not upload apps, how do i overcome the naming issue? The sheet changes name according to the date it is submitted.

[lmf232s]

what is the naming convention, maybe your script can dynamically build the sheet name everytime they try to run this.

[st111]

what is the naming convention, maybe your script can dynamically build the sheet name everytime they try to run this.

That's a great idea! Since i just started i am not sure if the name changes in a predetermined way (ie every 10 days)..

Actually, it's:

DETL1042

Which means Oct. Week 42 (the DETL stays the same).

The week numbering is a company thing, so it might be hard to figure out when one month ends and the other begins, no?

[lmf232s]

well its going to depend on when they upload them.
Do they stay on top of things and upload when there supposed to or do they miss a week. Things like this.

Its not that hard to build DELT [Month Number] [Week Number].
As long as it is consistent month after month or week after week.

If they will be doing this every week so you have the following files every week say like
DELT1040
DELT1041
DELT1042
DELT1043
DELT1145
DELT1146
etc.

You see what im saying. As long as there is some consistency on there part then it should not be an issue.

[st111]

well its going to depend on when they upload them.
Do they stay on top of things and upload when there supposed to or do they miss a week. Things like this.

Its not that hard to build DELT [Month Number] [Week Number].
As long as it is consistent month after month or week after week.

If they will be doing this every week so you have the following files every week say like
DELT1040
DELT1041
DELT1042
DELT1043
DELT1145
DELT1146
etc.

You see what im saying. As long as there is some consistency on there part then it should not be an issue.

I see, but then i guess it totally depends on them not making a mistake..

oh well, i'll figure it out...tHANKS!

[lmf232s]

if this situation holds true then there are other ways of going about it.
You could give them a drop down that has the weeks in it so when they upload they have to select the week they want to upload for, that way if they miss a week they can go back to it. Little things like could help.

[st111]

if this situation holds true then there are other ways of going about it.
You could give them a drop down that has the weeks in it so when they upload they have to select the week they want to upload for, that way if they miss a week they can go back to it. Little things like could help.

Thanks!

i have not developed for a few months, things like that slip my mind....

[eralper]

Hello all,

You can store your files (images, excel, word, etc.) in MS SQL Server databases. There is a sample with code files and sql scripts at File Upload and Save Submitted Files to SQL Server 2005, Sample ASP.NET Web Site Application (http://www.kodyaz.com/articles/file-upload-and-save-to-sql-server.aspx) article.

Also if you wish to store your files, backup them, use in sql transactions and keep tracking their updates, inserts etc. you can benefit from the new features of SQL Server 2008 FileStream enhancement.