Can not able to import Excel File into SQL

%3CLINGO-SUB%20id%3D%22lingo-sub-69680%22%20slang%3D%22en-US%22%3ECan%20not%20able%20to%20import%20Excel%20File%20into%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69680%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20mostly%20working%20with%26nbsp%3BExcel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20of%20excel%20files%20having%20large%20amount%20of%20data%20(Eg.%20Around%208%20lakh%20rows%20and%2052%20Columns)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20getting%20some%20results%2C%20excel%20is%20hanging%20or%20taking%20more%20time%20and%20more%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20SQL%20is%20very%20fine%20to%20get%20results%20within%26nbsp%3Bseconds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%20is%20while%20importing%20excel%20file%20(Ver.%202007)%20it%20shows%20an%20error%20(Please%20find%20attached%20file)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20It%20allows%20to%20import%20excel%20file%20of%2097-2003%20file%20format.%20i.e%20xls.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20getting%20result%2C%20i%20am%20saving%26nbsp%3B%22xlsx%22%20file%20into%20%26nbsp%3Bmultiple%20%22xls%22%20files%20(Coz%2C%20xls%20having%2065536%20rows%20only)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESingle%20%22Xlsx%22%20file%20of%208%20lakh%20records%20saved%20as%2016%20%22Xls%22%20files%20contains%2050000%20records%20per%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20importing%20them%20into%20MS%20SQL%20Server.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESolve%20this.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F14533i710F0262905A106A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Sqlerror.png%22%20title%3D%22Sqlerror.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-69680%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAnalysis%20Services%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-69817%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20not%20able%20to%20import%20Excel%20File%20into%20SQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-69817%22%20slang%3D%22en-US%22%3ETry%20downloading%20and%20installing%20the%20Microsoft.ACE.OLEDB.12.0%20Data%20Provider%20that%20is%20part%20of%20the%20Microsoft%20Access%20Database%20Engine.%3CBR%20%2F%3E%3CBR%20%2F%3ELink%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D13255%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D13255%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20page%20here%20give%20more%20information%20about%20the%20process%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsocial.technet.microsoft.com%2Fwiki%2Fcontents%2Farticles%2F24236.importing-an-excel-spreadsheet-into-a-sql-server-database.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsocial.technet.microsoft.com%2Fwiki%2Fcontents%2Farticles%2F24236.importing-an-excel-spreadsheet-into-a-sql-server-database.aspx%3C%2FA%3E%3C%2FLINGO-BODY%3E
Highlighted
Regular Contributor

Hi Team,

 

I mostly working with Excel.

 

Some of excel files having large amount of data (Eg. Around 8 lakh rows and 52 Columns)

 

For getting some results, excel is hanging or taking more time and more functions.

 

But SQL is very fine to get results within seconds.

 

Problem is while importing excel file (Ver. 2007) it shows an error (Please find attached file)

 

But, It allows to import excel file of 97-2003 file format. i.e xls.

 

For getting result, i am saving "xlsx" file into  multiple "xls" files (Coz, xls having 65536 rows only)

 

Single "Xlsx" file of 8 lakh records saved as 16 "Xls" files contains 50000 records per file.

 

And importing them into MS SQL Server.

 

Solve this.Sqlerror.png

1 Reply
Highlighted
Try downloading and installing the Microsoft.ACE.OLEDB.12.0 Data Provider that is part of the Microsoft Access Database Engine.

Link:
https://www.microsoft.com/en-us/download/details.aspx?id=13255

The page here give more information about the process:
https://social.technet.microsoft.com/wiki/contents/articles/24236.importing-an-excel-spreadsheet-int...