0

I want to dump my excel data into sql database

I want to made synchronization between SQL and Excel

Can anybody help me on this

Thanks

Rohan

R-T
  • 113
  • 1
  • 8
  • Hi, Julien I have stuck in this i have tried everything but not able to connect a link between SQL and Excel so that if we add some data in excel it automatically update in database by itself – R-T Jun 09 '16 at 07:21
  • Is this a one off exercise or a regular event? What version of SQL Server are you? – P.Salmon Jun 09 '16 at 07:21

1 Answers1

1

You can use SQL Server Import and Export Wizard (here is a good guide to import and export data). After import/export procedure you can save SSIS package based on what you just did and then use it in job to run it by schedule (good guide here).

EDIT

Also you can use linked server for this. Please, read this article.

One more way is to create macros that will update data on SQL Server side after changing the excel file.

But all options has one ultimate flaw: they can not provide online update in both sources. Only on demand (macros, job, SSIS package etc.)

gofr1
  • 15,741
  • 11
  • 42
  • 52
  • Hi, I have treid import and export data option but I can't see the Excel in import option in SQL and please tell me how to add screen shot on stackoverflow – R-T Jun 09 '16 at 07:10
  • Try this please https://stackoverflow.com/questions/30359446/missing-microsoft-excel-as-option-in-sql-server-data-import-tool . In other words - you need to find and install Microsoft Access Database Engine Redistributable for your system. – gofr1 Jun 09 '16 at 07:13
  • Note: this must be installed on machine o which SQL Server is running. – gofr1 Jun 09 '16 at 08:19
  • I have downloaded this engine but nothing gets change – R-T Jun 09 '16 at 08:40
  • can Task scheduler do this job – R-T Jun 09 '16 at 08:40
  • Yes it can, but it is a bit weird to done this via task scheduler and require some tuning. I assume you have no MS Office packages installed on the machine where SQL Server is running? – gofr1 Jun 09 '16 at 09:03
  • yes I have MS office Excel on my machine – R-T Jun 09 '16 at 09:29
  • And SQL server is on same machine? – gofr1 Jun 09 '16 at 09:30
  • yes both are on same machine – R-T Jun 09 '16 at 09:45
  • Well if you run all on same machine what is version of SQL Server and Office? 64bit or 32bit? And you start wizard this way *Right click on the MSSQLTipsSSISTutorial database in the Object Explorer, select Tasks, then Import Data from the context menu to launch the Import Wizard.* or else way? – gofr1 Jun 09 '16 at 09:52
  • I have windows 10 32bit . I can't find the version of sql and excel – R-T Jun 09 '16 at 10:29
  • If windows is 32bit then everything is 32bit. In SQL Server execute query `SELECT @@VERSION`. – gofr1 Jun 09 '16 at 10:34
  • 11.0.2100.60 this is the version which I found by configure manager option – R-T Jun 09 '16 at 10:57
  • I just remembered that I run SQL server on win 10 machine 32bit at home. I'll check if issue occurs on my machine too. – gofr1 Jun 09 '16 at 11:16
  • ok thnks for ur concern buddy I really appreciate that – R-T Jun 09 '16 at 11:38
  • You are welcome! I am curious why all is installed and Excel isn't in import option in SQL. Never encounter this in my experience. Ahhh.. just seen your question about screenshots: press Edit near your question. Then put cursor where you want to add image and press Ctrl+G or button with small pic in top menu. Than just enter link to your image or Drug&Drop it on opened form. – gofr1 Jun 09 '16 at 11:48
  • ok I will upload my screen shot should I download latest version of excel – R-T Jun 09 '16 at 11:53
  • buddy u understand my point na tables are import to sql from excel successfully but I want to create a link between excel and sql so that whenever I made any change in my excel the database automatically should updated – R-T Jun 09 '16 at 12:01
  • Check updated answer. Please. – gofr1 Jun 09 '16 at 12:15
  • yes I checked that u thinking about importing . tables are import in sql successfully but I want to do it automatic .everytime I should not import those tables from excel there has to be a way to connect a link between excel and sql so that database is update automatically – R-T Jun 09 '16 at 12:19
  • Options are: update by schedule, write macros that will update data on SQL side from excel after button press or after column change. I suggest first option. – gofr1 Jun 09 '16 at 12:27
  • I check it, all is on it's places (Windows 10 32bit SQL Server Express 2014 Office 2016). Did you scrolled up when chose data source? – gofr1 Jun 09 '16 at 18:23
  • Hi Buddy ,I got solution for this there is a tool called EXCEL SPREADS which is used to import data from excel to sql and vice versa ..But this tool is not free we have to purchase – R-T Jun 10 '16 at 09:57