Friday, January 29, 2016

0

Convert Excel file data in to comma seperated text file

  • Friday, January 29, 2016


  • Below code is to convert excel file (name.xls) in to text files sepetated values by comma.
    In my name.xls file contains 6 columns and 20 rows (20 student informations) it has two different school details in sheet1 and sheet2


    package com.excel.upload;

    import java.io.BufferedWriter;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStreamWriter;
    import jxl.Cell;
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.read.biff.BiffException;

    public class ExcelRead {

    public static void main(String[] args) {
           
           try {
               
                Workbook wrk1 =  Workbook.getWorkbook(new File("C:\\Users\\jayakumark\\Downloads\\test1.xlsx"));
               
               //i for number of row ,j for column , k for number of sheets in excel bassed on that you can change i, j, k values  here i am using 2 sheets
                int i,j,k;
                for(k=0;k<2;k++)
                {
               Sheet sheet1 = wrk1.getSheet(k);            
                      
               for (i=0;i<20;i++)
               {
                String [] crits = new String [6];
                for (j=0;j<=5;j++)
                {
                     Cell colArow1 = sheet1.getCell(j, i);      
             
                     String str_colArow1 = colArow1.getContents();
       
                     
                      crits[j] = str_colArow1;
                }
                   
       
         try{
                //to print those values in text file
                   OutputStreamWriter writer = new OutputStreamWriter(
                         new FileOutputStream("C:\\Users\\jayakumark\\Downloads\\DB_input_excel\\name.txt", true), "UTF-8");
                   BufferedWriter fbw = new BufferedWriter(writer);
                   fbw.write(crits[0]+"\t"+crits[1]+"\t"+crits[2]+"\t"+crits[3]+"\t"+crits[4]+"\t"+crits[5]");
                   fbw.newLine();
                   fbw.close();
               }catch (Exception e) {
                   System.out.println("Error: " + e.getMessage());
               }
               System.out.println("Contents of cell Col A Row 1: " +crits[1]);
           System.out.println("Done");
               }
                }
           } catch (BiffException e) {
               e.printStackTrace();
           } catch (IOException e) {
               e.printStackTrace();
           }
           

       }

    }

    here i -> number of rows
    j-> number of columns
    k -> number of sheets in excel
    if you have 5 sheets in your excel file you have to increase k value.
    Finally you will get all sheet values in a single text file .

    To use this code you should download and add below jar in your eclipse.
    jxl26jar

    Read more...

    Monday, November 9, 2015

    0

    Connect R with MYSQL database

  • Monday, November 9, 2015
  • R is simple and has more features.
    In this post we can see how write R code to connect Mysql DB which is installed in azure platform.

    I am using R studio .


     RJDBC package to connect Mysql.
    First install RJDBC  by using following command in R studio

    Here we are insert records in table and fetch the result by R script

    DB name is Department
    Table name is Maths

    install.packages("RJDBC")

    mydb = dbConnect(MySQL(), user='root', password='root', host='jay_hadoop.cloudapp.net', dbname="Department")

    To insert value in Maths table

    query1=dbGetQuery(mydb, "INSERT INTO Maths (StudentName, City) VALUES ('Saswath','Bangalore');")

    To fetch values from table Maths

    table_values <-dbGetQuery(mydb, "select * from Maths")

    print(table_values)


    You will get 

    Saswath   Bangalore

    then finally close the connection by

    dbDisconnect(mydb)


    Results are stored in Dataframe table_values , Now you can write r dataframe code .  RJDBC is easy and simple to connect R with mysql.




    Read more...

    Wednesday, November 4, 2015

    0

    Ubuntu remote login xrdp failed error

  • Wednesday, November 4, 2015
  •  Remote Desktop Connection xrdp_mm_process_login_response: login failed in ubuntu Resolved:

     Today i got issues with my Remote login . I cant able to login from my windows machine to Ubuntu installed in  Azure cloud machine got following error.



    connecting to sesman ip 127.0.0.1 port 3350
    sesman connect ok
    sending login info to sesman
    xrdp_mm_process_login_response: login failed


    I restarted the Ubuntu machine and recheck my username and password credentials, no luck . I guess there is problem with xrdp then find the solution . 
    Ubuntu supports multiple session,The issue could be X11 session created by xrdp.
     So i checked session in below path
    #cd /tmp/.X11-unix
    do #ls -lrt
    You can see results like
    srwxrwxrwx 1 root   root   0 Nov  4 05:20 X0
    srwxrwxrwx 1 userjay userjay 0 Nov  4 05:20 X10
    srwxrwxrwx 1 userjay userjay 0 Nov  4 05:36 X11
    srwxrwxrwx 1 userjay userjay 0 Nov  4 05:38 X12
    srwxrwxrwx 1 userjay userjay 0 Nov  4 05:39 X13
    srwxrwxrwx 1 userjay userjay 0 Nov  4 05:40 X14
    srwxrwxrwx 1 userjay userjay 0 Nov  4 05:66 X15
    srwxrwxrwx 1 userjay userjay 0 Nov  4 05:55 X16
    srwxrwxrwx 1 userjay userjay 0 Nov  4 05:56 X17
    srwxrwxrwx 1 userjay userjay 0 Nov  4 05:57 X18
    srwxrwxrwx 1 userjay userjay 0 Nov  4 05:59 X19
    Then i deleted X11,X12...X19 and restarted ubuntu now i can login to the  ubuntu machine.
    To delete use below command
    rm X11
    rm X12

    Read more...

    Subscribe