Data Conversion made Easier with Scala Scripts

Scala scripts are often used to quickly test Scala code or try out new ideas, but they also work well for handling small, one-time tasks or data conversions. For example, I often need to convert data from one format to another such as converting formatted text from one system’s extract file into a differently-formatted database file for another system or for a vendor. Most of these tasks will only ever be done once, and while I could use tools such as awk or Bash scripting or even a small C# or Java program, in a Scala-focused development group, creating these scripts as small Scala scripts lets us remain focused on just one language rather than requiring a multitude of languages, and keeping it as a script lets me rapidly build and tweak the script until it is ready.

 

For a simple example, assume you need to populate a mapping table to map names of famous artwork from a vendor’s system to a two different internal systems, one for the catalog database and the other for the analytics database. The painting name in the source file may or may not include a trailing space; if it does, you need to append a “#” to the name for the first system and a “$” for the second. Your task is to convert the source text file from the vendor’s PostgreSQL database that contains names of the artists and paintings into a SQL script that will insert the mappings, modified as necessary, into a SQL Server database. (This was actually a real, very typical, example…) A simple Scala script makes this task trivial.

 

 

Extract of Source File – Note the trailing spaces on line 2 and 3:

 

'Michelangelo_-_Creation_of_Adam'
'Edgar Degas - La famille Bellelli '
'  Claude Monet, Impression, soleil levant '
'Renoir:7505c319-a5ee-4285-a045-f783d6f7e975'

 

 

Scala script:

 

// run as a Scala script, as follows:
//       cat artwork_list.txt | scala CreateMappingInserts.scala > artwork_mapping_inserts.sql

io.Source.stdin.getLines.foreach {
  line => 
    val artworkId = line.trim.replace("'", "")
    val artworkIdForCatalog = (if (artworkId.endsWith(" ")) artworkId + "#" else artworkId)
    val artworkIdForAnalytics = (if (artworkId.endsWith(" ")) artworkId + "$" else artworkId)
    println("INSERT INTO artwork_mapping_table " + 
        "(artwork_id, artwork_id_catalog_db, artwork_id_analytics_db) " + 
        "VALUES ('" + artworkId + "', '" + artworkIdForCatalog + "', '" + artworkIdForAnalytics + "')\nGO")
}

 

 

Generated SQL Script:

 

INSERT INTO artwork_mapping_table (artwork_id, artwork_id_catalog_db, artwork_id_analytics_db)
VALUES ('Michelangelo_-_Creation_of_Adam', 'Michelangelo_-_Creation_of_Adam', 'Michelangelo_-_Creation_of_Adam')
GO
INSERT INTO artwork_mapping_table (artwork_id, artwork_id_catalog_db, artwork_id_analytics_db)
VALUES ('Edgar Degas - La famille Bellelli ', 'Edgar Degas - La famille Bellelli #', 'Edgar Degas - La famille Bellelli $')
GO
INSERT INTO artwork_mapping_table (artwork_id, artwork_id_catalog_db, artwork_id_analytics_db)
VALUES ('  Claude Monet, Impression, soleil levant ', '  Claude Monet, Impression, soleil levant #', '  Claude Monet, Impression, soleil levant $')
GO
INSERT INTO artwork_mapping_table (artwork_id, artwork_id_catalog_db, artwork_id_analytics_db)
VALUES ('Renoir:7505c319-a5ee-4285-a045-f783d6f7e975', 'Renoir:7505c319-a5ee-4285-a045-f783d6f7e975', 'Renoir:7505c319-a5ee-4285-a045-f783d6f7e975')
GO

 

Although this example is trivial, the concept scales equally well into much larger and more complex problems. More importantly, it avoids the need to introduce awk or another language when Scala can do the job quite nicely.