c r e a t i v e e n g i n e e r i n g a n d t e c h n o l o g i c a l a b u s e
S l u r p i n g u p a n d S p i t t i n g o u t C S V F i l e s i n R u b y w i t h F a s t e r C S V a n d R u p o r t
P o s t e d b y u n i x m o n k e y
o n M a y 0 1 , 2 0 0 8
I ’ v e g o t s o m e d a t a i n a n e x c e l f i l e t h a t I n e e d t o p u t i n t h e d a t a b a s e a n d i t s f a r t o o m u c h t o d o b y h a n d , w h a t w i l l I d o ?
L e t s t h r o w s o m e r u b y a t t h e p r o b l e m !
F i r s t , e x c e l i t t o o d a r n c o m p l i c a t e d a n d p r o p r i e t a r y a f o r m a t t o e v e n m e s s w i t h u n l e s s y o u a r e c r e a t i n g s o m e t h i n g r e a l l y w o r t h i t , s o l e t s o p e n t h a t . x l s w i t h E x c e l o r O p e n O f f i c e a n d d o a F i l e - > S a v e A s - > . c s v ( c o m m a s e p a r a t e d v a l u e s ) t o g e t a f i l e t h a t i s e a s i e r t o w o r k w i t h .
N o w , w e c o u l d w r i t e o u r o w n C S V p a r s e r s i n c e i t s s u c h a s i m p l e f o r m a t , b u t w h y f u t z w i t h i t w h e n s o m e o n e e l s e h a s a l r e a d y p u t o u t a g o o d l i b r a r y f o r t h a t w i l l l i k e l y b e m o r e e r r o r t o l e r a n t ? L e t s u s e F a s t e r C S V , a s i t s p r e t t y w e l l - k n o w n .
I n s t a l l b y i s s u i n g :
sudo gem install fastercsv
N o w y o u c a n j u s t f i r e u p s c r i p t / c o n s o l e o f y o u r R a i l s a p p a n d t y p e i n t h e b e l o w , o r j u s t p u t t h i s i n a d a t a b a s e m i g r a t i o n t o s l u r p u p a l l t h a t g o o d s p r e a d s h e e t d a t a .
T h e b e l o w a s s u m e s y o u h a v e a ‘ u s e r s ’ t a b l e w i t h f i e l d s n a m e , a d d r e s s , a n d e m a i l t h a t a r e a l s o r o w s i n y o u r e x c e l f i l e . A d j u s t a s n e c e s s a r y .
require 'fastercsv'
FasterCSV.foreach ( "#{RAILS_ROOT}/myfile.csv" ) do | row|
record = User.new (
:name => row[ 0 ] , # first column of csv file
:address => row[ 1 ] , # second column
:email => row[ 2 ] # third
)
record.save
end
require 'fastercsv'
FasterCSV.foreach("#{RAILS_ROOT}/myfile.csv") do |row|
record = User.new(
:name => row[0], # first column of csv file
:address => row[1 ], # second column
:email => row[2 ] # third
)
record.save
end
T h a t ’ s p r e t t y a w e s o m e ; n o w h o w c a n I e x p o r t t h a t s t u f f i n t h e d a t a b a s e b a c k o u t t o E x c e l a g a i n ?
L e t s u s e R u p o r t , t h e R u b y r e p o r t g e m !
sudo gem install acts_as_reportable
T o s s t h e r e q u i r e s t a t e m e n t s o m e w h e r e o b v i o u s ( l i k e e n v i r o n m e n t . r b o r a b o v e t h e m o d e l y o u w a n t t o e x p o r t ) , a n d p u t ‘ a c t s _ a s _ r e p o r t a b l e ’ i n y o u r m o d e l d e c l a r a t i o n .
require 'ruport'
class User < ActiveRecord::Base
acts_as_reportable
end
require 'ruport'
class User < ActiveRecord::Base
acts_as_reportable
end
N o w I c a n d o t h i s k i n d o f s t u f f t o e x p o r t t o a c s v f i l e ( a g a i n w i t h s c r i p t / c o n s o l e , b u t a m i g r a t i o n s h o u l d w o r k e q u a l l y w e l l ) :
c o n t e n t = U s e r . r e p o r t _ t a b l e . a s ( : c s v ) # c o n v e r t y o u r m o d e l t a b l e t o C S V
or
content = User.report_table_by_sql ( "SELECT name, address, email FROM users" ) .as ( :csv )
content = User.report_table_by_sql("SELECT name, address, email FROM users").as(:csv)
T h e n w r i t e t h a t t o a f i l e l i k e s o :
file = File .open ( "#{RAILS_ROOT}/report.csv" , "w" ) # open file
file.print ( content) # print that csv content to the open file
file.close # close the file
file = File.open("#{RAILS_ROOT}/report.csv", "w") # open file
file.print(content) # print that csv content to the open file
file.close # close the file
O p e n t h a t C S V f i l e w i t h E x c e l a n d a m a z e t h e s a l e s t e a m , y o u r b o s s , o r w h o e v e r .
T h i s i s a n a r r o w v i e w o f w h a t w e c a n d o w i t h F a s t e r C S V a n d R u p o r t , b u t I ’ m s u r e y o u c a n s e e h o w y o u c o u l d b u i l d o u t a f o r m a t . c s v i n a r e s p o n d _ t o b l o c k i n a R a i l s c o n t r o l l e r , o r h a v e a s e t t e r i n y o u r m o d e l t h a t s u c k s i n a n u p l o a d e d C S V t o c r e a t e s o m e r e c o r d s .
T h e s e a r e s o m e p r e t t y g r e a t l i b r a r i e s , a n d I ’ m v e r y g l a d t h e y w e r e a b l e t o h e l p m e l o a d , c o m b i n e , q u e r y a n d o u t p u t s o m e d a t a I ’ d b e e n w o r k i n g w i t h i n a p i n c h .
I h o p e t h i s p o s t s e r v e s t o h e l p s o m e o n e e l s e i n a s i m i l a r s i t u a t i o n .
● P o s t e d i n d a t a b a s e , p l u g i n s , P r o g r a m m i n g , R a i l s , r u b y
● M e t a
6 c o m m e n t s ,
p e r m a l i n k ,
r s s
U s e t h i s l i n k t o t r a c k b a c k f r o m y o u r o w n s i t e .
L e a v e a r e s p o n s e
Email:
(will not be published)
Url:
Comments:
Δ
I ' m a f o r m e r s y s a d m i n , u n i x w i z a r d , a n d P e r l h a c k e r t h a t g o t b i t b y t h e p r o g r a m m i n g b u g r e a l b a d w h e n I f o u n d R u b y a n d R a i l s . I w o r k a s a R u b y d e v e l o p e r a t a d a t a c e n t e r i n I n d i a n a p o l i s & I h e l p o r g a n i z e t h e I n d y . r b R u b y & R a i l s m e e t u p . .
●
R a i l s 3 M a i l g e m e r r o r c r e a t i n g m a i l
●
S e r v i n g H T M L t a b l e s a s E x c e l
●
I ’ m i n l o v e w i t h : s y m b o l . t o _ p r o c
●
R a i l s r e s p o n d _ t o r e n d e r i n g w r o n g f o r m a t i n I E
●
A c t i v e R e c o r d Q u e r y B u i l d i n g w i t h M u l t i p l e a n d O p t i o n a l C o n d i t i o n s
●
I ’ v e g o t s o m e A c t i v e R e c o r d i n m y S h o e s
●
S l u r p i n g u p a n d S p i t t i n g o u t C S V F i l e s i n R u b y w i t h F a s t e r C S V a n d R u p o r t
●
L o s i n g n e r d c r e d . S o l d m y C o m m o d o r e 1 2 8 .
●
O v e r k i l l E m a i l O b f u s c a t i o n w i t h R u b y a n d J a v a s c r i p t
●
G e t t i n g a t t a c h m e n t _ f u t o p l a y n i c e w i t h a c t s _ a s _ v e r s i o n e d
● S e p t e m b e r 2 0 1 1
● S e p t e m b e r 2 0 1 0
● A u g u s t 2 0 1 0
● D e c e m b e r 2 0 0 8
● N o v e m b e r 2 0 0 8
● M a y 2 0 0 8
● A p r i l 2 0 0 8
● M a r c h 2 0 0 8
● F e b r u a r y 2 0 0 8
● J a n u a r y 2 0 0 8
● D e c e m b e r 2 0 0 7
● N o v e m b e r 2 0 0 7
● S e p t e m b e r 2 0 0 7
2 0 0 1 - 2 0 0 6
● I n d y R u b y & R a i l s M e e t u p
● I n d y . r b R e c o r d i n g s
● M y d e l . i c i o . u s L i n k s
● M y G i t H u b P r o f i l e
● M y S t a c k o v e r f l o w P r o f i l e
● M y T w e e t s t r e a m
A c t i v e R e c o r d
A c t i v e R e c o r d R a i l s R u b y P r o g r a m m i n g D a t a b a s e S Q L
A c t s _ a s _ v e r s i o n e d
A t t a c h m e n t _ f u
B i g N e r d R a n c h
C S S
d a t a b a s e
D e p l o y m e n t
e m a i l
e n c r y p t i o n
E x c e l
H T M L
H u m o r
j a v a s c r i p t
l i n u x
o l d s c h o o l
p l u g i n s
P r o g r a m m i n g
p r o g r a m m i n g r u b y r a i l s
p r o g r a m m i n g r u b y r a i l s i e
R a i l s
r a i l s r a i l s 3 m a i l
r a m b l i n g
r u b y
r u b y p r o g r a m m i n g l i n u x
r u b y r a i l s g e m s p l u g i n s c s v d a t a b a s e
S h o e s
T e s t i n g O S X
T r a v e l
● A r t i c l e s
● C o m m e n t s
U n i x m o n k e y . n e t
● p o w e r e d b y W o r d P r e s s /
s t y l e d w i t h S c r i b b i s h W P