FIND THE LAST COLUMN IN A WORKSHEET USING VBA

Use the following code to find the last data column in a worksheet.

Read More

IMPORT CSV OR ANY DELIMITED TEXT FILE WITH UNICODE ENCODINGS IN EXCEL USING VBA

This post demonstrates how to import CSV or any delimited text file with Unicode encoded in Excel using VBA code.

By default, text files are encoded in ANSI representation on WINDOWS platform.

When you try to import the ANSI encoded text files into excel using VBA, you may not see problems with the data appearing in the excel worksheet. But there is a problem when importing a text file other than ANSI encoding.

  The Possible Encodings of a Text File – 

 The following are the available character encoding representations of a text file on WINDOWS platform.

• ANSI (default encoding representation)
• Unicode
• Unicode big endian
• UTF-8

How to know the encoding of a text file?

1. Open a text file with notepad editor.

 2. Select File menu and click on Save As

 3. Look at the Encoding at the bottom, which tells you the current encoding of a text file.

 4. Click on Encoding drop-down list, to see the supported encoding representations of a text file on your machine.

What are these encodings?

To understand more about these encodings, please check this link – Byte Order Mark (BOM)

What happens when Unicode encoded text is imported into Excel –

The below sample text file is used for testing. The copy of this file is saved with all possible encodings.

  4 Rows

 3 Columns

Delimited by ‘,‘ (Comma) 





The following is the output screen when each encoded text file is imported into Excel. When the import button is clicked, the data will be imported from the active cell in a worksheet.
 
If you look at the output screen, the every first cell of each encoding contains a garbage value.

Reason –

Byte Order Mark (or BOM) is a signal that tells the computer how the bytes are ordered in a Unicode document. Because Unicode can be used in the formats of 8, 16 and 32 bits – it is important for the computer to understand which encoding has been used in the Unicode document. BOM tells exactly the same to the computer. That is why we see the garbage values.

Representation of BOM by encoding –

This table illustrates how BOMs are represented as byte sequences and how they might appear in a text editor that is interpreting each byte as a legacy encoding (CP1252).

Encoding	Representation (hexadecimal)	Representation (decimal)	Bytes as CP1252characters
UTF-8[t 1]	EF BB BF	239 187 191	
UTF-16(BE)	FE FF	254 255	þÿ
UTF-16(LE)	FF FE	255 254	ÿþ

As per these representations, we can see the fixed characters for each Unicode encoding when the text file is imported.

How to Resolve –

This can be resolved by replacing the Unicode representation characters with an empty string.

Import Button Code –

1 2 3

Sub DoTheImport() ImportTextFile FName:="D:\data.txt", Sep:="," End Sub

ImportFile is the sub-routine which imports the data. Change the sep:= as per your requirement. In this example comma is the separator.

ImportFile Code –

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

Public Sub ImportTextFile(FName As String, Sep As String) Dim RowNdx As Long Dim ColNdx As Integer Dim TempVal As Variant Dim WholeLine As String Dim Pos As Integer Dim NextPos As Integer Dim SaveColNdx As Integer 'disable screen updates Application.ScreenUpdating = False 'error handling On Error GoTo EndMacro 'Importing data starts from the selected cell in a worksheet SaveColNdx = ActiveCell.Column RowNdx = ActiveCell.Row 'open the file in read mode Open FName For Input Access Read As #1 'Read the file until End of Line While Not EOF(1) 'read line by line Line Input #1, WholeLine WholeLine = Replace(WholeLine, "", "", vbTextCompare) 'UTF-8 WholeLine = Replace(WholeLine, "ÿþ", "", vbTextCompare) 'UTF-16 Unicode little endian WholeLine = Replace(WholeLine, "þÿ", "", vbTextCompare) 'UTF-16 Unicode big endian 'checking if the line is empty If Right(WholeLine, 1) <> Sep Then WholeLine = WholeLine & Sep End If ColNdx = SaveColNdx Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) 'finding each column data While NextPos >= 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cells(RowNdx, ColNdx).Value = TempVal Pos = NextPos + 1 ColNdx = ColNdx + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend RowNdx = RowNdx + 1 Wend Close #1 Exit Sub EndMacro: Application.ScreenUpdating = True MsgBox Err.Description, vbOKOnly, "Error" Close #1 End Sub

Summary –

This way you can import any delimited text file with Unicode encodings. Make sure that the text file is not exceeding the maximum number of rows of excel worksheet.

Please share your thoughts on this post in the comments section.

Read More

Difference between print(null.toString()) and print(null)

This post demonstrates difference between print(null.tostring()) and print(null) in Java programming.

The difference is print(null.tostring()) throws null pointer exception but print(null) doesn't. Why?

Consider the below sample code to understand the concept clearly.

public class NPETest {
    public static void main(String[] args) {
        Object obj1 = null;
        Object obj2 = null;
        System.out.print(obj1.toString()); //throws Null Pointer Exception
        System.out.print(obj2); // prints null
    }
}

 

The statement System.out.print(obj1.toString()) causes following runtime exception.

Exception in thread "main" java.lang.NullPointerException
at com.org.s20150310072124635.NPETest.main(NPETest.java:5)

 

The statement System.out.println(obj2) runs fine and prints a text "null".

Why an exception is caused?

Look at the java bytecode of the program.

> javap -classpath target\test-classes -c NPETest

Compiled from "NPETest.java"
public class NPETest extends java.lang.Object{
public NPETest();
  Code:
   0:   aload_0
   1:   invokespecial   #8; //Method java/lang/Object."<init>":()V
   4:   return

public static void main(java.lang.String[]);
  Code:
   0:   aconst_null
   1:   astore_1
   2:   aconst_null
   3:   astore_2
   4:   getstatic       #17; //Field java/lang/System.out:Ljava/io/PrintStream;
   7:   aload_1
   8:   invokevirtual   #23; //Method java/lang/Object.toString:()Ljava/lang/String;
   11:  invokevirtual   #27; //Method java/io/PrintStream.print:(Ljava/lang/String;)V
   14:  getstatic       #17; //Field java/lang/System.out:Ljava/io/PrintStream;
   17:  aload_2
   18:  invokevirtual   #33; //Method java/io/PrintStream.print:(Ljava/lang/Object;)V
   21:  return

}

 In code column, line #8 shows the bytecode representation of calling obj1.toString(). Here obj1 is null and so any attempt on a method invocation on null results in a NullPointerException.

Why print(null) works fine?

Consider the bytecode, in code column line #18 shows the bytecode representation of calling print(obj2). Here object is passed as a parameter to the PrintStream.print() method. According to the Java documentation, the source code will tell you that why this does not result in NullPointerException.

public void print(Object obj) {
    write(String.valueOf(obj));
}

public static String valueOf(Object obj) {
    return (obj == null) ? "null" : obj.toString();
}

 
 The documentation tells that the null reference is handled if null reference is passed. If argument is null, then a string equal to "null"; otherwise, the value of obj.toString() is returned. So this statement does not result in NullPointerException.

Summary -

print(null.tostring()) causes Null Pointer Exception. print(null) prints text "null".

---

Please share your thoughts on this post in the comments section.

Read More

System.out.println(null) ?

This post demonstrates why System.out.println(null) causes a compilation error and how to resolve.

When you run the statement System.out.println(null), you will see the following compilation error.

reference to println is ambiguous, 
both method println(char[]) in java.io.PrintStream and 
method println(java.lang.String) in java.io.PrintStream match

 Because there are (at least) two print methods that can accept null (one more is println(object)).

Since null can fit in both, the compiler doesn't know which method to use, leading to a compiler error.

The compiler tries to find the most specific version of an overloaded method. Both char[] and String are sub types of Object, so that's why the third method is not considered.

Why compile-time error?

The duty of compiler is to type-check the parameters to the method call. So because of ambiguity, the compiler error.

Possible Method over-loaders that accepts null -

1. public void println(String x) {}
2. public void println(char[] x) {}
3. public void println(Object x) {}

 

How to resolve?

If you replace the statement System.out.println(null); with any one of the following statements, the code works.

System.out.println((String)null);
System.out.println((Object)null);

 

Why not System.out.println((char [])null)?

you will receive the following runtime exception.

Exception in thread "main" java.lang.NullPointerException
at java.io.Writer.write(Writer.java:110)
at java.io.PrintStream.write(PrintStream.java:453)
at java.io.PrintStream.print(PrintStream.java:603)
at java.io.PrintStream.println(PrintStream.java:742)
at com.org.s20150309105710441.MyClass.main(MyClass.java:5)

 

Why Null Pointer Exception?

According to Java documentation,

char will not handle null pointer which leads to null pointer exception. Thus println(char[] s) prints the array of characters when s is not a null pointer.

Why println(String s) and println(Object obj) works?

According to Java documentation,

both String and Object handles null pointer.

println(String s) - If the argument is null then the string "null" is printed. Otherwise, the string's characters are converted into bytes according to the platform's default character encoding.

println(Object obj) - The string produced by the String.valueOf(Object) method is translated into bytes according to the platform's default character encoding.

valueOf(Object obj) -

public static String valueOf(Object obj) {
    return (obj == null) ? "null" : obj.toString();
}

 If argument is null, then a string equal to "null"; otherwise, the value of obj.toString() is returned.

Summary -

Thus char always leads to a null pointer exception when it encounters a null pointer reference.

Try -

public class PrintNull {
 public static void main(String[] args) {
  Object o = null;
  String s = null;
  char[] a = null;

  //System.out.println(null); // Compilation error
  System.out.println(o); // Prints "null"
  System.out.println(s); // Prints "null"
  System.out.println(a); // Throws NullPointerException
 }

 

---

Please share your thoughts on this post in the comments section.

Read More