Solved

reading text file and converting it to csv

Posted on 2016-07-25
8
68 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
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 83

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 83

Expert Comment

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

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 100 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 53

Accepted Solution

by:
Bill Prew earned 400 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

808 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