I have CSV's which I am trying to import into my oracle database but unfortunately I keep on getting the same error:

> CSV::MalformedCSVError: Unquoted fields do not allow \r or \n (line 1).

I know there are tons of similar questions which have been asked but none relate specifically to my issue other than this one, but unfortunately it didn't help.

To explain my scenario:

I have CSV's in which the rows don't always end with a value, but rather, just a comma because it's a null value hence it stays blank. I would like to import the CSV's regardless of whether the ending is with a comma or without a comma.

Here are the first 5 lines of my CSV with changed values due to privacy reasons,

id,customer_id,provider_id,name,username,password,salt,email,description,blocked,created_at,updated_at,deleted_at
1,,1,Default Administrator,admin,1,1," ",Initial default user.,f,2019-10-04 14:28:38.492000,2019-10-04 14:29:34.224000,
2,,2,Default Administrator,admin,2,1,,Initial default user.,,2019-10-04 14:28:38.633000,2019-10-04 14:28:38.633000,
3,1,,Default Administrator,admin,3,1," ",Initial default user.,f,2019-10-04 14:41:38.030000,2019-11-27 10:23:03.329000,
4,1,,admin,admin,4,1," ",,,2019-10-28 12:21:23.338000,2019-10-28 12:21:23.338000,
5,2,,Default Administrator,admin,5,1," ",Initial default user.,f,2019-11-12 09:00:49.430000,2020-02-04 08:20:06.601000,2020-02-04 08:20:06.601000

As you can see the ending is sometimes with or without a comma and this structure repeats quite often.

This is my code with which I have been playing around with:

def csv_replace_empty_string
  Dir.foreach(Rails.root.join('db', 'csv_export')) do |filename|
    next if filename == '.' or filename == '..' or filename == 'extract_db_into_csv.sh' or filename =='import_csv.rb'
    read_file = File.read(Rails.root.join('db', 'csv_export', filename))
    replace_empty_string = read_file.gsub(/(?<![^,])""(?![^,])/, '" "')
    format_csv = replace_empty_string.gsub(/\r\r?\n?/, "\n")
    # format_csv = remove_empty_lines.sub!(/(?:\r?\n) \z/, "")
    File.open(Rails.root.join('db', 'csv_export', filename), "w") {|file| file.puts format_csv }
  end
end

I have tried using many different kinds of gsubs found online in similar forums, but it didn't help.

Here is my function for importing the CSV in the db:

def import_csv_into_db
  Dir.foreach(Rails.root.join('db', 'csv_export')) do |filename|
    next if filename == '.' or filename == '..' or filename == 'extract_db_into_csv.sh' or filename =='import_csv.rb'
      filename_renamed = File.basename(filename, File.extname(filename)).classify
        CSV.foreach(Rails.root.join('db', 'csv_export',filename), :headers => true, :skip_blanks => true) do |row|
          class_name = filename_renamed.constantize
          class_name.create!(row.to_hash)
          puts "Insert on table #{filename_renamed} complete"
        end
      end
  end

I have also tried the options provided by CSV such as :row_sep => :"\n" or :row_sep => "\r" but keep on getting the same error.

I am pretty sure I have some sort of thinking error, but I can't seem to figure it out.

CodePudding user response：

I fixed the issue by using the following:

format_csv = replace_empty_string.gsub(/\r\r?\n?/, "\n")

This was originally @mgrims answer, but I had to adjust my code by further removing the :skip_blanks :row_sep options.

It is importing successfully now!