Friday, December 21, 2018

Parsing a text file and importing values to MS Excel

We can use Excel VBA script to analyze a text file for a specific purpose. In this example, I have a text file which contains a list of files and their details like name, date created, file size etc. This is an extraction of DIR command in dos prompt. I am trying to find a text string "CNT" in each line and if it is present then I will extract the name of the file and store that in the Excel Cell along with the date the is created. 

I have hardcoded the column here, you can modify it however you want. The first column will keep the name of the file and the second column will keep the file creation date. 

Sub import_text_file_to_excel()
Dim fileHandle As Integer
Dim fileLine As String

Dim r As Integer
Dim c As Integer
r = 1
c = 1

Open "C:\\Users\\krsanjee\\files.txt" For Input As #1

While Not EOF(1)
Line Input #1, myline


On Error Resume Next
line_first_part = Left(myline, 40)
line_second_part = Right(myline, Len(myline) - 40)
If InStr(line_second_part, "CNT") Then
arr = Split(line_first_part, " ")
ActiveSheet.Cells(r, c).Value = arr(0)
ActiveSheet.Cells(r, c + 1).Value = line_second_part
r = r + 1
End If
Wend
Close #1
End Sub

Sunday, December 16, 2018

You should know these excel skills if you are using MS excel

If you are using excel for whatever reason then knowing these skills will definitely increase your productivity. These are not advanced but moderate skills. You can manage with excel without knowing these skills but knowing these skills will improve the proficiency in Excel.

1. Defining named tables.

You can define a range as a table and give a name to it. Later you can use this name in a formula. You can also use these named tables in references.

Under formulas menu bar, select Define names. Then give a name to the tables, for example, Sales. Then select the range and select ok. After you create the named table, you can use the table name 'Sales' in the formula to calculate the sum.

2. Autofill 

Using autofill you can complete the series based on the current values. For example, if you want to fill multiple of a number, you can enter the first 2 number of the series and click on the autofill icon and drag down the column. In the newer version of the office, you will get multiple options. 
The options are

copy cells

This will repeat the already available items, instead of completing the series with the next numbers. 

Fill series

Completes the series with next values. 

Fill format only

It will ignore the values. Just copies the format of the existing cells. It can alternate the colors based on the selection.

Fill without formatting

This will ignore the format but copies the value of the series. 


3. How to get to the new line within a cell

As you know Excel is a spreadsheet tool, the basic building block is a cell. Unlike any word processing tool, if you press 'Enter Key' it will take you to the next cell. What if you want to enter to a new line within the cell. You can do that by holding 'ALT' key and pressing 'ENTER' key. 

4. Data bars

If you want to represent your data visually amazing, you can use data bars. It is very simple and it looks very attractive. The data needs to be of numerical values. This is one kind of conditional formatting. You can create a bar horizontal graph just by applying a conditional format. 

Select the range you want to apply the format. Go to Conditional formatting and select Data Bars. Under the gradient fill, select whatever color you want. 

   

If you want the Data Bars in a separate column, then copy the values the next column. Apply the conditional format on the new column but not on the old column. Edit the condition formula and check the box 'Show Only Bars'

5. Paste special 

Under the paste special you have options like transposing a the selected. That means you converted a row into column and column to row. 
You can paste only the value if the source is a formula
You can paste only the formula, not the values. 
You can also link destination cells the source cell. By this, you can make sure that any change in the source will also get affected in the destination. 

6. Text to column


Suppose you have a bank statement in PDF format. If bring the statement into excel you will have a lot of options to analyze it. One example would be a BRS, Bank Reconciliation Statement. Import the statement to excel and apply formulas on the imported statement.

You can split the single line into multiple columns based on a specific delimiter. In the below example list folders and files has been extracted to a text file and it contains columns like date and time, type of the file and a name of the file and folder. Each of these columns can be split into separate columns in the excel. Once the data is in tabular form, it can be used for various analysis. In this case, you can sort the files based on their creation date or based on the names etc.





After converting a single line into multi-column 


7. Autosizing a column width or row height. 

By clicking on the line between 2 rows or a column, you can auto adjust the width of column or height of a row based on the content of that column or row.


8. Row and column selection shortcuts 

Ctrl + Space  To select the entire column
Shift + space  To select an entire row
Ctrl + Shift + Space:  The First click will select the filled range surrounded be the current cell. Second space press selects the entire worksheet. 
Ctrl + shift + + Insert a new row or a column
Ctrl + - To delete a row or a column
  

How to connect wireless Bluetooth headphones to your old TV or non smart TV

Have you ever thought of making your old TV wireless audio capable? Wanted to connect your wireless headphone or a wireless speaker to your ...