locked
Extract Data from Text Files

    Question

  • Hello Everyone,
    I've seen may VB scripts that look at text files and extract information from it and then import everything in excel. I don't have a specific Text file I am working on but is there a generic sample of script you can share with me that will show me how to basically.

    1. Read a file saved on my C:\ called Sample.txt
    2. Extract everything from say character 2 to character 50
    3. Import everything in an Excel Spreadsheet called Sample.xls and save it on my C:\ drive

    I've read many books on VB but haven’t found anything that demonstrates how to extract data from text files effectively. Also if you have any good sites or books that will demonstrate with various examples how to do this I would appreciate it.

    Thanks

    Joe Serrone

    Tuesday, August 19, 2008 8:45 PM

Answers

  • Imports System.Data

    Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sample.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

     

    Dim aFile As String = File.ReadAllText("C:\Sample.txt").Substring(2, 50)

     

    As for the updating see here

     

    Tuesday, August 19, 2008 11:09 PM

All replies

  • Imports System.Data

    Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sample.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

     

    Dim aFile As String = File.ReadAllText("C:\Sample.txt").Substring(2, 50)

     

    As for the updating see here

     

    Tuesday, August 19, 2008 11:09 PM
  • What is the format of the text file and how do you want the text exported to the Excel Worksheet? Do you just want the text exported to a single cell?
    Tuesday, September 09, 2008 12:07 PM