Require Assistance with importing Excel file into SQL table (Without Excel installed)
Home › Forums › Other Microsoft Servers and SaaS › SQL Server 2005 / 2008 / 2008 R2 / 2012 / 2016 › Require Assistance with importing Excel file into SQL table (Without Excel installed)
This topic contains 4 replies, has 3 voices, and was last updated by Anonymous 1 year, 7 months ago.
-
AuthorPosts
-
July 5, 2017 at 10:50 pm #167095
First off, its nice to be back in IT… secondly, taking years off makes everything so much harder.
Now, I need assistance with something that should be quite simple, but I have run into constant Road blocks so needed to put up a flare for help.
I have an excel file that is generated from a 3rd party app that I have no control over, and I need to get it into a SQL table that I have very limited control over.
Excel Table has 8 cols (StaffStudent,Surname, Firstname,Year, HomeGroup, DOB, StaffStudentID, Email, ImageName, CardKey).
I need to import 5 cols (StaffStudentID, FirstName, Surname, DOB, Cardkey) into SQL Express.As I cant have Excel installed on the server, I cant use the com method, I tried PSexcel (but was unable to get it too work) and I tried ImportExcel (again, was unable to get it too work), and was hoping that I would be able to ask someone here for how they would achieve it.
I know its not the most information, but I can achieve this with any method that can be automated and will work.
Thank you,
WofenJuly 6, 2017 at 12:42 am #191738You haven’t said which version of SQL server you are using, but all should have the data import / export wizard. This should get you started:
http://searchsqlserver.techtarget.com/feature/The-SQL-Server-Import-and-Export-Wizard-how-to-guideAs it produces an SSIS package, it can be automated
AnonymousJuly 6, 2017 at 4:39 pm #371185Doh, Rookie mistake on my part; Its SQL express 2014.
I will review that guide and attempt to make a package and let you know.Thank you,
Wofen
AnonymousJuly 6, 2017 at 8:52 pm #371186It seems like you can only use SSIS if you have a full copy of SQL, https://docs.microsoft.com/en-gb/sql/integration-services/integration-services-features-supported-by-the-editions-of-sql-server
:(
July 7, 2017 at 12:39 am #191740Double check the import wizard (SSIS lite) isn’t there – I don’t have a copy of Express handy to check
If your Excel Macro (VBA) skills are up to it, you could push from excel:
https://community.powerbi.com/t5/Integrations-with-Files-and/best-way-to-push-data-from-excel-to-sql-server-no-of-rows-keep/td-p/107182
(second accepted solution in the thread) -
AuthorPosts
You must be logged in to reply to this topic.