0

I was prompted in the thread: MySqlConnector C# INSERT large number of rows that it is possible to use bulk insert for a quick write to the database. But there were problems, since my data has different UTF characters, , and \r\n, when reading the csv file and writing to the DB, the data is incorrectly read from the file. Here is the code when writing to a file and writing it to the database:

using (MySqlConnection mySqlConnection2 = new MySqlConnection(sqlconnect))
{
    using (StreamWriter streamWriter = new StreamWriter(path, true, Encoding.GetEncoding("windows-1251")))
    {                        
        foreach (Mail_Data data in List)
        {

            streamWriter.WriteLine($"{data.threadID};{data.mailID};{data.UserID};{data.UserName};{data.mailTime};{data.body}");

        }
    }

    var bl = new MySqlBulkLoader(mySqlConnection2);
    bl.TableName = "Mail";
    bl.FieldTerminator = ";";
    bl.LineTerminator = "\r\n";
    bl.FileName = path;
    bl.NumberOfLinesToSkip = 0;
    var inserted = bl.Load();

}

Data from file:

207;207;66;Kondommm;3/15/2022 3:02:26 PM;selam :D
207;254;82;OkanOzcelik;3/15/2022 3:03:19 PM;asssss 
207;4200;82;OkanOzcelik;3/15/2022 7:53:03 PM;discord
207;7039;66;Kondommm;3/16/2022 9:14:18 AM;sirket bilgisayar?ndan oynuyorum discord acam?yorum burda . bisey dersen mail at 
207;7366;82;OkanOzcelik;3/16/2022 10:40:52 AM;Tamamd?r abi
207;8937;82;OkanOzcelik;3/16/2022 4:22:54 PM;Koy ad?n? MAD ile ilgili bir sey yapsana abi
207;38036;66;Kondommm;3/22/2022 2:57:34 PM;discord sifremi unuttum sacma bi sekilde hangi maille kaydoldugumuda unuttum. D: 
207;39142;82;OkanOzcelik;3/22/2022 5:59:41 PM;Abi nas?l basard?n bunu hahah
207;39767;82;OkanOzcelik;3/22/2022 7:16:36 PM;ne zaman Page be abi
207;42204;82;OkanOzcelik;3/23/2022 10:35:02 AM;:D
207;43615;66;Kondommm;3/23/2022 3:37:30 PM;2 bar var :D
207;68426;82;OkanOzcelik;3/28/2022 9:30:26 PM;Nas?l gidiyor abi
207;71877;66;Kondommm;3/29/2022 7:21:22 PM;isler yogun bi t?k 2 gundur bakamad?m bile :(
207;72166;82;OkanOzcelik;3/29/2022 8:30:36 PM;2 gun bakmad?n hala gecememisim seni :D
473;477;96;reCONNECT-;3/15/2022 3:08:00 PM;hallo nachbar :)
473;563;157;wolkrus;3/15/2022 3:10:42 PM;hey wie gehts
473;577;96;reCONNECT-;3/15/2022 3:11:16 PM;alles gut
hoffe dir gehts auch gut :)
473;583;157;wolkrus;3/15/2022 3:11:27 PM;immer

473;8964;96;reCONNECT-;3/16/2022 4:28:40 PM;?
473;8980;157;wolkrus;3/16/2022 4:31:56 PM;Mision
473;8985;96;reCONNECT-;3/16/2022 4:32:27 PM;ach so;

sag bei so was aber bitte vorher Bescheid in Zukunft ;)
523;528;165;Silenced_13d;3/15/2022 3:09:32 PM;hey
523;535;99;Draft911;3/15/2022 3:09:50 PM;hi man
523;656;99;Draft911;3/15/2022 3:14:07 PM;where you landed
523;668;165;Silenced_13d;3/15/2022 3:14:37 PM;105156 just south of porto seat

523;1523;99;Draft911;3/15/2022 3:59:24 PM;I don't know how fast are you have rank.7 ^^

Screen from DB : here

Gaben
  • 1
  • 2
  • `Encoding.GetEncoding("windows-1251")` <-- Well, that's a blast from the past - why are you using that horrible encoding instead of UTF-8? – Dai Aug 09 '22 at 00:21
  • How is this not-quite-CSV file being generated? – Dai Aug 09 '22 at 00:22
  • Your export is useless because the free-text (the content of the chat-messages) is not enquoted. You need to re-export it and ensure you're using RFC 4180-compliant CSV files: see here: https://stackoverflow.com/questions/5341219/is-there-a-library-that-can-write-an-rfc-4180-csv-file-with-php – Dai Aug 09 '22 at 00:23
  • seems like you manually need to specify the id column and not rely on it being auto incremental.so extend the line streamWriter.WriteLine($"{id};… and increment id in the for each loop id++ – Rand Random Aug 09 '22 at 00:56

0 Answers0