Solved

reading txt file and converting it to csv.

Posted on 2016-08-01
11
60 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
  • 4
  • 3
  • 3
11 Comments
 
LVL 51

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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 51

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 51

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
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…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

747 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

12 Experts available now in Live!

Get 1:1 Help Now