Powershell: OLEDB CSV query returns bad IP Address format

PowerShell Help
1

I can connect to a csv file using OLEDB but I do not know how to force text output as when an IP Address is returned I believe it is looking at it as a numeric field and is improperly formatted. I presume Powershell Import-CSV module has this built in as it displays correctly.

Code to connect to a .csv file and read IP Address column

#CSV OLEDB connection

$datasource = "$PSScriptRoot\source_files"
$filename = "file.csv"
$connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$datasource;Extended Properties='text;HDR=Yes;';"
$conn = New-Object System.Data.OleDb.OleDbconnection
$conn.ConnectionString = $connstring
$conn.Open()
$cmd = New-Object System.Data.OleDB.OleDBCommand
$cmd.CommandText = "SELECT DISTINCT TOP 5 [IP Address] FROM [$filename]"
$cmd.CommandType = "Text"
$cmd.Connection = $conn

$dataReader = $cmd.ExecuteReader()

while ($dataReader.Read())
{
	 
     $datareader.Item("IP Address")
}

$dataReader.close()

Output - current formatting

192.1313
192.1314
192.1315
192.1316
192.1317

Output - expected

192.13.13.1
192.13.14.1
192.13.15.1
192.13.16.1
192.13.17.1
2

From a quick test, it looks like your CSV file doesn’t have quote marks around the fields. Can you fix the source data?

3

Can I ask why you’re getting data this way from a csv? This seems overly complicated when you could simply do

$Data = Import-Csv -Path <path to file>
4

Sure…2 quick reasons,

  1. I am not proficient in PowerShell coding nor messing with arrays and having to use cryptic synatx whereas I am familiar with sql syntax and queries
  2. From researching:
    Import-CSV with large files, can exhaust all of your RAM…The file I have is large and it takes way to long…the IP Address is just one column out 100k+ rows from a data dump

Eventually, I will dig into the various methods/properties PowerShell offers but if OLEDB can be faster and I know immediately how to create the quiers I need in SQL then I have to go this route - thanks for replying!

5

Hey Matt…thanks for stopping by…

You’re correct…the IP Address field is indeed not surrounded by quotes. Unfortunately, I have no control over this as the data is from a server dump I have no access to. I thought perhaps PowerShell had a switch or a command to apply on that datareader …so, I thought I’d post something here.

6

Understandable, Import-Csv has a -Header switch so you can import only the columns you want and technically that could be fed to sort-object, group-object etc and only store a subset of the data in memory

7

EDIT:::didn’t realize it posted to correct user creating a duplicate

8

Yes…I do realize powershell has the tools i need to do what i have to do with the data…I may end up posting back with a grouping > dashboard simulation post

I will be trying out the header switch to see if that makes a difference on the import - thanks for that info!

9

Have a look at the PSDuckDB module. It integrates DuckDB with PowerShell. DuckDB will meet all your requirements.

psduckdb -Command "SELECT `"IP Address`" FROM 'E:\Temp\file.csv';" | 
    Select-Object -ExpandProperty 'IP Address'