Solved

reading text file and converting it to csv

Posted on 2016-07-25
8
76 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 36

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 82

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 56

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

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

630 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