[SOLVED] Scriptella and CSV column headers that contain spaces

Scriptella and CSV column headers that contain spaces

I am attempting to move data from CSV files into a database table, and I'd like to use the column header names in the script. For example:

<connection id="csvin" driver="csv" url="/path/to/file.csv" />
<connection id="dbout" driver="h2" url="connection string" />
<query connection-id="csvin">
  <script connection-id="out">
    INSERT INTO mytable VALUES (?column one,?column five,?column eight)
  </script>
</query>

I get a SQL syntax error from the above. If I instead use the SQL statement

INSERT INTO mytable VALUES (?1, ?5, ?8)

Then it works. But I'd really rather use the column headers. Due to logistical reasons, I cannot have the column header names changed to not contain spaces. Is there any way to accomplish using the names that have spaces?

I have tried escaping the spaces, quoting the names, using brackets, and using unicode notation (\u0020), but have had no luck so far.

Thanks!

Solution

Unfortunately special symbols in variable names cannot be escaped, but Scriptella provides a utility method to achieve a similar effect. See EtlVariable.getParameter

In your case, simply use ?{etl.getParameter('column one')}