Solved

reading txt file and converting it to csv.

Posted on 2016-08-01
11
76 Views
Last Modified: 2016-08-10
Hi All i have this file which i want to to read and convert to csv.

Attach is the file.

I need only three columns but third column is a bit difficult one.

1. I need  DATE AND TIME from the top right.
2. I need PRODUCT
3. DESCRIPTION
4. FREE and ORder Bal column.
         Now in this column there are 2 rows the top one is Free stock and under it has Bal on order. I want Bal on order value as 5 column.

CURRENTLY:
Product Code                     Description                             FREE
                                                                                               Bal Ord

100560                                ABC                                                8
                                                                                                     2

Required:    

Product Code                     Description                             FREE            Bal Ord
                                                                                           
100560                                ABC                                                8                  2
stock.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
  • 4
  • 3
  • 3
11 Comments
 
LVL 55

Expert Comment

by:Bill Prew
ID: 41737322
How do you want the script to work - processing just a single file, or all files in a folder, etc?

~bp
0
 

Author Comment

by:ammartahir1978
ID: 41737325
its a single file which get created after running it from system
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41737637
Please test this.  You will need to change the two path constants (cPath, cOut)
    Dim strData
    Dim strOut(3)
    Dim oFS, oTS
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
    Const cPath = "C:\users\mark\downloads\stock.txt"
    Const cOut = "C:\users\mark\downloads\stock_extract.txt"
    
    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile(cPath, ForReading, True, TristateFalse)
    strData = oTS.readall
    oTS.Close
    
    Dim oRE
    Dim oMatches
    Dim oM
    Dim oSM
    Dim lngSM

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "\n(\S+)\s+(\S.{1,28})\s+(\d+)([ -])[^\r]+\r\n {12}(.{1,29})?\s+(\d+)([ -])"

    If oRE.test(strData) Then
        Set oTS = oFS.OpenTextFile(cOut, ForWriting, True, TristateFalse)
        oTS.writeline "Product Code,Description,FREE,Bal Ord"
        Set oMatches = oRE.Execute(strData)
        
        For Each oM In oMatches
            With oM
                strOut(0) = .submatches(0)
                strOut(1) = """" & Trim(Trim(.submatches(1)) & " " & Trim(.submatches(4))) & """"
                strOut(2) = CLng(.submatches(3) & .submatches(2))
                strOut(3) = CLng(.submatches(6) & .submatches(5))
                oTS.writeline Join(strOut, ",")
            End With
        Next
    End If
    oTS.Close

Open in new window

0
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 

Author Comment

by:ammartahir1978
ID: 41737646
Thank you perfect
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41737649
Note: I edited the posted code to add quotes around the description text.  At least one description contains a comma character, which produced a CSV file that was not well formed.
0
 
LVL 55

Accepted Solution

by:
Bill Prew earned 300 total points
ID: 41737706
A bit more code, but i did notice one case that this handles that the REGEX approach didn't.  Test the output from this and the accepted approach, and then check the output line for product "x0001019551".

Run passing the file names on the command line, like:

cscript EE28960720.vbs stock.txt stock.csv

Option Explicit

' ==============================================================================
' C O N S T A N T S   &   V A R I A B L E S
' ==============================================================================

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Dim vbCrFf
vbCrFf = Chr(13) & Chr(12)

' Define global variables
Dim objFSO
Dim strInFile
Dim strOutFile

' ==============================================================================
' I N I T I A L I Z A T I O N
' ==============================================================================

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

' Get command line parms
GetParms()

' ==============================================================================
' M A I N   L O G I C
' ==============================================================================

' Process the input file
ProcessFile strInFile, strOutFile

' ==============================================================================
' W R A P U P
' ==============================================================================

' Done, cleanup and exit
Wscript.Quit

' ==============================================================================
' S U B R O U T I N E S   &   F U N C T I O N S
' ==============================================================================

Sub GetParms()

   ' 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

End Sub

Sub ProcessFile(strInFile, strOutFile)

   ' Define local varaibles
   Dim objInFile
   Dim objOutFile
   Dim strData
   Dim strLine
   Dim strOutput
   Dim arrLines
   Dim strDelim
   Dim i
   Dim strProductCode
   Dim strDescription
   Dim strFree
   Dim strOrderBal

   ' Open input file, read into array for processing
   Set objInFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
   strData = objInFile.ReadAll
   objInFile.Close
   Set objInFile = Nothing

   ' Determine what is used in the file for line delimiters
   If InStr(strData, vbCrLf) > 0 Then
      strDelim = vbCrLf
   ElseIf InStr(strData, vbLf) > 0 Then
      strDelim = vbLf
   ElseIf InStr(strData, vbCr) > 0 Then
      strDelim = vbCr
   End If
   strData = Replace(strData, vbCrFf, strDelim)
   strData = Replace(strData, vbLf& vbLf, vbLf)

   ' Split file data into lines based on delimiter
   arrLines = Split(strData, strDelim)

   ' Open output file, output header line
   Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)
   objOutFile.WriteLine Quote("ProductCode") & "," & Quote("Description") & "," & Quote("Free") & "," & Quote("OrderBal")
   strOutput = ""
   strProductCode = ""

   ' Process each line of the file
   For Each strLine in arrLines

      If Trim(Mid(strLine, 1, 12)) = "Product" Or Trim(Mid(strLine, 1, 12)) = "Code" Then

      ' Is this a detail line 1?
      ElseIf Trim(Mid(strLine, 1, 12)) <> "" And Trim(Mid(strLine, 131, 1)) <> "" Then

         ' Collect detail line 1 fields
         strProductCode = Trim(Mid(strLine, 1, 12))
         strDescription = Trim(Mid(strLine, 13, 30))
         strFree = Trim(Mid(strLine, 51, 1)) & Trim(Mid(strLine, 43, 8))

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

         ' Collect detail line 2 fields (if description overflowed)
         If Trim(Mid(strLine, 13, 30)) <> "" Then
            strDescription = Trim(strDescription & " " & Trim(Mid(strLine, 13, 30)))
         End If
         strOrderBal = Trim(Mid(strLine, 51, 1)) & Trim(Mid(strLine, 43, 8))

         objOutFile.WriteLine Quote(strProductCode) & "," & Quote(strDescription) & "," & strFree & "," & strOrderBal
         strProductCode = ""
         strDescription = ""
         strFree = ""
         strOrderBal = ""

      End If

   Next

   objOutFile.Close
   Set objOutFile = Nothing

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
1
 
LVL 45

Expert Comment

by:aikimark
ID: 41737722
Good catch, Bill.

===========================
@ammartahir1978,

How should products like x0001019551 be handled?  There is no text on the first line of the description.

Are there products that contain no descriptive text?  If so, how should they be handled?
0
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 200 total points
ID: 41737738
I updated the regex pattern in the following code snippet.  It seems to take care of the x0001019551 case.
    Dim strData
    Dim strOut(3)
    Dim oFS, oTS
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Const cPath = "C:\users\aikimark\downloads\stock.txt"
    Const cOut = "C:\users\aikimark\downloads\stock_extract.txt"
    
    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile(cPath, ForReading, True, TristateFalse)
    strData = oTS.readall
    oTS.Close
    
    Dim oRE
    Dim oMatches
    Dim oM
    Dim oSM
    Dim lngSM

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "\n(\S{1,11})\s{1,6}(.{29}) + (\d+)([ -])[^\r]+\r\n {12}(.{29})\s+(\d+)([ -])"

    If oRE.test(strData) Then
        Set oTS = oFS.OpenTextFile(cOut, ForWriting, True, TristateFalse)
        oTS.writeline "Product Code,Description,FREE,Bal Ord"
        Set oMatches = oRE.Execute(strData)
        
        For Each oM In oMatches
            With oM
                strOut(0) = .submatches(0)
                strOut(1) = """" & Trim(Trim(.submatches(1)) & " " & Trim(.submatches(4))) & """"
                strOut(2) = CLng(.submatches(3) & .submatches(2))
                strOut(3) = CLng(.submatches(6) & .submatches(5))
                oTS.writeline Join(strOut, ",")
            End With
        Next
    End If
    oTS.Close

Open in new window

0
 
LVL 55

Expert Comment

by:Bill Prew
ID: 41737742
Yes, that looks better.

~bp
0
 

Author Closing Comment

by:ammartahir1978
ID: 41750273
Great experts very valueable resource
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

696 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