CODEWITHSUNDEEP
powershell
mnieto
mnieto

Reputation: 3874

file not found in current directory

I have the following script:

function Export-sql-toExcel {
    [CmdletBinding()]
    Param (
        [string]$scriptFile,
        [string]$excelFile,
        [string]$serverInstance,
        [string]$database,
        [string]$userName,
        [string]$password
    )
    $tokens = ( [system.io.file]::ReadAllText( $scriptFile ) -split '(?:\bGO\b)' )
    foreach ($token in $tokens) {
        $token = $token.Trim()
        if ($token -ne "") {
            $lines = $token -split '\n'
            $title = $lines[0]
            if ($title.StartsWith("--")) {
                $title = $title.Substring(2)
                $title
            }
            Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database -Username $userName -Password $password -Query $token  |
            Export-Excel -Path $excelFile -WorkSheetname $title -FreezeTopRow -ExcludeProperty RowError,RowState,Table,ItemArray,HasErrors
        }
    }

}

I have installed this function as a module. When I invoke the command from, for example, desktop folder, like this:

PS D:\Usuarios\mnieto\Desktop> Export-sql-toExcel -scriptFile .\EXPORT.txt -excelFile Excel.xlsx

I get the following error (the export.txt file is in the desktop folder):

Exception calling "ReadAllText" with "1" argument(s): "Can't find the file 'D:\Usuarios\<MyUserName>\EXPORT.txt'."

EDITED

if I debug and try [system.environment]::CurrentDirectory, it returns 'D:\Usuarios\<MyUserName> That is because my script fails. NET functions and powershell don't share the 'current directory'

Any other way to get the content and parse the $scriptFile file?

Upvotes: 1

Views: 851

Answers (3)

user6811411
user6811411

Reputation:

To my experience .dot NET functions don't like relative path's.

I'd use 

$scriptfile = (Get-Item $Scriptfile).FullName

to resolve to a full path in the function just ahead :

$tokens = ( [system.io.file]::ReadAllText( $scriptFile ) -split '(?:\bGO\b)' )

Upvotes: 1

mnieto
mnieto

Reputation: 3874

I got the solution changing the NET call by a powershell command at this line

$content = Get-Content $scriptFile -Raw
$tokens = ( $content -split '(?:\bGO\b)' )

the trick was in the -Raw parameter, so the file is read as a single string

Upvotes: 1

Quiron
Quiron

Reputation: 370

I had the same situation, it was resolved when i added a dot "." to call the csv

$path2 = $path + ".\file.csv"

Or check for spaces at the $excelFile variable.

Upvotes: 0

Related Questions