Insert white space between double quotation marks using gsub in Ruby-CodePudding

I have a CSV file in which columns are supposed to be empty strings and not null. Unfortunately when I try to import the CSV with the empty string into my oracle database, for example:

3,3,"","","",f,f

I get the error message:

ORA-01400: cannot insert NULL into...

I get that because the columns are set not to accept null but rather an empty string and for some strange reason quote marks without white space is interpreted by oracle as nulll whilst reading through the CSV.

My idea was to use gsub to go through the CSV file and to add a white space to the occurrence

,"",

so that it looks like the following:

3,3," "," "," ",f,f

This seems to work when I tested it by manually adding spaces between the quote marks and importing into the db. I tried automating this as I have 40 CSV files. So far I had:

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(/,"",/, '," ",')
    File.open(Rails.root.join('db', 'csv_export', filename), "w") {|file| file.puts replace_empty_string }
  end
end

but this only replaces the first occurrence in every row in the file.

Is there a better more efficient way to go through the directory and insert white space between the double quote marks in every occurrence in the CSV file?

CodePudding user response：

You need to match locations that are at the start/end of string or double quotation marks.

You can use

replace_empty_string = read_file.gsub(/(?<![^,])""(?![^,])/, '" "')

See the regex demo.

Details

(?<![^,]) - (same as (?<=\A|,)) - a location at the start of string or immediately preceded with a comma
"" - two " chars
(?![^,]) - (same as (?=\z|,)) - a location at the end of string or immediately followed with a comma.