Solved

Read and write csv file

Posted on 2004-04-19
44
67,440 Views
Last Modified: 2011-08-18
Can i use the .csv file to store my address book information? If yes, how can i read and write the .csv files? What is the benefit of using .csv file than normal .txt file? Is't possible to make the .csv file is unreadable by user or do some encoding inside the .csv file?

thanks
0
Comment
Question by:Kennywen
  • 22
  • 7
  • 7
  • +3
44 Comments
 
LVL 1

Expert Comment

by:manifoldronin
ID: 10865358
CSV stands for Comma Separated Values.  That's the only thing you need to know to be able to read and write one - in the end, they are little more than regular text files.
Anything you can/can't do to/with a text file you can/can't do to/with a csv file.
0
 

Author Comment

by:Kennywen
ID: 10865373
any other tool that allow us to store the address book information with the encoding or make the file is unreable by user?

thanks
0
 

Author Comment

by:Kennywen
ID: 10865376
But i don't want to use mysql or other databases.
0
 
LVL 1

Expert Comment

by:manifoldronin
ID: 10865690
You can use XML which supports encoding and structural query.  It still doesn't have any built-in feature to prevent particular users from reading it, but you can always set the access rights at the OS level, can't you?
0
 

Author Comment

by:Kennywen
ID: 10865895
Can u give me some example so that i can learn from it. BTW is't the XML support insert, delete update and select function and XML can be use in any OS?
0
 

Author Comment

by:Kennywen
ID: 10866251
beside XML can anyone show me how to read and write the .csv files.

thanks
0
 

Author Comment

by:Kennywen
ID: 10866702
Actually i undestand that i can use java.util.StringTokenizer to split the data when i want to read it but how can i write the data to the .csv file?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10866738
You can encrypt any text file. Here's an example:

http://javaalmanac.com/egs/javax.crypto/PassKey.html


Writing to a csv file is as simple as writing any other file. Suppose you have each column value in an array called 'values':

// 'out' is a PrintWriter opened on a FileWriter, opened on a file
for(int i = 0;i < values.length - 1;i++) {
      out.print(values[i]);
      out.print(",")
}
out.println(values[values.length - 1]);

in your case, where 'values' appears in the print\println you'd want to call print(encrypt(values[i])) or whatever
0
 

Author Comment

by:Kennywen
ID: 10866766
should i write:

PrintWriter out = null;
for(int i = 0;i < values.length - 1;i++) {
     out.print(values[i]);
     out.print(",")
}
out.println(values[values.length - 1]);

But how can i specify the file name? If the file already exist with some data so the out.println(values[values.length - 1]); will overwrite the current data in the file? If yes, how can i append it ?

thanks
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10866785
>>But how can i specify the file name?

PrintWriter out = new PrintWriter(new FileWriter("x.csv"));


>>how can i append it ?

PrintWriter out = new PrintWriter(new FileWriter("x.csv"), true);

0
 

Author Comment

by:Kennywen
ID: 10866808
>> PrintWriter out = new PrintWriter(new FileWriter("x.csv"), true);

if i put true then it will append additional data into the csv file? else it will just overwrite the current data in the csv file?

why at the end i should write out.println(values[values.length - 1]);

thanks
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10866840
>>if i put true ...

Yes

>>why at the end...

Because you don't need a comma at the end of the line

0
 

Author Comment

by:Kennywen
ID: 10866861
then the csv file will contain information like :
Smith Joe, M, UK, 22
Smith Joe, M, UK, 22
Smith Joe, M, UK, 22

every new data will in the different row?

Can i count there is how many rows in the csv file?

thanks
0
 

Author Comment

by:Kennywen
ID: 10866879
The read file:

int counter=0;

BufferedReader buf = new BufferedReader(new FileReader(new File("data.csv);
            while ( (text = buf.readLine()) != null) {
               String split = line.split(",");
               >> how can i put the two dimensional array in here?
               counter++;
            }

thanks


0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10866881
>>every new data will in the different row?

>>Can i count there is how many rows in the csv file?

why would you want to do that btw?
0
 

Assisted Solution

by:johnknapp
johnknapp earned 50 total points
ID: 10866891
I am currently writing a software program which generates a number of different types of files, one of which is csv, and it is the easiest to create.

My preferred way is to create a data object with a similar type of method as getFields():

import java.util.Iterator;

public class DataRecord {

    private int id;
   
    private String name;
   
    private String phone;

    // regular getter setter methods
   
    public void setId(int id) { this.id = id; }
   
    public int getId() { return (id); }
   
    public void setName(String name) { this.name = name; }
   
    public String getName() { return (name); }
   
    public void setPhone(String phone) { this.phone = phone; }
   
    public String getPhone() { return (phone); }
   
    /**
     * Gets an array of all fields in the data object
     * promoted to strings for easy writing to the file
     * since CSV files are text anyway, string is the perfect
     * choice
     * @return An array of strings of all fields in the data object
     */
    public String[] getFields() {
        String[] fields = new String[3];
        fields[0] = String.valueOf(id);
        fields[1] = name;
        fields[2] = phone;
        return (fields);
    }
   
}

then create a simple writer, this one allows you to specify if you would like the fields quoted or not (depends if your data has internal commas) such as if the name field is "last, first" than you would want it quoted

import java.io.*;

public class CSVWriter {

    private File file;

    private BufferedWriter writer;
   
    private boolean quotedFields;
   
    private String pnl;
   
    public CSVWriter(File file, boolean quotedFields) {
        this.file = file;
        this.quotedFields = quotedFields;
        pnl = System.getProperty("line.separator");
    }
   
    public void open() throws IOException {
        FileWriter fw = new FileWriter(file);
        writer = new BufferedWriter(fw);
    }
   
    public void writeRecord(DataRecord dr) throws IOException {
        String[] fields = dr.getFields();
        for (int i = 0; i < fields.length; i++) {
            writer.write(wrapQuotes(fields[i]));
            if (i + i < fields.length)
                writer.write(",");
            else
                writer.write(pnl); // must be end of record so write the new line
        }
    }
   
    private String wrapQuotes(String val) {
        return ((quotedFields ? "\"" + val + "\"" : val));
    }
   
    public void close() throws IOException {
        if (writer != null) {
            writer.flush();
            writer.close();
        }
    }
   
}

then simply iterate through the collection of data records inserting commas, quote marks, and new lines as necessary

Vector records; // holds a bunch of data objects

String fileName = "mycsvfile.csv"; // the sample file name

CSVWriter csvWriter = new CSVWriter(new File(fileName), false); // constructs the writer
try {
    csvWriter.open();
    Iterator iter = records.iterator();
    while (iter.hasNext()) {
        csvWriter.writeRecord((DataRecord) iter.next());
    }
    csvWriter.close();
} catch (IOException ioe) {
   // handle io exception
}

And That is about all there is to it, you can do better and if performance is an issue this wouldn't be the perfect solution, but it works, the file would come out in the format
0,john,5555555
1,joe,5555555
2,carol,555555
etc ...

or, if we specified true in the second parameter of the CSVWriter it would look like
"0","john","5555555"
"1","joe","5555555"
"2","carol","5555555"
etc ...

either of which are valid CSV formats

If you would like to store data in a format that someone couldn't read and you just want it quick and dirty and your data object or record is serializable or externalizable you could simply open a ObjectOutputStream over a file output stream and call writeObject(Object o);

It isn't the best way to handle it, but it works (but isn't platform portable) different versions of the jre serialize objects differently.

I would however, as best practice generally look for a versitile format like XML, its verbose and sometimes impractical, but is much better for data exchange (which is generally what CSV was originally designed to do).

Hope That Helps,
John



0
 

Author Comment

by:Kennywen
ID: 10866906
>> why would you want to do that btw?

i want to count there is how many rows in the files so that i can create a two dimensional array.
0
 

Author Comment

by:Kennywen
ID: 10866913
I want to create two dimensional array like;
private Object[][] data = {
            {"Mary", "Campione",
             "Snowboarding", new Integer(5), new Boolean(false)},
            {"Alison", "Huml",
             "Rowing", new Integer(3), new Boolean(true)},
            {"Kathy", "Walrath",
             "Knitting", new Integer(2), new Boolean(false)},
            {"Sharon", "Zakhour",
             "Speed reading", new Integer(20), new Boolean(true)},
            {"Philip", "Milne",
             "Pool", new Integer(10), new Boolean(false)}
        };
0
 

Author Comment

by:Kennywen
ID: 10867129
Please show me how to read the file and assign it to two dimensional array like:
private Object[][] data = {
            {"Mary", "Campione",
             "Snowboarding", new Integer(5), new Boolean(false)},
            {"Alison", "Huml",
             "Rowing", new Integer(3), new Boolean(true)},
            {"Kathy", "Walrath",
             "Knitting", new Integer(2), new Boolean(false)},
            {"Sharon", "Zakhour",
             "Speed reading", new Integer(20), new Boolean(true)},
            {"Philip", "Milne",
             "Pool", new Integer(10), new Boolean(false)}
        };


my sample file:
BufferedReader buf = new BufferedReader(new FileReader(new File("data.csv);
            while ( (text = buf.readLine()) != null) {
               String split = line.split(",");
               >> how can i put the two dimensional array in here?
               counter++;
            }

thanks
0
 
LVL 13

Expert Comment

by:Webstorm
ID: 10867216
>>  if (i + i < fields.length)
You probably mean :
    if (i + 1 < fields.length)

0
 
LVL 13

Assisted Solution

by:Webstorm
Webstorm earned 150 total points
ID: 10867250
Hi Kennywen,

You need to count the number of lines:

            BufferedReader buf = new BufferedReader(new FileReader(new File("data.csv);
            while ( (text = buf.readLine()) != null) {
               if (text.trim().length()>0)   counter++; // count non-empty lines
            }

Then you can store data in the array :

            data = new Object[counter][5];
            BufferedReader buf = new BufferedReader(new FileReader(new File("data.csv);
            counter=0;
            while ( (text = buf.readLine()) != null) {
               text=text.trim();
               if (text.length()==0)  continue; // empty line
               String[] split = line.split(",");
               for (int j=0;j<5;j++) // for each field:
               {
                   switch(j)
                   {
                       case 3: data[counter] = new Integer(text); break;
                       case 4: data[counter] = new Boolean(text); break;
                       default: data[counter] = text; break;
               }
                counter++;
            }
0
 
LVL 13

Expert Comment

by:Webstorm
ID: 10867256
Replace :
           BufferedReader buf = new BufferedReader(new FileReader(new File("data.csv);
By :
           BufferedReader buf = new BufferedReader(new FileReader("data.csv"));
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 86

Accepted Solution

by:
CEHJ earned 50 total points
ID: 10868529
I assume those data (in the array) are for the purposes of a TableModel. You don't need to construct your TableModel from an array - you can create it from a Vector of Vectors, then you don't need to bother about counting lines.

Vector data = new Vector();
// in loop
String[] values = lineJustRead.split(",");
Vector row = new Vector();
for(int i = 0;i < values.length;i++) {
      row.add(values[i].trim()); // adjust if different data types required
}
data.add(row);

// After loop

DefaultTableModel dtm = new DefaultTableModel(data, otherVectorContainingColumnNames);
0
 

Author Comment

by:Kennywen
ID: 10873918
How can i update and delete the information in the csv file?

thanks
0
 

Author Comment

by:Kennywen
ID: 10874303
Is't possible to perform update and delete in the csv file without using sql statement?

thanks
0
 

Author Comment

by:Kennywen
ID: 10874880
is't possible just delete a specify line in the csv file?
0
 

Expert Comment

by:johnknapp
ID: 10875450
It the csv file is not that large why not cache it all in memory and then rewrite it when you are finished modifying it,

and why would you be using a SQL statement?
0
 

Author Comment

by:Kennywen
ID: 10875454
>> why would you be using a SQL statement?

because i just afraid i cannot delete or update the csv file without using sql statement.
0
 

Expert Comment

by:johnknapp
ID: 10875462
I guess the question is what exactly are you trying to achieve by using a CSV file?  What data and how much of it are you trying to store.  CSV Files are really designed for quick write and read not really for modifications.  The strong point about using csv is easy implementation, for more sophisticated operations on files I would recommend possibly keeping an index into the file stored separately - a record, byte location kind of key file and then make sure modifications to the csv file always happen through the same interface to make sure the index stays correct with the file.
0
 

Expert Comment

by:johnknapp
ID: 10875535
I am not sure if the scope of your original question has changed, and I am still unclear as to how you would even use a SQL query to perform an operation on a CSV file . . . are you using a JDBC driver that operates on CSV files?

Which I have never heard of myself.

If you want to change a CSV file and then save it and it is not a huge file, say under 10,000 records or so try something like this . . .

1) read file into 2 dimensional object array (is that the data type you want to use?)

2) modify the elements accordingly

3) rewrite the file, ie open the file writer with ...
FileWriter fileWriter = new FileWriter("somecsvfile.csv", false); // so as to completely write over the existing file

4) reiterate over you modified object array and write out each element.
Object[][] some2dArray; // initialized elsewhere, say an array of strings for sake of example
BufferedWriter writer = new BufferedWriter(fileWriter); // since this will give us easy string processing
for (int i = 0; i < some2dArray.length; i++) {
    for (int inner = 0; inner < some2dArray[i].length; inner++) {
        writer.write(some2dArray[i][inner].toString());
        if (inner + 1 < some2dArray[i].length)
            writer.write(",");
        else
            writer.write(System.getProperty("line.separator", "\n"));
    }
}
writer.flush();
writer.close();

// handle all IOExceptions omitted for clarity
0
 

Author Comment

by:Kennywen
ID: 10875567
So i need to write the csv file to some2dArray first and then change the value (if user press the edit button) or delete the value (if user press delete button) and then write it into the file again?

thanks
0
 

Expert Comment

by:johnknapp
ID: 10875586
Sort of,

The user will be operating on the array for some amount of time correct? When the user is finished this is when you commit the changes to the file.

So this way (I am assuming this is kind of what you were talking about in pseudo-pseudo code)

1) User opens program component

2) Load the file into a 2d object array or whatever data structure you feel is appropriate

3) User clicks edit button or delete button

4) Modify array

5) User repeats process 3 n times, each time your code repeats 4 n times

6) User finishes editing

7) resave file
0
 

Author Comment

by:Kennywen
ID: 10875619
No, i will not load the file into a 2d object array when user run the program.
The csv file is to keep the address book information. so when the user press edit button then a pop up window will display then the user can edit it. once the user press ok button then i will  Load the file into a 2d object array.

Can i use the above approach?

thanks
0
 

Author Comment

by:Kennywen
ID: 10875926
Object[] data = new Object[3];
data[0] = new String(nameField.getText());
data[1] = new String(mobileField.getText());
data[2] = new String(emailField.getText());

PrintWriter out = new PrintWriter(new FileWriter("phone.csv"), true);
for(int i = 0;i < data.length - 1;i++) {
      out.print(data[i]);
      out.print(",");
}
out.println(data[data.length - 1]);

The above code will not append the file. why?
0
 

Author Comment

by:Kennywen
ID: 10875967
i get the answer, i should use:

PrintWriter out = new PrintWriter(new BufferedWriter( new FileWriter ("phone.csv", true)));

0
 

Expert Comment

by:johnknapp
ID: 10876016
one quick thing with your sample code,

Make sure if your data has any possibility of ever having a comma embedded in the field (seems unlikely in your case unless the name field is last,first)

to wrap the data in quotes
such as
"name","mobile","email"
"name2","mobile2","email2"

otherwise you will have troubles reading it (but only in the case that your data could contain embedded commas)
0
 

Author Comment

by:Kennywen
ID: 10876174
Object[][] data = new Object[counter][3];
            BufferedReader buf1 = new BufferedReader(new FileReader("phone.csv"));
            counter=0;
            while ( (text = buf1.readLine()) != null) {
               text=text.trim();
               if (text.length()==0)  continue; // empty line
               String[] split = text.split(",");
               for (int j=0;j<5;j++) // for each field:
               {
                   switch(j)
                   {
                       //case 3: data[counter] = new Integer(text); break;
                       //case 4: data[counter] = new Boolean(text); break;
                       default: data[counter] = split[j]; break;
                           }
               }
                counter++;
            }

an error occur:
BoxAlignmentDemo.java:588: incompatible types
found   : java.lang.String
required: java.lang.Object[]
default: data[counter] = split[j]; break;
                                         ^
1 error
0
 
LVL 13

Expert Comment

by:Webstorm
ID: 10876235
Replace :
  data[counter] =
By :
  data[counter][j] =
0
 

Expert Comment

by:johnknapp
ID: 10876254
You can't assign a string to an object array reference

you should do

data[counter][j] = split[j];

or in context

default: data[counter][j] = split[j]; break;

try that, but why the switch (j) if you aren't using the switch construct for what it is useful for?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10876607
You can use a class like this to add/delete lines in a csv file. e.g.:

StringList sl = new StringList("your.csv");
sl.remove(1); // remove first line after column headers





SNIP==================================

import java.io.*;
import java.util.ArrayList;

  public class StringList extends ArrayList {

  public StringList() {
    super();
  }

  public StringList(String fileName) {
    this();
    String line = null;
    BufferedReader in = null;
    try {
      in = new BufferedReader(new FileReader(fileName));
      while ((line = in.readLine()) != null) {
        add(line);
      }
    }
    catch (IOException e) {
      e.printStackTrace();
    }
    finally {
      try {
        in.close();
      }
      catch (IOException e) {
        e.printStackTrace();
      }
    }
  }
}
0
 

Author Comment

by:Kennywen
ID: 10895492
thanks to all
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 10897798
8-)
0
 
LVL 13

Expert Comment

by:Webstorm
ID: 10897891
:-)
0
 

Expert Comment

by:geckomaniak44
ID: 11091712
There's a few jdbc drivers that you can use rather than such a low level solution.
For read-only, I've had good success with csvjdbc.jar. There are a few of them at sourceforge.

HTH - rich
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This video teaches viewers about errors in exception handling.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now