separate a row of strings into separate columns using ruby

Question

I am trying to manipulate a csv file using Ruby which will separate a row of strings into separate columns. Starting with 'Part#' to create a column then move past the comma to 'Quantity' and create a second column next to it and so on... I anticipate that I will need to utilize the split method to create an array. Is this the best method and how would I paste the array into excel so that it creates rows?

I would like the same thing to happen for the rows below the header containing the actual data where it separates into S-001, 1, [Mela] etc.

Here is a sample of the csv:

Sheet Goods
Part#,Quantity,Description,Length(L),Width(W),Thickness(T),Square Foot (per),Square       Foot (total),Total Length (Feet),Material,
S-001,1, [Mela] Fridge Sides, 30",12",0 5/8",2.5,2.5,2.5,Not assigned,
S-002,1, [Mela] Fridge Sides#1,30",12",0 5/8",2.5,2.5,2.5,Not assigned,
S-003,1, [Mela] Fridge TB,32 1/4", 30",0 5/8",6.72,6.72,2.69,Not assigned,
S-004,1, [Mela] Fridge TB#1,32 1/4", 30",0 5/8",6.72,6.72,2.69,Not assigned,
S-005,1, [Mela] Fridge back,32 3/4",11 1/4",0 5/8",2.56,2.56,2.73,Not assigned,

Any help would be appreciated!

EDIT:

This is what the data should look like by the time it's done:

Sheet Goods                             
Pat#    Quantity    Description Length (L)  Thickness (T)   Square Foor (per)   Square Foot (total) Total Length (Feet) Material
S-001   1   [Mela] Fridge Sides 30   5/8    2.5 2.5 2.5 Not assigned

Where the commas are removed and the data between the commas are put into separate columns.

Mark

What should it look like after being operated on?

Linuxios
– Linuxios

2013-03-23 23:53:32 +00:00
Commented Mar 23, 2013 at 23:53 — Linuxios
– Linuxios, Commented Mar 23, 2013 at 23:53

tokland · Accepted Answer · 2013-03-24 00:05:37Z

1

First, use the libraries for the task: CSV. Secondly, it's pretty handy to have the rows indexed by column name (and not by a meaningless number). An example (where you'd get all widths):

require 'csv'

rows = CSV.open("data.csv")
name, headers = rows.take(2)

quantities = rows.map do |row_values|
   row = Hash[headers.zip(row_values)]
   # here you specific processing
   row["Width(W)"]
end

As noted by Jim, your text is not valid CSV, double quotes are reserved.

edited Mar 24, 2013 at 0:05

answered Mar 24, 2013 at 0:00

tokland

68.2k13 gold badges151 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jim Stewart Over a year ago

I definitely recommend using Ruby's core CSV library as above, but bear in mind that the CSV as pasted in the question is invalid. The quotes (") are not escaped. So long as your source CSV is actually valid, you should use @tokland's answer. If your CSV isn't valid, fix that problem first.

user2195595 Over a year ago

That is great, thank you for the advice regarding both CSV format and how to have the rows indexed. What do you mean about 'getting all widths?' I am trying to figure out how to split the data between the commas and have it paste in the adjacent column. Any idea what the structure of that specific processing would be?

tokland Over a year ago

"getting all widths" was just an example of doing something with the data. I don't understand your question of spliting between the commas.

user2195595 Over a year ago

just edited the main post to show what the data should look like. Trying to learn some basic Ruby programming as I am definitely a beginner. Thanks for the help!

Collectives™ on Stack Overflow

separate a row of strings into separate columns using ruby

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related