Sql-server – I gave 3 large Excel files need to be loaded in Single SQL table

bulk-insertexcelsql servervisual studio

I have 3 large Excel files each sized 56896 KB, I need to load all 3 files into same SQL table. what is better way to handle?
since My excel has more data, my excel hang up when I open and search any data.

I tried to create a ssis package that had excel source and OLDEB destination to load into table. But I create EXCEL source by selecting one file atleast, the Visual studio hangs up. I could not see the preview of my sheet also.

Please let me know how can we proceed? I need to load every month 3 large files to do some analysis.

enter image description here

Best Answer

Here is an example of using Powershell to import Excel data to SQL Server. Referencing the example in Using Powershell to Import Excel file into SQL Server:

The script uses the Provider=Microsoft.ACE.OLEDB.12.0 which I downloaded from Microsoft Access Database Engine 2010 Redistributable. I had to download the 32-bit version on my computer due to other Office products being installed. Due to the fact that I had to use the 32-bit version, I also had to run Powershell under the 32-bit version (which on my computer was located at C:\Windows\SysWOW64\WindowsPowerShell\v1.0). I created the sample table ExcelData_fill (which is referenced in the script below) before running the Powershell script. You'd need to make changes to the top of the script to point to your Excel files and also specify the correct SQL Server instance information. You should be able to import your data by modifying the script to point to each Excel file.

#Change these settings as needed
$filepath = 'C:\temp\ps.xlsx'
#Comment/Uncomment connection string based on version
#Connection String for Excel 2007:
$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
$qry = ‘select name from [sheet1$]’
$sqlserver = 'localhost'
$dbname = 'test'
#Create a table in destination database with the with referenced columns and table name.
$tblname = 'ExcelData_fill'

#######################
function Get-ExcelData
{

    param($connString, $qry='select * from [sheet1$]')

    $conn = new-object System.Data.OleDb.OleDbConnection($connString)
    $conn.open()
    $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn)
    $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
    $dt = new-object System.Data.dataTable
    [void]$da.fill($dt)
    $conn.close()
    $dt

} #Get-ExcelData

#######################
function Write-DataTableToDatabase
{
    param($dt,$destServer,$destDb,$destTbl)

    $connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
    $bulkCopy.DestinationTableName = "$destTbl"
    $bulkCopy.WriteToServer($dt)

}# Write-DataTableToDatabase

#######################
$dt = Get-ExcelData $connString $qry
Write-DataTableToDatabase $dt $sqlserver $dbname $tblname
Related Question