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
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
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”
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.
 and store data in CSV file in android studio.){kind=link}
[…] fetch data from database and store in CSV file format android […]