How to drag, drop, and parse a CSV file in AIR
I’ve been playing around with Adobe AIR lately and I’ve really been enjoying it. So I thought I’d put out this quick tutorial on how to drag and drop a file into your AIR application; in this case we will be using a CSV file, and then we will parse that file so that its data can be used in a datagrid. You can download the full source code here.
Dragging a file into AIR:
First, we need to set up handlers to listen for the drag events. We are going to set up two listeners, one for when an item is dragged over the app and one for when that item is dropped.
this.addEventListener(NativeDragEvent.NATIVE_DRAG_ENTER, dragEnterHandler);
this.addEventListener(NativeDragEvent.NATIVE_DRAG_DROP, dragDropHandler);
Then we’ll set up the two handler function. The dragEnterHandler function determines if the item is an acceptable file type. You could use the different constants of the ClipboardFormats class to only accept specific file formats, but for now we will accept any valid file.
}
}
Next, when the user drops the file, we will copy it from the clipboard, read its contents, and parse its values into a more usable data structure.
//get an array of the files dropped in
var dropFiles:Array = evt.clipboard.getData(ClipboardFormats.FILE_LIST_FORMAT) as Array;
//get the content of the file
var fileContent:String = getFileContent(dropFiles[0]);
//parse it to get an array of Orders
orders = parseCSV(fileContent);
}
Reading the content of the dropped file:
For this we will use the FileStream class. The getFileContent function takes a generic file as its only parameter; it opens a FileStream, reads the contents of the file and returns that value as a string.
var fileStream:FileStream = new FileStream();
fileStream.open(_file, FileMode.READ);
var fileContent:String = fileStream.readUTFBytes(fileStream.bytesAvailable);
fileStream.close();
return fileContent;
}
Parsing the CSV file:
Finally, we are going to take the content of the CSV file, which is now in the form of a string, and parse out its data to populate an Array. We start by using the split() function to create an Array of line items. We use character code 10, which represents carriage returns, and character code 13, which represents new lines. to separate each line item. Then we loop through the newly created array of line items and pull out each comma separated value. At this point we have the information we need to populate the Order object and add that to our Array.
var csvArray:Array = new Array();
//break the csv into individual lines
var csvLines:Array = _content.split(String.fromCharCode(13,10));
//remove title row
csvLines.splice(0,1);
//loop over each line
for each(var s:String in csvLines){
var transaction:Order = new Order( lineItems[0],
lineItems[2],
lineItems[3],
lineItems[4]);
csvArray.push(transaction);
}
return csvArray;
}
The data pulled from the CSV file is now in a more manageable form and can be used as a dataProvider for components or to populate AIR’s built in SQLite database. Of course, in this tutorial, for simplicity’s sake, we assume that the file dropped into the application is a CSV file. In your application you should have some trapping to determine the file type and process it accordingly.
Tags: CSV, drag, drop, FileStream, NativeDragEvent, NativeDragManager, parse
Thanks Kalen for this example. I am curious however how to import this data into the SQLlite db. Would you happen to have a quick explanation?
Thanks
Hello Kastro,
Sorry for the late response, I’m in the process of moving my blog to a new technology. I hope to write a full blog post about this soon, but for the meantime here are some basic examples of how to work with SQLite in AIR. I pulled these examples from separate pieces of code, so please keep in mind that these functions deal with different tables and won’t necessarily work together. I hope this gets you started in the right direction.
TO CREATE A DATABASE CONNECTION: (this will create the db if it doesn't exist) ---------------------------------------------- public function createDBConnection(_dbName:String, _tableName:String){ this.dbName = _dbName; this.tableName = _tableName; dbReference = File.applicationStorageDirectory.resolvePath(dbName+".db"); dbConnection = new SQLConnection(); dbConnection.open(dbReference); } TO CREATE A TABLE: ---------------------------------------------- public function createTable():void{ var statement:SQLStatement = new SQLStatement(); statement.sqlConnection = dbConnection; statement.text = "CREATE TABLE IF NOT EXISTS " + tableName + " "; statement.text += "(ID INTEGER PRIMARY KEY AUTOINCREMENT, "; statement.text += "category TEXT, "; statement.text += "parentID INTEGER)"; statement.execute(); } INSERT RECORD: ---------------------------------------------- public function save(categories:Array):void{ var statement:SQLStatement = new SQLStatement(); statement.sqlConnection = dbConnection; // do this async, and in transaction/commit dbConnection.begin(); for(var i:int=categories.length-1; i>=0; i--){ statement.text = ""; statement.text += "INSERT INTO " + tableName + " "; statement.text += "(category, parentID) "; statement.text += "VALUES(@category, @parentID)"; statement.parameters["@category"] = categories[i].category; statement.parameters["@parentID"] = categories[i].parentID; statement.execute(); } dbConnection.commit(); } GET RECORDS: ---------------------------------------------- public function getData(_id:int=0):Array{ var statement:SQLStatement = new SQLStatement(); statement.sqlConnection = dbConnection; statement.text = "SELECT * FROM " + tableName; if(_id > 0){ statement.text += "WHERE transactionID = @id"; statement.parameters["@id"] = _id; } statement.execute(); var result:SQLResult = statement.getResult(); return result.data; }I took a stab at completing this code to add info to a SQLite database. Everytime you drop the CSV file into the app, it will add the rows to the database and then display all rows. Below is the complete code.:
=0; i--){ statement.text = ""; statement.text += "INSERT INTO " + tableName + " "; statement.text += "(orderID, date, type, description, amount) "; statement.text += "VALUES(@orderID, @date, @type, @description, @amount)"; statement.parameters["@orderID"] = categories[i].orderID; statement.parameters["@date"] = categories[i].date; statement.parameters["@type"] = categories[i].type; statement.parameters["@description"] = categories[i].description; statement.parameters["@amount"] = categories[i].amount; statement.execute(); } dbConnection.commit(); } //GET RECORDS: //---------------------------------------------- public function getData(_id:int=0):Array{ var statement:SQLStatement = new SQLStatement(); statement.sqlConnection = dbConnection; statement.text = "SELECT * FROM " + tableName; if(_id > 0){ statement.text += "WHERE transactionID = @id"; statement.parameters["@id"] = _id; } statement.execute(); var result:SQLResult = statement.getResult(); return result.data; } private function onInit():void{ //register event listeners this.addEventListener(NativeDragEvent.NATIVE_DRAG_ENTER, dragEnterHandler); this.addEventListener(NativeDragEvent.NATIVE_DRAG_DROP, dragDropHandler); } private function dragEnterHandler(evt:NativeDragEvent):void{ /** * if the file format is allowable, accept the file. * you could easily limit this to only accept the file extension you want * */ if(evt.clipboard.hasFormat(ClipboardFormats.FILE_LIST_FORMAT)){ NativeDragManager.acceptDragDrop(this); } } private function dragDropHandler(evt:NativeDragEvent):void{ NativeDragManager.dropAction = NativeDragActions.COPY; myLabel.text = "File Received"; //get an array of the files dropped in var dropFiles:Array = evt.clipboard.getData(ClipboardFormats.FILE_LIST_FORMAT) as Array; //get the content of the file var fileContent:String = getFileContent(dropFiles[0]); //parse it to get an array of Orders var tempOrders:Array = new Array(); tempOrders = parseCSV(fileContent); createDBConnection("orderDB","orders"); save(tempOrders); orders = getData(); } private function getFileContent(_file:File):String{ //open a fileStream to read the content of the file var fileStream:FileStream = new FileStream(); fileStream.open(_file, FileMode.READ); var fileContent:String = fileStream.readUTFBytes(fileStream.bytesAvailable); fileStream.close(); return fileContent; } private function parseCSV(_content:String):Array{ //create temporary array to store the Orders var csvArray:Array = new Array(); //break the csv into individual lines var csvLines:Array = _content.split(String.fromCharCode(13,10)); //remove title row csvLines.splice(0,1); //loop over each line for each(var s:String in csvLines){ var lineItems:Array = s.split(","); var transaction:Order = new Order( lineItems[0], new Date(lineItems[1]), lineItems[2], lineItems[3], lineItems[4]); csvArray.push(transaction); } return csvArray; } ]]>RR-007 – I am very interested in your code to add sqlite but I can’t seem to get it to work on my end. Would you be able to send me the source file?
Hi Kalen
I am trying to adapt your code to accept dragging an e-mail (and preferably with attachments) from a Thunderbird client.
I don´t know if I should consider an e-mail as a file or a text. Thunderbird seems to be a mix of both.
Should it be implemented as a custom clipboard format? What would be the strategy to solve this?
Hope not to confuse you by explaining what I am trying to do…
This would be a cool way to implement an e-mail forwarding or even files uploading to an air application.
I am saying AIR because I imagine that it must be a native approach to it.
Can you give me a hint?
Thank you very much indeed.
Carlos
with above example i’m able to upload .csv file but not .xls file also how to use same code for uploaded file ?
is that one day it will be possible in Flash in browsers like firefox and iexplorer? What I mean is do these events in the NativeDragManager in browsers that neither silverligth.
Hi Kalen,
For some reason, when I run this on my Mac it does not break it into individual lines
var csvLines:Array = _content.split(String.fromCharCode(13,10));
The csv I’m dropping was saved from MS Office Excel as csv.
Is there any specific save settings you need for the csv to recognize fromCharCode(13,10) ?
Your orders sample data works fine, but other csvs do not. It like they are not outputting an end-of-line.
Any thoughts?
Thanks,
Don