Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Excel and SQL

datanitro.com

Post reply on HN
Print view View original

21–30 of 66 posts

Re: Excel and SQL

#21

Post by jiggy2011 »

slg wrote: post #20
mattmanser wrote: post #15

Earlier quoted context omitted.

Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers or get some proper programming training rather than a SAMS teach yourself VBA in 21 days book (OT, do they still have them?). But most businesses won't realize that/be too cheap to spend the money to save much more money. Although it's much better these days than…

I agree with most of what you are saying, but I just don't see how this tool solves that problem. Both these options seem to be middle steps between the layman and full fledged software development. The Access/VBA route is slightly closer to the layman side while the DataNitro route is closer to the programming end (if you are the type of person who thinks Excel is a great front end for your database interactions, yo…

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 about 10 years ago) was that it was prone to performance issues and data loss.

Re: Excel and SQL

#22

Post by dergachev »

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 many records it has, or to filter or join these records, it can be just a matter of running something like following:

ruby ~/csv2sqlite/csv2sqlite.rb baby-names-10.csv --output babynames.db

sqlite3 babynames.db "SELECT * FROM baby_names_10 WHERE percent > .05;"

Hope it helps you!

Re: Excel and SQL

#23

Post by sokoloff »

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)

Re: Excel and SQL

#24

Post by mistermann »

mattmanser wrote: post #15
slg wrote: post #11

I might be laughed off of HN for even suggesting this, but why not use Access and VBA for something like this? Most computers that have Excel installed will already come with a copy of Access. Plus Microsoft built the three to work together pretty seamlessly. It might not provide all the same advantages of Python and other database software, but it is easier to pickup and is a huge improvement over the old Excel as a…

Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers or get some proper programming training rather than a SAMS teach yourself VBA in 21 days book (OT, do they still have them?). But most businesses won't realize that/be too cheap to spend the money to save much more money. Although it's much better these days than…

> Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers

I hear this all the time, I wonder if you could expand on your reasons for this belief. Is Access always the wrong tool for any job?

I know of a few smallish (5 to 25 or so employees) that have been running their companies for well over a decade on quite large and complex custom written Access applications that were developed for far, far less than it would have cost to have it done "properly".

Re: Excel and SQL

#25

Post by dragonwriter »

rz2k wrote: post #19
mattmanser wrote: post #15

Earlier quoted context omitted.

Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers or get some proper programming training rather than a SAMS teach yourself VBA in 21 days book (OT, do they still have them?). But most businesses won't realize that/be too cheap to spend the money to save much more money. Although it's much better these days than…

Access can be a convenient way to distribute structured data that is relatively static to other people, such as colleagues in another office, when it is not worth the effort to get approval for and implement a longer term solution. I'm surprised that Microsoft has stopped including it in the more basic versions of Office, since it encourages people to seek better solutions who otherwise might stay completely in the M…

> Access can be a convenient way to distribute structured data that is relatively static to other people, such as colleagues in another office, when it is not worth the effort to get approval for and implement a longer term solution.

Right, but that's an artifact of broken bureaucracy -- its not that Access is the right tool for the job, its that its the best tool left when bureaucratic controls are misapplied to prevent the use of the best tool for the job.

Re: Excel and SQL

#26

Post by redman25 »

dergachev wrote: post #22

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's CSV export has a lot to be desired. From what I've tried, it doesn't export data in Unicode and exports non-standard CSV that many parsers choke on.

Re: Excel and SQL

#27

Post by Someone »

dergachev wrote: post #22

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 comma-separated fields.

[...]

Fields containing a line-break, double-quote, and/or commas should be quoted.

[...]

The format is simple and can be processed by most programs that claim to read CSV files.

Re: Excel and SQL

#28

Post by Someone »

sokoloff wrote: post #23

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)

Also, one do it the other way around: use SQL queries to read (and update!) Excel files. For examples, see http://support.microsoft.com/kb/306397 and http://sqlserverpedia.com/wiki/Access_Excel_Sheets_as_a_Tabl...

Re: Excel and SQL

#30

Post by fusiongyro »

mistermann wrote: post #24
mattmanser wrote: post #15

Earlier quoted context omitted.

Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers or get some proper programming training rather than a SAMS teach yourself VBA in 21 days book (OT, do they still have them?). But most businesses won't realize that/be too cheap to spend the money to save much more money. Although it's much better these days than…

> Access is for people who should be programmers but aren't yet. If you find yourself thinking about Access it's probably time to hire some proper programmers I hear this all the time, I wonder if you could expand on your reasons for this belief. Is Access always the wrong tool for any job? I know of a few smallish (5 to 25 or so employees) that have been running their companies for well over a decade on quite large…

There are hard limits to Access's internal engine (JET, IIRC) which you're likely to run into as time goes on. The unfortunate thing is that there isn't anything you can do to avoid the limits or recover once you've hit them.

On the other hand, you can install Postgres under Windows and then build an Access frontend over ODBC. This gets you the best of both worlds, IMO, at the cost of running a kind of odd stack. Cheaper than SQL Server though, and I like the maintenance story better.

TBH the last time I considered a solution like this I was consulting, so it was a few years ago, and all my clients either didn't sign up at all or went for a custom web solution instead, so I don't have a lot of war stories about this platform. I do think it would work though.

Post reply on HN