• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

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
0
ammartahir1978
Asked:
ammartahir1978
  • 4
  • 3
  • 3
2 Solutions
 
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
 
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
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.

 
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
 
Bill PrewCommented:
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
 
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
 
aikimarkCommented:
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

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!

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now