CODEWITHSUNDEEP
bashsqlitesedtr
redolent
redolent

Reputation: 4259

SQLite import fields with new lines

I have some data that is delimited by the \002 character. This is what I use to import it:

cat data.txt             \
  | tr -d '\r'           \
  | tr    '\n'   '\003'  \
  | tr    '\t'   ' '     \
  | tr    '\001' '\t'    \
  | tr    '\002' '\n'    \
  | sed 's%\003%\\n%g'   \
  > data-formatted.txt

This command erases \r characters, changes \n to \\n, and changes \002 to new lines \n

Formatting before:

http://wikisend.com/download/486524/data.txt

Not Escaped:
1 new
line 2 new
line
2   

Escaped:
1\001new\nline\002\001
2\001new\nline\n2\002\001
 

Formatting after: 

Not Escaped:
1   new\\nline  
2   new\\nline\\n2  

Escaped:
1\tnew\\nline\t\n
2\tnew\\nline\\n2\t\n

The problem is that my data is importing the \n instead of a new line.

Here is my import script:

import.txt:

    .separator  "\t"
    .import     data-formatted.txt my_table

And my import command: cat import.txt | sqlite3 my.db

To test the data:

echo 'SELECT * FROM my_table;' | sqlite3 my.db

    1|new\nline|
    2|new\nline\n2|
 

How do I import \n as a newline in the data field?

Upvotes: 2

Views: 881

Answers (1)

konsolebox
konsolebox

Reputation: 75588

You can try this Ruby code. This would convert your data to csv format.

#!/usr/bin/env ruby

require 'csv'

data = File.read(ARGV.shift)

set = data.split("\x02\x0a").map{|e| e.strip.split("\x01")}.select{|e| e.any?}

output = CSV.generate do |csv|
    set.each do |e|
        csv << e
    end
end

puts output

Save it to a file like script.rb and run ruby script.rb data > output.csv

I hope your data is not that large though. If it lags try using a faster generator like FasterCSV.

Example output:

1,"new
line"
2,"new
line
2"

Upvotes: 1

Related Questions