Solved

reading text file and converting it to csv

Posted on 2016-07-25
8
65 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 35

Expert Comment

by:Kimputer
Comment Utility
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
Comment Utility
thanks Kimputer,

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

Expert Comment

by:oBdA
Comment Utility
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
 

Author Comment

by:ammartahir1978
Comment Utility
Hi oBdA, THANK YOU
How can i run this?

is it via excel module or a VBS?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 82

Expert Comment

by:oBdA
Comment Utility
As i said: Powershell to the rescue.
0
 
LVL 78

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 100 total points
Comment Utility
#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 51

Accepted Solution

by:
Bill Prew earned 400 total points
Comment Utility
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
Comment Utility
Brilliant!!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article will show, step by step, how to integrate R code into a R Sweave document
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now