Search code examples
vbaexcelpowershellformatted

Take Excel cell content and place it into a formatted .txt file

So I have an Excel document, which is essentially a table.

For the purposes of this, we will use this: Dummy Data

What I'm wanting to do is take the values from these cells and place them into a .txt file. However the text file has some required formatting, and follows a pattern per 'entry' (row) of the spreadsheet.

E.g.

***********
**THIS IS A FANCY TEXT FILE**
***********
<Start of entry text>
Predefined text here (Entry 1): $A1
Predefined text here:           $B1,$C1
Predefined text here:           $D1,$E1,$F1
Predefined text here: $G1,$H1
Predefined text here: $I1
Predefined text here: $J1,$K1,$L1
Predefined text here: $M1,$N1
<End of entry text>

<Start of entry text>
"As Above"
<End of entry text>
etc, etc

Also, if possible it would be beneficial to have all the values lined together. ie. Like the first three lines are, as opposed to the last 4.

I'd like to be able to run a script that will then output a .txt file with some predefined data, followed by the relevant values of the cells for each entry in the table. I'm assuming I'll need some kind of 'For Each' loop, and I'll have to define the many variables, I'm just not really a coder by nature, and only dabble when I absolutely have to.

Thanks so much in advance for any help you can give.

I'm assuming VBA might be the most popular option, although I'm open to a potential powershell solution as well.

  • XViper

Ok, so I've looked into some powershell options and think I would like to take that approach.

I've managed to get it working using an Import-XLS Function. However I'm not sure how to get the output to a .txt file.

Import-XLS '.\DummyData.xlsx' | Foreach-Object{
Write-host "Predefined data:" `t $_."Value 1"
Write-host "Predata:" `t`t`t $_."Value 2"

Which gives me this.

Predefined data:     AA
Predata:             11
Predefined data:     BB
Predata:             BB11
Predefined data:     CC
Predata:             CC11
Predefined data:     DD
Predata:             DD11
Predefined data:     EE
Predata:             EE11
Predefined data:     FF
Predata:             FF11
Predefined data:     GG
Predata:             GG11
Predefined data:     HH
Predata:             HH11

I know I can't use Write-Host to output, but how can I 'collect' all that data, and then have it output 'ForEach' to a .txt at the end?

Can I output it all collectively? or do I need to output each line one at a time?

Thanks!

Ok... so I've tried some more stuff.

$OutFile = ".\OutTest$(get-date -Format dd-MM).txt"

$Content = Import-XLS '.\DummyData.xlsx'

$Content | foreach-object{
Write-Output "Text1" $_."Value 1"
Write-Output "Text2" $_."Value 2"

} | Out-File $OutFile

So this appears to work, however the problem is, everything is on a new line now.

So I get this:

Text1
AA
Text2
11
Text1
BB
Text2
BB11

I need to be able to format/layout the text like I am able to using Write-Host. Is this possible?

"Value 1" NEEDS to be on the same line as "Text1"

I may also need to do

"Text1: $Value1, $Value2, $Value3" on some lines.

Thanks again!

Solution

  • Format your line as desired using Windows PowerShell -f Format operator, see about_Operators help topic, e.g. Get-Help 'about_Operators' -ShowWindow:

    -f Format operator
      Formats strings by using the format method of string 
      objects. Enter the format string on the left side of the operator 
      and the objects to be formatted on the right side of the operator.


         C:\PS> "{0} {1,-10} {2:N}" -f 1,"hello",[math]::pi
         1 hello      3.14

    For more information, see the String.Format method and Composite Formatting.

    Example:

    Get-ChildItem | ForEach-Object { Write-Output $("{0},{1}" -f "Text1", $_."Name") }
###                                                       ↑

    Sample output:

    Text1,Downloaded
Text1,SF
Text1,SO
Text1,SU
Text1,addF7.ps1

    Edit to answer extending question: So, how would I go about writing my code so as to exclude writing the Output for Null values? You could use Split(Char(), StringSplitOptions) form of String.Split Method from .NET framework and join the result back into a single string as follows (although maybe there are smarter straightforward methods):

    $strng = "{0}:{1},{2},{3},{4},{5}" -f "Item x", 1, $null, 3, $null, $null
$strng
$strng.Split(",",[System.StringSplitOptions]::RemoveEmptyEntries) -join ","

    Output

    Item x:1,,3,,
Item x:1,3