0

This is my first time copying a CSV file to my Rails 4 Heroku app.
Following the steps below receives an error message.

Message received from command line:

ERROR:  invalid input syntax for integer: "Employee personnel files"
CONTEXT:  COPY articles, line 29, column id: "Employee personnel files"

Using this command: 

PGPASSWORD=PWHERE psql -h HOSTHERE -U USERHERE DBNAMEHERE -c "\copy articles FROM 'lib/articles' WITH CSV HEADER;"

Here's a snippet of the CSV file: 

 line 1 "Title","Body"
 line 2 "Employee personnel files","
    As an HR professional you are no stranger to paperwork. It seems that for every employment action - applying, interviewing, hiring, disciplining, on and on - there is a specific form that needs to be filled out. Making sure you complete the paperwork properly is only half the battle though. Once you finish completing a form, you are faced with a whole new issue: what to do with it.  Being the smarty that you are, you know that proper documentation is key in protecting your company in the unfortunate case of a lawsuit, but knowing what needs to be kept where and for how long and who can see it can be kind of tricky. Let's take a minute to go over the basics.

...

 line 29 Looking for more sample polices and important forms? Click here to gain access."

Any suggestions on what is missing?

jgrant
  • 582
  • 6
  • 20
  • that's not csv. csv has one record per-line. you have one record spread across MULTIPLE lines. – Marc B Feb 25 '16 at 19:14
  • What would you suggest using? – jgrant Feb 25 '16 at 19:16
  • csv is a last resort when everything else fails format, and even then you should think twice about using it. use pretty much anything else, e.g. xml - which has explicit provisions for handling internal escaping of data which would otherwise make for an invalid xml document. – Marc B Feb 25 '16 at 19:17
  • 1
    As Marc said, it should be one record per line. You must also remember to create a table without an ID column if you plan on using the COPY command or else you'll have to put a unique sequential number value in each row to fit into the ID column on copying. – bkunzi01 Feb 25 '16 at 19:18
  • @MarcB how would one import an xml file? I started with CSV on my local machine, which worked very well, using /lib/tasks/import.rake file to import the articles data. I'm assuming this isn't the way things work with rails production sites? – jgrant Feb 25 '16 at 19:39
  • @MarcB: Fields containing newlines are fine as long as they're quoted. – Nick Barnes Feb 25 '16 at 20:29
  • @jgrant: You probably just need to tell `\copy` which columns in the `article` table you're trying to load, e.g. `\copy articles (title,body) from ...` – Nick Barnes Feb 25 '16 at 20:31
  • @NickBarnes this creates `syntax error near unexpected token `('` – jgrant Feb 25 '16 at 21:11
  • @jgrant: Unless you're still on Postgres 7.2, this should be valid. See the [docs](http://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS-COPY) for details. – Nick Barnes Feb 26 '16 at 00:42
  • @NickBarnes, @MarcB, @bkunzi01 Thanks for your help! I was able to seed my heroku database with the `heroku run rake db:seed` command – jgrant Feb 26 '16 at 00:45

3 Answers3

0

It seems like you might have a field defined as an integer in the table that postgres is trying to store the string value "Employee personnel files". Check your table for additional fields that aren't defined in your CSV.

Header is used to specify that the CSV has a header row so that it can be ignored during the load process, not as a specification of what fields to import the data into.

If you're developing a rails application and using migrations you probably have an id field that's defined as an integer.

blankenshipz
  • 365
  • 2
  • 10
0

Firstly, make sure you are creating your table, that is going to house this information, that doesn't have an ID column. The ID column is automagically created by rails and will be a problem unless you plan on adding numbers to every row. You can then add an ID column after the fact to make it right.

Ex. create_table :products, id: false do |t|

This will leave off an ID column. Then, once you get all records formatted correctly in which there is one record per line, you will be ready to COPY them into postgres using the /COPY command. You can then add an ID column back in after the fact.

bkunzi01
  • 4,504
  • 1
  • 18
  • 25
  • Where would I create `create_table :products, id: false do |t|`? I commented above regarding the method I used for my development database – jgrant Feb 25 '16 at 19:40
  • When using the copy command for postgres, you need to already have a table in the database made. What is the name of the table you want to put this information in? Give me all of the columns you need and their datatypes such as string or integer etc. and I'll type it out for you. – bkunzi01 Feb 25 '16 at 19:44
  • The table is named Article – jgrant Feb 25 '16 at 19:45
  • Ok: On your command line inside your rails app do > rails g model Article title:string body:string Let me know when that is done – bkunzi01 Feb 25 '16 at 19:46
  • I have the Article model. I did a scaffold with Article before trying to import csv to heroku – jgrant Feb 25 '16 at 19:47
  • Ok did you run the migration file already to create the table? Did you run "bundle exec rake db:migrate" already? If you dont' remember you can type "bundle exec rake db:migrate:status" on the command line and if it shows its status as "up" it means you ran it. – bkunzi01 Feb 25 '16 at 19:48
  • I should have mentioned this in the question. The Article Model is already generated with a CSV file in development. – jgrant Feb 25 '16 at 19:49
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/104574/discussion-between-bkunzi01-and-jgrant). – bkunzi01 Feb 25 '16 at 19:49
0

I was able to seed my heroku database with the heroku run rake db:seed command and this import.rake file in /lib/tasks.

    require 'csv'

    namespace :import do

      desc 'An optional description for what the task does'
      task :articles => :environment do
            CSV.foreach("lib/articles.csv", headers: true, encoding: "UTF-8") do |row|
            Article.create!(
              title: row["Title"], 
              body: row["Body"], 
            )
          end
      end
    end
jgrant
  • 582
  • 6
  • 20