BASH - how do i make sure the csv file is really csv file and its parsing correctly?

Question

i have a file /var/tmp/file.csv:

f1;f2;f3;f4;f5;f6;f7;f8;f9;f10;f11;f12;13;14;15;16;^MB4NMA;AV;xx;28D;3;1;1;11160221;W6;3082;OTP;1510;;;0;0;^MABCD;EFG;MARION;33E;2;1;1;12160221;FR;3223;MAN;2215;;;0;0;

i need to insert in mysql but with all tools its failing to read or insert.

failing:

#!/bin/bash
input="/var/tmp/file.csv"
while IFS=';' read -r f1 f2 f3 f4 f5 f6 f7
do
  echo "$f1 $f2 $f3 $f4 $f5 $f6 $f7"
done < "$input"

failing:

mysql> LOAD DATA LOCAL INFILE '/var/tmp/file.csv' replace
INTO TABLE file
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(f1,f2,f3)

failing:

$ mysqlimport --local -u root --ignore-lines=1 --fields-terminated-by=';' --columns='f1,f2,f3' database /var/tmp/file.csv -vvv --debug-info

Answer 1

1) ^M needed to be fixed otherwise the CSV file was unable to read/parse (corrupted).

2) After fixing the ^M with this perl -pE 's/(\^M|\r)//g' /var/tmp/flie.csv

Then i was able to run the same failure commands on that modified file:

WORKS

$ mysqlimport --local -u root --ignore-lines=1 --fields-terminated-by=';' --columns='NOM' air /var/tmp/file.csv -vvv
Connecting to localhost
Selecting database ap
Loading data from LOCAL file: /var/tmp/file.csv into file
air.file: Records: 12306  Deleted: 0  Skipped: 0  Warnings: 12306
Disconnecting from localhost

WORKS:

#!/bin/bash
input="/var/tmp/file.csv"
while IFS=';' read -r f1 f2 f3 f4 f5 f6 f7
do
  echo "$f1 $f2 $f3 $f4 $f5 $f6 $f7"
done < "$input"

Answer 2

You can fix the ^M in your while loop with process substitution:

#!/bin/bash
input="/var/tmp/file.csv"
while IFS=';' read -r f1 f2 f3 f4 f5 f6 f7
do
  echo "$f1 $f2 $f3 $f4 $f5 $f6 $f7"
done < <(tr -d "\r" < "$input")