0

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

  • My files were in .xlsb format. I created the files in .CSV file and imported the files in table through SSIS package. I was able to load the CSV file through flat file connection source without any issues – Sandhya Balakrishnan May 27 at 12:32
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.