load xls file to sql

  • Looking for a script to load xls files to SQL without using dbatools or a Driver that's need to be loaded... Is there any Native

    loads of xls to SQL using PS script?

    I've been trying to use a script that converts cls to csv, but I run into issues on fields where the delimter is a comma(,) and there are

    commas in the field text.

    Thanks.

  • Change the delimiter from a comma to a pipe to avoid that issue.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I tried didn't seem to work..

    function ConvertFrom-XLx {
    param ([parameter( Mandatory=$true,
    ValueFromPipeline=$true,
    ValueFromPipelineByPropertyName=$true)]
    [string]$path ,
    [switch]$PassThru
    )

    begin { $objExcel = New-Object -ComObject Excel.Application }
    Process { if ((test-path $path) -and ( $path -match ".xl\w*$")) {
    $path = (resolve-path -Path $path).path
    $savePath = $path -replace ".xl\w*$",".csv"
    $objworkbook=$objExcel.Workbooks.Open( $path)
    $objworkbook.SaveAs($savePath,6) # 6 is the code for .CSV
    $objworkbook.Close($false)
    if ($PassThru) {Import-Csv -Path $savePath -Delimiter "|" }
    }
    else {Write-Host "$path : not found"}
    }
    end { $objExcel.Quit() }
    }
    ConvertFrom-XLx 'C:\temp\MWAddersSXB.xls'
  • I think you've been a forum member long enough to know that "it didn't work" doesn't cut it as a technical reply.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Sorry it left them as Comma(,) delimted in the newly created csv.

  • Can't see an easy way of fixing that. Seems that the Excel code does not allow you to specify the delimiter and instead picks whatever you have in Regional Settings.

    The final post on this thread looks promising: https://stackoverflow.com/questions/45416377/powershell-excel-saveas-csv-with-specified-delimiter

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I didn't want to use:

    $oleDbConn.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data

    THanks.

  • That is because you didn't export from Excel as pipe-delimited, you exported it as a CSV file.  The problem here is that Excel uses the regional settings on the machine to determine the list separator when exporting.  Since that is defined as a comma for US region - that is what will be used when exporting from Excel.

    When you export from Excel in CSV - it should (and does) place quotes around each field where there can be a comma.  I don't recall whether Excel does that for every field or only those that could contain the separator...

    When importing - you need to tell the import process whether or not the fields will be quoted and what they will be quoted with...which is probably where you are having issues in your import process.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • You are correct Jeffrey when I try to use a PS script to import the data, it's getting confused on columns from csv versus

    sql Table.

  • I assume you aren't going to be running this on a server - " New-Object -ComObject Excel.Application }" - this requires Excel to be installed on the server which is not supported and is likely in break of whatever license you have for Office.

    If this is to install on a server its surprising that you are not willing to install a driver (fully supported) but yet are willing to install unsupported software.

    regardless - you can use one of the free/commercial excel readers available on the net (all of them use OpenXML to process excel files) and do something on Powershell or on C# (easier) to read any supplied file and output as a Valid CSV (which can contain fields with the delimiter on them).

    this link contains a sample using EPPlus - but others exist and this should not be taken as a recommendation to use this one over the others.

    https://stackoverflow.com/questions/17688468/how-to-export-a-csv-to-excel-using-powershell

  • What file do I need to Install the "Microsoft.ACE.OLEDB.12.0" driver...

  • I'll like to find a solution without loading the driver..

  • Bruin wrote:

    I'll like to find a solution without loading the driver..

    To do that you would need a file that can be opened using a driver that is already installed on the server.  The drivers for opening text files already exist - therefore you could build a solution that processes any type of text file.  Some valid formats would be comma-separated values (CSV), tab-separate values (TSV), pipe-delimited or fixed width files.

    This is exactly the same as asking for a solution to read from an Oracle, MySql or PostgreSql database system, but you don't want to install the drivers for those database products.  Can't be done...you need the drivers to be able to make the connection.

    I would download and install both the 32-bit (x86) and 64-bit (x64) versions on my workstation.  On the server - you only need the x64 version, but if you install the client tools (SSDT) on the server you would need the x86 version.  The only reason to do that is if you have a requirement to provide access to the tools for development on that server (e.g. for a vendor who accesses the server through Citrix).

    To get the appropriate driver - you need to download and install the Microsoft ACE OLEDB xx driver where xx is the version.  You should be able to download 16.0 and use that version.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I already gave you a alternative solution - up to you to decide which route you wish to take.

  • I have this script working, but would like to add a few features.

    1) backup original xls file at the start of the process with a datetime stamp

    2) produce-record-count-of-data-processed.

    Thanks.

    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $false
    $Excel.DisplayAlerts = $False
    $file = "c:\temp\MWAddersSXB.xls"
    $csv = "c:\temp\MWAddersSXB.csv"
    $csvfinal = "c:\temp\sxbfilefinal.csv"
    $workfile = $Excel.Workbooks.open($file)
    $Sheet = $workfile.Worksheets.Item(1)
    $Sheet.Activate()
    $Sheet.SaveAs($csv,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVWindows)
    $workfile.Close()
    sleep 5
    $Excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
    Import-Csv $csv | Export-Csv $csvfinal -Delimiter "|" -NoTypeInformation

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply