Solved

reading text file and converting it to csv

Posted on 2016-07-25
8
74 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 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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 81

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 55

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

Independent Software Vendors: 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

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…
This article will show, step by step, how to integrate R code into a R Sweave document
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

729 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