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 -
What sort of tools do you have available? Considering that you say you don't know SQL Server, or at least, where to start, it'll help us to understand what you do know, and what you have access to.
I assume the business does the business already have SQL Server? If so, what version is it, and what licence do they have? For example SQL Server 2012 Standard Edition, SQL Server 2016 Enterprise Edition. What tools do you also have available? For example, does your business have SSIS as well? This is one of the most common ETL (Extraction Transform Load) tools used with SQL Server, and normally comes along with the package.
Excel files are a little "tricky" to work with (I'm sure Phil will appear in this topic and tell you more than I), but the ACE, the drivers used for external applications to read data from xls(x) file, can be quite a challenge to get to work the way you want. Unfortunately this is a failing of the product, and there's not a lot you can do about it apart from grit your teeth and try and deal with it. Thus, if you have to option of exporting files to csv things are actually much easier (as they're really just raw text files).
These are just a few questions for you to answer, which will help any users here give you answer that should be able to work with what you have and your needs. Once we know more, we'll probably have more questions, but also can outline some ideas for you at the same time.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, July 5, 2017 7:18 AM
Thanks for the reply Thom,
Unfortunately I don't know what tools do I have at my disposal, as far as i know I have nothing and no rights to install anything. I was thinking of making a list of software needed and show it to the IT department and maybe if it's approved they will install it on my computer. From what I saw in control panel I see Microsoft SQL server 2008 R2 native client but nothing more related to SQL in my opinion.
The department that i'm in mostly uses Office 2013 and another software that generates reports from the CSV files processed by them. That's all I know so far ... i'm the new guy.
I think the main problem is that they use many web apps that export the excel data in different formats and order. They need to format that data from many sources and put it back in one place.
From what I researched excel is not built to handle so much data and that's why it breaks. I tried a few tricks today but it will take me a long time to put them in practice and maybe they will not even work at all.
I was thinking of using a vba macro to split that 100 000 rows sheet into 10-30 equal files and run the formulas on smaller files then using another macro to cumulate the files back into one large file and save it as CSV. I was half way with that when I found online that the thing dragging down excel is all those vlookups, So I kinda stopped and thought that the best way would be building a database. - Whiteshadow - Wednesday, July 5, 2017 7:33 AM
Knowing what tools you have available is the first important step.
You've posted on a SQL Server forum, so the assumption there is that you are aiming to use/or are using SQL Server. SQL Server is not a cheap purchase, and it's not something you just install on your desktop. it's meant to sit on some kind of dedicated machine, that is up and running as much as possible.
Unfortunately, at the moment, all we really have to work with is:
- You have an Excel while you download from the web
- You open that file, and copy to contents to another file
- That file does something, that produces a result set
- You upload those results back to the web.
That doesn't really give us much to be able to tell you to do, without knowing what you have. Perhaps some others here might have worked with a tool that could help, however, I would not suggest that SQL Server is the answer to just create an ETL package.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Wednesday, July 5, 2017 7:59 AM
You summarized perfectly the situation, and i'm sure you are right.
I will talk with IT maybe they already have some servers available and they will give us a little access to that. There are 2000 employees here all using computers... there must be servers.
I will come back with more details.Thank you for taking the time to talk about my problem.
Have a great day. - Thom A - Wednesday, July 5, 2017 7:59 AM
That's not entirely true. There's always Express Edition with advanced tools. However, SQL Server requires some time to learn how to deal with these automated(ish) processes.
- Luis Cazares - Thursday, July 6, 2017 6:24 AM
True, however, unless I'm mistaken (I rarely look at Express), you can't run an SSIS task on an Express edition of SQL Server. You'd need to boot up VS every time and run it locally. Considering the OP wants to have a process the business can use, having untrained users opening a VS project and task to do the import could be asking for all sorts problems. Especially when SSIS and ACE are rarely cooperative. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Thursday, July 6, 2017 6:29 AM
If the files are CSV, then BULK INSERT can work perfectly fine without the need of using ACE drivers. Express actually doesn't have full SSIS capabilities and you can't open SSIS packages. You can only use the Import/Export wizard, but I'm not sure if you can run saved packages with dtexec.
For these reasons, I was thinking on working on a T-SQL based project more than an SSIS project. - Whiteshadow - Wednesday, July 5, 2017 7:06 AM
The quote above is from the original post and is possibly a key statement. Are you saying that the original form of the files is CSV and that they import those into Excel before doing anything else?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply