From: John Mcleod on
Hello all,
I'm kind of new to fastercsv and only have 2 months with Ruby on Rails.
So this my sound a little newbie.

First, I read a csv file (approx. 6,000 lines). This file comes from a
different department, so I have no control over the headers.

Second, while reading or parsing the file, the rows are inputted into a
database.

My problem is my database columns can not have special characters, but
the csv headers have the special characters.

Is there a way to remove the special characters before inserting in
database?

Here is a view of my code.

def import_irb_file
# set file name
file = params[:irb][:file]
rowcount = 0

Irb.transaction do
FasterCSV.parse(file,
:headers => true,
:header_converters => :symbol,
:converters => :all,
:encoding => 'u' ) do |row|
Irb.create!(row.to_hash)
rowcount += 1
end
end
# if successful then display, then redirect to index page
flash[:notice] = "Successfully added #{rowcount} project(s)."
redirect_to :action => :index

rescue => exception
file_name = params[:irb]['file'].original_filename
file_parts = params[:irb]['file'].original_filename.split('.')
ext = file_parts[1]

if ext != 'csv'
error = "CSV file is required"
else
error = ERB::Util.h(exception.to_s) # get the error and HTML
escape it
end
# If an exception in thrown, the transaction rolls back and we end
up in this
# rescue block

flash[:error] = "Error adding projects to IRB table. (#{error}).
Please try again. "

redirect_to :controller => 'irbs', :action => 'new'

end

Thank you for any advice.

JohnM
--
Posted via http://www.ruby-forum.com/.

From: Marnen Laibow-Koser on
John Mcleod wrote:
> Hello all,
> I'm kind of new to fastercsv and only have 2 months with Ruby on Rails.
> So this my sound a little newbie.
>
> First, I read a csv file (approx. 6,000 lines). This file comes from a
> different department, so I have no control over the headers.
>
> Second, while reading or parsing the file, the rows are inputted into a
> database.
>
> My problem is my database columns can not have special characters,
[...]

Why can't they? And are you creating a DB column for each column in the
CSV file?

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen(a)marnen.org
--
Posted via http://www.ruby-forum.com/.

From: John Mcleod on
Hello Marnen,
Thank you for replying.

I would consider the non-use of special characters in a database column
title, a matter of good form.
The only special character I use in database column titles is an
underscore and sometimes not that.

John

Marnen Laibow-Koser wrote:
> John Mcleod wrote:
>> Hello all,
>> I'm kind of new to fastercsv and only have 2 months with Ruby on Rails.
>> So this my sound a little newbie.
>>
>> First, I read a csv file (approx. 6,000 lines). This file comes from a
>> different department, so I have no control over the headers.
>>
>> Second, while reading or parsing the file, the rows are inputted into a
>> database.
>>
>> My problem is my database columns can not have special characters,
> [...]
>
> Why can't they? And are you creating a DB column for each column in the
> CSV file?
>
> Best,
> --
> Marnen Laibow-Koser
> http://www.marnen.org
> marnen(a)marnen.org

--
Posted via http://www.ruby-forum.com/.

From: James Edward Gray II on
On Nov 17, 2009, at 10:24 AM, John Mcleod wrote:

> Hello all,

Hello.

> First, I read a csv file (approx. 6,000 lines). This file comes from a
> different department, so I have no control over the headers.
>
> Second, while reading or parsing the file, the rows are inputted into a
> database.
>
> My problem is my database columns can not have special characters, but
> the csv headers have the special characters.
>
> Is there a way to remove the special characters before inserting in
> database?

Sure. FasterCSV support header_converters that can transform your headers in any way that you need. Can you show a sample header and what you would like it to become?

James Edward Gray II
From: John Mcleod on
James,
Thank you for replying.

sample headers:
Q.20C - Population May Include (Target)
IRB#

Desired headers:
Q20C_Population_May_Include_Target
IRB_id

John


James Edward Gray II wrote:
> On Nov 17, 2009, at 10:24 AM, John Mcleod wrote:
>
>> Hello all,
>
> Hello.
>
>> database?
> Sure. FasterCSV support header_converters that can transform your
> headers in any way that you need. Can you show a sample header and what
> you would like it to become?
>
> James Edward Gray II

--
Posted via http://www.ruby-forum.com/.