MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL CSV Output

without comments

Saturday, I posted how to use Microsoft ODBC DSN to connect to MySQL. Somebody didn’t like the fact that the PowerShell program failed to write a *.csv file to disk because the program used the Write-Host command to write to the content of the query to the console.

I thought that approach was a better as an example. However, it appears that it wasn’t because not everybody knows simple redirection. The original program can transfer the console output to a file, like:

powershell .\MySQLODBC.ps1 > output.csv

So, the first thing you need to do is add a parameter list, like:

2
3
4

param (
  [Parameter(Mandatory)][string]$fileName
)

Anyway, it’s trivial to demonstrate how to modify the PowerShell program to write to a disk. You should also create a virtual PowerShell drive before writing the file. That’s because you can change the physical directory anytime you want with minimal changes to rest of your code’s file references.

You can create a PowerShell virtual drive with the following command:

7
8

New-PSDrive -Name test -PSProvider FileSystem -Description 'Test area' `
            -Root C:\Data\cit225\mysql\test

but, it will write the following to console:

Name           Used (GB)     Free (GB) Provider      Root                                                                                 CurrentLocation
----           ---------     --------- --------      ----                                                                                 ---------------
test                0.00         28.74 FileSystem    C:\Data\cit225\mysql\test

You can suppress the console output with Microsoft’s version of redirection to the void (> /dev/null), which pipes (|) the standard out (stdout) to Out-Null, like:

7
8

New-PSDrive -Name test -PSProvider FileSystem -Description 'Test area' `
            -Root C:\Data\cit225\mysql\test | Out-Null

Since the program may run before an output file has been created, or after its been created and removed, you need to check whether the file exists before attempting to remove it. PowerShell provides the Test-Path command to check for the existence of a file and the Remove-Item command to remove a file, like:

11
12

if (Test-Path test:$fileName) {
  Remove-Item -Path test:$fileName }

Then, you simply replace the Write-Host call in the other program with the Add-Content command:

Add-Content -Value $output -Path test:$fileName

Now, the PowerShell script file writes the MySQL query’s output to an output.csv file. You can call the MySQLContact.ps1 script file with the following syntax:

powershell MySQLContact.ps1 output.csv

In case these changes don’t make sense outside the scope of the full script, here is the rewritten script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

# Define parameter list for mandatory file name.
param (
  [Parameter(Mandatory)][string]$fileName
)
 
# Define a PowerShell Virtual Drive.
New-PSDrive -Name test -PSProvider FileSystem -Description 'Test area' `
            -Root C:\Data\cit225\mysql\test | Out-Null
 
# Remove the file only when it exists.
if (Test-Path test:$fileName) {
  Remove-Item -Path test:$fileName }
 
# Define a ODBC DSN connection string.
$ConnectionString = 'DSN=MySQLODBC2'
 
# Define a MySQL Command Object for a non-query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = "SELECT last_name " + 
                         ",      first_name " +
                         "FROM   contact " +
                         "ORDER BY 1, 2";
 
  # Attempt to read SQL command.
  try {
    $row = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($row.Read()) {
      # Initialize output for each row.
      $output = ""
 
      # Navigate across all columns (only two in this example).
      for ($column = 0; $column -lt $row.FieldCount; $column += 1) {
        # Mechanic for comma-delimit between last and first name.  
        if ($output.length -eq 0) { 
          $output += $row[$column] }
        else {
          $output += ", " + $row[$column] }
      }
        # Write the output from the database to a file.
        Add-Content -Value $output -Path test:$fileName
    }
 
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $row.Close() }
 
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

While I understand you might want to go to this level of effort if you where building a formal cmdlet, I’m not convinced its worth the effort in an ordinary PowerShell script. However, I don’t like to leave a question unanswered.

Written by maclochlainn

April 4th, 2022 at 12:45 am

Posted in Database,Database Design,DevOps,Microsoft Windows 10,MySQL,MySQL 8,Windows 10,Windows OS

Tagged with ,

«
»