reading txt file and converting it to csv.

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
ammartahir1978Asked:
Who is Participating?
 
Bill PrewConnect With a Mentor Commented:
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
 
Bill PrewCommented:
How do you want the script to work - processing just a single file, or all files in a folder, etc?

~bp
0
 
ammartahir1978Author Commented:
its a single file which get created after running it from system
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
aikimarkCommented:
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
 
ammartahir1978Author Commented:
Thank you perfect
0
 
aikimarkCommented:
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
 
aikimarkCommented:
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
 
aikimarkConnect With a Mentor Commented:
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
 
Bill PrewCommented:
Yes, that looks better.

~bp
0
 
ammartahir1978Author Commented:
Great experts very valueable resource
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.