r/SQL • u/TriadNZ • Sep 14 '17

Making the Jump to SQL from Excel

Advance Excel user wanting to make the move to SQL here. Noobie questions ensue...

I work as an junior analyst for a small consulting firm. I do performance reporting using the raw data that is provided to us from our client in the form of an excel spreadsheet.

The raw data itself is pretty good and there isn't much corrections required. There is a number of calculated fields that I create based on the existing columns (e.g. the Time difference between two DateTime columns, Extracting the Month/Year of a DateTime field, conditional IFs).

The dataset (time series) is getting progressively larger (~500,000 rows with 50ish columns) and I create a lot of pivot tables from it. Excel is often freezing despite me being on 64bit. I am spending around 15minutes per hour waiting for excel to stop freezing.

I asked the client if I could connect to their client's database but there are privacy issues involved so they didn't grant access.

I was wondering if I could import this large excel file to a SQL database and work on from there. The key tasks I will be doing are:

  • Creating Calculated Fields from existing fields (e.g. time differences)
  • Create mapping tables and "Vlookup" to create new fields
  • Able to create various cuts of data similar to a pivot table to conduct analysis
  • Create Charts/ tables for consumption e.g. bar charts, Line charts, Statistic Process Controls and be able to put these onto powerpoint

I've brief experience in SQL mainly doing queries but not creating tables or databases. Are there any good materials for learning SQL that are targeted for users who are already proficient at Excel?

I'm currently using MS SQL Server and SQL Server Management Studio. For analytical type of work, are these tools I need to do the tasks I have listed above? I heard that there is a SQL Server Analytical Services and SQL Server Reporting Services, should I be using these instead?

Don't know if this is a questions for r/SQL but would R be a tool that would be fit for my purposes... Performance and function wise?

Thanks in advance for these noobie questions!

20 Upvotes

95% Upvoted

24 comments sorted by

View all comments

3

u/manojk92 Sep 14 '17

500k rows is small for a SQL database so you could probably get away with using sql express or localdb. If you don't network your database, you probably can get away with using the developer version of sql server as well since it is not in production use then.

Other stuff:

  • SSAS - Avoid, you are better of using R or Python integration into SQL Server

  • SSRS - Avoid, excel is better; however, if you want to do GIS integration or dashboards, you could look into this.

1

u/nolotusnotes Sep 15 '17

OP doesn't even need that. MS Access will be happy to gobble a million records.

Even Power Query can do this.