CODEWITHSUNDEEP
powershell-4.0
Big Pimpin
Big Pimpin

Reputation: 437

Keep Leading Zero In Export

I am using powershell to run a sql query and export to a csv file. Process works great, but it is dropping a leading 0 in one of my columns. Field type in SQL Server is a varchar (not an option to change it unfortunately), and here is my syntax. Is it possible to continue to use my powershell export process and keep the leading zero?

$GoodSyntax = "Select * From tableunknown"
$extractFile = "C:\Test.csv"
Execute-SQLquery 
if (Execute-SQLquery $GoodSyntax) 

Function Execute-SQLquery {
  param ($GoodSyntax)

$server = "Server01"
$database = "database01"    
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $QueryString
$command.Connection = $connection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$rowCount = $SqlAdapter.Fill($DataSet)
    $DataSet.Tables[0] | Export-Csv $extractFile -NoTypeInformation

    $connection.Close()

EDIT --- Upon further examination, and opening my CSV file in a raw editor like notepad++ the leading zeros are their! They are just dropped when you attempt to view the file in Excel. So Excel is the culprit here.

Upvotes: 0

Views: 2243

Answers (2)

Visual Micro
Visual Micro

Reputation: 1561

I have never seen sql drop any data from varchar. How are you testing this? Maybe the problem is not in the sql but in whatever you use to look at the data.

I know that Excel does remove leading 0's because it tries to be clever and convert chars to numbers but sql does not.

Try viewing the result of $DataSet.GetXml() which will show the raw data more clearly.

$connection.Close()

$DataSet.GetXml()

Update: Excel strips the leading 0's and is confusing you. The sql data is correct.

How to specify formatting when opening a csv file in excel

http://www.upenn.edu/computing/da/bo/webi/qna/iv_csvLeadingZeros.html

Normally I create an xml/html file from the data. Excel will open it and supports various attributes with the data that control formatting, but its messy.

Upvotes: 1

Leptonator
Leptonator

Reputation: 3519

You should be able to do something like:

$a = "1000"
$a = $a.substring($a.length - 3, 37)

Ref. - https://technet.microsoft.com/en-us/library/ee176945.aspx

Upvotes: 1

Related Questions