0

The following script execute a query against a database with the command $con.ConnectionString = "Data Source=$DB_PATH" and export all the results to a CSV file. Firstly, when I createad it, the file was working fine with no issue. 

$DB_PATH = "C:\ProgramData\PROISER\ISASPSUS\datastore\dsfile.db"

Add-Type -Path "C:\Program Files\System.Data.SQLite\2010\bin\System.Data.SQLite.dll"

$con = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$con.ConnectionString = "Data Source=$DB_PATH"
$con.Open()

$sql = $con.CreateCommand()
$sql.CommandText = "SELECT * FROM analysis"
$adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $sql
$data = New-Object System.Data.DataSet
[void]$adapter.Fill($data)

$table = $data.Tables

foreach ($t in $table) {
    $CurrentDate = Get-Date
    $CurrentDate = $CurrentDate.ToString('MM-dd-yyyy')
    $t | Export-Csv -Path "C:\Users\santiago.corso\analisis_$CurrentDate" -NoTypeInformation -Append
}

$sql.Dispose()
$con.Close()

From one point to another, the execution of the ps1 file stored in a task scheduled inside Task Scheduler in Windows started showing the "how to open this file" window and no file was created from that point onwards.

Executing the ps1 file on its own works fine, but inside a scheduled task is when the problem occurs.

Santiago Corso
  • 33
  • 5
  • Now it tends to open the powershell file with notepad as if it were for edition. It is really freaking me out, since the problem changes all the time. – Santiago Corso Sep 03 '18 at 18:18
  • Learn about extensions and file associations in Windows! `Export-Csv` creates Comma Separated Values files. NOT Excel .xlsx files. – Theo Sep 03 '18 at 18:53
  • Well yes but thats not the point. I already changed that and nothing still happens – Santiago Corso Sep 03 '18 at 19:11
  • There you have the mistake corrected – Santiago Corso Sep 03 '18 at 19:31
  • I mean that you should not name the exported file `xlsx`. Furthermore the `Export-Csv` can not deal with a Datatable object I think. You will first need to convert that to an array of PSObjects. – Theo Sep 03 '18 at 20:21
  • And how it is possible that with not any change being made at the very beggining, the program was running OK. – Santiago Corso Sep 03 '18 at 20:23
  • 2
    @Theo The pipeline automatically unrolls the `DataTable` object to a list of `DataRow` objects, which `Export-Csv` can handle just fine. – Ansgar Wiechers Sep 03 '18 at 20:38
  • @AnsgarWiechers ok, I was not sure about that. Thanks, for explaining. – Theo Sep 03 '18 at 20:43
  • @SantiagoCorso As has already been pointed out `.xlsx` files are not CSV files. They're zip archives that contain a bunch of XML files, and you'd need an entirely different approach for creating them. If you already fixed the extension in your code then please [edit] your question to reflect that change. Also, it's not clear to me what exactly isn't working the way you expected. Please describe expected and actual result in more detail (examples usually help). For some general tips on troubleshooting scheduled tasks [see here](https://stackoverflow.com/a/41635982/1630171). – Ansgar Wiechers Sep 03 '18 at 20:45
  • First result was that no csv file was created and instead the "how to open file" windows appeared all the time and when i clicked to open with excel nothing happened. Then, a second reaction, at a certain time, started to open only the ps1file with the text editor and no ps1 execution was made. – Santiago Corso Sep 04 '18 at 12:38
  • I see you have edited the question, but now.. You leave out the `.CSV` extension altogether!. Because there is no extension, Windows will ask you which application to use. Normal Windows behavior really. Also, **if you are doing all this on the same machine**, I would advice to add the `-UseCulture` parameter to the `Export-Csv` command. That way the delimiter will be what EXcel expects when double-clicking a .csv file. – Theo Sep 04 '18 at 15:07
  • Well than you a lot for your support then. – Santiago Corso Sep 04 '18 at 19:35

0 Answers0