Skip to main content

Reading/importing tab delimited text file using VBA–Excel


One of my friend, who is a mechanical engineer, had to import a tab delimited text file in Excel. He gets this (these) file(s) as input for God Knows What Smile. He asked for my help and then I realized it is worth blogging about as may be helpful for many of my civil/mech/chem…. friends.
So first thing first:-
How to create a tab delimited text file
1. Open notepad –> Write text in first column –> press tab –>write in second column and so on. –> save the file with .tab extension.
2. Open excel-> enter values in different column-> Open the File menu and select the Save as... command.-> In the Save as type drop-down box, select the Text (tab delimited) (*.txt) option.->Select the Save button. If you see warning messages pop up, select the OK or Yes button.
Here I am going to write a macro to read all the values
Sub ReadTextFile()
 
  Dim iRow      As Long
  Dim Fname     As Variant
  Dim Record    As String
  Dim P         As Variant
  Dim iCol      As Integer
 
  Fname = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
           "Select Text Data File")
  If Fname = False Then Exit Sub
 
  Open Fname For Input As #1
  iRow = 1
  Line Input #1, Record
  Do Until EOF(1)
     P = Split(Record, vbTab)
    
     For iCol = 1 To 3
        Cells(iRow, iCol) = P(iCol - 1)
     Next iCol
    
     iRow = iRow + 1
     Line Input #1, Record
  Loop
  Close 1
End Sub
Here I am writing a macro to read value present in a specific cell.
We will get the information about cell from user. Supposing he has provide row and cell value in 15th column (under N).
Sub ReadCell()
    Dim iRow As Long
    Dim iCol As Integer
   
    iRow = CInt(Cells(2, 15))
    iCol = CInt(Cells(1, 15))
   
    Dim Fname     As Variant
    Dim Record    As String
    Dim P         As Variant
    Dim iTemp      As Integer
 
  Fname = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
           "Select Text Data File")
  If Fname = False Then Exit Sub
 
  Open Fname For Input As #1
  iTemp = 1
  Line Input #1, Record
  Do Until EOF(1)
     P = Split(Record, vbTab)
    
     If iTemp = iRow Then
     MsgBox (P(iCol - 1))
     Exit Sub
     End If
         
     iTemp = iTemp + 1
     Line Input #1, Record
  Loop
   
  Close 1

End Sub

Disclaimar
I have not applied validations and it is by no means the professional code which we would like. But this something you can really build upon. Also contact me if you need something specific.
Labels:

Comments

Post a Comment

Popular posts from this blog

Laravel XAMPP MySQL artisan migrate install error mysql.sock

In my previous post I wrote about setting up Laravel 4 on Mac with XAMPP . When I tried to use migrations (using artisan) I faced some issue. I will also post about Laravel migrations soon.    php artisan migrate:install Error :                                                     [PDOException]                                       SQLSTATE[HY000] [2002] No such file or directory                                              Solution : We need to tell artisan which mysql we want it to use. For this to work we need to porivde it with mysql.sock which we want it to use. So change your database settings like this: 'mysql' => array( 'driver'    => 'mysql', 'host'      => 'localhost',     'unix_socket' => '/Applications/xampp/xamppfiles/var/mysql/mysql.sock', 'database'  => 'wedding', 'username'  => 'root', 'password'  => '', '
6 comments
Read more

Add (Drop) a new article (table) at publisher in merge replication

Let us add the article using the GUI first. First of all create the table on publisher database. Now right click on the publisher and select properties from the menu. Click on articles. Uncheck the - "Show only checked articles in the list" checkbox, in order to see the newly added table. Select the table as shown in the figure below. Press ok The article has now been added to the publisher We now need to recreate the snapshot Right click the publication and select – “View snapshot agent status”. Click start to regenerate snapshot. Now right click on the subscription (I have both on same server you may have on different servers) and select “View synchronization status” Click on start on the agent. The schema changes will propagate to the client if you have "Replicate schema changes" property set to true in the publisher.
Post a Comment
Read more

Check SQL Server Job status (State) using sp_help_job and xp_sqlagent_enum_jobs

This article is about checking the status of a SQL job. In our work place we have lot of SQL jobs. These jobs will run whole day and are business critical. They will load the data and generate extracts which will be used by business people. Thus, it becomes quite essential to support the system efficiently so that the job finishes in time and as desired. Also, while designing a new system sometimes we need to check the dependency of one job over another. In such scenario we need to check whether a particular job has finished or not. All this can be achieved in SQL Server by using the procedures:- sp_help_job xp_sqlagent_enum_jobs Note: xp_sqlagent_enum_jobs is an undocumented proc inside of sp_help_job and is used extensively to get SQL agent job information. sp_help_job: This procedure gives some insight into the status, and information, about a job. This stored procedure provides information such as last start time, job status etc. Syntax sp_help_job { [ @job_id= ] jo
2 comments
Read more