Create files from spreadsheet data or CSV?

[kennysarmy]

Is this a strange question...

 

Can I create txt files from data held in a spreadsheet?

 

I want to create some RAW mail format messages from data I have in a spreadsheet.

 

(The RAW Message Specification)

[DJ-1701]

Well, you can read csv data in using PowerShell and write text to files using PowerShell. You just have to working on the formatting.

[Arthur]

Can I create txt files from data held in a spreadsheet?

Assuming it's an Excel spreadsheet, there's a PowerShell module called ImportExcel that should be able to do what you want.

[kennysarmy]

Assuming it's an Excel spreadsheet, there's a PowerShell module called ImportExcel that should be able to do what you want.

 

Thanks - I'll look in to this tomorrow.

[mavhc]

Same with vbscript, or python, etc etc etc

[ReadTheNetwork]

If it's CSV you could try the following powershell. Changing the $raw= to suit your needs.

$path='.\mail.csv'
Import-Csv $path | ForEach-Object { 
 $raw = "From: "+$_.From+"`r`nTo: "+$_.To
 $raw > $_.FileName
}

 

FileName being the name of the txt file it saves to.

CSV would look similar to:

From,To,CC,ReplyTo,Header,Subject,FileName

...

Edited March 20, 2018 by ReadTheNetwork

[kennysarmy]

Assuming it's an Excel spreadsheet, there's a PowerShell module called ImportExcel that should be able to do what you want.

 

Not getting anywhere importing the script

 

[ThomL]

Running behind a proxy?

 

netsh winhttp show proxy #show current proxy PS is using

netsh winhttp import proxy source=ie #import proxy setting from IE


#*****collect/apply proxy creds if required*****

$webclient=New-Object System.Net.WebClient
$creds=Get-Credential$webclient.Proxy.Credentials=$creds

#***********************************************

[kennysarmy]

Running behind a proxy?

 

netsh winhttp show proxy #show current proxy PS is using

netsh winhttp import proxy source=ie #import proxy setting from IE


#*****collect/apply proxy creds if required*****

$webclient=New-Object System.Net.WebClient
$creds=Get-Credential$webclient.Proxy.Credentials=$creds

#***********************************************

 

Yep, Smoothwall, but I can bypass that and put in the SWGfL proxy which we use for downloading updates, that failed too.

 

Current WinHTTP proxy settings:

 

Proxy Server(s) : http=sslfilter.proxy.swgfl.org.uk:8080;https=sslfilter.proxy.swgfl.org.uk:8080

Bypass List : ;10.*

[ThomL]

We run a smoothwall here, I use the code I posted to make sure PS has the correct proxy settings, then give it my domain creds and web sessions run correctly from PS via smoothwall following the script - my domain account is rather light on filtering though.

 

Might be worth using the smoothwall - have you tried my code/monitoring traffic on the smoothwall

[Arthur]

Not getting anywhere importing the script

Definitely looks proxy related like @ThomL mentioned.

 

To save time I have attached the latest version of the module from the PSGallery.

 

Download, unblock .zip, then extract to:

 

%UserProfile%\Documents\WindowsPowerShell\Modules\ImportExcel

 

or import the module like this:

 

Import-Module ".\ImportExcel v4.0.10\ImportExcel.psm1"

ImportExcel v4.0.10.zip

[kennysarmy]

We run a smoothwall here, I use the code I posted to make sure PS has the correct proxy settings, then give it my domain creds and web sessions run correctly from PS via smoothwall following the script - my domain account is rather light on filtering though.

 

Might be worth using the smoothwall - have you tried my code/monitoring traffic on the smoothwall

 

 

PS C:\Users\ja\Documents\WindowsPowerShell\Modules\ImportExcel> $creds=Get-Credential$webclient.Proxy.Credentials=$creds

 

Get-Credential$webclient.Proxy.Credentials=$creds : The term 'Get-Credential$webclient.Proxy.Credentials=$creds' is

not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or

if a path was included, verify that the path is correct and try again.

At line:1 char:8

+ $creds=Get-Credential$webclient.Proxy.Credentials=$creds

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : ObjectNotFound: (Get-Credential$...dentials=$creds:String) [], CommandNotFoundException

+ FullyQualifiedErrorId : CommandNotFoundException

 

PS C:\Users\ja\Documents\WindowsPowerShell\Modules\ImportExcel>

[ThomL]

As a separate issue it might be worth spending some time getting internet connectivity in powershell - updating of the help is a very handy thing and the easy modules etc.

@Arthur should have you sorted for the module now though, good luck!

[ThomL]

PS C:\Users\ja\Documents\WindowsPowerShell\Modules\ImportExcel> $creds=Get-Credential$webclient.Proxy.Credentials=$creds

 

Get-Credential$webclient.Proxy.Credentials=$creds : The term 'Get-Credential$webclient.Proxy.Credentials=$creds' is

not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or

if a path was included, verify that the path is correct and try again.

At line:1 char:8

+ $creds=Get-Credential$webclient.Proxy.Credentials=$creds

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : ObjectNotFound: (Get-Credential$...dentials=$creds:String) [], CommandNotFoundException

+ FullyQualifiedErrorId : CommandNotFoundException

 

PS C:\Users\ja\Documents\WindowsPowerShell\Modules\ImportExcel>

 

Mistake on my part here, I deleted a carriage return when writing the post - this should be correct:

 

netsh winhttp show proxy #show current proxy PS is using

netsh winhttp import proxy source=ie #import proxy setting from IE


#*****collect/apply proxy creds if required*****

$webclient=New-Object System.Net.WebClient
$creds=Get-Credential
$webclient.Proxy.Credentials=$creds

#***********************************************

Edited March 20, 2018 by ThomL

[kennysarmy]

As a separate issue it might be worth spending some time getting internet connectivity in powershell - updating of the help is a very handy thing and the easy modules etc.

@Arthur should have you sorted for the module now though, good luck!

 

Thanks, is there a way to simply check if I've got internet access in Powershell?

[ThomL]

Not sure, this way worked for me - once I got the modules I needed and updated the help files I stuck with this way of doing it. There might be another/better way of connecting when behind a proxy.

 

Anyone got an ideas?

[kennysarmy]

Not sure, this way worked for me - once I got the modules I needed and updated the help files I stuck with this way of doing it. There might be another/better way of connecting when behind a proxy.

 

Anyone got an ideas?

 

I've found this - https://dscottraynsford.wordpress.com/2016/06/24/allow-powershell-to-traverse-a-secure-proxy/

 

but I'm getting a little bit out of my depth - might need to start watching some YouTube Powershell for dummies videos !

[ReadTheNetwork]

If you want a quick easy solution which doesn't require powershell having Internet access, the method I mentioned should suffice (using csv files).

Then you can spend more time looking at connecting your powershell to the Internet at a later date.

[ThomL]

I've found this - https://dscottraynsford.wordpress.com/2016/06/24/allow-powershell-to-traverse-a-secure-proxy/

 

but I'm getting a little bit out of my depth - might need to start watching some YouTube Powershell for dummies videos !

 

Look like they are taking a script that is rather similar to what I posted, turning it into a function and then calling that function when then need inet access from powershell. What you could really do with is the function being part of your default powershell profile and connecting automatically when you launch PS - but I have never bothered with this as for me it's overkill. I connect using the script as needed.

 

I think powershell profile might be the way to go as I said then the code could run with powershell launch and always connect or wrap the code as a function in the profile (I thin) meaning you could call with something like: Connect-ProxyInternet once powershell had launched

[kennysarmy]

If you want a quick easy solution which doesn't require powershell having Internet access, the method I mentioned should suffice (using csv files).

Then you can spend more time looking at connecting your powershell to the Internet at a later date.

 

Hi,

 

Really useful, and I'm almost there.

 

Code:

$path='.\mail.csv'

Import-Csv $path | ForEach-Object {

$raw = "From: "+$_.From+"`r`nTo: "+$_.To+"`r`nSubject: "+$_.Subject+"`r`nBody1: "+$_.Body1+"`r`nBody2: "+$_.Body2

$raw > $_.FileName

}

 

CSV file:

from,to,Subject,Body1,Body2,Filename

,,,Username is XXX,Password is YYY,test.raw

 

 

Output:

From:

To:

Subject:

Body1: Username is XXX

Body2: Password is YYY

 

 

I just need to work out how to remove the Body1 and Body2 bits from the output

[kennysarmy]

$path='.\mail.csv'

Import-Csv $path | ForEach-Object {

$raw = "From: "+$_.From+"`r`nTo: "+$_.To+"`r`nSubject: "+$_.Subject+"`r`n"+$_.Body1+"`r`n"+$_.Body2

$raw > $_.FileName

}

 

That does it

[ReadTheNetwork]

Awesome

[kennysarmy]

Awesome

 

Yes the files look great...

 

Sadly Mdaemon does not agree lol

 

The following RAW format message had parsing problems. This message has

been removed from the outbound queue and has not been delivered. Please

check with your email administrator if you need help.

 

Problem: Missing "to

"

 

[Original RAW Message]

ÿþf

[Arthur]

Sadly Mdaemon does not agree lol

Try replacing...

 

$raw > $_.FileName

 

with

 

$raw | Out-File -FilePath $_.FileName -Encoding ascii

[kennysarmy]

Try replacing...

 

$raw > $_.FileName

 

with

 

$raw | Out-File -FilePath $_.FileName -Encoding ascii

 

Working like a charm now.

 

Others might find this useful so final code is:

 

$path='.\mail.csv'

Import-Csv $path | ForEach-Object {

$raw = "from "+$_.From+"`r`nto "+$_.To+"`r`nsubject "+$_.Subject+"`r`n"+"`r`n"+$_.Body1+"`r`n"+$_.Body2

$raw | Out-File -FilePath $_.FileName -Encoding ascii

}

 

 

Where mail.csv contains:

from,to,Subject,Body1,Body2,Filename

,,,,,