Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

reading txt file and converting it to csv.

Posted on 2016-08-01
11
Medium Priority
?
80 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 58

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 46

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
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.

 

Author Comment

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

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 58

Accepted Solution

by:
Bill Prew earned 1200 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 46

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 46

Assisted Solution

by:aikimark
aikimark earned 800 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 58

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
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 …
Suggested Courses

636 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