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

drezner7Asked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
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:
Any help please ?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
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.