June 30, 2021 at 5:21 pm
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.
June 30, 2021 at 5:23 pm
Change the delimiter from a comma to a pipe to avoid that issue.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 30, 2021 at 5:42 pm
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' June 30, 2021 at 5:49 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 30, 2021 at 5:53 pm
Sorry it left them as Comma(,) delimted in the newly created csv.
June 30, 2021 at 6:22 pm
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 30, 2021 at 6:50 pm
I didn't want to use:
$oleDbConn.ConnectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data
THanks.
June 30, 2021 at 7:01 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 30, 2021 at 7:27 pm
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.
June 30, 2021 at 7:35 pm
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
July 1, 2021 at 3:05 pm
What file do I need to Install the "Microsoft.ACE.OLEDB.12.0" driver...
July 1, 2021 at 3:12 pm
I'll like to find a solution without loading the driver..
July 1, 2021 at 3:55 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 1, 2021 at 5:26 pm
I already gave you a alternative solution - up to you to decide which route you wish to take.
July 12, 2021 at 11:42 am
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