Back to discussion

convert text file to excel

Last post was 19 Apr 2011 at 12:00
.net , vb6 Malaysia
  • 13 years ago
    Hi VB Experts,
     
    I need help regarding visual basic 6. Anyone who is kind enough to help me.
     
    The form should have the Drive list, Dir list, file list and cmdConvert button. Once pulling down the drive list and clicking to the specific drive, folders will be shown in the  dir list. When clicking the folders in the dir list, files in that certain folder should apprear in the file list, but this program should limit only to show the txt files in the file list. Please consider that all txt files in that particular drive have the same format. Now when the intended txt files are now shown in the file list, you then have to click in the cmdConvert button. Each txt files in that certain folder will be converted into excel. The program will generate only one excel file for all the txt files converted.
    This program is to make easy analysis of data using excel. Below is a sample of data inside the txt file, but please consider, its not only one txt file. Many txt files but the same format. __________________________________________________________________
    Bender:                                        Julie
    Bending machine number:             a
    cofffee size:                                  large
    Date Blend:                                  Aug 25, 2007
    space
    space
    space
    Number of Cups released:               5
    Number of Drinkers:                       1
    Coin used:                                    Standard
    Customer rating:                           ok
    Customer Taste:                           good











    __________________________________________________________________
                                   
    The resulting Excel file should be in the below. Resulting format is in the row in a single excel sheet. I used comma instead of cell.

    Excel file: Considering 5 txt file that I need to convert.
    __________________________________________________________________
    Julie , a , large , 25-Aug , 5 , 1 , standard , ok, good
    Chris , a , small , 25-Aug , 5 , 2 , standard , ok, good
    Julie , a , small , 25-Aug , 5 , 1 , standard , ok, ng
    Julie , a , medium , 25-Aug , 5 , 3 , standard , not ok, good
    Chris , a , large , 25-Aug , 5 , 1 , standard , ok, good



    _Sheet 1__________________________________________________________
    Report abuse
  • 13 years ago
    Hi VB Experts,
     
    I need help regarding visual basic 6. Anyone who is kind enough to help me.
     
    The form should have the Drive list, Dir list, file list and cmdConvert button. Once pulling down the drive list and clicking to the specific drive, folders will be shown in the  dir list. When clicking the folders in the dir list, files in that certain folder should apprear in the file list, but this program should limit only to show the txt files in the file list. Please consider that all txt files in that particular drive have the same format. Now when the intended txt files are now shown in the file list, you then have to click in the cmdConvert button. Each txt files in that certain folder will be converted into excel. The program will generate only one excel file for all the txt files converted.
    This program is to make easy analysis of data using excel. Below is a sample of data inside the txt file, but please consider, its not only one txt file. Many txt files but the same format. __________________________________________________________________
    Bender:                                        Julie
    Bending machine number:             a
    cofffee size:                                  large
    Date Blend:                                  Aug 25, 2007
    space
    space
    space
    Number of Cups released:               5
    Number of Drinkers:                       1
    Coin used:                                    Standard
    Customer rating:                           ok
    Customer Taste:                           good











    __________________________________________________________________
                                   
    The resulting Excel file should be in the below. Resulting format is in the row in a single excel sheet. I used comma instead of cell.

    Excel file: Considering 5 txt file that I need to convert.
    __________________________________________________________________
    Julie , a , large , 25-Aug , 5 , 1 , standard , ok, good
    Chris , a , small , 25-Aug , 5 , 2 , standard , ok, good
    Julie , a , small , 25-Aug , 5 , 1 , standard , ok, ng
    Julie , a , medium , 25-Aug , 5 , 3 , standard , not ok, good
    Chris , a , large , 25-Aug , 5 , 1 , standard , ok, good



    _Sheet 1__________________________________________________________
     
     
    Thanks in advance.
     
    Chris  
    Report abuse
  • 13 years ago

    Sorry but... if you want help, can you show us your code?

    Without code just can tell you that you need to:

    1- open files for input and output

    2- initialize internal variables

    3- read file

    4- check for 2 spaces in reverse

    5 add to internal variable until you have a full record, if so write to file

    6- close files

    Report abuse
  • 13 years ago

    You can create an excel file in this way:

    Add a reference to an Excel Object Library to your project.

    Dim xl as Object
Set xl = CreateObject("Excel.Application")
xl.Visible = True
xl.Workbooks.Add
xl.Columns(2).ColumnWidth = 30
xl.Cells(1, 2).Value = "My Value"
xl.Cells(1, 2).Font.Bold = True
xl.Range("C7", "D8").Value = 42
xl.Worksheets(1).SaveAs ("c:\test1.xls")
xl.Quit
Set xl = Nothing


    More generally, you'll need a bunch of functions:

    1. Reading a text file (you can use Open, Input# and Close or use a FileSystemObject)

    2. Extracting the useful content of the text file (you can use InStr to locate the ":" char, you can use Right$, Left$ or Mid$ to extract useful data, you can use Trim to delete spaces, etc.)

    3. Creating an Excel file (see code above)

    4. Adding data to the Excel file (see code above)

    5. Save the Excel file (see code above)

    Report abuse
  • 13 years ago

    Thanks Zorro,

    This might be a good start.  I just started reading a book and study by my own in visual basic one month ago. I havent even finish reading the book. Anyway, thanks for the guide. I'll make a try on these one if I can.

    Chris, [email protected]

    [email protected]

    Report abuse
  • 13 years ago

    Thanks Darius for your time responding my query. I dont have the code yet cause I dont know how to do it. I  just started studying visual basic a month ago.I only have the form and the little code to show the Directories in the dir list when clicking the drive list and will show the files in the file list when clicking the dir folder in the dir list.

    chris, [email protected]

     

     

    Report abuse
  • 13 years ago

    That's the same way I learnt VB a lot of years ago. Smiley Face [:)]

    Dont be afraid when you have to rewrite your code a lot. In the beginning you'll make a lot of mistakes but on the long run this will pay off. Your code will become more and more reusable and get better over time.

    When you have just started learning VB you may also consider to ignore VB 6 and have a look at VB.Net Express Edition (its free) and the .NET Framework (its free, too) instead. VB.Net is much more object orientated than VB 6. In the beginning, the OOP concept may be a little confusing but it is a very common concept and is found in many programming languages. The .NET Framework offers a lot of built-in methods that you have to code yourself in VB 6.

    The major drawback of VB.Net (compared to VB 6) is its hardware usage. You need a memory of 512 MB and a CPU faster than 1 GHz to get a decent speed.

    Report abuse
  • 13 years ago

    Well, I will trust you, that is no class homework.

    Add a module and put this code on it (this will open excel with the converted file)

    ------------------------------------------------------------------

    Declare Function apiFindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As Any, ByVal lpCaption As Any) As Long
    Declare Function ShellExecute Lib "shell32.dll" Alias _
       "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation _
       As String, ByVal lpFile As String, ByVal lpParameters _
       As String, ByVal lpDirectory As String, ByVal nShowCmd _
       As Long) As Long




    Sub ExcelFileOpen(ByVal Filename As String)
       Dim hwnd
       Dim StartDoc
       hwnd = apiFindWindow("OPUSAPP", "0")
       StartDoc = ShellExecute(hwnd, "open", Filename, "", "C:\", SW_SHOWNORMAL)
    End Sub




    ----------------------------------------------------------

    Add this to the form where the filelist object is (change name acordingly to yours, I take it as the automatic name VB give)

    Private Sub File1_Click()
      Open File1.Path & File1.List(File1.ListIndex) For Input As #1
      Open File1.Path & Left(File1.List(File1.ListIndex), Len(File1.List(File1.ListIndex)) - 3) & "csv" For Output As #2
      ST = "": i = 1
      While Not (EOF(1))
         Line Input #1, texti
         k = InStrRev(texti, "  ", , vbTextCompare)
         If k > 0 Then
           TXT = TXT & Right(texti, Len(texti) - k) & ","
         End If
         If i = 12 Then
           Print #2, TXT
           TXT = ""
           i = 1
         Else
           i = i + 1
         End If
      Wend
      Close #2
      Close #1
      ExcelFileOpen File1.Path & Left(File1.List(File1.ListIndex), Len(File1.List(File1.ListIndex)) - 3) & "csv"
    End Sub






















    Report abuse
  • 13 years ago

    Thanks Darius,

     

    no its not. Its just a project of mine. I see the program from my colleage who already resigned. I dont know how he do it but I get interested to know how he did it since the program is applicable to my job especially in data analysis/yield analysis  in manufacturing . I can send you the code and the sample and files i need to convert. Im still using his program but not applicable anymore to my presnt project, thats why i need to create a new one.

    chris, [email protected]

     

    Report abuse
  • 13 years ago

    Thanks Zorro, yeah  ill consider that advise . I'll make a try when i finish learning vb6.

    chris, [email protected]

     

    Report abuse
  • 9 years ago

    I am not much of an Excel hand; spending most of my time either in Microsoft Word or PowerPoint. My most regular use of Excel is as a tool...

    Daniel Learn English Online
    Report abuse

Post a reply

Enter your message below

Sign in or Join us (it's free).

.NET forum discussion

.NET tutorials

Quick links

Recent activity

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Web Development

Developer Jobs

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

Cancel
“I invented the term Object-Oriented, and I can tell you I did not have C++ in mind.” - Alan Kay