Changing data type while importing CSV file using OLE DB

Couple months ago a bug has been reported in one of my scripts: occasionally when importing data a MAC address field would get corrupted in the process. Having some time this week I looked into the issue and found the underlying problem. In this instance MAC addresses are presented in plain text format (no delimiters), e.g. 6451065F6FF2. If first MAC address record in imported file began with at least 7 digits, script would treat such entry as Decimal number (disregarding that remaining characters in 12-character string are letters!). As first record auto-determines data type for the whole column, all subsequent entries got mishandled too. To shed a bit more light on the problem, have a quick look at the code below.

$CSVfile = Get-ChildItem $textBoxSelectedFile.Text
$path = split-path $CSVfile -Parent
$name = split-path $CSVfile -Leaf
$ConnectionString = ""
# Use different provider depending on OS architecture
if([IntPtr]::Size -eq 4)
{
$ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=$path;Extended properties='text;HDR=Yes;FMT=Delimited'"
}
Else
{
$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$path;Extended properties='text;HDR=Yes;FMT=Delimited'"
}

$Connection = new-object System.Data.OleDb.OleDbConnection($ConnectionString)
$Connection.open()
$cmd = new-object System.Data.OleDb.OleDbCommand("Select NewUserName,NewUser_ID,SystemName,NewMacAddress,NewBuildVersion,NewDomainName,NewLanguage,NewUserDeveloperGroup,NewSCCM_Apps from [$name]",$Connection)
$dataAdapter = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dataTable = new-object System.Data.dataTable
[void]$dataAdapter.fill($dataTable)

$dataGridViewAdvertise.DataSource = $dataTable
$dataGridViewAdvertise.AutoSize = $True

The above code would truncate our example MAC address from 6451065F6FF2 to 6451065, while all other MAC addresses would yield blank cells due to data type mismatch. At this point data type for NewMacAddress column has been defined as Decimal and importing a String was not supported. The simplest solution to resolve this would be to surround data in NewMacAddress column with speech marks, forcing the script to treat data as string, but since format of source CSV file was not under my control another solution was required.

Initially I suspected that my Data Grid View was to blame for data corruption, so I have attempted to define relevant columns and data types prior to data import. Unfortunately, even with Data Grid View column data type set to String, data was being displayed incorrectly meaning corruption must have occurred prior to binding data source with Data Grid View. Similarly  I have found no fault with Data Table, so my Select query became the prime suspect.

In the world of SQL the task would be fairly easy: either Convert or Cast function would do the trick. Unfortunately Microsoft’s OLE DB provider is somewhat limited in its functionality and syntax, so that’s where head scratching begun. Solution I came up with is a bit of a blast from the past, but here it is. I had to use Schema.ini file, so called Text File Driver, to define how to handle each of the import files. The concept is fairly simple: a file called “schema.ini” needs to be present in the same directory as the file you are importing data from, the .ini file needs to contain section for the import file in square brackets followed by set of rules for each of the columns. More on the schema file format and syntax can be found on the following MSDN page: https://msdn.microsoft.com/en-us/library/ms709353.aspx

To solve my particular problem, a following set of statements was required in schema.ini file:

[sample_import_file.csv]
ColNameHeader=True
Format=CSVDelimited
Col1=NewUserName Text
Col2=NewUser_ID Text
Col3=SystemName Text
Col4=NewMacAddress Text
Col5=NewBuildVersion Text
Col6=NewDomainName Text
Col7=NewLanguage Text
Col8=NewUserDeveloperGroup Text
Col9=NewSCCM_Apps Memo

Having the above information at hand, the task was fairly simple and the code has transformed into the following:

$CSVfile = Get-ChildItem $textBoxSelectedFile.Text
$path = split-path $CSVfile -Parent
$name = split-path $CSVfile -Leaf
$ConnectionString = ""
# Use different provider depending on OS architecture
if([IntPtr]::Size -eq 4)
{
$ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=$path;Extended properties='text;HDR=Yes;FMT=Delimited'"
}
Else
{
$ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$path;Extended properties='text;HDR=Yes;FMT=Delimited'"
}

# Append schema.ini file to cater for currently processed file
$schemaPath = $path+"\schema.ini"
Add-Content $schemaPath "[$name]"
Add-Content $schemaPath "ColNameHeader=True"
Add-Content $schemaPath "Format=CSVDelimited"
Add-Content $schemaPath "Col1=NewUserName Text"
Add-Content $schemaPath "Col2=NewUser_ID Text"
Add-Content $schemaPath "Col3=SystemName Text"
Add-Content $schemaPath "Col4=NewMacAddress Text"
Add-Content $schemaPath "Col5=NewBuildVersion Text"
Add-Content $schemaPath "Col6=NewDomainName Text"
Add-Content $schemaPath "Col7=NewLanguage Text"
Add-Content $schemaPath "Col8=NewUserDeveloperGroup Text"
Add-Content $schemaPath "Col9=NewSCCM_Apps Memo"

$Connection = new-object System.Data.OleDb.OleDbConnection($ConnectionString)
$Connection.open()

$cmd = new-object System.Data.OleDb.OleDbCommand("Select NewUserName,NewUser_ID,SystemName,NewMacAddress,NewBuildVersion,NewDomainName,NewLanguage,NewUserDeveloperGroup,NewSCCM_Apps from [$name]",$Connection)
$dataAdapter = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dataTable = new-object System.Data.dataTable
[void]$dataAdapter.fill($dataTable)

$dataGridViewAdvertise.DataSource = $dataTable
$dataGridViewAdvertise.AutoSize = $True