Docstoc

Converting an excel file to txt file

Document Sample
Converting an excel file to txt file Powered By Docstoc
					This script is having the following actions
Actions
      00 Main Driver
      01 Create Text File
      02 Opening Input Excel File
Reusable Actions
      03 Update text File
      04 Update LineItem Part

Description: Conversion of the EDI Gentran input excel file into text file

Prerequisites:
In the data table
1) Fill the following required information in the 00 Main Driver sheet .
            1) FilePath --> Enter the input file's folder path (e.g: C:\EDI\Input)
            2) FileName --> Enter the input excel file's name with extension (e.g: EDI input file.xls)
            3) SheetName --> Enter the sheet name, in which the data is available (E.g: Sheet1)
            4) TargetPath --> Enter the folder path in which the output text file needs to be created. (e.g:
C:\EDI\Output)
            5) TargetFileName --> Enter the output file's name with the extension ".txt" (e.g: EDI output
file.txt)
2) Please Don’t Click/use mouse action when the script is running
Script coding

Action: 00 Main Driver

RunAction "01 Create Text File", oneIteration
RunAction "02 Opening Input Excel File", oneIteration

Action: 01 Create Text File

'Creatting Text file
Option Explicit

Dim fso, txtFile
Dim IsTrgtPath
Dim IsTrgtFileName

'Fetching the target path from DataTable
DataTable.SetCurrentRow(1)
IsTrgtPath = DataTable.Value("TargetPath", "00 Main Driver")
IsTrgtFileName = DataTable.Value("TargetFileName", "00 Main Driver")

Set fso = CreateObject("Scripting.FileSystemObject")
Set txtFile = fso.CreateTextFile(IsTrgtPath & "\" & IsTrgtFIleName, True)


Action: 02 Opening Input Excel File

Option Explicit

' Variable declarations
Dim IsExFileName
Dim IsExFilePath
Dim IsExFileCompletePath
Dim IsSheetName
Dim objexcel
Dim objWB
Dim objWS
Dim I, J
Dim IsHdrStr(50)
Dim IsHdrVal(50)
Dim IsTxtFile
Dim InItmCnt
Dim IsLnItm(100)
Dim FSO
Dim txtFile
Dim IsPrevContent

'Constants
Const InRowCnt = 65536
Const InColCnt = 256
Const ForWriting = 2
Const ForReading = 1

DataTable.SetCurrentRow(1)
IsExFilePath = DataTable.Value("FilePath", "00 Main Driver")
IsExFIleName = DataTable.Value("FileName", "00 Main Driver")
IsExFileCompletePath = IsExFilePath & "\" & IsExFileName
IsSheetName = DataTable.Value("ExSheetName", "00 Main Driver")

Set objExcel = createobject("Excel.application")
Set objWB = objExcel.Workbooks.Open(IsExFileCompletePath)
Set objWS = objExcel.ActiveWorkbook.Worksheets(IsSheetName)
objexcel.Visible = True

'Fetching the Header strings from the excel file and putting them into DataTable sheet.
For J = 1 To InColCnt
       IsHdrStr(J) = objWS.Cells(2, J)
       If Trim(IsHdrStr(J)) = "" Then
              Exit For
       End If
       DataTable.SetCurrentRow(J)
       DataTable.Value("HeaderString", "02 Opening Input Excel File") = IsHdrStr(J)
Next

'Fetching the Header field values from the excel sheet and putting them into DataTable
For J = 1 to InColCnt
       IsHdrVal(J) = objWS.Cells(3, J)
       If IsHdrVal(J) = "" Then
               Exit For
       End If
       DataTable.SetCurrentRow(J)
       DataTable.Value("HeaderValue", "02 Opening Input Excel File") = IsHdrVal(J)
Next

'Fetching the Output text files complete path with name from DataTable
DataTable.SetCurrentRow(1)
IsTxtFile = DataTable.Value("TargetPath", "00 Main Driver") & "\" &
DataTable.Value("TargetFileName", "00 Main Driver")

'Updating the Text file.
RunAction "03 Update text File", oneIteration, IsTxtFile

'Fetching the Line Item field values from the excel sheet
InItmCnt = 0
For I = 7 To InRowCnt
       InItmCnt = InItmCnt + 1
       If Trim(objWS.Cells(I, 1)) = "" Then
              Exit For
       End If
       IsLnItm(1) = objWS.Cells(I, 1)
       IsLnItm(2) = objWS.Cells(I, 2)
       IsLnItm(3) = objWS.Cells(I, 3)
       IsLnItm(4) = objWS.Cells(I, 4)
       IsLnItm(5) = objWS.Cells(I, 5)
       IsLnItm(6) = objWS.Cells(I, 6)
       IsLnItm(7) = objWS.Cells(I, 7)
       IsLnItm(8) = objWS.Cells(I, 8)
       IsLnItm(9) = objWS.Cells(I, 9)
       IsLnItm(10) = objWS.Cells(I, 10)
       'Updating the Line Items into Text file
       RunAction "04 Update LineItem Part", oneIteration, IsLnItm(1), IsLnItm(2), IsLnItm(3),
       IsLnItm(4), IsLnItm(5), IsLnItm(6), IsLnItm(7), IsLnItm(8), IsLnItm(9), IsLnItm(10), InItmCnt
Next

'Adding the Final Line string to the text file.
DataTable.SetCurrentRow(1)
IsTxtFile = DataTable.Value("TargetPath", "00 Main Driver") & "\" &
DataTable.Value("TargetFileName", "00 Main Driver")

Set FSO = CreateObject("Scripting.FileSystemObject")
Set txtFile = FSO.OpenTextFile(IsTxtFile, ForReading)
IsPrevContent = txtFile.ReadAll
txtFile.Close

Set txtFile = FSO.OpenTextFile(IsTxtFile, ForWriting, True)
txtFile.Write(IsPrevContent)
txtFile.Write("UNS+S' ")
txtFile.WriteLine()
txtFile.Close

Set objWS = Nothing
objWB.Close
Set objWB = Nothing
objExcel.Quit

Action: 03 Update text File

Option Explicit

'Variable declarions
Dim FSO
Dim txtFile
Dim IsFileName
Dim IsHdr
Dim IsPos1, IsPos2
Dim IsHdrTxt
Const ForWriting = 2

IsFileName = Parameter("IsFileName")
Set FSO = CreateObject("Scripting.FileSystemObject")
Set txtFile = FSO.OpenTextFile(IsFileName, ForWriting, True)

'Updating the Header part
'Heading 1
DataTable.SetCurrentRow(1)
IsHdr= DataTable.Value("HeaderString", "02 Opening Input Excel File")
If Instr(1, IsHdr, "(") > 0 and InStr(1, IsHdr, ")") > 0 Then
         IsPos1 = InStr(1, IsHdr, "(")
         IsPos2 = InStr(1, IsHdr, ")")
         IsHdrTxt = split(mid(IsHdr, IsPos1+1), ")")
End If
txtFile.Write(IsHdrTxt(0) & "+UNOA:1+")

'Value 1
IsHdr = DataTable.Value("HeaderValue", "02 Opening Input Excel File")
IsHdr = Replace(IsHdr, Right(IsHdr, 1), "T")
txtFile.Write(IsHdr & ":")

'Heading 2
DataTable.SetCurrentRow(2)
IsHdr = DataTable.Value("HeaderValue", "02 Opening Input Excel File")
txtFile.Write(IsHdr & "+138128942:" & IsHdr & "+100112:1111+000000024'" & Space(1))
txtFile.WriteLine()
txtFile.Write("UNH+100100024+ORDERS:D:97A:UN'" & Space(1))
txtFile.WriteLine()

'Heading 3
DataTable.SetCurrentRow(3)
IsHdr = DataTable.Value("HeaderString", "02 Opening Input Excel File")
If Instr(1, IsHdr, "(") > 0 and InStr(1, IsHdr, ")") > 0 Then
         IsPos1 = InStr(1, IsHdr, "(")
         IsPos2 = InStr(1, IsHdr, ")")
         IsHdrTxt = split(mid(IsHdr, IsPos1+1), ")")
End If
txtFile.Write(IsHdrTxt(0) & "+220+")

'Value 3
IsHdr = DataTable.Value("HeaderValue", "02 Opening Input Excel File")
txtFile.Write(IsHdr & "+9'" & Space(1))
txtFile.WriteLine()

'Heading 4
DataTable.SetCurrentRow(4)
IsHdr = DataTable.Value("HeaderString", "02 Opening Input Excel File")
If Instr(1, IsHdr, "(") > 0 and InStr(1, IsHdr, ")") > 0 Then
      IsPos1 = InStr(1, IsHdr, "(")
      IsPos2 = InStr(1, IsHdr, ")")
      IsHdrTxt = split(mid(IsHdr, IsPos1+1), ")")
End If
txtFile.Write(IsHdrTxt(0) & "+137:")

'Value 4
IsHdr = DataTable.Value("HeaderValue", "02 Opening Input Excel File")
txtFile.Write(IsHdr & ":102'" & Space(1))
txtFile.WriteLine()

'Heading 5
DataTable.SetCurrentRow(5)
IsHdr = DataTable.Value("HeaderString", "02 Opening Input Excel File")
If Instr(1, IsHdr, "(") > 0 and InStr(1, IsHdr, ")") > 0 Then
      IsPos1 = InStr(1, IsHdr, "(")
      IsPos2 = InStr(1, IsHdr, ")")
      IsHdrTxt = split(mid(IsHdr, IsPos1+1), ")")
End If
txtFile.Write(IsHdrTxt(0) & "+")

'Value 5
IsHdr = DataTable.Value("HeaderValue", "02 Opening Input Excel File")
txtFile.Write(IsHdr & "::92'" & Space(1))
txtFile.WriteLine()

'Heading 6
DataTable.SetCurrentRow(6)
IsHdr = DataTable.Value("HeaderString", "02 Opening Input Excel File")
If Instr(1, IsHdr, "(") > 0 and InStr(1, IsHdr, ")") > 0 Then
      IsPos1 = InStr(1, IsHdr, "(")
      IsPos2 = InStr(1, IsHdr, ")")
      IsHdrTxt = split(mid(IsHdr, IsPos1+1), ")")
End If
txtFile.Write(IsHdrTxt(0) & "+")

'Value 6
IsHdr = DataTable.Value("HeaderValue", "02 Opening Input Excel File")
txtFile.Write(IsHdr & "::92'" & Space(1))
txtFile.WriteLine()

'Heading 7 & Value 7
txtFile.Write("CUX+2:")
DataTable.SetCurrentRow(7)
IsHdr = DataTable.Value("HeaderValue", "02 Opening Input Excel File")
txtFile.Write(IsHdr & ":9'" & Space(1))
txtFile.WriteLine()

PrintPass "Header Section", "The Header Section is updated successfully in the text file"

'Closing the text file
txtFile.Close
Action: 04 Update LineItem Part

Option Explicit

Dim FSO
Dim txtFile
Dim IsTxtFile
Dim IsPrevContent
Dim IsLnItm1, IsLnItm2, IsLnItm3, IsLnItm4, IsLnItm5, IsLnItm6, IsLnItm7, IsLnItm8, IsLnItm9,
IsLnItm10
Dim InLnItmNo

Const ForWriting = 2
Const ForReading = 1
IsLnItm1 = Parameter("LineItem1")
IsLnItm2 = Parameter("LineItem2")
IsLnItm3 = Parameter("LineItem3")
IsLnItm4 = Parameter("LineItem4")
IsLnItm5 = Parameter("LineItem5")
IsLnItm6 = Parameter("LineItem6")
IsLnItm7 = Parameter("LineItem7")
IsLnItm8 = Parameter("LineItem8")
IsLnItm9 = Parameter("LineItem9")
IsLnItm10 = Parameter("LineItem10")
InLnItmNo = Parameter("ItmCnt")

DataTable.SetCurrentRow(1)
IsTxtFile = DataTable.Value("TargetPath", "00 Main Driver") & "\" &
DataTable.Value("TargetFileName", "00 Main Driver")

Set FSO = CreateObject("SCripting.FileSystemObject")
Set txtFile = FSO.OpenTextFile(IsTxtFile, ForReading)
IsPrevContent = txtFile.ReadAll
txtFile.Close

Set txtFile = FSO.OpenTextFile(IsTxtFile, ForWriting, True)

txtFile.Write(IsPrevContent)
txtFile.Write("LIN+" & IsLnItm1 &"++" & IsLnItm2 & ":BP::92' ")
txtFile.WriteLine()
txtFile.Write("PIA+1+" & IsLnItm3 & ":VP::91' ")
txtFile.WriteLine()
txtFile.Write("QTY+21:" & IsLnItm4 & ":PCE' ")
txtFile.WriteLine()
txtFile.Write("PRI+AAA:" & IsLnItm5 & ":CT::1:PCE' ")
txtFile.WriteLine()
txtFile.Write("RFF+LI::" & IsLnItm6 & "' ")
txtFile.WriteLine()
txtFile.Write("NAD+EC++" & IsLnItm7 &"+++" & IsLnItm9 & "+++' ")
txtFile.WriteLine()
txtFile.Write("SCC+1' ")
txtFile.WriteLine()
txtFile.Write("QTY+21:" & IsLnItm4 & ":PCE' ")
txtFile.WriteLine()
txtFile.Write("DTM+2:" & IsLnItm10 & ":102'    ")
txtFile.WriteLine()

PrintPass "Line Item " & InLnItmNo, "Line Item " & InLnItmNo & " has been updated"

Please refer below the screen shot for the input and output file formats

				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:84
posted:3/18/2010
language:English
pages:8
Description: This script will convert the input excel file to a text file format with some additional texts.