Help save me hours of busy work

February 16, 2009 12:47 PM

How can I update whole columns in a sql database using new information from an excel spreadsheet while using a Mac?

I'm using Version 1.3 SQlite Database Browser to manipulate data in a .sql database. For three of the columns, I actually now need to change all values so that decimal point moves 2 places to the left. As of now, the only way I can seem to do this is one cell at a time, which will take forever (because there are 1800+ cells).

(By the way, I'm using the word "column" in the excel manner, as opposed to row - the information associated with each record.)

Here's an example of what I'm talking about. How could I do a mass update to the LAT or the LONG, assuming I have the updated information in Mac OS Excel?

Is there a way to be able to make mass updates (which I now have in an excel spreadsheet), using a Mac? I'd be willing to download software for pay!

posted by visual mechanic to Technology (5 answers total)

Best answer: I am not sure what your two data sources are -- but here is what I do --

If you have an Excel file that you can coorolate to your data (ie, your Excel file has:

PK, LAT, LONG
1, 33.90, -118.20
2, xxx,xxx
4, xx, xxx

Then I usually make a new column in Excel to make the change..

=CONCATENATE("UPDATE table SET LAT =",B2,", LONG=",C2," WHERE PK =",A2)

Then copy those changes into a SQL command prompt.
posted by SirStan at 1:03 PM on February 16, 2009 [1 favorite]

Best answer: You can't just use real SQL?

update TABLE set LONG = LONG/100;
posted by smackfu at 1:42 PM on February 16, 2009 [1 favorite]

I just want to say that I love SirStan's answer for its outside-the-box quality! I was personally thinking of "export to CSV and write a quick perl script."

But yeah, smackfu has the best answer if all you need to do is update all the existing rows in the same way.
posted by sbutler at 4:16 PM on February 16, 2009

Response by poster: If you assumed I knew nothing (which is true) about SQL, is there a tutorial or something you could point me towards to help me understand either of these answers?
posted by visual mechanic at 7:30 PM on February 16, 2009

Response by poster: Thanks guys! I used smackfu's, but Sir Stan's will be VERY useful for future updates that I have to make, too. So awesome!
posted by visual mechanic at 11:44 AM on February 17, 2009