javastringctl

Read the Table Name and Column Names from .ctl file in Java


Hello I am trying to read the read the table name and column names for a sql query for my test validation

I have a .ctl file which has the table details as shown

LOAD DATA APPEND
  INTO TABLE ALM_LAV_CD_BSTG
FIELDS TERMINATED BY '~~' 
TRAILING NULLCOLS                                                               
(                                                                               
   ALM_VERS_ID                      DECIMAL EXTERNAL NULLIF (ALM_VERS_ID=BLANKS)
 , LAV_CD                           CHAR NULLIF (LAV_CD=BLANKS)                 
 , LAV_CD_OFSE                      INTEGER EXTERNAL NULLIF (LAV_CD_OFSE=BLANKS)
 , LAV_CD_AMT                       DECIMAL EXTERNAL NULLIF (LAV_CD_AMT=BLANKS) 
 , EXPRT_DT                         DATE "YYYY-MM-DD"
 , DUA_INSRT_ID                     CHAR NULLIF (DUA_INSRT_ID=BLANKS)           
 , DUA_INSRT_TMSTP                  TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6"                                            
 , DUA_UPDT_ID                      CHAR NULLIF (DUA_UPDT_ID=BLANKS)            
 , DUA_UPDT_TMSTP                   TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6"                                            
 , DUA_VERS_NBR                     DECIMAL EXTERNAL NULLIF (DUA_VERS_NBR=BLANKS)                                                                               
)                                                                               

My requirement is to read table name into string and column names ignoring EXPRT_DT into string

My code is as below

package com.cf.ODSAutomation;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.swing.filechooser.FileNameExtensionFilter;

public class ReadControlFile {

	public static void main(String[] args) throws Exception {

		// Control Files Folder Path

		File path = new File("C:\\ODS\\control_File");
		final FileNameExtensionFilter extensionFilter = new FileNameExtensionFilter("Control Files", "ctl");
		File[] files = path.listFiles();
		System.out.println("Number of Control Files : " + files.length);

		// Looping the files in the folder

		for (final File file : path.listFiles()) {
			if (extensionFilter.accept(file)) {
				System.out.println("File Name : " + file.getName().trim());
				BufferedReader reader = new BufferedReader(new FileReader(file));
				StringBuilder builder = new StringBuilder();
				String currentLine = reader.readLine();
				while (currentLine != null) {
					builder.append(currentLine);
					builder.append("\n");
					currentLine = reader.readLine();
				}

				// Appending all lines into single line
				System.out.println(builder.toString());

				// Getting Table Name
				String temp[] = builder.toString().split("TABLE ");
				String temp1[] = temp[1].split("_BSTG");
				String TableName = temp1[0].trim();
				System.out.println("Table Name : " + TableName);

				Pattern p = Pattern.compile("[a-zA-Z]+_*");

				Matcher m1 = p.matcher(temp[1]);

				System.out.println("Words from string \"" + temp[1] + "\" : ");
				while (m1.find()) {
					System.out.println(m1.group());
				}

				reader.close();

			}
		}

	}

}

I am able to read the table name using Strings class but I am not able to get the column name.

My Desire result is to get the column names appended in a string with comma separated like below

ALM_VERS_ID , LAV_CD , LAV_CD_OFSE , LAV_CD_AMT, EXPRT_DT , DUA_INSRT_ID , DUA_INSRT_TMSTP, DUA_UPDT_ID , DUA_UPDT_TMSTP, DUA_VERS_NBR

Currently my Result is

ALM_ LAV_ CD_ BSTG FIELDS TERMINATED BY TRAILING NULLCOLS ALM_ VERS_ ID DECIMAL EXTERNAL NULLIF ALM_ VERS_ ID BLANKS LAV_ CD CHAR NULLIF LAV_ CD BLANKS LAV_ CD_ OFSE INTEGER EXTERNAL NULLIF LAV_ CD_ OFSE BLANKS LAV_ CD_ AMT DECIMAL EXTERNAL NULLIF LAV_ CD_ AMT BLANKS EXPRT_ DT DATE YYYY MM DD DUA_ INSRT_ ID CHAR NULLIF DUA_ INSRT_ ID BLANKS DUA_ INSRT_ TMSTP TIMESTAMP YYYY MM DD HH MI SS FF DUA_ UPDT_ ID CHAR NULLIF DUA_ UPDT_ ID BLANKS DUA_ UPDT_ TMSTP TIMESTAMP YYYY MM DD HH MI SS FF DUA_ VERS_ NBR DECIMAL EXTERNAL NULLIF DUA_ VERS_ NBR BLANKS


Solution

  • Please take a look into the C# solution that I have coded for you to process the control files. I happened to name the control file with *.txt.

    Coded a Control File Parser for processing the table name, all column names per control file.

    https://github.com/ranjancse26/ControlFileProcessor

    You can easily reverse engineer code from C# to Java.