Fetch Data from Server and save it in csv file
Fetch Data from Server and save it in csv file

Hi Guys, Welcome to Proto Coders Point, In this android tutorial we will fetch data from our database (phpmyadmin) and store data in CSV file in android studio.

In other words we will learn store all the data recieved from server in to .csv file.

Final Output

android data in to csv file format

So let’s begin.

My Database structure

 This is my database with student table with some data in it

student table have data such as:

  • id
  • Firstname
  • Lastname
  • Phone

mydatabase

Creating of database and table in your phpmyadmin

just open phpmyadmin dashboard

Here create a new database named: mydatabase then in SQL tab query box copy paste below sql command/query, this command will create a new table by name “student”

database query import query

Query:

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
-- Database: `mydatabase`
--

-- Table structure for table `student`
--
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `firstname` varchar(20) NOT NULL,
  `lastname` varchar(20) NOT NULL,
  `phone` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `student` (`id`, `firstname`, `lastname`, `phone`) VALUES
(1, 'Rajat', 'Palankar', '875899XXX11'),
(2, 'Pavan', 'Raikar', '9585XXX454'),
(3, 'Suraj', 'Somnache', '875899XXX22'),
(4, 'Manoj', 'Raikar', '8758XX8754'),
(5, 'sahil', 'pinjar', '75848XX555');

ALTER TABLE `student`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `student`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

My Server php script to Fetch data from database

connect.php

<?php
//replace username,password with your phpmyadmin login credential
$conn = mysqli_connect("localhost", "username", "password", "mydatabase");

if($conn)
{
  //echo "Connection Success";
}
else
{
//  echo "Connection Failed";

}

//

?>

This connect.php code will help use in getting connected with our database server.

fetch_all_data.php

<?php

include 'connect.php';

$query="select * from student";

      $result=mysqli_query($conn,$query) or die('ERROR IN SELETION '.mysql_error());

      $list="";

    while($row=mysqli_fetch_array($result))
      {
          if($list=="")
            $list=$row['id']."#".$row['firstname']."#".$row['lastname']."#".$row['phone'];
          else
            $list.="@".$row['id']."#".$row['firstname']."#".$row['lastname']."#".$row['phone'];

      }

      if($list=="")
        echo "NONE";
      else
        echo $list;



?>

In above php code we are fetching all the data present in student table by using select * from student; sql command.

The above code will return data in a form of list.

each users data is been seperated using # and @. so that we can split using those symbols.

The above code response is as below:

Here @ symbol is been used to identify or split the data for individual student data.

Now, we are done with Server Side coding, let’s go to Android Studio

Android Studio Coding java

We are making use of StringBuilder that will hold all the data received from the php code(as above)

Snippit code

StringBuilder data =new StringBuilder();

data.append("id,firstname,lastname,phone"); // table row

data.append("\n"+ each_user[0]+","+ each_user[1]+","+ each_user[2]+","+ each_user[3]); // add data received from server

Step 1: Add Volley library dependencies

In your android studio project open Gradle.build(App Level) and add the below Volley library dependencies 

implementation 'com.android.volley:volley:1.1.0'

Then hit the sync now. this will download the volley library  package into your project as External library.

Step 2: Add Permission to your Project

As we need to fetch data from our server into our android application, we need to ANDROID INTERNET PERMISSION to be activated.

Internet Permission

<uses-permission android:name="android.permission.INTERNET"/>

The data we received from our server we gonna store it in csv format and then we need to store it in our local storage location to do so we need to add ANDROID WRITE PERMISSION

Read Write Permission

<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
<uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE"/>

Then, Create a xml folder/directory under res folder and create a xml resource file by name “provider_path.xml”.  and paste the below path access code.

<?xml version="1.0" encoding="utf-8"?>
<paths xmlns:android="http://schemas.android.com/apk/res/android">
    <external-path name="external_files" path="."/>
    <files-path path="." name="name" />
</paths>

then add this file in android manifest between <application> tag

<provider
            android:name="androidx.core.content.FileProvider"
            android:authorities="com.example.dataintocsvformat"
            android:grantUriPermissions="true"
            android:exported="false">
            <meta-data
                android:name="android.support.FILE_PROVIDER_PATHS"
                android:resource="@xml/provider_path" />
</provider>

Step 3: Adding Network Configuration

Note that Andorid 7.0 Nouget (API Level 24) There is not support for unauthorized URL Request or response, So we need to provide the application with the server IP that we gonna use to fetch data from.

to do so you need to create a network_configuration.xml file in xml folder that you have created in step 2

right click on xml folder > new > xml resource file

network_security_config.xml

<?xml version="1.0" encoding="utf-8"?>
<network-security-config>
    <domain-config cleartextTrafficPermitted="true">
        <domain includeSubdomains="true">35.232.167.171</domain>   // replace with your IP address or website domain name

    </domain-config>
</network-security-config>

Then add this file in android_manifest.xml file in <application tag

<application

        android:requestLegacyExternalStorage="true"
        android:networkSecurityConfig="@xml/network_security_config"
        android:usesCleartextTraffic="true"

Step 4: Android XML

In xml file we just have a single view that is a BUTTON which on Click will fetch data from our php code and store the data in CSV file  on your android phone local device.

activity_main.xml

This only have a button for Demo purpose.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:gravity="center"
    tools:context=".MainActivity">
    <Button
        android:id="@+id/download"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Download CSV"/>
    
</LinearLayout>

Step 5: Android java code, to fetch data and store in csv file

This is the complete java code that you just need to add in MainActivity.java file

Code Explanation is given in below code as a Comment

package com.example.dataintocsvformat;

import androidx.appcompat.app.AppCompatActivity;
import androidx.core.app.ActivityCompat;
import androidx.core.content.ContextCompat;
import androidx.core.content.FileProvider;

import android.app.ProgressDialog;
import android.content.Context;
import android.content.Intent;
import android.content.pm.PackageManager;
import android.net.Uri;
import android.os.Bundle;
import android.os.Environment;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.Toast;

import com.android.volley.Request;
import com.android.volley.RequestQueue;
import com.android.volley.Response;
import com.android.volley.VolleyError;
import com.android.volley.toolbox.StringRequest;
import com.android.volley.toolbox.Volley;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;

public class MainActivity extends AppCompatActivity {

    Button download ;
    ProgressDialog pdDialog;
    private static final int PERMISSION_REQUEST_CODE = 100;
    //php code URL path
    String URL = "http://35.232.167.171/fetch_all_data.php";
    StringBuilder data;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        download=(Button)findViewById(R.id.download);
        pdDialog = new ProgressDialog(MainActivity.this);
        pdDialog.setMessage("Fetching Date...");
        pdDialog.setCancelable(false);

        download.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                // to store csv file we need to write storage permission 
                // here we are checking is write permission is granted or no 
                if(checkPermission())
                {
                    FetchData(URL);

                }else{
                    // If permission is not granted we will request for the Permission
                    requestPermission();
                }
            }
        });
    }

    // fetch data from server
    private void FetchData(String url)
    {
        pdDialog.show();
        StringRequest stringRequest = new StringRequest(Request.Method.POST, url,
                new Response.Listener<String>() {
                    @Override
                    public void onResponse(String response) {

                        //we get the successful in String response 
                        Log.e("MY_DATA",response);
                        try{
                            pdDialog.dismiss();

                            if(response.equals("NONE"))
                            {
                                Toast.makeText(MainActivity.this,"NO Data Found",Toast.LENGTH_LONG).show();
                                pdDialog.dismiss();
                            }else{

                                pdDialog.dismiss();
                                // In String response we get full data in a form of list
                                splitdata(response);
                            }

                        } catch (Exception e) {
                            e.printStackTrace();

                            pdDialog.dismiss();
                        }
                    }
                }, new Response.ErrorListener() {
            @Override
            public void onErrorResponse(VolleyError error) {
                pdDialog.dismiss();

            }
        })
        {
            @Override
            protected Map<String, String> getParams() {
                Map<String,String> params = new HashMap<>();


                return params;
            }
        };

        RequestQueue requestQueue = Volley.newRequestQueue(MainActivity.this);
        requestQueue.add(stringRequest);
    }

    private void splitdata(String response) {

        System.out.println("GET DATA IS "+response);
        
       // response will have a @ symbol so that we can split individual user data
        String res_data[] = response.split("@");

        //StringBuilder  to store the data 
        data = new StringBuilder();
        
        //row heading to store in CSV file
        data.append("id,firstname,lastname,phone");
        
        for(int i = 0; i<res_data.length;i++){
            //then we split each user data using # symbol as we have in the response string
            final String[] each_user =res_data[i].split("#");


            System.out.println("Splited # ID: "+ each_user[0]);
            System.out.println("Splited # Firstname? : "+ each_user[1]);
            System.out.println("Splited # Lastname? : "+ each_user[2]);
            System.out.println("Splited # Phone ? : "+ each_user[3]);

            // then add each user data in data string builder
            data.append("\n"+ each_user[0]+","+ each_user[1]+","+ each_user[2]+","+ each_user[3]);



        }
        CreateCSV(data);
    }
    private void CreateCSV(StringBuilder data) {

        Calendar calendar = Calendar.getInstance();
        long time= calendar.getTimeInMillis();

        try {
            //
            FileOutputStream out = openFileOutput("CSV_Data_"+time+".csv", Context.MODE_PRIVATE);
            
            //store the data in CSV file by passing String Builder data
            out.write(data.toString().getBytes());
            out.close();

            Context context = getApplicationContext();
            final File newFile = new File(Environment.getExternalStorageDirectory(),"SimpleCVS");
            if(!newFile.exists())
            {
                newFile.mkdir();
            }

            File file = new File(context.getFilesDir(),"CSV_Data_"+time+".csv");


            Uri path = FileProvider.getUriForFile(context,"com.example.dataintocsvformat",file);

            //once the file is ready a share option will pop up using which you can share 
            // the same CSV from via Gmail or store in Google Drive
            Intent intent = new Intent(Intent.ACTION_SEND);
            intent.setType("text/csv");
            intent.putExtra(Intent.EXTRA_SUBJECT, "Data");
            intent.putExtra(Intent.EXTRA_STREAM, path);
            intent.addFlags(Intent.FLAG_GRANT_READ_URI_PERMISSION);
            startActivity(Intent.createChooser(intent,"Excel Data"));

        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    
    // checking permission To WRITE
    private boolean checkPermission() {
        int result = ContextCompat.checkSelfPermission(getApplicationContext(), android.Manifest.permission.WRITE_EXTERNAL_STORAGE);
        if (result == PackageManager.PERMISSION_GRANTED) {
            return true;
        } else {
            return false;
        }
    }

    // request permission for WRITE Access
    private void requestPermission() {
        if (ActivityCompat.shouldShowRequestPermissionRationale(MainActivity.this, android.Manifest.permission.WRITE_EXTERNAL_STORAGE)) {
            Toast.makeText(MainActivity.this, "Write External Storage permission allows us to save files. Please allow this permission in App Settings.", Toast.LENGTH_LONG).show();
        } else {
            ActivityCompat.requestPermissions(MainActivity.this, new String[]{android.Manifest.permission.WRITE_EXTERNAL_STORAGE}, PERMISSION_REQUEST_CODE);
        }
    }

    @Override
    public void onRequestPermissionsResult(int requestCode, String permissions[], int[] grantResults) {
        switch (requestCode) {
            case PERMISSION_REQUEST_CODE:
                if (grantResults.length > 0 && grantResults[0] == PackageManager.PERMISSION_GRANTED) {
                    Log.e("value", "Permission Granted, Now you can use local drive .");
                } else {
                    Log.e("value", "Permission Denied, You cannot use local drive .");
                }
                break;
        }
    }
}

My android_manifest.xml file 

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.example.dataintocsvformat">
    <uses-permission android:name="android.permission.INTERNET"/>
    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
    <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE"/>


    <application

        android:requestLegacyExternalStorage="true"
        android:networkSecurityConfig="@xml/network_security_config"
        android:usesCleartextTraffic="true"
        android:allowBackup="true"
        android:icon="@mipmap/ic_launcher"
        android:label="@string/app_name"
        android:roundIcon="@mipmap/ic_launcher_round"
        android:supportsRtl="true"
        android:theme="@style/AppTheme">
        <activity android:name=".MainActivity">
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>

        <provider
            android:name="androidx.core.content.FileProvider"
            android:authorities="com.example.dataintocsvformat"
            android:grantUriPermissions="true"
            android:exported="false">
            <meta-data
                android:name="android.support.FILE_PROVIDER_PATHS"
                android:resource="@xml/provider_path" />
        </provider>
    </application>

</manifest>

 

Conclusion

In this Android Studio Tutorial on Proto Coders Point, we have learned how to fetch data from database using PHP code and then store the response data in .csv file format using android app.