Insert lots of row-by-line data with correlation checking and possible bad data

Question

I read a lot of articles and questions similar to this, but havnt found something close enough.

I have 40,000 lines in an excel file of 10 columns. The data in this document is printed by hand.

There are two main numbers in the file that I have to check: MO and Order. Because they are entered manually, they may be incorrect.

I have 2 tables table MO

moID     MO       Order
3409    87234    23845
3410    84562    21342
etc... (38k rows)

and

printID   moID   user   date   printer
5         3409   Brad   01/24  printer1
34        3409   Brad   01/30  printer1
40        3410   Joe    01/31  printer1
etc... (9k ish and growing)

PROCESS:

I go through every line in my C # application to get the moID. then a query to find out if the work exists, and then type Insert. If the moID cannot be found, I request to register the possible combinations of MO and Order so that they later correct the file.

LINK QUERYS:

curMOID = dbReports.MoNumbers.Where(r => r.moNumber == moNum && r.moOrder == orderNum).Select(c => c.id_moNumber).FirstOrDefault();
var printJobs = dbReports.PaperPrints.Where(q => q.id_moNumber == curMOID && q.printDate == printDate && q.rowExcelFile == curRow);
if (printJobs.Where(q => q.printSize == null).Count() == 0)

Question:

moID ~ 250 ( , ~.075). . , 0,5 . 40k 5 . . - .

Answer 1

, , . , - .

1: CSV

2: LOAD DATA INFILE
,

LOAD DATA INFILE

3.

, , , , .

CREATE TABLE LIKE

4: temp final
,

INSERT IGNORE INTO destination SELECT * FROM tmp_table

5: , tmp_table

, .

Answer 2

, moID , , , :

.

5000 moID , , 5000, , , . .

Answer 3

@e4c5. , . , . 5+ 5 . # , csv LOAD DATA INFILE.

1) CSV , .

• EPPLUS, . , , , , .

2) ,

• StagePrintJob

3) MO Order .

4) moID , 0

5) LOAD DATA INFILE

• () , . .

          "TRUNCATE TABLE  `" + table + "`; " +
  
          string.Format("LOAD DATA LOCAL INFILE '{0}' INTO TABLE `" + table + "` ", csvFullName).Replace(@"\", @"\\") +
          "FIELDS TERMINATED BY ',' " +
          "ENCLOSED BY '\"' " +
          "LINES TERMINATED BY '\\r\\n' " +
          "ignore 1 lines " +
          "(@printFix, moClient, moNumber, moOrder, printComment, printPrinter, printStatus, printUser, printIssue, printSolution, printQty, rowExcelFile, excelFile) " +
          "SET printDate = STR_TO_DATE(@printFix, '%m/%d/%Y'); " +
          "Call PaperPrintProcedure('" + printFileName + "')";
  

• SET printDate - , mySQL

• LOCAL LOCAL INFILE LOAD DATA, mySQL.

6) ( ). , , .

        update StagePrint test inner join MoNumbers mo on test.moNumber = mo.moNumber && test.moOrder = mo.moOrder
        set test.id_moNumber = mo.id_moNumber;

7) , .

• , , , excel , .

      DELETE FROM  `PaperPrint` WHERE  `excelFile` =  printFileName;
  
      insert into PaperPrint(id_moNumber, printQty, printDate, printSize, printUser, printPrinter, printStatus, printIssue, printSolution, printComment, rowExcelFile, excelFile) 
      select id_moNumber, printQty, printDate, printSize, printUser, printPrinter, printStatus, printIssue, printSolution, printComment, rowExcelFile, excelFile from StagePrint 
      where StagePrint.id_moNumber <> 0;
  

8) StagedTable , ID = 0 .