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

Read Text File and Export excel

I am trying to read a text file that has fixed length and then exporting it to Excel via a vsbcript. For example line 1 in the text file would be:
HA            12345                 6789                         10111213
HB             Spoon     fork                      torch

Open in new window

Here is what I have for a vsbscript code, but I am stuck somewhere:

Option Explicit 
Dim oFSO, sFile, oFile, sText, sXLS, oOut
 
sFile = "C:\Sample\test.txt"
 
sXLS = "C:\Sample\testouput.xlsx"
 
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oOut = oFSO.CreateTextFile(sXLS, True)
 
If oFSO.FileExists(sFile) Then
  Set oFile = oFSO.OpenTextFile(sFile, 1)
  Do While Not oFile.AtEndOfStream
     sText = oFile.ReadLine
     If (sText) = "HA" Then 
       oOut.WriteLine (oSheet.Cells(Row, 1).Value)
     End If
     
     if Trim(sText) = "HB" Then
       oOut.WriteLine (oSheet.Cells(Row, 2).Value)
     End If
      
     Exit Do
  Loop 

  oFile.Close 
   
End if

Open in new window

0
drezner7
Asked:
drezner7
  • 9
  • 6
1 Solution
 
drezner7Author Commented:
Any help please ?
0
 
QlemoC++ DeveloperCommented:
The text file doesn't look that fixed length to me, but that might be just a copy&paste issue.

Readline will read the entire line. Comparing that line to be equal with some fragment will not succeed. One way to accomplish this is to compare only the first two chars:
     sText = oFile.ReadLine
     If left(sText,2) = "HA" Then 

Open in new window

Next part, you do not want to use "oOut.WriteLine (oSheet.Cells(Row, 1).Value)" to write into an Excel sheet. You cannot treat a XLSX file just as a text file, because it needs to be a XML file with proper style sheets aso. Usually you open the Excel application with your XLS(X) file opened, and then write directly into the application. That would look like this:
Option Explicit 
Dim excel, sFile, sXLS, oFSO, sText, row

sFile = "C:\Sample\test.txt"
sXLS = "C:\Sample\testouput.xlsx"

Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false

Set oFSO = CreateObject("Scripting.FileSystemObject")

row = 1
If oFSO.FileExists(sFile) Then
  Set wbs = excel.Workbooks.Open(sXLS)
  Set ws  = wbs.WorkSheets(1)
  Set oFile = oFSO.OpenTextFile(sFile, 1)
  Do While Not oFile.AtEndOfStream
    sText = oFile.ReadLine
    If Left(sText, 2) = "HA" then
      ws.Range("A" & row).Value = sText
    End If
    If Left(sText, 2) = "HB" then
      ws.Range("B" & row).Value = sText
    End If
  Loop

  oFile.Close 
  Application.DisplayAlerts = False
  wbs.Save
  wbs.Close 0
  Application.DisplayAlerts = true
End If
excel.quit
set excel = nothing

Open in new window

0
 
drezner7Author Commented:
Thank you Qlemo, but if you can be more descriptive as to what do you mean about "the kind of post does not help yourself'.  I am trying to determine what is wrong with my question? Is it to direct? or does it not imply a question?  Thank you very much
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
drezner7Author Commented:
Thank you for the code, but is there a way to make it grab the fixed length text and place each one in a seperate column in excel. Right now th code grabs the whole line and places it in the specified row.

Thank you
0
 
drezner7Author Commented:
Ahh I understand now, thank you for the explanation about the the 'ping-like, comment'.
0
 
QlemoC++ DeveloperCommented:
Please provide an ample example for both the text file (the above shown isn't fixed length at all), and where the fields should be stuffed into in Excel then for the different cases (HA, HB, ...).
0
 
drezner7Author Commented:
Thank you Qlemo, here is the sample set. The text file is fixed length and I have also attached the excel spreadsheet of the output. Thank you very much input.txt TestOutput.xlsx
0
 
QlemoC++ DeveloperCommented:
TXT and XLSX do not correspond. Further I need the exact field boundaries in the TXT file (either starting position or length of each field).
0
 
drezner7Author Commented:
ok, I have fixed the problem, I have a Vbscript that generates the Input.txt file and it is Fixed Length. Here is the code for every column and it specifies the length of each column. I have also attached the new input and output files..

FIxed Length:

    colA = Left((oSheet.Cells(iRow, 1).Value)& Space(4),4)      
    colB = Left((oSheet.Cells(iRow, 2).Value)& Space(10),10)    
    colC = Left((oSheet.Cells(iRow, 3).Value)& Space(18),18)    
    colD = Left((oSheet.Cells(iRow, 4).Value)& Space(2),2)      
    colE = Left((oSheet.Cells(iRow, 5).Value)& Space(1),1)      
    colF = Left((oSheet.Cells(iRow, 6).Value)& Space(18),18)    
    colG = Left((oSheet.Cells(iRow, 7).Value)& Space(2),2)      
    colH = Left((oSheet.Cells(iRow, 8).Value)& Space(1),1)      
    colI = Left((oSheet.Cells(iRow, 9).Value)& Space(10),10)    
    colJ = Left((oSheet.Cells(iRow, 10).Value)& Space(10),10)

Input.txt TestOutput.xlsx

Thank you
0
 
QlemoC++ DeveloperCommented:
Option Explicit 
Dim excel, sFile, sXLS, oFSO, sText, row

sFile = "C:\Sample\test.txt"
sXLS = "C:\Sample\testoutput.xlsx"

Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false

Set oFSO = CreateObject("Scripting.FileSystemObject")

row = 2
If oFSO.FileExists(sFile) Then
  Set wbs = excel.Workbooks.Open(sXLS)
  Set ws  = wbs.WorkSheets(1)
  Set oFile = oFSO.OpenTextFile(sFile, 1)
  Do While Not oFile.AtEndOfStream
    sText = oFile.ReadLine
    ws.Range("A" & row).Value = getField(sText,  4)
    ws.Range("B" & row).Value = getField(sText, 10)
    ws.Range("C" & row).Value = getField(sText, 18)
    ws.Range("D" & row).Value = getField(sText,  2)
    ws.Range("E" & row).Value = getField(sText,  1)
    ws.Range("F" & row).Value = getField(sText, 18)
    ws.Range("G" & row).Value = getField(sText,  2)
    ws.Range("H" & row).Value = getField(sText,  1)
    ws.Range("I" & row).Value = getField(sText, 10)
    ws.Range("J" & row).Value = getField(sText, 10)
    row = row+1
  Loop

  oFile.Close 
  Application.DisplayAlerts = False
  wbs.Save
  wbs.Close 0
  Application.DisplayAlerts = true
End If
excel.quit
set excel = nothing


Function getField(ByRef line As String, ByVal length As Integer) as String
  getField = Left(line, length)
  line = Mid$(line, length + 1)
End Function

Open in new window

0
 
drezner7Author Commented:
Thank you, I am getting an error on line 42:   Expected ')'

Here is the line that is having the error

Function getField(ByRef line As String, ByVal length As Integer)
0
 
QlemoC++ DeveloperCommented:
Rechecking your example files, I see there is a column missing after your "colB" - there is another one with length 10 here, so it is  4, 10, 10, 18, 2, 1, 18, 2, 1, 10, 10.

The code I provided has been writting in VBA, and obviously there are some changes applied which do not work in VBS. This code should do now:
Option Explicit 
Dim sFile, sXLS, excel, oFSO, oFile, wbs, ws, sText, row

sFile = "C:\Sample\input.txt"
sXLS = "C:\Sample\testoutput.xlsx"

Set excel = WScript.CreateObject ("Excel.Application")
excel.Visible = false

Set oFSO = CreateObject("Scripting.FileSystemObject")

row = 2
If oFSO.FileExists(sFile) Then
  Set wbs = excel.Workbooks.Open(sXLS)
  Set ws  = wbs.WorkSheets(1)
  Set oFile = oFSO.OpenTextFile(sFile, 1)
  Do While Not oFile.AtEndOfStream
    sText = oFile.ReadLine
    ws.Range("A" & row).Value = getField(sText,  4)
    ws.Range("B" & row).Value = getField(sText, 10)
    ws.Range("C" & row).Value = getField(sText, 10)
    ws.Range("D" & row).Value = getField(sText, 18)
    ws.Range("E" & row).Value = getField(sText,  2)
    ws.Range("F" & row).Value = getField(sText,  1)
    ws.Range("G" & row).Value = getField(sText, 18)
    ws.Range("H" & row).Value = getField(sText,  2)
    ws.Range("I" & row).Value = getField(sText,  1)
    ws.Range("J" & row).Value = getField(sText, 10)
    ws.Range("K" & row).Value = getField(sText, 10)
    row = row+1
  Loop

  oFile.Close 
  excel.DisplayAlerts = False
  wbs.Save
  wbs.Close 0
  excel.DisplayAlerts = true
End If
excel.quit
set excel = nothing

Function getField(ByRef line, ByVal length) 
  getField = Left(line, length)
  line = Mid(line, length + 1)
End Function

Open in new window

0
 
drezner7Author Commented:
This is great !  I have tried to add an If statement like this but it looks like the width is getting all messed up when it parses. Just by looking it at it would you be able to tell me what I did wrong? I really appreciate your help, this will save me a ton of work from parsing out 50,000 lines manually.

if left(sText, 2) = "XE" then
    ws.Range("A" & row).Value = getField(sText,  4)
    ws.Range("B" & row).Value = getField(sText, 10)
    ws.Range("C" & row).Value = getField(sText, 10)
    ws.Range("D" & row).Value = getField(sText, 18)
    ws.Range("E" & row).Value = getField(sText,  2)
    ws.Range("F" & row).Value = getField(sText,  1)
    ws.Range("G" & row).Value = getField(sText, 18)
    ws.Range("H" & row).Value = getField(sText,  2)
    ws.Range("I" & row).Value = getField(sText,  1)
    ws.Range("J" & row).Value = getField(sText, 10)
    ws.Range("K" & row).Value = getField(sText, 10)
   
   
    End if
   
if left(sText, 2) = "XB" then

    ws.Range("A" & row).Value = getField(sText,  4)
    ws.Range("B" & row).Value = getField(sText, 10)
    ws.Range("C" & row).Value = getField(sText, 18)
    ws.Range("D" & row).Value = getField(sText,  2)
    ws.Range("E" & row).Value = getField(sText,  1)
    ws.Range("F" & row).Value = getField(sText,  1)
    ws.Range("G" & row).Value = getField(sText, 19)
    ws.Range("H" & row).Value = getField(sText,  11)
   
    End if


Thank you
0
 
QlemoC++ DeveloperCommented:
Comparing both parts, the first one has a overall length of 86 chars, while the second has only 66 chars. I assume there are two columns with 10 chars each missing.
Do you have to differ between more than those two prefixes? If so, the processing could made even easier by encapsulating the ws.Range stuff into a function, too, and only provide an array with the field lengths.
0
 
drezner7Author Commented:
Very awesome, Thank you so much. I really appreciate the help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now