Android Question Saving SQlite records to csv table..

[Beja]

Hi,
I have a large Excel CSV file, from which only a few records are read into SQLite table.. is it possible that I can edit those records and then save them back to the original CSV file, with each record in its original place before parsing it to the SQLite?
Example:
1- Original csv file contains: "aaa","bbb" ,"ccc","ddd" (single record)

2- I read the above record in a new SQLite table and change the "ccc" to "EEE" and saved to the original csv file.

3- Now the original csv file should read: "aaa","bbb" ,"EEE","ddd"

4- In real world the csv file has 1000 records and I want to change or edit records number 3, 6, 12, 44 and save them back.


Thanks in advance

 

[klaus]

I have a large Excel CSV file, from which only a few records are read into SQLite table.. is it possible that I can edit those records and then save them back to the original CSV file, with each record in its original place before parsing it to the SQLite?

I'm afraid that this is not easy.

Do you really need to maintain the csv file ?
Couldn't you transform the csv file once into a SQLite database and then use only this one, this would be much easier.

2) Read a single record, not sure if it can be done easily. Why save the record in a SQLite databse to change it ?

4)Not sure if you can update single records in a csv file.

 

[nwhitfield]

What are you using the database for? Is it really necessary

Personally, I'd do something like:

Read the original file into a list where, perhaps, each item is an array of strings, so each element of the list is a line of the file, and each line has one array element per CSV field (or make each element into a map, so you can refer to them by name, if that makes it easier to remember what parts you're editing)

You can change an item in the data by altering the appropriate part of the array or map, and you can write it back to a CSV fairly simply if you need to.

If you have to link with a database for some reason, then add an extra column to the database which stores the index of that entry in the list. That will enable you to find the appropriate line and ensure it's also updated.

 

[nwhitfield]

An addendum to that - depending on how long the real CSV items are, and how many, storing them all in a list may, obviously, eat up memory. If that's likely to be an issue, then instead simply read it one line at a time, and write to a temp file; if the line is one that's marked as changed in the database, create a new one from that, instead. At the end, close both files. Rename the original to something like "input.csv.old" and rename the new one to whatever name the old one had originally.

See http://www.b4x.com/android/forum/threads/text-files.6690/ for more tips.

 

[Beja]

Thanks Klaus and nwhitfield,
One mistake in the post I must correct first and I apologize if caused you waste of time:
Not a few record, but all the records but a few fields from each record.. so the record count is quote]same. Sorry
@klaus

Do you really need to maintain the csv file ?

Yes, because there is a third party tool that will process the csv file extensively and it will need a lot of work and time if
I wrote a similar tool. What I need to do is edit a few fields in each record and then pass the csv file to that tool.

@nwhitfield
Apologize to you also and I think you built your solution on the assumption that only a few records need to be edited. I owe you one!

 

[klaus]

You could use the Table Class.
Define a Table like Table1 and:
- Load the csv file with Table1.LoadTableFromCSV.
- When selecting a row get the record entries with Table1.GetValues.
- Modify the values somewhere.
- Update the table with Table1.UpdateRow.
- Save the table with Table1.SaveTableToCSV.

 

[Beja]

Thanks Klaus,
will act accordingly