sed command to remove invalid characters when import CSV file into Sqlite3 Database

Question

I have CSV file which is having more than 100K records (File size is 50mb). When I am importing data from CSV to Sqlite3 Database getting errors saying "expected 43 columns of data but found 78".

The reason I found was, CSV file has some invalid characters. (Refer Screenshot)

Please help me to remove invalid characters from CSV file using "Linux Sed Command"

https://prnt.sc/odr7hb

I need to modify this command with sed command to remove invalid characters.

sqlite3 /path_to_db/date_sqlite.db 
"<<EOS" 
.separator '|' 
.import /path_to_data_file/data_log_filr tbl_sqlite_data EOS

Null Byte = These are the characters I need to remove

I just applied code-format (4 leading spaces) to your code but it still doesn't look quite right. So that people can help, please make the code shown match the code that you actually use. — John1024, Jul 11 '19 at 18:54

score 1 · Accepted Answer · answered Jul 14 '19 at 04:20

1

To remove Null Byte in a Text file

sed 's/\x0//g' PATH TO INPUT FILE > PATH TO OUTPUT FILE

To Use in PHP Exec

 $regx = 's/\x0//g';  

 $command = sprintf(" sed '%s' $pathToInput > $pathToOutput   ", $regx );

 exec($command)

Hope this helps.

answered Jul 14 '19 at 04:20

Kalana Perera

127
8

1

After removing null bytes and import data into sqlite database, it took longer time than I expected. Because of file size 40/50 Mb. Ref: https://medium.com/@JasonWyatt/squeezing-performance-from-sqlite-indexes-indexes-c4e175f3c346 https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite?rq=1 Conclusion : To Improve performance of Sqlite bulk data insert/update/delete, Drop table indexing when you do sqlite operations and once the operation is done recreate table Indexing this will drastically improve performance. Hope this will help someone – Sharon.B Jul 15 '19 at 06:00

score 0 · Answer 2 · answered Jul 12 '19 at 07:14

0

Assuming that your input file is not Unicode, and so you really want to remove all NUL bytes, you can do this on the shell with

tr -d \\000 <input.csv >output.csv

answered Jul 12 '19 at 07:14

user1934428

19,864
7
42
87

You can run any shell command you like.... Just be careful with the number of `\` characters. – user1934428 Jul 12 '19 at 13:33

sed command to remove invalid characters when import CSV file into Sqlite3 Database

2 Answers2