SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 7 of 7
  1. #1
    Junior Member
    Join Date
    Jul 2010
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    copy the contents from text file to excel sheet

    Hello all,

    I have a query in test complete,

    can anyone send me code to copy the contents from text file to excel sheet?

    thanks

  2. #2
    Member
    Join Date
    Jan 2010
    Posts
    94
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: copy the contents from text file to excel sheet

    Hi,

    It depends on how are you going to arrange text file contents in the excel cells.
    For example, the below code (JScript) will copy each line of text file to a separate row in first column.
    TextFileName - full path to the text file; ExcelFileName - full path to excel file.

    function TextToExcel(TextFileName, ExcelFileName)
    {
    var Excel = Sys.OleObject("Excel.Application");
    Excel.DisplayAlerts = false;
    var book = Excel.Workbooks.Open(ExcelFileName);
    var sheet = book.Sheets(1);

    var FileObj = aqFile.OpenTextFile(TextFileName, aqFile.faRead, aqFile.ctANSI);
    var row = 1;
    while (!FileObj.IsEndOfFile())
    {
    sheet.Cells(row, 1) = FileObj.ReadLine();
    row++
    }
    book.SaveAs(ExcelFileName);
    book.close();
    Excel.Quit();
    FileObj.Close();
    }

  3. #3
    Junior Member
    Join Date
    Jul 2010
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: copy the contents from text file to excel sheet

    Thank you,

    Can i get this code in VBScript?

    Thanks in advance.
    sowmya

  4. #4
    Junior Member
    Join Date
    Jul 2010
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: copy the contents from text file to excel sheet

    for example the text file should liek attached, and in my excel each content should display in different columns with seperate row:

    like in first column it should be date and time, 2nd column name of the solution, 3rd column: total time.

    Please let me know if you need any more clarification.

    Thank you
    Attached Files Attached Files

  5. #5
    Senior Member
    Join Date
    Jul 2008
    Posts
    1,049
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: copy the contents from text file to excel sheet

    Hi,

    Here is a sample code demonstrating how you can work with Excel files and text files in VBScript. It reads data from the existing 'C:\Data.txt' file by lines and writes them in the first column of the existing 'C:\Data.xls' file.

    <font class="small">Code:</font><hr /><pre>
    Sub Main
    Set Obj = Sys.OleObject("Excel.Application")
    Obj.Visible = True
    Obj.Workbooks.Open("C:\Data.xls")
    AFileName = "C:\Data.txt"
    Set F = aqFile.OpenTextFile(AFileName, aqFile.faRead, aqFile.ctANSI)
    F.Cursor = 0
    line = 1
    While Not F.IsEndOfFile()
    Obj.Cells(line,1) = F.ReadLine()
    line = line + 1
    WEnd
    F.Close()
    End Sub
    </pre><hr />

    As for parsing the file in your specific task, you can use regular expressions to split the lines and retrieve the needed data. Please see the "Using Regular Expressions in Scripts" help topic ( http://www.automatedqa.com/support/v....aspx?aid=4321 ) for more information on regular expressions.
    Regards,
    Allen - SmartBear Support

  6. #6
    Junior Member
    Join Date
    Jul 2010
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: copy the contents from text file to excel sheet

    Hi Allen,

    I tried the above code it works fine , but im facing a problem in splitting the strings, for example: i want to copy the contents of text file into excel sheet in each column by row by row.

    i want to seperate the text contents using DateTime, solutionname, and TimeinSeconds. Attached is the text file which im using.

    i have used regular expressions but they didnt work for me.

    can you please help me out to fix this issue?

    i have inserted following lines of code in your code, but splitting im unable to fix.

    Obj.ActiveSheet.Cells(line,"A").Value = "DateTime"
    Obj.ActiveSheet.Cells(line,"B").Value = "Action"
    Obj.ActiveSheet.Cells(line,"C").Value = "TotalTimeinSec"


    thanks,
    Attached Files Attached Files

  7. #7
    Senior Member
    Join Date
    Oct 2003
    Posts
    928
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: copy the contents from text file to excel sheet

    Hi,

    Here is the example of parsing your file with regular expressions:

    <font class="small">Code:</font><hr /><pre>
    Function RegExpTest(patrn, strng)
    Dim regEx, Matches
    Set regEx = New RegExp
    regEx.Pattern = patrn
    regEx.IgnoreCase = True
    regEx.Global = True
    Set Matches = regEx.Execute(strng)
    RegExpTest = Matches(0).SubMatches(0)
    End Function

    'Parses the example string from the file and posts the results to the log
    Sub Main
    ExampleString = "16/06/2010 15:37:34: Rebuild Solution Explorer tree: 13ms (0.013s)"
    DateTime = "(\d{2}\/\d{2}\/\d{4}\s\d{2}:\d{2}:\d{2})"
    SolutionName = "([A-Za-z](\s*\w*)*):"
    TimeInSeconds = "\((\d+(\.\d+)?s)\)"
    Log.Message(RegExpTest(DateTime, ExampleString))
    Log.Message(RegExpTest(SolutionName, ExampleString))
    Log.Message(RegExpTest(TimeInSeconds, ExampleString))
    End Sub
    </pre><hr />

    Please let me know if you need more assistance.
    Regards,
    Alex - SmartBear Support

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 9.68%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 11:57 PM.

Copyright BetaSoft Inc.