# How to find the specific row of CSV file
**URL:** https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028
**Category:** ruby-talk
**Created:** [27 February 2009 14:19 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028 "2009-02-27T14:19:36Z")
**Posts on this page:** 18
**Page:** 1
### Author:  [@Salil\_Gaikwad](https://rubytalk.org/u/Salil_Gaikwad)
#### Post date: [27 February 2009 14:19 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/1 "2009-02-27T14:19:36Z")
I want to change or read only specific row of CSV file, i don't know how
to do that.i.e. In my CSV file there are 10 no. of lines i want to
read/write only 5th one how to do that.
Any help is appreciated.
> **···**
>
> --
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
---
### Author:  [@James\_Edward\_Gray\_II](https://rubytalk.org/u/James_Edward_Gray_II)
#### Post date: [27 February 2009 15:52 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/2 "2009-02-27T15:52:57Z")
The steps I would use:
1. Open the file you want to read with FasterCSV::open()
2. Open a different file to write into with FasterCSV::open()
3. Use each() to read the rows from the in file
4. Inside the each() block, write the rows to the out file
5. Add some code before the write that changes the line you want to change (if you really want an exact row, FasterCSV#lineno() can help)
6. Rename new file to replace old (if you don't want to save the old data)
If you get stuck along the way, send us your code and we will try to help.
James Edward Gray II
> **···**
>
> On Feb 27, 2009, at 8:19 AM, Salil Gaikwad wrote:
>
> > I want to change or read only specific row of CSV file, i don't know how
> > to do that.i.e. In my CSV file there are 10 no. of lines i want to
> > read/write only 5th one how to do that.
> >
> > Any help is appreciated.
---
### Author:  [@Salil\_Gaikwad](https://rubytalk.org/u/Salil_Gaikwad)
#### Post date: [28 February 2009 05:00 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/3 "2009-02-28T05:00:32Z")
My code is as follows
@parsed\_file=CSV::Reader.parse(File.open("auctiva.csv", 'rb'))
@parsed\_file.each do |row|
if row[last]=="XYZ" //
// some more code here
end
end
i want to run my code only for last row and also if row[last]=="XYZ".
There are some more requirement 4 my project
if row[last]=="XYZ" is FALSE
the loop should start from 1st row.
> **···**
>
> --
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
---
### Author:  [@Craig\_Demyanovich](https://rubytalk.org/u/Craig_Demyanovich)
#### Post date: [28 February 2009 15:04 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/4 "2009-02-28T15:04:28Z")
First, let's create a rough, high-level solution to your problem.
read the last line of a file
if the last line matches a pattern
process only the last line
else
process the file starting at the first line
end
If that's accurate, the next step that I'd take is figuring out ways to read
the last line of the file. You want to use FasterCSV. If I go to the
FasterCSV site [[http://fastercsv.rubyforge.org/](http://fastercsv.rubyforge.org/) ] and click on the class
FasterCSV, I see that I can read all lines from a file like this:
arr\_of\_arrs = FasterCSV.read("path/to/file.csv")
If the file isn't large, then reading it all into memory is probably fine.
If it is large, you should adapt what follows to use a solution like James
proposed where you read a line, process it and write it instead of reading
everything into memory.
Now that I have the whole file in an array of arrays (
[[field1,field2,field3],[field1,field2,field3]] ), I can just ask for the
last line, so to speak:
last\_line = arr\_of\_arrs.last
and check it for the match:
# I'm making an assumption about the structure of the last line being
something like
# XYZ,some content,some more content,end of line
# which would've been parsed into an array
# ["XYZ","some content","some more content","end of line"]
# and you just wanting to check the first field on the last line
if last\_line.first == "XYZ"
do something based on the contents of last\_line
else
# since we have the whole file parsed into an array of arrays by
FasterCSV,
# just process the whole thing from the beginning
arr\_of\_arrs.each do |line|
# make some changes to content
# write changed line to new csv file
end
end
From this point, hopefully you can fill in what needs to be filled in. Don't
hesitate to write back if we can help further.
Regards,
Craig
> **···**
>
> --
> Craig Demyanovich
> Mutually Human Software
>
> > **[Mutually Human](https://www.mutuallyhuman.com/)**
> >
> > Mutually Human designs & develops web, mobile, and embedded applications.
---
### Author:  [@Salil\_Gaikwad](https://rubytalk.org/u/Salil_Gaikwad)
#### Post date: [1 March 2009 13:06 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/5 "2009-03-01T13:06:19Z")
thanx james & craig
it works 4 me but is it right to use for a csv file which has more than
5000 rows?
also
I use following method to read CSV file.
@parsed\_file=FasterCSV.read("#{RAILS\_ROOT}/.....",:col\_sep=\>",",
:quote\_char=\>",", :force\_quotes=\>true)
@parsed\_file.each\_with\_index do |row, index|
// more code here
end
My problem is that when there is a special characters in csv row-field
it gives me either an error or wrong value for ex. "\".63\"" for .63
what should i do so that it accept and hence read all the characters &
special characters so that i save it into the database(mysql) correctly.
Regards,
Salil
> **···**
>
> --
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
---
### Author:  [@Craig\_Demyanovich](https://rubytalk.org/u/Craig_Demyanovich)
#### Post date: [1 March 2009 14:16 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/6 "2009-03-01T14:16:42Z")
> thanx james & craig
>
> it works 4 me but is it right to use for a csv file which has more than
> 5000 rows?
I don't think that there's any intrinsic reason not to use CSV for a 5000
line file. However, if you're bulk-loading a database, I'd try to use your
database's tools for that. In your case, though, you want to apply some
custom logic to decide what to do. So we're playing w/ CSV. 🙂
> also
> I use following method to read CSV file.
>
> @parsed\_file=FasterCSV.read("#{RAILS\_ROOT}/.....",:col\_sep=\>",",
> :quote\_char=\>",", :force\_quotes=\>true)
> @parsed\_file.each\_with\_index do |row, index|
> // more code here
>
> end
>
> My problem is that when there is a special characters in csv row-field
> it gives me either an error or wrong value for ex. "\".63\"" for .63
>
> what should i do so that it accept and hence read all the characters &
> special characters so that i save it into the database(mysql) correctly.
What strikes me as odd is that you have comma as both the :col\_sep and the
:quote\_char. The FasterCSV defaults are comma for :col\_sep and " for
:quote\_char.
Can you give the whole row that contains the .63 value and that isn't
working? If so, maybe we can come up with code that will parse it into the
values that you want.
Regards,
Craig
> **···**
>
> On Sun, Mar 1, 2009 at 8:06 AM, Salil Gaikwad \ wrote:
>
> --
> Craig Demyanovich
> Mutually Human Software
>
> > **[Homepage | Mutually Human](https://www.mutuallyhuman.com/)**
> >
> > Technology Made for Humans by humans Data Analytics Data Analytics Data has become a critical part of business for most organizations. We work with you to ensure you have confidence in your data, can properly access it, and present it in a way that...
---
### Author:  [@James\_Edward\_Gray\_II](https://rubytalk.org/u/James_Edward_Gray_II)
#### Post date: [1 March 2009 16:20 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/7 "2009-03-01T16:20:25Z")
> it works 4 me but is it right to use for a csv file which has more than
> 5000 rows?
It would probably be better to iterate over the rows and deal with them one at a time if possible.
> also I use following method to read CSV file.
>
> @parsed\_file=FasterCSV.read("#{RAILS\_ROOT}/.....",:col\_sep=\>",",
> :quote\_char=\>",", :force\_quotes=\>true)
> @parsed\_file.each\_with\_index do |row, index|
> // more code here
>
> end
As Craig said, you don't want the same quote character as your column separator. That makes the data impossible to parse correctly.
> My problem is that when there is a special characters in csv row-field
> it gives me either an error or wrong value for ex. "\".63\"" for .63
I'm almost certain this is caused by your bad quote character setting.
> what should i do so that it accept and hence read all the characters &
> special characters so that i save it into the database(mysql) correctly.
If you are in control of the output and input, just let FasterCSV spit out the data with normal CSV rules and escaping. It will then read it back in just as it was.
James Edward Gray II
> **···**
>
> On Mar 1, 2009, at 7:06 AM, Salil Gaikwad wrote:
---
### Author:  [@Salil\_Gaikwad](https://rubytalk.org/u/Salil_Gaikwad)
#### Post date: [1 March 2009 14:55 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/8 "2009-03-01T14:55:36Z")
thanx for quick reply
sorry i didn't get that row but i find out different with same problem
please find the attachment it gives me error (not exactly but not saved
true value in database)on line no 4 as given below.
B001K809QG,18736111556,A,1,3.04,3.04,Aaron Lacrate,B-more Gutter
Music Vol Too! [Explicit],Delicious Vinyl
> What strikes me as odd is that you have comma as both the :col\_sep and
> the
> :quote\_char. The FasterCSV defaults are comma for :col\_sep and " for
> :quote\_char.
ya i know but i want one special character as :quote\_char which not in a
csv file so i try comma if csv file is comma separator, bar for bar
separator and so on.....
also i don't understand what exactly use of :quote\_char but it solve my
problem of " while using CSV so i go 4 fastercsv.
but it gives me problem if row field has same character as in a
quote\_char or if 4 row-fields are null.
Attachments:
[http://www.ruby-forum.com/attachment/3371/AUBI\_Summary\_Statement\_UK\_12-2008.csv](http://www.ruby-forum.com/attachment/3371/AUBI_Summary_Statement_UK_12-2008.csv)
> **···**
>
> --
> Posted via [http://www.ruby-forum.com/\](http://www.ruby-forum.com/%5C).
---
### Author:  [@James\_Edward\_Gray\_II](https://rubytalk.org/u/James_Edward_Gray_II)
#### Post date: [1 March 2009 16:23 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/9 "2009-03-01T16:23:14Z")
> thanx for quick reply
> sorry i didn't get that row but i find out different with same problem
> please find the attachment it gives me error (not exactly but not saved
> true value in database)on line no 4 as given below.
>
> B001K809QG,18736111556,A,1,3.04,3.04,Aaron Lacrate,B-more Gutter
> Music Vol Too! [Explicit],Delicious Vinyl
How should this data be read? Does a simple:
fields = line.split(",")
work for this case?
> > What strikes me as odd is that you have comma as both the :col\_sep and
> > the
> > :quote\_char. The FasterCSV defaults are comma for :col\_sep and " for
> > :quote\_char.
>
> ya i know but i want one special character as :quote\_char which not in a
> csv file so i try comma if csv file is comma separator, bar for bar
> separator and so on.....
FasterCSV handle's proper escaping if the character is in your file, so you shouldn't need to worry about this.
James Edward Gray II
> **···**
>
> On Mar 1, 2009, at 8:55 AM, Salil Gaikwad wrote:
---
### Author:  [@Salil\_Gaikwad](https://rubytalk.org/u/Salil_Gaikwad)
#### Post date: [2 March 2009 10:04 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/10 "2009-03-02T10:04:00Z")
it doesn't work.
I am going to basic
file="C:/Documents and Settings/All Users/Documents/auctiva2.csv"
File.open(file,"r").each\_line("\r") {|sFile|
for row in sFile.split(",")
#More code here
end
}
but it gives me error when line separator is \n.
is it a bug that fastercsv/csv can't read all the special characters at
once.
because i try all the possible things i can do if u have any solution
please explain in detail.
Regards,
Salil
> **···**
>
> --
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
---
### Author:  [@Salil\_Gaikwad](https://rubytalk.org/u/Salil_Gaikwad)
#### Post date: [2 March 2009 15:04 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/11 "2009-03-02T15:04:57Z")
also in fastercsv when :quote\_char=\>"," and col\_sep=\>","
then if in data fields like this comes up "abc,pqr" it gives invalid
data as a output.
please help me out i am very confused i don't know what to do?
Regards
Salil
> **···**
>
> --
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
---
### Author:  [@Craig\_Demyanovich](https://rubytalk.org/u/Craig_Demyanovich)
#### Post date: [2 March 2009 15:08 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/12 "2009-03-02T15:08:20Z")
If I take your earlier attached file of MP3 sales, this simple program
appears to me to parse it correctly.
require 'fastercsv'
FasterCSV.foreach('AUBI\_Summary\_Statement\_UK\_12-2008.csv', :headers =\>
:first\_row) do |row|
puts row.inspect
end
Note that I removed the first two lines of the file, since I don't know how
to skip them. Then, I just treat what's now the first line as a header row.
Each line is then parsed into an instance of FasterCSV::Row. Here's what the
first row looks like when inspected.
#\
To access a specific value, you could do something like
row["ASIN"]
which would return "B001K809QG".
By the way, I don't know what to do w/ the last two lines of the MP3 sales
file. They look like junk to me right now.
Regards,
Craig
> **···**
>
> On Mon, Mar 2, 2009 at 5:04 AM, Salil Gaikwad \ wrote:
>
> > it doesn't work.
> > I am going to basic
> >
> > file="C:/Documents and Settings/All Users/Documents/auctiva2.csv"
> > File.open(file,"r").each\_line("\r") {|sFile|
> > for row in sFile.split(",")
> >
> > #More code here
> >
> > end
> >
> > }
> >
> > but it gives me error when line separator is \n.
> >
> > is it a bug that fastercsv/csv can't read all the special characters at
> > once.
> > because i try all the possible things i can do if u have any solution
> > please explain in detail.
>
> --
> Craig Demyanovich
> Mutually Human Software
>
> > **[Homepage | Mutually Human](https://www.mutuallyhuman.com/)**
> >
> > Technology Made for Humans by humans Data Analytics Data Analytics Data has become a critical part of business for most organizations. We work with you to ensure you have confidence in your data, can properly access it, and present it in a way that...
---
### Author:  [@James\_Edward\_Gray\_II](https://rubytalk.org/u/James_Edward_Gray_II)
#### Post date: [2 March 2009 15:57 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/13 "2009-03-02T15:57:25Z")
> it doesn't work.
> I am going to basic
>
> file="C:/Documents and Settings/All Users/Documents/auctiva2.csv"
> File.open(file,"r").each\_line("\r") {|sFile|
> for row in sFile.split(",")
>
> #More code here
>
> end
>
> }
>
> but it gives me error when line separator is \n.
Well, you are telling Ruby you want lines ending in "\r" with the parameter you passed to each\_line(). That does mean lines ending in "\n" will be a problem. I think you need a new strategy here, if the file has both types of line ending (a bad thing).
> is it a bug that fastercsv/csv can't read all the special characters at
> once.
FasterCSV is not used in the above code, so I don't see how this is related.
James Edward Gray II
> **···**
>
> On Mar 2, 2009, at 4:04 AM, Salil Gaikwad wrote:
---
### Author:  [@Salil\_Gaikwad](https://rubytalk.org/u/Salil_Gaikwad)
#### Post date: [2 March 2009 15:52 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/14 "2009-03-02T15:52:19Z")
Thanx craig.
i try this but it doesn't work
please check following post i try this ....
[http://www.ruby-forum.com/topic/180237#789081](http://www.ruby-forum.com/topic/180237#789081)
> **···**
>
> --
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
---
### Author:  [@James\_Edward\_Gray\_II](https://rubytalk.org/u/James_Edward_Gray_II)
#### Post date: [2 March 2009 15:59 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/15 "2009-03-02T15:59:00Z")
I've told you before, these settings will break FasterCSV. They don't make any sense. You're going to have to try something different.
James Edward Gray II
> **···**
>
> On Mar 2, 2009, at 9:04 AM, Salil Gaikwad wrote:
>
> > also in fastercsv when :quote\_char=\>"," and col\_sep=\>","
---
### Author:  [@James\_Edward\_Gray\_II](https://rubytalk.org/u/James_Edward_Gray_II)
#### Post date: [2 March 2009 16:06 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/16 "2009-03-02T16:06:19Z")
You could do something like:
File.open(…) do |f|
2.times do
f.gets
end
FCSV.new(f, …).each do |row|
…
end
end
James Edward Gray II
> **···**
>
> On Mar 2, 2009, at 9:08 AM, Craig Demyanovich wrote:
>
> > Note that I removed the first two lines of the file, since I don't know how to skip them.
---
### Author:  [@Salil\_Gaikwad](https://rubytalk.org/u/Salil_Gaikwad)
#### Post date: [3 March 2009 13:07 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/17 "2009-03-03T13:07:54Z")
Hi all
i am using following method to read the csv file and i want to save it
into the database.
@parsed\_file=CSV.open("filename.csv",'r',"#{col\_sep}")
@parsed\_file.each\_with\_index do |row, index|
// more code here
end
end
my problem is i am unable to read data correctly when my data-fields
are as follows
"abc", "sdfds"string"ddsfdsf", "xyz", "pqr"
it gives me error illegal file format at above line.
Regards
Salil
> **···**
>
> --
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).
---
### Author:  [@Greg\_Brown1](https://rubytalk.org/u/Greg_Brown1)
#### Post date: [3 March 2009 15:14 UTC](https://rubytalk.org/t/how-to-find-the-specific-row-of-csv-file/52028/18 "2009-03-03T15:14:22Z")
Please read the CSV RFC before posting more questions. It should
clear a lot of things up:
[http://www.rfc-editor.org/rfc/rfc4180.txt](http://www.rfc-editor.org/rfc/rfc4180.txt)
The Ruby CSV library on 1.9 (FasterCSV on 1.8) is very strict about
how it parses CSV. If you want something a little more loose, you'll
need to roll your own parser.
-greg
> **···**
>
> On Tue, Mar 3, 2009 at 8:07 AM, Salil Gaikwad \ wrote:
>
> > my problem is i am unable to read data correctly when my data-fields
> > are as follows
> > "abc", "sdfds"string"ddsfdsf", "xyz", "pqr"
> >
> > it gives me error illegal file format at above line.