- Ukusebenzisa amathuluzi omugqa wemiyalo njenge-bcp kanye ne-LOAD DATA INFILE ukuze kulayishwe idatha eningi.
- Ukusetshenziswa kwemisebenzi ezenzakalelayo kusetshenziswa i-Python ne-SSIS ukuphatha amafayela aguquguqukayo.
- Umehluko phakathi kokungenisa ngesandla nge-GUI kanye nokwenza ngokuzenzakalela usebenzisa izikripthi ezinjini ezahlukene ze-SQL.
Uma uke wahlangana nenqwaba yedatha ebanjwe kumafayela e-CSV futhi wazibuza ukuthi ungawabuza kanjani usebenzisa i-SQL , awuwedwa. Nakuba amanani ahlukaniswe ngamakhoma (i-CSV) esabusa ekuhambiseni ulwazi phakathi kwezinhlelo, ukusebenza nawo ngesandla kuyiphupho elibi. Yingakho ukuwaguqula abe yisizindalwazi kuyindlela yokuhlaziya idatha ngokushesha nangokunembile.
Ingxenye engcono kakhulu ukuthi kungakhathaliseki ukuthi usebenzisa yiphi injini yedatha—kungakhathaliseki ukuthi amandla e-SQL Server, ukuguquguquka kwe-MySQL, noma uhlobo olulula lwe-SQLite—kuhlale kunendlela yokwenza ngokuzenzakalelayo ukulayisha idatha . Kusukela kuma-script e-Python ahlanza idatha ngaphambi kokulayisha kuya kumathuluzi ezithombe akwenzela umsebenzi onzima, kunezindlela eziningi zokuguqula umbhalo olula ube ithebula elihlelekile nelinemibuzo.
Ukuqonda Ukungenisa ku-Azure SQL kanye ne-SQL Server
Uma usebenza nezindawo ze-Microsoft, ikakhulukazi i-Azure SQL Database noma i-Managed Instance, elinye lamathuluzi anamandla kakhulu yi- bcp command-line utility . Leli thuluzi lisebenza kahle kakhulu ekungeniseni idatha, yize linezinkinga zalo: isibonelo, alisebenzi kahle ne-UTF-8, ngakho-ke kufanele siqinisekise ukuthi amafayela ethu afakwe ikhodi ku -ASCII noma ku-UTF-16.
Inqubo evamile iqala ngokuchaza ithebula lendawo okuyo kusetshenziswa i-sqlcmd.exe , siqinisekisa ukuthi amakholomu afana ncamashi nefayela. Uma isakhiwo sesidaliwe, sisebenzisa umyalo we-bcp onepharamitha ethi "in" ukuze silahle okuqukethwe kwefayela le-CSV etafuleni. Kulabo abafuna indlela eyinkimbinkimbi kakhulu, ukusebenzisa i-Python kuyindlela efanelekile yokwenza izinqubo ngokuzenzakalela kusuka ku-SharePoint, okukuvumela ukuthi uqinisekise amakholomu namanani ngaphambi kokufaka idatha kumathebula esikhashana ukuvimbela amaphutha etafuleni eliyinhloko.
Ukuzenzakalela okuthuthukisiwe nge-SSIS
Uma usebenza endaweni yebhizinisi, kungenzeka ukuthi uke wezwa nge -SQL Server Integration Services (SSIS) . Leli thuluzi lihle kakhulu ekudaleni ukugeleza kwedatha okuyinkimbinkimbi. Cabanga ukuthi udinga ukukhipha idatha ku-SQL Server bese uyigcina kufayela le-CSV, kodwa ngemininingwane eyodwa ebalulekile: igama lefayela kumele lishintshe ngokuya ngosuku nesikhathi samanje ukuze ugweme ukubhala ngaphezulu ifayela langaphambilini.
Ukuze kufezwe lokhu, kusetshenziswa i -SSIS Expression Builder , elungiselela impahla ye-ConnectionString ukwenza igama lefayela lishintshe. Lokhu kuguqula ifayela elingaguquki libe uhlelo lokuthumela oluzenzakalelayo oluhlela amafayela ngesikhathi sokusebenza, okwenza kube lula kakhulu ukulandelwa kokukhishwa kwansuku zonke.
Ukungenisa idatha ku-MySQL: CLI vs GUI
Ezweni le-MySQL, kunezindlela ezimbili zokufika endaweni efanayo. Eyokuqala umugqa womyalo, lapho umyalo othi LOAD DATA INFILE uyisihluthulelo. Lo myalo ukuvumela ukuthi ucacise i-field delimiter (ngokuvamile i-comma), uhlamvu lokuvala (izingcaphuno eziphindwe kabili), kanye, okubaluleke kakhulu, umyalo we -IGNORE 1 ROWS ukuze i-CSV header ingangeniswa njengedatha evamile.
Ngakolunye uhlangothi, uma ungafuni ukubhekana nekhonsoli, i-MySQL Workbench inikeza iwizadi yokungenisa enembile kakhulu. Into enhle ngesibonisi sezithombe ukuthi isofthiwe ihlaziya ifayela futhi iphakamise izinhlobo zedatha zekholomu ngayinye, idale ithebula ngokuzenzakalelayo ngaphandle kokuthi ubhale umugqa owodwa wekhodi ye-DDL.
Izixazululo ezilula nge-SQLite namathuluzi ewebhu
Kumaphrojekthi amancane noma izinhlelo zokusebenza zasendaweni, i-SQLite isindisa impilo. Ngokusebenzisa isikhombimsebenzisi sayo somugqa womyalo (i-CLI), singasebenzisa imiyalo efana ne -`.mode csv` kanye ne-`.import` ukuthola idatha ngokushesha. Kuyindlela ekahle uma singafuni ukusetha iseva yedatha yonke ukuze siqalise imibuzo embalwa esheshayo kufayela eliyisicaba.
Ngaphezu kwalokho, kuneziguquli zamahhala eziku-inthanethi eziguqula ngqo okuqukethwe kwefayela le-CSV kube yizitatimende ze-SQL INSERT. Lawa mathuluzi ahumusha umugqa wokuqala njengamagama ekholomu futhi akhiqize ikhodi edingekayo ukulayisha idatha, okuwukusindisa impilo uma unefayela elincane futhi ungafuni ukusetha indawo yokuthuthukiswa.
Kungakhathaliseki ukuthi ngamandla e-bcp ku-Azure, ukuguquguquka kwe-Python, ukuqina kwe-SSIS, noma ubulula be-MySQL ne-SQLite, ukuhlanganisa amafayela e-CSV endaweni ye-SQL kukuvumela ukuthi uguqule idatha engahlelekile ibe ulwazi oluhlelekile nolubalulekile , uthuthukise izikhathi zokuhlaziya futhi uqede umthwalo womsebenzi ngesandla ngokuzenzakalelayo izinqubo zokungenisa.