Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

reading text file and converting it to csv

Posted on 2016-07-25
8
Medium Priority
?
81 Views
Last Modified: 2016-07-26
Hi All,

I have this txt file which i want to convert to csv.

from the attach text file i want all the columns : Product_code , Description, Bin,  On file, Value, Counted, Value, Variances , Value and the last column i want to name it Status.

Thank you for your help.

Regards,

Ammar
stock25072016.txt
0
Comment
Question by:ammartahir1978
8 Comments
 
LVL 37

Expert Comment

by:Kimputer
ID: 41727177
Seems quite feasible, from what I see. Since all needed lines start with a number, it's quite doable. But need time to figure out the exact fixed width, and how to parse the next line neatly. Probably I need one or two days.
0
 

Author Comment

by:ammartahir1978
ID: 41727432
thanks Kimputer,

can i do anything to speed it up i need it today if possible please?
0
 
LVL 85

Expert Comment

by:oBdA
ID: 41727739
Powershell to the rescue.
Since you didn't provide any information whatsoever about the format (like column width, or allowed characters), the column width used is guesswork.
Note: the Powershell console should be at least 143 characters wide; if you don't want the console output, comment line 57.
$StockFile = 'C:\Temp\stock25072016.txt'
$CsvFile = $StockFile -replace '\.txt\Z', '.csv'

$Columns = @(
	'ProductCode'
	'Description'
	'Bin'
	'OnFile'
	'FValue'
	'Counted'
	'CValue'
	'Variance'
	'VValue'
	'Status'
)
## Column lengths MUST add up to 132!
$re_Product = '\A' +
	"(?<$($Columns[0])>\d{6}.{6})" + 
	"(?<$($Columns[1])>.{30})" + 
	"(?<$($Columns[2])>.{10})" +
	"(?<$($Columns[3])>.{13})" +
	"(?<$($Columns[4])>.{12})" +
	"(?<$($Columns[5])>.{12})" +
	"(?<$($Columns[6])>.{12})" +
	"(?<$($Columns[7])>.{14})" +
	"(?<$($Columns[8])>.{12})" +
	"(?<$($Columns[9])>.{5})" +
	'\Z'
$re_DescriptionOverflow = '\A\s{12}(?<DescriptionOverflow>\S.{0,29})\Z'

Remove-Variable -Name 'Row' -ErrorAction SilentlyContinue
$Results = Switch -RegEx -File $StockFile {
	$re_Product {
		If ($Row) {
			$Row
		}
		$Row = '' | Select-Object -Property $Columns
		ForEach ($Column In $Columns) {
			$Row.$Column = $Matches[$Column].Trim()
		}
		For ($i = 3; $i -lt $Columns.Count; $i++) {
			If (($Row.($Columns[$i])).EndsWith('-')) {
				$Row.($Columns[$i]) = "-$(($Row.($Columns[$i])).Trim('-'))"
			}
		}
	}
	$re_DescriptionOverflow {
		$Row.Description += " $($Matches['DescriptionOverflow'])"
	}
	default {
		If ($Row) {
			$Row
			$Row = $Null
		}
	}
}
$Results | Format-Table -AutoSize
$Results | Export-Csv -Path $CsvFile -NoTypeInformation
"`r`nExported $($Results.Count) entries to '$($CsvFile)'`r`n" | Write-Host

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ammartahir1978
ID: 41727745
Hi oBdA, THANK YOU
How can i run this?

is it via excel module or a VBS?
0
 
LVL 85

Expert Comment

by:oBdA
ID: 41727748
As i said: Powershell to the rescue.
0
 
LVL 84

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 400 total points
ID: 41728128
#trap error on line 14 substring
$ErrorActionPreference = 'SilentlyContinue'
"1 - $ErrorActionPreference;"
(Get-PSSessionConfiguration -Name 'MyShellUri' -ErrorAction SilentlyContinue)
"2 - $ErrorActionPreference;"
$datas = get-content -path d:\downloads\stock25072016.txt
$datalines = @()

for ($counter = 0;$counter -le $datas.Count; $counter++)
{
$desc = $false
if ($datas[$counter] -eq $null) { break;}
if ($datas[$counter].length -le 5 ) {$counter ++; }  #skip empty lines
if ($datas[$counter] -inotlike '*Stock Variance*') {
if ($datas[$counter].Substring(0,5) -match '(\d+)' -eq $true)
        {
        $desc = $true
        $datas[$counter]
        $object = new-object -typename PSObject
        $object | Add-Member –MemberType NoteProperty –Name Product_Code -Value $datas[$counter].Substring(0,11).trim()
        $object | Add-Member –MemberType NoteProperty –Name Description -Value  $myobject[$counter].substring(12,30).trim()
        $object | Add-Member –MemberType NoteProperty –Name Bin –Value $myobject[$counter].substring(46,11).trim()
        $object | Add-Member –MemberType NoteProperty –Name On_File –Value $myobject[$counter].substring(57,10).trim()
        $object | Add-Member –MemberType NoteProperty –Name Value –Value $myobject[$counter].substring(67,15).trim()
        $object | Add-Member –MemberType NoteProperty –Name Counted –Value $myobject[$counter].substring(82,11).trim()
        $object | Add-Member –MemberType NoteProperty –Name Counted_Value –Value $myobject[$counter].substring(93,9).trim()
        $object | Add-Member –MemberType NoteProperty –Name Variances –Value $myobject[$counter].substring(106,11).trim()
        $object | Add-Member –MemberType NoteProperty –Name Variance_Value –Value $myobject[$counter].substring(117,11).trim()
        $object | Add-Member –MemberType NoteProperty –Name Status –Value $myobject[$counter].substring(128).trim()
        #
        #cHECK FOR 2ND LINE OF DESCRIPTION
        #       
        $temp = $myobject[$counter+1].Substring(0,5) 
        if(($temp -match '(\d+)' -eq $false) -and ($desc -eq $true)) {
            $object.Description = ($object.Description+ ' ' + $myobject[$counter+1].substring(12)).trim()
            $counter++
        }
        $datalines += $object
    }

}
}
$datalines | Export-Csv -Path c:\temp\ee25Jul2016.csv -nti 
#start-process c:\temp\ee25jul2016.csv

Open in new window

0
 
LVL 59

Accepted Solution

by:
Bill Prew earned 1600 total points
ID: 41729588
Okay, in case you are interested in a VBS approach - it's a little bit long, but I think it handles everything I could see in the data.  Run as below, and let me know how it goes.

cscript EE28959297.vbs stock25072016.txt out.csv

Option Explicit

' Define needed I/O constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Define global variables
Dim objFSO
Dim strInFile
Dim strOutFile
Dim objInFile
Dim objOutFile
Dim strLine
Dim strProductCode
Dim strDescription
Dim strBin
Dim strOnFileQty
Dim strOnFileValue
Dim strCountedQty
Dim strCountedValue
Dim strVariancesQty
Dim strVariancesValue
Dim strStatus
Dim i

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get parms from command line
If (WScript.Arguments.Count < 2) Then
    WScript.Echo "Usage: " & Wscript.ScriptName & " <input-report-file> <output-csv-file>"
    WScript.Quit
Else
    strInFile = objFSO.GetAbsolutePathname(WScript.Arguments(0))
    strOutFile = objFSO.GetAbsolutePathname(WScript.Arguments(1))
End If

' Make sure input file exists
If Not objFSO.FileExists(strInFile) Then
   Wscript.Echo "*ERROR* Input file does not exist. (" & strInFile & ")"
   Wscript.Quit
End If

' Open input and output files
Set objInFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

' Output header line
objOutFile.WriteLine Quote("ProductCode") & "," & Quote("Description") & "," & Quote("Bin") & "," & Quote("OnFileQty") & "," & Quote("OnFileValue") & "," & Quote("CountedQty") & "," & Quote("CountedValue") & "," & Quote("VariancesQty") & "," & Quote("VariancesValue") & "," & Quote("Status")

' Initialize product code
strProductCode = ""

' Loop through all input file lines
Do While objInFile.AtEndOfStream <> True

   ' Read next line
   strLine = objInFile.ReadLine

   ' Handle form feed in header top line of page header (discard header line after form feed)
   i = InStr(strLine, Chr(13) & Chr(12))
   If i > 0 Then
      strLine = Left(strLine, i-1)
   End If

   ' Is this a detail line 1?
   If Mid(strLine, 1, 1) <> "" And Mid(strLine, 61, 1) = "." And Mid(strLine, 74, 1) = "." Then

      ' Flush prior detail line to output file if we have one
      If strProductCode <> "" Then
         WriteOutput
      End If

      ' Collect detail line 1 fields
      strProductCode = Trim(Mid(strLine, 1, 12))
      strDescription = Trim(Mid(strLine, 13, 30))
      strBin = Trim(Mid(strLine, 47, 5))
      strOnFileQty = Trim(Mid(strLine, 65, 1)) & Trim(Mid(strLine, 53, 12))
      strOnFileValue = Trim(Mid(strLine, 77, 1)) & Trim(Mid(strLine, 66, 11))
      strCountedQty = Trim(Mid(strLine, 90, 1)) & Trim(Mid(strLine, 78, 12))
      strCountedValue = Trim(Mid(strLine, 102, 1)) & Trim(Mid(strLine, 91, 11))
      strVariancesQty = Trim(Mid(strLine, 115, 1)) & Trim(Mid(strLine, 103, 12))
      strVariancesValue = Trim(Mid(strLine, 127, 1)) & Trim(Mid(strLine, 116, 11))
      strStatus = Trim(Mid(strLine, 129, 4))

   ' Is this a detail line 2?
   ElseIf Mid(strLine, 1, 1) = " " And Mid(strLine, 13, 1) <> " " Then

      ' Collect detail line 2 fields (if description overflowed)
      strDescription = strDescription & " " & Trim(Mid(strLine, 13, 30))

   End If

Loop

' Write out last detail line collected if needed
If strProductCode <> "" Then
   WriteOutput
End If

' Close input and output files
objInFile.Close
objOutFile.Close


Sub WriteOutput
   ' Write out prior detail line data
   strDescription = Replace(strDescription, """", """""")
   objOutFile.WriteLine Quote(strProductCode) & "," & Quote(strDescription) & "," & Quote(strBin) & "," & strOnFileQty & "," & strOnFileValue & "," & strCountedQty & "," & strCountedValue & "," & strVariancesQty & "," & strVariancesValue & "," & Quote(strStatus)

   ' Clear out data before starting a new detail line...
   strProductCode = ""
   strDescription = ""
   strBin = ""
   strOnFileQty = ""
   strOnFileValue = ""
   strCountedQty = ""
   strCountedValue = ""
   strVariancesQty = ""
   strVariancesValue = ""
   strStatus = ""
End Sub

' Add surrounding double quotes to a string
Function Quote(s)
   Quote = Chr(34) & s & Chr(34)
End Function

Open in new window

~bp
0
 

Author Closing Comment

by:ammartahir1978
ID: 41730198
Brilliant!!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Utilizing an array to gracefully append to a list of EmailAddresses
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Suggested Courses

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question