0
mysql -u username -p"password" DbName -h host.com.us < inputFile.sql > outputFile.csv

I'm using the above command to run a query that will output the file locally. The file is only delimited by tabs. I have seen several answers that use shell command and I need formatting commands for Windows command prompt.

user3704920
  • 617
  • 1
  • 8
  • 19

1 Answers1

1

The following command should do what you want at a PowerShell command prompt on Windows:

Get-Content inputFile.sql | mysql -u username -p'password' DbName -h host.com.us | ForEach-Object { $_ -replace "\t","," } | Out-File outputFile.csv

Note that I've written the above out using the full names of the PowerShell cmdlets for clarity. One would typically use the aliases gc and % for Get-Content and ForEach-Object respectively.

A wrinkle that often catches me is that PowerShell uses a 16-bit Unicode encoding by default, so the outputFile.csv produced by the above example command line will be 16-bit Unicode. The Out-File cmdlet has an -Encoding parameter to allow you to make a different choice, such as UTF8. The above command could be rewritten as:

gc inputFile.sql | mysql -u username -p'password' dbName -h host.com.us | % { $_ -replace "\t","," } | Out-File -Encoding utf8 outputFile.csv

Edit: Changed password delimiters to single quotes to prevent PowerShell from attempting string interpolations.

Dan Blanchard
  • 4,014
  • 1
  • 30
  • 26
  • "The splatting operator '@' cannot be used to reference variables in an expression. '@S2d' can be used only as an argume nt to a command. To reference variables in an expression use '$S2d'." – user3704920 Feb 27 '18 at 15:25
  • @user3704920 I need a little more context to understand your comment – Dan Blanchard Feb 27 '18 at 15:31
  • my apologies. It's not recognizing the connection password as a string. Sees the characters as commands. – user3704920 Feb 27 '18 at 15:50
  • Ah, likewise apologies - if you enclose the password in single quotes 'thus' instead of double quotes ("not like this") then PowerShell shouldn't interfere with it - I'll edit my answer accordingly. – Dan Blanchard Feb 27 '18 at 15:53
  • same issue with single quotes. Odd. gc '/Users/first.last/Desktop/queryHere.sql' | mysql -u 'first.last' -p'KS2d&0j89j(@&jSjsJ!' DBName -h host.com.co.uk | % { $_ -replace "\t","," } | Out-File -Encoding utf8 '/Users/first.last/Desktop/myfile.csv' – user3704920 Feb 27 '18 at 15:58
  • As you say, odd! I tried the parameter -p'KS2d&0j89j(@&jSjsJ!' in my environment (Windows 10 version 1709, PowerShell version 5.1.16299.248) and MySQL gave me "Access denied" which is what I'd expect - no sign of string manipulation there. Which versions of Windows and PowerShell do you have? – Dan Blanchard Feb 27 '18 at 16:07
  • windows 7 pro. CLRVersion 2.0.50727.8762 BuildVersion 6.1.7601.17514 PSVersion 2.0 – user3704920 Feb 27 '18 at 16:12
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/165912/discussion-between-dan-blanchard-and-user3704920). – Dan Blanchard Feb 27 '18 at 16:16