CODEWITHSUNDEEP
csvh2
Aejaz
Aejaz

Reputation: 110

H2 Database issue : Column interpretation with CSVREAD and convert function

I am currently using h2 version 1.4.196. I have a CSV file as follows,

Status,message,code
1,hello,13
2,world,14
3,ciao,26

Following query works as expected & returns proper Status values.

select "Status" from  CSVREAD('myfile.csv', 
'Status,message,code', null) ;

Following query produces an exception. It seems like the literal string "Status" is being passed to convert instead of the values returned by the execution of the query. I am not sure if my understanding of convert is correct. Please help me with fixing this query to return the desired result. Thanks in advance.

select convert("Status", int) from  CSVREAD('myfile.csv', 'Status,message,code', null) ;

Caused by: java.lang.NumberFormatException: For input string: "Status" 
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) 
    at java.lang.Integer.parseInt(Integer.java:580) 
    at java.lang.Integer.parseInt(Integer.java:615) 
    at org.h2.value.Value.convertTo(Value.java:940)

----------------------Detailed Exception------------------------------

select convert("Status", int) from  CSVREAD('myfile.csv', 
'Status,message,code', null) ;
Data conversion error converting "Status"; SQL statement:
select convert("Status", int) from  CSVREAD('myfile.csv', 
'Status,message,code', null) [22018-196] 22018/22018 (Help)
org.h2.jdbc.JdbcSQLException: Data conversion error converting "Status"; SQL statement:
select convert("Status", int) from  CSVREAD('myfile.csv', 
'Status,message,code', null) [22018-196] 
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) 
    at org.h2.message.DbException.get(DbException.java:168) 
    at org.h2.value.Value.convertTo(Value.java:996) 
    at org.h2.expression.Function.getSimpleValue(Function.java:945) 
    at org.h2.expression.Function.getValueWithArgs(Function.java:1196) 
    at org.h2.expression.Function.getValue(Function.java:591) 
    at org.h2.command.dml.Select$LazyResultQueryFlat.fetchNextRow(Select.java:1459) 
    at org.h2.result.LazyResult.hasNext(LazyResult.java:79) 
    at org.h2.result.LazyResult.next(LazyResult.java:59) 
    at org.h2.command.dml.Select.queryFlat(Select.java:519) 
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:625) 
    at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:114) 
    at org.h2.command.dml.Query.query(Query.java:371) 
    at org.h2.command.dml.Query.query(Query.java:333) 
    at org.h2.command.CommandContainer.query(CommandContainer.java:113) 
    at org.h2.command.Command.executeQuery(Command.java:201) 
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:186) 
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164) 
    at org.h2.server.web.WebApp.getResult(WebApp.java:1380) 
    at org.h2.server.web.WebApp.query(WebApp.java:1053) 
    at org.h2.server.web.WebApp$1.next(WebApp.java:1015) 
    at org.h2.server.web.WebApp$1.next(WebApp.java:1002) 
    at org.h2.server.web.WebThread.process(WebThread.java:164) 
    at org.h2.server.web.WebThread.run(WebThread.java:89) 
    at java.lang.Thread.run(Thread.java:748) 
Caused by: java.lang.NumberFormatException: For input string: "Status" 
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) 
    at java.lang.Integer.parseInt(Integer.java:580) 
    at java.lang.Integer.parseInt(Integer.java:615) 
    at org.h2.value.Value.convertTo(Value.java:940) 
    ... 22 more

Upvotes: 1

Views: 1368

Answers (1)

Oleg
Oleg

Reputation: 6314

The way you're using it you're defining the columns and reading all the rows from the csv, so you're getting the first row with Status as well. Then you try to convert the string Status to an integer which obviously results in exeption.

Instead you shouldn't define the columns but let h2 get them from the first row of the csv:

select convert(status,int) from CSVREAD('myfile.csv');

Upvotes: 1

Related Questions