Powershell pipe file contents into application without loading file in memory

Question

With cmd I'd run mysql -uroot database < filename.sql to import a database dump (read from file and pass to MySQL). However, < is "reserved" in powershell.

Instead, in powershell I use get-content filename.sql | mysql -uroot database. The caveat is that powershell reads filename.sql completely into memory before passing it along to MySQL, and with large database dumps it simply runs out of memory.

Obviously, I could execute this via cmd but I have a handful of powershell scripts automating various tasks like this and I don't want to have to rewrite them all in batch. In this particular case, filename.sql is a variable that's specified via PS parameters when the automation kicks off.

So how do I get around this memory limitation? Is there another way to pipe the file contents into MySQL directly?

Tony · Accepted Answer · 2011-01-25 01:25:34Z

17

You can Try

mysql -uroot -pYourPassword -e "source C:\temp\filename.SQL"

or

mysql --user=root --password=YourPassword --execute="source C:\temp\filename.SQL"

If things start to get complicated maybe you should write a C# Console application that does the complex tasks.

edited Jan 25, 2011 at 1:25

answered Jan 25, 2011 at 1:19

Tony

17.9k17 gold badges85 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cbednarski Over a year ago

Up and running again. Cheers!

Pritam Banerjee · Accepted Answer · 2018-07-27 21:50:08Z

3

Not sure if this will work for your application or not (it should process the file in chunks of 1000 records at a time, rather than all at once):

get-content filename.sql -readcount 1000 |% {$_ | mysql -uroot database}

edited Jul 27, 2018 at 21:50

Pritam Banerjee

19.1k10 gold badges97 silver badges113 bronze badges

answered Jan 25, 2011 at 1:26

mjolinor

68.7k7 gold badges118 silver badges141 bronze badges

1 Comment

cbednarski Over a year ago

Slick technique, but splitting arbitrarily on number of lines seems to mangle the SQL syntax. This'll definitely come in handy in other places, though, thanks. :)

Jeremy · Accepted Answer · 2014-04-27 00:20:40Z

0

I'd say stay away from the cmdlets for large files. I've been doing something similar with files that are 30+ million lines long and have not had a issue by using the below code. It performs extremely well both speed-wise and memory consumption-wise.

$reader = [IO.File]::OpenText($filetoread)
while ($reader.Peek() -ge 0) {
   $line = $reader.ReadLine()

   #do your thing here

}
$reader.Close()

answered Apr 27, 2014 at 0:20

Jeremy

2433 silver badges11 bronze badges

1 Comment

Uldis Valneris Over a year ago

This will not work when single sql command is split into more than one line.

Thorsten Franz Brune · Accepted Answer · 2021-07-22 15:37:06Z

-1

Windows cmd (example for postgre, translate it individually): psql -h 127.0.0.1 -p 5432 -f database.sql -U .... ....

answered Jul 22, 2021 at 15:37

Thorsten Franz Brune

34 bronze badges

Collectives™ on Stack Overflow

Powershell pipe file contents into application without loading file in memory

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related