3

I have a google doc that I'm using to update a table in a sql table and at the moment I do it manually; copy and paste the data from the google doc. into excel, remove the columns that I don't need then save as a CSV and upload.

I'm wondering if there is a way that I can automate this? The report it's for is daily and the upload is becoming a bit of a bore!

d_-
  • 1,391
  • 2
  • 19
  • 37
Pedlere
  • 61
  • 1
  • 3
  • Not sure why all the down-votes BUT you could use the Google Spreadsheets API to grab your data then insert it directly into your DB - https://developers.google.com/google-apps/spreadsheets/ – timothyclifford Feb 11 '15 at 14:18
  • 2
    Quite sure why the downvotes - there's no attempt at implementation of the above question. – user2366842 Feb 11 '15 at 14:35

2 Answers2

0

You can also go the python-route by utilising the following python libraries: gspread, oauth2client, pandas and sqlalchemy (source). If you want to automate it, use cron or crontab to run your python-script on a hourly, daily, weekly or monthly basis.

d_-
  • 1,391
  • 2
  • 19
  • 37
-1

What DBMS do you use? If you use Microsoft SQL Server, you can use SSIS (SQL Server Integration Services), the easiest way to do this for a novice would be to right-click on the database in SQL Server Management Studio --> Tasks --> Import Data, then follow the wizard to set up the data source (flat file / Excel file etc.) and the data destination (DB table). Once you've finished with the wizard, you can save the package and re-use it later on (or even automate it via SQL Agent job). You can edit the package in Visual Studio (aka SQL Server Data Tools) prior to deployment/automation if needed. More on SSIS: https://msdn.microsoft.com/en-us/library/ms169917.aspx

RobW
  • 128
  • 2
  • 10