Create list of columns based on sql file in grroovy

Question

I try to create list of column names based on sql file in groovy using the following funtion:

def get_column_names() {
        def create_sql = new File("../create.sql").text.replaceAll("\n", '')
        def result = (create_sql =~ /\(.*?\);/).findAll().first().replace('(', '').split(',')
        return result.stream().map({ x -> x.split(' ')[0] }).toList()
    }

Here is an example of create.sql file :

CREATE TABLE test_table (
id varchar(5) PRIMARY KEY,
created BIGINT,
company TEXT,
income FLOAT,
percent FLOAT
);

but after executing I get the following error:

java.util.NoSuchElementException: Cannot access first() element from an empty List

Othersides when I try to execute the same in groovy console, where I use sql as a string I get correct list:

def create_sql = """

CREATE TABLE test_table (
id varchar(5) PRIMARY KEY,
created BIGINT,
company TEXT,
income FLOAT,
percent FLOAT
);
"""

def create_sql1 = create_sql.replaceAll("\n", '')
def result = (create_sql1 =~ /\(.*?\);/).findAll().first().replace('(', '').split(',')
def result1= result.stream().map({ x -> x.split(' ')[0] }).toList()

Result: [id, created, company, income, percent]

Any idea what is wrong with the function or how to extract list of columns from file on other way?

Answer 1

It may be that the file was created in Windows, where newlines are typically \r\n. If that's the case, maybe you can try to match \r also:

def create_sql = "\r\n\r\nCREATE TABLE test_table (\r\n\r\nid varchar(5) PRIMARY KEY,\r\ncreated BIGINT,\r\ncompany TEXT,\r\nincome FLOAT,\r\npercent FLOAT\r\n);"

def create_sql1 = create_sql.replaceAll("[\r\n]+", '')
def result = (create_sql1 =~ /\(.*?\);/).findAll().first().replace('(', '').split(',')
def result1= result.stream().map({ x -> x.split(' ')[0] }).toList()
println(result1) //[id, created, company, income, percent]