How to find the specific row of CSV file

Ruby
1

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.

2

On Feb 27, 2009, at 8:19 AM, Salil G. 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.

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 G. II

3

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.

4

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/ ] 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

5

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

6

On Sun, Mar 1, 2009 at 8:06 AM, Salil G. [email protected]
wrote:

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. :slight_smile:

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

7

On Mar 1, 2009, at 7:06 AM, Salil G. wrote:

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 G. II

8

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.

9

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

10

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

11

On Mar 1, 2009, at 8:55 AM, Salil G. wrote:

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 G. II

12

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

13

On Mar 2, 2009, at 4:04 AM, Salil G. wrote:

   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 G. II

14

On Mon, Mar 2, 2009 at 5:04 AM, Salil G. [email protected]
wrote:

   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.

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.

#<FasterCSV::Row “ASIN”:“B001K809QG” “ALBUM_ID”:“18736111556”
“RELATED_UPC”:nil “Track_ID”:nil “ISRC”:nil “ALBUM_TRACK”:“A”
“Units”:“1”
“COST”:“3.04” “Amount_Due”:“3.04” “ALBUM_ARTIST”:“Aaron Lacrate”
“ALBUM_NAME”:“B-more Gutter Music Vol Too! [Explicit]” “Track_Name”:nil
“LABEL_NAME”:“Delicious Vinyl”>

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

15

On Mar 2, 2009, at 9:04 AM, Salil G. wrote:

also in fastercsv when :quote_char=>"," and col_sep=>","

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 G. II

16

On Mar 2, 2009, at 9:08 AM, Craig D. wrote:

Note that I removed the first two lines of the file, since I don’t
know how to skip them.

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 G. II

17

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

18

On Tue, Mar 3, 2009 at 8:07 AM, Salil G. [email protected]
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.

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

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