Require Assistance with importing Excel file into SQL table (Without Excel installed)

[WofenMember]

#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,
Wofen

[OssianModerator]

#191738

You 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-guide

As it produces an SSIS package, it can be automated

[Anonymous]

#371185

Doh, 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

[Anonymous]

#371186

It 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

:(

[OssianModerator]

#191740

Double 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)

[Author]

Posts