Update access Table from an Excel table in ASP (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

DaveKstl -> Update access Table from an Excel table in ASP (4/30/2008 16:22:40)

I want to take the data from an Excel file that I have uploaded and use that information to update an access table that resides in the same folder on a server.

How do I get started?

Do I need 2 connections open?

Can the sql statement do an Update where 2 fields match?

Dave



rdouglass -> RE: Update access Table from an Excel table in ASP (4/30/2008 17:36:48)

Hi Dave,

quote:

Do I need 2 connections open?


Yes, but not necessarily at the same time.

quote:

Can the sql statement do an Update where 2 fields match?


Yes they can. I do it frequently if they're stored in an array.

quote:

How do I get started?


I think this link is pretty good:

http://www.asp101.com/samples/xl_data.asp

Example code and stuff as well as 2 links to MS at the bottom.

With that stuff you should be able to get a spreadsheet onto the site and reading from it. There are a few gotcha's while dealing with Excel as a DB; the one that I see the most is how the ADO determines the field type.

IMO you should remove any column header data from the worksheets and be sure the first 5 cells of the column contain the correct data type. For instance, if it's a date field, be sure you have the first 5 cells of that column containing dates and not blanks. That one got me pretty good before. [&o]

Hope it helps.



DaveKstl -> RE: Update access Table from an Excel table in ASP (4/30/2008 18:31:14)

Thanks for the response.

I am able to query the excel data just fine and the access data also.

Now I don't know how to take the excel data and update the access data.

I have never opened 2 tables much less 2 dbs at the same time.

I have updated from forms, but not tables.

Can you get me going?

Dave



rdouglass -> RE: Update access Table from an Excel table in ASP (4/30/2008 22:46:16)

OK, are you familiar with arrays?

When you say UPDATE do you mean just UPDATE or UPDATE and INSERT where necessary?

If you know there's always a "1 to 1" relationship, you can read the Excel file into an array and then loop thru the array building your update statememnts.

It's kinda' like saying "get me started on building a car". It's hard to get you started without knowing what kind of vehile you want and what you have to work with.

OK, having said that; this is probably the basics you're looking for.

1. Let's assume for simplicity sake that you're key is an Access PK field called ID and the matching ID is in the first column of the spreadsheet.
2. Let's also assume that we have 1 text field to update. In the Access DB, it'll be called 'myText' and it will be the second column in the Excel file.

So we assume you already have your connection to the DB built but we're *not* going to use recordsets but rather 'getrows' which will put our Excel data into an array: 

'First, read your Excel data into an array:

Set conntemp = Server.CreateObject("ADODB.Connection")
conntemp.Open "DRIVER=Microsoft Excel Driver (*.xls); DBQ=" & Server.MapPath("/fpdb/myFile.xls")
set rstemp=conntemp.execute("Select * From [$sheet1]")
IF Not rstemp.eof THEN
myArray = rstemp.getrows
End IF
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing

'Now set the Access DB connection and loop thru the Excel date
'creating UPDATEs as we go

Set conntemp = Server.CreateObject("ADODB.Connection")
conntemp.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/fpdb/myDB.mdb")

FOR i = 0 TO ubound(myArray,2)
conntemp.execute("UPDATE myTable SET myText = '" & myArray(1,i) & "' WHERE ID = " & myArray(0,i))
NEXT

conntemp.close
set conntemp=nothing


Now I didn't do any error checking nor checked any syntax and that's strongly reccommended; it's right from memory so I'm sure there's probably at least 1 problem with it. But I think the concept should get through.

1. read the data into an array using getrows.
2. loop thru the array creating UPDATE statements
3. execute each update during the loop

Does that make sense at all? If not, try brushing up on arrays. We're using a 2 dimensional array here.



DaveKstl -> RE: Update access Table from an Excel table in ASP (5/1/2008 7:15:31)

Interesting!

In the end there will be an Update one to one for the files that match, a Delete for the files that no longer exist and an Insert (Append) for the new records.

I have worked a bit with Arrays, they scare me :-(
Arrays are something I need to learn, but the learning curve has been difficult for me.

But I will take all the ideas I can get.

Thanks



rdouglass -> RE: Update access Table from an Excel table in ASP (5/1/2008 8:26:46)

quote:

In the end there will be an Update one to one for the files that match, a Delete for the files that no longer exist and an Insert (Append) for the new records.


If you're going thru all that, why aren't you just replacing the whole table? Or are there some other tables that are cross-referenced? If you're making the Access table match the spreadsheet, it seems as tho you could just delete the items in the table and import the SS.

I'm sure there's something else I'm missing. [;)]



DaveKstl -> RE: Update access Table from an Excel table in ASP (5/1/2008 8:36:22)

It's sort like an inventory. The basic items stay and particulars about them change from the main server, but the item number and quantities on hand stay in the local db (Access).

When the new data comes via excel, we update the particulars like the description and number that should be on hand, delete those items that have are to be removed from our active inventory and add the items that we will have to carry.

So the data is access which we use to manage the business remains as long as they are to be in the active inventory. We do not get rid of where they are stored, quanties we do have, who manages this inventory.

Confusing I know, hurts my head also.

I do apreciate your help. I am firing up now and see if I can apply the concept code you sent.

Dave




rdouglass -> RE: Update access Table from an Excel table in ASP (5/1/2008 9:14:03)

I have an example of a .NET script I recently built that UPDATEs and INSERTs from a CSV (Excel) file I can post if you want but I have nothing prebuilt for DELETEs like that at the moment.

The DELETE one would be a little trickier IMO. I'd approach it by first doing all the UPDATEs and INSERT's and then instead of looping thru the Excel data array, loop thru the Access DB array and check for matches in the Excel array. If no match from Excel array found, delete the record.

If you can get thru this project using arrays, it should cure your aversion to them. Either that, or push you over the edge. [:D] [:D]



DaveKstl -> RE: Update access Table from an Excel table in ASP (5/1/2008 9:37:23)

OK I am getting there - this is good. I can see the data in the array- a good thing.

In my excel file I am using a defined area so can I use the Excel field name in the update

UPDATE myAccessTable SET myAccessName= '" & myExcelArray(1,i) & "' WHERE AccessID = " & myArray(0,i)

Ok how would apply Excel field names. I know I can see the Excel array data by using a field name

The reason is that if someone decides to do me a favor and add more Excel fields or change the order of the Excel fields the data will be messed up.

Thanks for staying with me. I feel I am getting close :-)

Dave



rdouglass -> RE: Update access Table from an Excel table in ASP (5/1/2008 9:43:22)

quote:

Ok how would apply Excel field names.


Not sure what you mean there. Do you mean columns? You can always address columns in a 2-D array using the first 'ordinal' of the array:

myArray(columnNumber,rowNumber)

but remember they are '0-based' meaning the first column is 0 and not 1.

That what you're looking for?



DaveKstl -> RE: Update access Table from an Excel table in ASP (5/1/2008 9:50:49)

Sorry

I want to use the Excel Field (Column) Name in the array and match it to the appropriate Access Field (Column) Name

UPDATE myAccessTable SET myAccessFieldName= '" & myExcelArray(1,i) & "' WHERE AccessID = " & myArray(0,i)

would Equal

UPDATE myAccessTable SET myAccessLastName= '" & myExcelArray(1,i) & "' WHERE AccessID = " & myArray(0,i) so maybe it is this?

UPDATE myAccessTable SET myAccessLastName= '" & myExcelArray(ExcelLastName,i) & "' WHERE AccessID = " & myArray(ExcelID,i)

would the 1 in the myExcelArray exmaple be myExcelArray(ExcelLastName,i)

and the 0 would be AccessID = " & myArray(ExcelID,i)




rdouglass -> RE: Update access Table from an Excel table in ASP (5/1/2008 9:58:09)

quote:

ExcelLastName


Is that a variable? You need to remember that it is no longer an Excel spreadsheet but an array and any names you defined in the Excel SS will not be available in the array.

You need to know the ordinal (or column number) the name is in. Are you saying the column that the name is in will change from time to time? [:'(] [:D]



rdouglass -> RE: Update access Table from an Excel table in ASP (5/1/2008 10:00:18)

quote:

IMO you should remove any column header data from the worksheets and be sure the first 5 cells of the column contain the correct data type.


Also, remember what I said right there. If you have column names as the first row of the SS, they will be interpreted as data and *not* column names.



DaveKstl -> RE: Update access Table from an Excel table in ASP (5/1/2008 10:06:06)

The column position might change if fields are added or if someone gets creative and switches the positions.

So I am trying to guard against that possibility.

I think I can pull off the Array position myArray(1,i) with what you demonstrated earlier.

In my old world, I would pull the Access file down, run queries saved in access, then push the file back up. It took less than 5 minutes.

The Excel file was linked in Access. Now I need to pass the maintenance of the data to someone else and stay on the Dev side of the house.

Thanks



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.125