Getting excel data into a web database

Posted on 2005-04-06
Medium Priority
Last Modified: 2010-04-06
Hello experts
I have an excel spreadsheet which I would like to get into a web based access database.

I realise that I could import the data into the database itself,  but was hoping to allow some users an easy way to enter the information already in excel into the web database, other than copy each field from excel and paste into a web update form.

Any ideas please?
Question by:johnhardy

Expert Comment

ID: 13720312
You go to your acces database.

Select file; get external data; import;

choose file type : excel and move to the directory where your excel data are.
 Choose the page on which your data are.

Go through the rest of the wizard.

Note : you do not need to transfer your data from excel to access to make them available over the web. You can directly use an ODBC driver instead.

Author Comment

ID: 13720467
The users are not experienced in getting to the database, downloading and uploading and which is on a remote server and uses odbc.

I am hoping to have a front end which can allow an inexperienced user to be able to input new records into the relational access database.

I am using Dreamweaver, wouldnt it be nice if there were an extension?

LVL 30

Expert Comment

ID: 13722040
You have two options that I can think of.

1. upload the file to the remote server, then import it on the remote server, obviously the user would only see the Browse button part of all that.

2. Copy and paste their entire Excel sheet into some kind of special web form that can recognise when an entire sheet is being pasted.

However I find validating the scrappy data that users put into Excel is hard enough even without going through a web interface!

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 12

Expert Comment

ID: 13722709
I like number one of the two options above.  I don't have a solution but if you were to attempt this you could import the data from the excel document into your form and do validation through the form.  the tricky part would be extracting the plain text from the excel document and I'd be willing to wager there is a tool out there to do just that.  It wouldn't be a great idea to allow users to dump their data directly into the DB.

Good luck!
LVL 30

Expert Comment

ID: 13722750
regarding the second option.

If you copy an Excel sheet, it goes into the clipboard as a TAB seperated file.

If you can pick up the local machines clipboard (or even paste into a free text control) then you can effectively reproduce whats in the Excel sheet from the tab-delimited file.

Author Comment

ID: 13725287
Hopefully the format of the data that the users will have will be consistant and if I can make it straight forward we may even get a consistant "volunteer"

I like the simplicity of copying cels and creating the tab delimited file say in note pad from  nmcdermaid,  now how can I make it straightforward to input that info into the database on the server perferably via a web form.  Thanks


Expert Comment

ID: 13729215
LVL 30

Expert Comment

ID: 13732140
So after the last post you have three options.

1. Use the web page to upload the file to the server. Import it on the server
2. Copy and paste the data into the web page. Transfer the data from web page to server
3. Use the web page to upload the data directly to the server

Author Comment

ID: 13734123
I had a look at the link above and thought that is was about excel and not access which is what I am concerned with.

I understand how to get the text into the tab delimited format.

I want to avoid having to cut and paste the individual fields into an insert form.

I cant see a way above of doing this. I expect I just dont understand the terminology

1. Use the web page to upload the file to the server. Import it on the server
Thats after Copy and paste into the individual field?
2. Copy and paste the data into the web page. Transfer the data from web page to server
3. Use the web page to upload the data directly to the server
Seems the same
Sorry if I am not understanding whats being said.
Thanks John
LVL 30

Accepted Solution

nmcdermaid earned 1400 total points
ID: 13734388
If we look at it from a users point of view,

Regarding option one, from the web page they would hit a browse button, and select their Excel file.

Then behind the scenes:
1. This file gets uploaded to whatever server has the Access database.
2. The file gets transferred into the Access database locally.

However there are proabably a few security issues with that method.

Regarding option two (as linked to by apparition) , the same thing happens except that the web page opens the Excel file and pushes it into the database. The Excel file remains on the client system, it doesn't get uploaded to the server. The data is pushed into the Access database from the web page.

That article explains how to draw data from an Excel sheet. To then push that data into Access is just a slight variation.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question