How to pipe Excel Data to CSV in PowerShell

The CData Cmdlets Module for Excel is a standard PowerShell module offering straightforward integration with Excel. Below, you will find examples of using our Excel Cmdlets with native PowerShell cmdlets.

Creating a Connection to Your Excel Data

Connecting to Local or Cloud-Stored (Box, Google Drive, Amazon S3, SharePoint) Excel Files

CData Drivers let you work with Excel files stored locally and stored in cloud storage services like Box, Amazon S3, Google Drive, or SharePoint, right where they are.

Setting connection properties for local files

Set the URI property to local folder path.

Setting connection properties for files stored in Amazon S3

To connect to Excel file(s) within Amazon S3, set the URI property to the URI of the Bucket and Folder where the intended Excel files exist. In addition, at least set these properties:

• AWSAccessKey: AWS Access Key (username)
• AWSSecretKey: AWS Secret Key

Setting connection properties for files stored in Box

To connect to Excel file(s) within Box, set the URI property to the URI of the folder that includes the intended Excel file(s). Use the OAuth authentication method to connect to Box.

Dropbox

To connect to Excel file(s) within Dropbox, set the URI proprerty to the URI of the folder that includes the intended Excel file(s). Use the OAuth authentication method to connect to Dropbox. Either User Account or Service Account can be used to authenticate.

SharePoint Online (SOAP)

To connect to Excel file(s) within SharePoint with SOAP Schema, set the URI proprerty to the URI of the document library that includes the intended Excel file. Set User, Password, and StorageBaseURL.

SharePoint Online REST

To connect to Excel file(s) within SharePoint with REST Schema, set the URI proprerty to the URI of the document library that includes the intended Excel file. StorageBaseURL is optional. If not set, the driver will use the root drive. OAuth is used to authenticate.

Google Drive

To connect to Excel file(s) within Google Drive, set the URI property to the URI of the folder that includes the intended Excel file(s). Use the OAuth authentication method to connect and set InitiateOAuth to GETANDREFRESH.

$conn = Connect-Excel -URI "$URI"

Selecting Data

Follow the steps below to retrieve data from the Sheet table and pipe the result into to a CSV file:

Select-Excel -Connection $conn -Table Sheet | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\mySheetData.csv -NoTypeInformation

You will notice that we piped the results from Select-Excel into a Select-Object cmdlet and excluded some properties before piping them into an Export-Csv cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each "row" in the result set, and we do not necessarily want that information in our CSV file.

The Connection, Table, and Columns are appended to the results in order to facilitate piping results from one of the CData Cmdlets directly into another one.

Deleting Data

The following line deletes any records that match the criteria:

Select-Excel -Connection $conn -Table Sheet -Where "Name = Bob" | Remove-Excel

Inserting and Updating Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into Excel, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MySheetUpdates.csv | %{
 $record = Select-Excel -Connection $Excel -Table Sheet -Where ("Id = `'"+$_.Id+"`'")
 if($record){
 Update-Excel -Connection $excel -Table Sheet -Columns ("Name","Revenue") -Values ($_.Name, $_.Revenue) -Where ("Id = `'"+$_.Id+"`'")
 }else{
 Add-Excel -Connection $excel -Table Sheet -Columns ("Name","Revenue") -Values ($_.Name, $_.Revenue)
 }
}

As always, our goal is to simplify the way you connect to data. With cmdlets users can install a data module, set the connection properties, and start building. Download Cmdlets and start working with your data in PowerShell today!