Thursday, September 12, 2013

Llenar una tabla de MySQL utilizando un archivo local (MySQL Loader)

Muchas veces me envían reportes llenos de data que desean analizar y/o cruzar con otra. Si los archivos son pequeños no hay problema pues muchas veces podemos hacer el cruce con grep y diffs utilizando shell scripting. Pero, ¿si son millones de records y tienes que comparalos con una tabla existente? Para esto utilizamos el SQL Loader. El SQL loader nos permite cargar archivos directamente a la tabla que necesitamos. 

La estructura del comando es la siguiente:

'load data LOCAL infile '/carpeta/archivo.txt' into TABLE <NombreDeLaTabla> FIELDS <OPCION1> <OPCION2> (CAMPO1,CAMPO2,...,CAMPON);

<OPCION> - Entre las opciones que puedes poner aquí están:
TERMINATED BY ',' : Con este le decimos que los campos estan separados por comas.
ENCLOSED BY '"': Con este le decidmos que los campos estan encerrados entre comillas ".

Hay muchas más pero usualmente estas son las más que uso. 

Si utilizamos las dos opciones le decimos que los campos estan encerrados entre comillas pero separados por comas.

La parte de CAMPO1,CAMPO2 van los nombres de los campos de la tabla a donde vas a insertarlos. Es importante que estén en el mismo orden que se encuentran en el archivo.


Tabla Usuarios:



Para subir este archivo a la tabla Usuarios el comando sería el siguiente:

load data LOCAL infile '/desktop/files/usuarios.csv' into TABLE USUARIOS FIELDS TERMINATED BY ',' '(ID,NOMBRE);

Pendiente a los errores y a los warnings que te pueda tirar el SQL.

Ya con eso el archivo se cargo a tu base de datos! Suerte!

Controlling an Air Conditioner with Android and Arduino

For my first Arduino project I decided to control my Air Conditioner over the internet with the help of Arduino and my Android Cellphone wherever I have internet access.

Video: (its in spanish but you'll get what I'm demonstrating"

I made this on a Windows environment but it should be pretty similar in Linux.

- An Air Conditioner ( You don't say!)
- AC IR Control Remote (It MUST be in good working order because we need to decode the IR codes)

- Arduino MEGA 2560  - Bought it here
- Arduino compatible Ethernet Shield  Bought it here
- Small breadboard Bought it here
Jumper Cables Bought it here
(1) IR Receiver Diode Bought it here
- (1) IR LED Bought it here

- Arduino SDK -
- IR Remote Arduino Library - Download (Library official website)
- WebDuino Library - Download (Library official website)

We are ready!

First of all we need to unzip the IR Remote and Webduino library that we just downloaded to the Arduino SDK libraries folder.

Now we have to make sure that our Arduino Mega is connected to our computer and that Windows has the correct driver.

Now we have to tell the Arduino SDK which type of Arduino we are using. So we go to:
Tools > Board > Arduino Mega 2560 or Mega ADK
And choose Arduino Mega.

Now we choose the port where your Arduino was detected
Tools > Serial Port > <Port>

Now we have to connect the IR Receiver to the Arduino. The pins are connected as follows:

Pin 1 goes to pin 19 from Arduino.
Pin 2 goes to pin from Ground.
Pin 3 goes to pin from 5V.

*Orden de Pins means "Pin's Order"

Now we copy this sketch to the Arduino SDK. (This sketch decodes the IR from your Remote Control) **Careful copy pasting, it might give you compilation errors

 * IRremote: IRrecvDump - dump details of IR codes with IRrecv
 * An IR detector/demodulator must be connected to the input RECV_PIN.
 * Version 0.1 July, 2009
 * Copyright 2009 Ken Shirriff
 * JVC and Panasonic protocol added by Kristian Lauszus (Thanks to zenwheel and other people at the original blog post)

#include <IRremote.h>

int RECV_PIN = 19;

IRrecv irrecv(RECV_PIN);

decode_results results;

void setup()
  irrecv.enableIRIn(); // Start the receiver

// Dumps out the decode_results structure.
// Call this after IRrecv::decode()
// void * to work around compiler issue
//void dump(void *v) {
//  decode_results *results = (decode_results *)v
void dump(decode_results *results) {
  int count = results->rawlen;
  if (results->decode_type == UNKNOWN) {
    Serial.print("Unknown encoding: ");
  else if (results->decode_type == NEC) {
    Serial.print("Decoded NEC: ");
  else if (results->decode_type == SONY) {
    Serial.print("Decoded SONY: ");
  else if (results->decode_type == RC5) {
    Serial.print("Decoded RC5: ");
  else if (results->decode_type == RC6) {
    Serial.print("Decoded RC6: ");
  else if (results->decode_type == PANASONIC) { 
    Serial.print("Decoded PANASONIC - Address: ");
    Serial.print(" Value: ");
  else if (results->decode_type == JVC) {
     Serial.print("Decoded JVC: ");
  Serial.print(results->value, HEX);
  Serial.print(" (");
  Serial.print(results->bits, DEC);
  Serial.println(" bits)");
  Serial.print("Raw (");
  Serial.print(count, DEC);
  Serial.print("): ");

  for (int i = 0; i < count; i++) {
    if ((i % 2) == 1) {
      Serial.print(results->rawbuf[i]*USECPERTICK, DEC);
    else {
      Serial.print(-(int)results->rawbuf[i]*USECPERTICK, DEC);
    Serial.print(" ");

void loop() {
  if (irrecv.decode(&results)) {
    Serial.println(results.value, HEX);
    irrecv.resume(); // Receive the next value
You'll have something like this:

Now let's press  (verify) to compile the code then 
 (upload) to load it up to our Arduino.

Now we have to go to Menu > Tool to open up the Serial Monitor windows. After its open, point your AC Remote Control to the IR receiver connected to your Arduino and press each button one by one. You'll get something like this:

NOTE* The IR library can decode most brands of remote controls, but there's a slight chance that yours might not be supported. Go check on the library website if support was added. In my case my remote control ended up being manufactured by NEC. 

Decoded NEC: 80FF48B7 (32 bits) - AC ON
Decoded NEC: 80FFC837 (32 bits) - TEMP UP
Decoded NEC: 80FF58A7 (32 bits) - TEMP DOWN
Decoded NEC: 80FFD827 (32 bits) - TIMER
We have decode our control's codes. Now we have to create another sketch, this time to implement a webserver that will help us to communicate with the arduino over the internet. To do this I modified the Web_Demo example from the Webduino library. 

In order for this to work you'll have to make sure about the following:

-You must have the ethernet shield installed in your Arduino.
-You have to define your Arduino's MAC address and IP in the sketch.
-You have to define the port where the webserver will be working.
- The IR Led should be installed in the Arduino with the anode (shortest leg) on GRD (ground) and the cathode (largest leg, positive) in pin #9.

* Web_Demo.pde -- sample code for Webduino server library */

 * To use this demo,  enter one of the following USLs into your browser.
 * Replace "host" with the IP address assigned to the Arduino.
 * http://host/
 * http://host/acOn
 * This URL turns on the Air Conditioner

#include "SPI.h"
#include "Ethernet.h"
#include "WebServer.h"
#include <IRremote.h>

// no-cost stream operator as described at 
template<class T>
inline Print &operator <<(Print &obj, T arg)
{ obj.print(arg); return obj; }

//IR Variable declaration
IRsend irsend;

static uint8_t mac[] = { 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };

static uint8_t ip[] = { 192, 168, 1, X };

#define PREFIX ""

WebServer webserver(PREFIX, 80 ); //ASSIGN PORT HERE

// commands are functions that get called by the webserver framework
// they send the IR Code to the AC

void acOn(WebServer &server, WebServer::ConnectionType type, char *url_tail, bool tail_complete)
  if (type == WebServer::POST)

  //server.httpSuccess(false, "application/acOn");

  //Replace for your IR Code here. 
  irsend.sendNEC(0x80FF48B7, 32); //irsend.send(IRTYPE)(0x(IRCODE), (BITS)); 

void tempUp(WebServer &server, WebServer::ConnectionType type, char *url_tail, bool tail_complete)
  if (type == WebServer::POST)

 //Replace for your IR Code here. 
  irsend.sendNEC(0x80FFC8A7, 32); //irsend.send(COMPANYIR)(0x(IRCODE), (BITS)); 

void tempDown(WebServer &server, WebServer::ConnectionType type, char *url_tail, bool tail_complete)
  if (type == WebServer::POST)


  //Replace for your IR Code here.
  irsend.sendNEC(0x80FF5837, 32); // 

void timer(WebServer &server, WebServer::ConnectionType type, char *url_tail, bool tail_complete)
  if (type == WebServer::POST)


  //Replace for your IR Code here.
  irsend.sendNEC(0x80FFD827, 32); // 

void setup()

  Ethernet.begin(mac, ip);

//Rename the commands to your liking
  webserver.addCommand("acon", &acOn);
  webserver.addCommand("tempup", &tempUp);
  webserver.addCommand("tempdown", &tempDown);
  webserver.addCommand("timer", &timer);

void loop()
  // process incoming connections one at a time forever

  // if you wanted to do other work based on a connecton, it would go here

After pasting this to our Arduino SDK we press  (verify) to compile the code and then (upload) to upload it.

At this very moment you can actually control your Air Conditioner from any PC connected to your Network. All you have to do is to put in your address bar http://<Assigned IP>/acon your AC should turn on. Make sure the IR is pointing to your AC.

Now, this is optional and it's all under your own risk due to security issues. In order to be able to control your AC from anywhere you have internet access you must create a Port Forwarding rule in your router. That rules should contain the IP and Port you assigned to your Arduino. I recommend using a port that's NOT port 80.

After creating that Port Forwarding rule you should be able to control your AC anywhere you have internet access. (You have to know your home IP address)

The android application is not really necessary because you can actually control the AC from your cellphone's browser but its more interesting (and look really cool!) to have an android app for that.

I'm a beginner on Android so most probably there's a better way to do most of the things I did in the app, but hey, it works!

Here you can download the project for the android app: Download

Modify it as you like and you are done!

Any doubts or questions let me know!