Groovy and CSV How to Get Your Data Out I dont know exactly how many CSV files Ive readwritten to date, but Im willing to bet its a lot. These kind of files are a simple and common way to exchange data and are interoperable with spreadsheet programs as well, making them more easily accessible to non programmer types. There is some excellent support out there for reading and writing CSV files and so here, from simplest to most complex, are a few different ways to load and query the data trapped inside your CSV files. But first offCSV is Not XMLOr any other suitably constrained data format for that matter. There are some pretty clear rules out there for transporting data in this format, but of course hardly anybody listens to them. I found this and this in the first page of a quick Google search. However, while looking for available sample material on the web to demonstrate with, I found many great examples of how to screw it up not quoting empty strings, not escaping embedded line breaks, commas embedded in unquoted text, etc. All of these are things that prevent automation from properly interacting with the data. There are also, of course, the CSV formats that insist on embedding comments and descriptions, trying to present more than one matrix of information or any of the other crazy things you can do when writing to a format with no real metadata layer. Unfortunately, theres nothing as convenient as an xml schema available for describing what information is encoded in the document and, in general, the only potentially useful metadata in an individual file is the header line. The good news is that all of these deficiencies can be overcome if you know they exist. And theres some pretty nifty tools you can use to take out much of the guesswork. Ill suggest a couple of ideas for how to deal with files that dont adhere to the rules, but the main example Ive decided on is a straightforward and well formed set representing the periodic table of elements, provided generously by Akiscode. This file is directly machine readablehas no headers or embedded data, quotes all values and just follows all the rules. Heres the first five lines as an example. Atomic Number. Atomic Mass. Name. Symbol. 11. Hydrogen. H2. 4. 0. Helium. He. 36. 9. Lithium. Li. 49. 0. Beryllium. Be. 51. Boron. B     The simple format of the example CSV file. Some easy to implement techniques for turning data trapped in CSV flat files into knowledge you can use. Find examples of how to use the BULK INSERT statement in SQL Server 20 to bulkload data files into a database, copy data into a table and more. Note. UNIQUE, and PRIMARY KEY constraints are always enforced. When importing into a character column that is defined with a NOT NULL constraint, BULK INSERT inserts. BULK INSERT MULTIPLE FILES From a Folder a table to loop thru filenames drop table ALLFILENAMES CREATE TABLE ALLFILENAMESWHICHPATH VARCHAR255,WHICHFILE. Groovy All by Itself. Groovy makes dealing with String objects pretty painless. By adding facilities like easy casting and findfind. All for quickly turning raw Strings into real data, you can program some fairly complex questions. In this simple example Im depending on all values being quoted and I am not protecting against casting problems. Im running this through Maven which really helps to keep test data organized all you have to do is drop your test file in srctestresources and its automagically available on the test classpath. No specific handling is done for different types here everything is read in as a String and cast to a more specific type as needed. In a real application youre very likely going to encounter mixed alphanumeric data in any given column of data UNKNOWN instead of a number in a column labeled Quantity for instance and be more careful about casting. Input. Stream input. File get. Class. Loader. Resource. As. StreamTESTFILENAME. String lines input. File. text. splitn. Listlt String rows lines. A little helper method to get rid of the quotes in the input. To. Doublestring. All,. to. Double. OK, its parsed lets ask some questions. ATOMICMASS 1. def elements. Over. 20. 0Mass rows. Unbound Medicine Serial Number here. KB/database/775961/extracted-png-image2.png' alt='Bulk Insert Multiple Csv Files' title='Bulk Insert Multiple Csv Files' />A blog about IT workload optimization, performance, IT efficiency and SQL PDW. Caution Use extreme care when editing the CSV file. Phones might not work if you insert records that are in the wrong format. Bulk Insert Multiple Csv Files' title='Bulk Insert Multiple Csv Files' />All cast. To. DoubleitATOMICMASS 2. Between. 10. And. All row. double mass cast. To. DoublerowATOMICMASS. So this approach works fine for well formed input, but falls apart quickly in other cases. For instance, if any columns in the file are missing values the split function treats them as nulls, leading to different size arrays being stored in the list. Similarly you need to deal with quoted VS non quoted content, embedded line breaks and other issues individually. Fortunately, some nice guys have done that for us. Open. CSVGlen Smiths. Open. CSV library nicely abstracts away a lot of the minutiae of dealing with CSV. It properly deals with escaped characters, multiline input and a host of other issues for you. Give it pretty much anything that satisfies the Reader interface as an input and its good to go. The return type of the CSVReader. All method also satisfies the same contract as the plain Groovy version shown above, so we can interact with the parsed results in exactly the same way as in the previous example. All values are still typed as Strings, so we need to cast in order to compare numeric values. CSVReader. Listlt String rows new CSVReader. Input. Stream. Readerget. Class. class. Loader. Resource. As. StreamTESTFILENAME. All. same finders as in the Groovy version. Over. 20. 0Mass rows. All itATOMICMASS. Double 2. 00. Between. And. All row. double mass cast. To. DoublerowATOMICMASS. Hsql. DBHsql. DB allows for using flat files as Text Tables, effectively turning a CSV file into a database table. Data values are cast to the types specified in your table definition, so any potential type cast failures happen right up front when loading the data. The benefit is that once the data is read in no further manual processing is necessary. Note by default the csv file and the database file must be located in the same directory, as a security precaution can be overridden through configuration. In addition, leveraging sql makes querying the data extremely easy. Groovy brings in the Sql class to abstract away a lot of the normal Java boilerplate you encounter when working with a Result. Set. I didnt do any in depth testing to prove it out, but this is also the only one of the strategies described here that doesnt require holding the entire data in memory in order to do arbitrary queries. That can be mitigated in the previously shown methods by processing files line by line rather than in bulk if memory usage is a concern. String table. Name elements. String table. Definition CREATE TEXT TABLE table. Name. atomicnumber INTEGER PRIMARY KEY. NUMERIC. name VARCHAR2. VARCHAR3. create a new file database and a table corresponding to the csv file. Sql sql Sql. new. Instancejdbc hsqldb file testdb. Dir. absolute. Pathtestdb, sa,. Driver. sql. executetable. Definition. set the source to the csv file. SET TABLE elements SOURCE TESTFILENAME allquotedtrue. String. querying the database thats wrapping our CSV file. Over. 20. 0Mass sql. SELECT FROM table. Name WHERE atomicmass, 2. Between. 10. And. SELECT FROM table. Name WHERE atomicmass lt AND atomicmass, 2. RowSELECT count1 FROM table. Name WHERE atomicmass lt, 2. RowSELECT avgatomicmass FROM table. Name. to. Stringrow. Which One do I Use Pick the one that fits best for your use case is the real answer. I didnt know about the Hsql. DB option until recently, but Open. CSV has been a personal standby for years. Then again, if youre writing something quick and dirty at the script level, the simplicity of just applying a couple of split operations is pretty appealing. Heres how I generally decide.