Set date format to be used in PowerShell export-csv?

Question

I'm trying to export a database table to text (CSV-ish) for a later BULK INSERT. It would be a lot less hassle to have dates in ISO format yyyy-mm-dd. I have, I believe, finally persuaded SQL Server Express to expect British format in its import (despite the greyed out Server Properties being stuck in "English (US)" no matter what I do). I changed the user accounts to British, and that corresponds to my PowerShell CSV export format. But I'd rather use ISO format to route around the problem for good.

At the moment, having filled a table variable from a SELECT * FROM Table and piped that into Export-CSV, the dates are coming out in the generated text file as dd/mm/yyyy format.

How can I force the PowerShell script to use ISO format dates in all statements (i.e. no specifying formats in each individual command), so the Export-CSV will write them as I need? I've been going around in circles for a couple of hours looking at 'cultures' and things, but I'm utterly confused!

Answer 1

Many Thanks jbockle for the help, I can now take data home from the office (SQL server 2005) and import it into identical tables (from CREATE .sql scripts) on my home Win XP machine running SQL Server 2008 Express.

In this first example, the table is exported directly to CSV and then cleaned up afterwards. The Convert-Line function removes " quotes because BULK INSERT doesn't like them, and also adds extra backtick delimiters to the start and end of each line, so that it can then replace any True with 1 and any False with 0 (anywhere on the line) because Booleans are tricky :)
(it seems to have a problem with adjacent Booleans, so this pass runs twice to mop them all up!)
The final line trims the unwanted ` from the start & end of each line.

##  PowerShell newbies : for scripts to run, you must first execute:  Set-ExecutionPolicy RemoteSigned
##  and then all scripts will work (it's a global setting, remembered once run)

$SQLDT = New-Object "System.Data.DataTable"
$path = "C:"
(Get-Culture).DateTimeFormat.ShortDatePattern="yyyy-MM-dd"   # temp setting, for dates in ISO format

function Convert-Line
{  param( [string]$line=$(throw 'a CSV line is required.'))
  ## add ` to start and end, combined with removing quotes
   $line = "``" + $line + "``" -replace "`"", ""
  ## swap Boolean True/False to 1 or 0
  ##  !! Need to do it twice, as it has trouble with adjacent ones!!
   $line = $line -replace "``True``","``1``" -replace "``False``","``0``"
   $line = $line -replace "``True``","``1``" -replace "``False``","``0``"
  ## return with trimmed off start/end delimiters
   $line.TrimStart("``").TrimEnd("``")
}

function Table-Export
{   param( [string]$table=$(throw 'table is required.'))

   ## Get whole SQL table into $SQLDT datatable
    $sqldt.reset()
    $connString = "Server=.\SQLEXPRESS;Database=Test1;Integrated Security=SSPI;"
    $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ("select * from $table",$connString)
    [void]$da.fill($SQLDT)

   ## Export to CSV with ` delimiter
    $sqldt | Export-Csv $path\$table.CSV -NoTypeInformation -delimiter "``"

   ## read entire file, parse line by line, process line, write back out again
    (gc $path\$table.CSV) | Foreach-Object { Convert-Line -line $_ } | Set-Content $path\$table.CSV
}

# main...
Table-Export -table "Table1"
Table-Export -table "Table2"
Table-Export -table "Table3etc"

This imports nicely using SQL

DELETE FROM table1;
BULK INSERT table1 FROM 'C:\table1.csv' WITH (KEEPIDENTITY, FIELDTERMINATOR = '`');
DELETE FROM table2;
BULK INSERT table2 FROM 'C:\table2.csv' WITH (KEEPIDENTITY, FIELDTERMINATOR = '`');
-- etc, all tables

Original Identity fields are preserved for table joins to still work.

Works fine with field types : numerical, text, boolean, date.

BULK INSERT will complain about the first line containing field names, but that's an ignorable warning (don't bother trying FIRSTROW = 2 as it doesn't work).

In this second example, another approach is taken - this time the DataTable is copied to a new one where each column is string type, so that each field can be adjusted without type problems. The copy datatable is then exported to CSV, and then all we need to do is process it to remove the unwanted doublequotes.

This time we get a chance to replace " in any string fields, so neither doublequotes or commas will break them e.g. a name like John "JJ" Smith will end up as John 'JJ' Smith, which is hopefully acceptable enough.

$SQLDT = New-Object "System.Data.DataTable"
$path = "C:"
(Get-Culture).DateTimeFormat.ShortDatePattern="yyyy-MM-dd"   # temp setting, for dates in ISO format

function Table-Export
{   param( [string]$table=$(throw 'table is required.'))

   ## Get whole SQL table into $SQLDT datatable
    $sqldt.reset()
    $connString = "Server=.\SQLEXPRESS;Database=Test1;Integrated Security=SSPI;"
    $da = New-Object "System.Data.SqlClient.SqlDataAdapter" ("select * from $table",$connString)
    [void]$da.fill($SQLDT)

   ## Copy $SqlDt DataTable to a new $DT2 copy, with all columns now String type
    $DT2 = New-Object "System.Data.DataTable"
    $sqldt.columns | Foreach-Object { $DT2.Columns.Add($_.Caption) > $null }

   ## copy all $SqlDt rows to the new $DT2
   ## and change any " double quote in any field to a ' single quote, to preserve meaning in text fields
   ##  ( or you could use an odd char and replace in SQL database later, to return to " )
    For($i=0;$i -lt $sqldt.Rows.Count;$i++)
    { $DT2.Rows.Add() > $null
      For($i2=0;$i2 -lt $sqldt.Columns.Count;$i2++)
      { $DT2.Rows[$i][$i2] = $SQLDT.Rows[$i][$i2] -replace "`"","'" }
    }

  ## If any $SqlDt column was Boolean...
  ## use column name.. and for all rows in the new $DT2 : convert True/False to 1/0
   $sqldt.columns | Foreach-Object {
     If ($_.DataType.Name -EQ "Boolean")
     { $ColName = $_.Caption
       For($i=0;$i -lt $sqldt.Rows.Count;$i++)
       { If ($DT2.Rows[$i][$ColName] -EQ "True") { $DT2.Rows[$i][$ColName]="1" }
         If ($DT2.Rows[$i][$ColName] -EQ "False") { $DT2.Rows[$i][$ColName]="0" }
       }
     }
   }

   ## Export to CSV with ` delimiter
    $DT2 | Export-Csv $path\$table.CSV -NoTypeInformation -delimiter "``"

   ## read entire file, parse line by line, remove all ", write back out again
   (gc $path\$table.CSV) | Foreach-Object {$_ -replace "`"", "" } | Set-Content $path\$table.CSV
}

# main...
Table-Export -table "Table1"
Table-Export -table "Table2"
Table-Export -table "Table3etc"

Emtpy tables won't break this script, you'll just get a zero-bytes CSV file.

Answer 2

FYI I've done quite a bit with BULK Inserting into SQL using PowerShell, and I found that the simplest way to approach the problem was to:

1. Export the data to CSV with Export-Csv -Delimited "`t" - this is a tab delimited file.
2. When Bulk Inserting, insert into a temp table that has all the columns set to NVARCHAR(MAX) datatype.
3. Create a 2nd Temp Table that has the proper data types set.
4. Select the records from Temp Table 1 into Temp Table 2, with a REPLACE command in SQL to replace all quotes with nothing.

This only really falls over for me when I come across a column that contains a tab within it's own data, a pain but I just replace the tabs with spaces in those columns if I come across them.

As I was dealing with CSV files with many thousands of lines, this was the simplest way I could do it, and the quickest speed wise as it's all set based.

Answer 3

try formatting your culture:

PS C:\> $(get-date).ToShortDateString()
2/16/2013
PS C:\> $(Get-Culture).DateTimeFormat.ShortDatePattern = 'yyyy-MM-dd'
PS C:\> $(get-date).ToShortDateString()
2013-02-16