51–60 of 66 posts
In this case you have someone that understands SQL and Python, but still wants to use a spreadsheet. Applying the predicates at the start, this person should dump Excel entirely - they can get much better integrity using SQL and Python alone.
I'll definitely give this plugin a try.
Oops, bad example. Never store money using a floating-point data type! SQLite has a NUMERIC type that preserves the exact value of your decimal amount.
Excel can easily export CSV files, which are easy to parse. However, standard Linux tools aren't great for working with CSVs, especially ones with multi-line content (eg exports from a CMS). As an experiment, I wrote a quick ruby script called csv2sqlite which parses one more CSV files (and their headers), and automatically populates an SQLite database based on the CSV. If you have a CSV and want to easily know how m…
"Excel can easily export CSV files, which are easy to parse" 'CSV' and 'easy to parse' do not go together that well http://en.wikipedia.org/wiki/Comma-separated_values#Toward_s... also is instructive: Nevertheless, RFC 4180 is an effort to formalize CSV. It defines the MIME type "text/csv", and CSV files that follow its rules should be very widely portable. [...] Each record " should " contain the same number of comm…
$ irb
>> require 'csv'
# => true
>> CSV.parse(file)
Is Python/DataNitro really "One of the easiest ways to connect Excel to a database"? I can go to the Data tab, click on the ribbon item "From other sources:From SQL server" or "From other sources:From Microsoft Query" in far less time than it takes me to read this blog post. Am I missing something? (other the possibility for SQL injection attacks by users of that spreadsheet)
Not sure of the audience for this. If you understand SQL you'd already know most of it... The rest use Excel because that's what they understand. In this case you have someone that understands SQL and Python, but still wants to use a spreadsheet. Applying the predicates at the start, this person should dump Excel entirely - they can get much better integrity using SQL and Python alone.
This could we useful to integrate Excel sheets & data with other systems without too much manual intervention.
I actually wrote a wrapper for the ADO/Jet DB engine in VBA which does exactly this [1]. However, doing it all in python would be a heck of a lot easier.
1) {=DB_QUERY("/path/to/.csv|.xls|.db","SELECT * FROM....")}
As a heavy user of Excel+VBA I would love to be able to switch to coding in Python. I detest VBA with a passion. Plus the DB stuff is nice. It's much better able to return results as a formula array vs the native MS facilities which populate ranges with static data (and potentially clobber existing content). Plus it also means your data is refreshed with a simple Shift-F9. I actually wrote a wrapper for the ADO/Jet D…
Excel supports querying databases out of the box - Data > Get External Data, no coding besides writing the query required.
Whilst this is a blog post by the party who have created the library, I also disagree with some of their assertions.
Hosting it in a sharepoint type thing, with track changes on, multiple users work quite well indeed. Not to mention that if someone is doing modifies or deletes, I'd much, much rather have that kind of history (hell even git/svn) than having a database without an audit setup. Given the amount of work involved in setting up an audit system, merging it into the excel UI they've just created, I really can't see the point he is making, or where he is coming from.
In fact I wouldn't really suggest people moved away from Excel for the volume of data he speaks of ether, it is very easy to backup (host on sharepoint or similar) incredibly easy to share with the people work on it.
What I would say for it being time to move is when you have a relationship then its damn well time to move.
Earlier quoted context omitted.
Once you are going down the road of actually writing code you may as well just take the plunge and use a nicer language than VBA. VBA isn't really going to be any easier unless you already have visual basic experience in some capacity. If you use a "proper" database you're also going to get advantages in robustness and ability to easily deploy over a network. My last memories of using access for anything (admittedly…
It also doesn't do some extremely basic things properly, e.g. foreign keys. Basically Excel -> Access is easy. Excel -> SQL is easy for a proper programmer. But Excel -> Access -> SQL is a complete nightmare. In the end Excel -> Access is a short-term cheap, long-term very expensive decision. It means you invest a lot of money and effort in a tool that can't do databases properly and can't do forms properly and when…