Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2017
    Posts
    1

    Lightbulb Unanswered: Need some pointers in the right direction Excel to SQL

    Hello guys,

    I'm new here and new to the forums in general. I see that you can find an answer to your problem if you ask nicely on the forums and i'm giving this a try
    Here's the story:
    I'm new in a big company and from what I see the work here could be much improved if they would use a good database.
    At the moment they are using Excel for preparing the CSV files that are used in lots of reports.
    For example they have some excel files that are 100 000 rows and 30 columns(about 15-20mb each), in 20 of those 30 columns there are formulas (mostly vlookups), the formulas point to another file ( a support file ) that is full of info , it has 30 sheets and some sheets have 30-40 columns. The procedure is like this : They extract an excel file with 10 columns and 100 000 rows from a webapp, they copy and paste that data in the file full of formulas and they wait 2-3 hours for the calculations to be complete. They link that file to the webapp. They have to make 20-30 of those files monthly.
    Computers are always stuck with excel computing and many times not responding or crashing.
    I want to help and resolve this situation but I need some help.
    My experience with SQL is limited. I used PLSQL to extract data before, I know some selects, where, like , between etc, but only simple queries I guess.
    I would like to design a nice and easy to use database to help my colleagues and be a hero among men but unfortunately I don't have a clue where to begin.
    Please help me.
    Thank you

  2. #2
    Join Date
    Jun 2004
    Posts
    809
    Provided Answers: 1
    Wow! Where to start? If you don't know where to begin then I would question whether you are the right person to undertake such a task. However...

    First steps:
    1. Decide what you're trying to achieve, and why.
    2. Decide what data you need in order to achieve Step 1.
    3. Decide how this data is to be captured, manipulated, presented etc (e.g. forms, queries, reports. On-screen, printed.)
    3. Decide how best to achieve Step 3.

    Be ready to challenge everything!
    Just because things are done a certain way doesn't mean that that is the correct way. Nor does it mean it is the wrong way.
    Don't decide on a database & then build the solution around your choice. The solution determines which database (e.g. Oracle, Access, etc) you should be using.
    Lastly, and most importantly, read up on database design. You can't do anything if you don't know what you're supposed to be doing or how you're supposed to be doing it.
    One more tip - do not rush to produce a solution - make sure that what you design is better than what you have now, and will still be better in 5 years' time.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,111
    Provided Answers: 5
    With such a limited knowledge, but obvious enthusiasm, have a look at Oracle Application Express (Apex) at apex.oracle.com. If you find it interesting, you have two options: the first one is to acquire access to online & free Apex service (on apex.oracle.com) - you'd get your own workspace and limited quota on the database, but that's enough to have a look and taste how it feels. Or, you could download & install Oracle 11g XE database which is absolutely free, and it already contains Apex (I *think* 4.2) which you can upgrade to the latest version (5.1.2 at the moment).

    Why Apex? Because its wizard(s) allow you to use an Excel file as a source, and Apex generates database table and rudimentary form (so that you could insert/update/delete data) as well as a report (so that you could see what you have). Apex does a lot of work for you. As you said that you don't know much of SQL, I believe that it is just enough to improve wizard's queries, if necessary. On the other hand, many things (such as sorting, filtering, ...) can be done directly in the Interactive Report by an end user (that's why it is called "interactive").

    I'd suggest you to pick a simple Excel file you currently have (not with hundreds of thousands of lines; make it short, but meaningful) and try to create your first "application". You might be surprised how well it works and looks.

    Now, you could - obviously - repeat that for all files you have, but that wouldn't make it a true application because you'd still have separate data in their own tables, not related to each other. I suppose that such a "solution" would be worse than the one you currently have.

    That's why you should listen to @cis_groupie and read up on database design. Once you learn how to properly do that (and it won't take just a few days!), you'd be able to create a good data model, store data into tables (which will now be related to each other) and create your own reports (i.e. writing your queries, not relying on what wizard does).

    Obviously, it means that you'd use Oracle. Previously mentioned XE is free (even for production purposes), but has certain limitations (I think it is 8 GB of user's data, can use only one processor, ...). There's no problem in upgrading it to Standard Edition once you need it (but it'll cost money if you want to - and should - use it legally).

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,519
    We used to do our daily balancing of our 2 billion dollar company on excel. We just bit the bullet and moved the data into database files and moved the formulas into code. It works MUCH faster and is more reliable. What you want to do is to sit down for a full review of the excel system. You will find that it will be fairly easy and fast to do the same calculations in a database tables and code. Good luck and if you need any help don't forget you can always ask questions on the forums.

    Good Luck.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •