CSV ל-SQL: הפוך גיליון אלקטרוני להצהרות INSERT ללא באגי הציטוט

המשימה שסוף סוף גרמה לי לבנות את זה הייתה משעממת וזה עלה לי שעה שלא הייתה לי לקוח הגיש לי גיליון אלקטרוני של 1,800 שורות מוצרים וביקש ממני לטעון אותו למסד הנתונים של הבימוי שלו לפני הדגמה למחרת בבוקר אין API, אין מסך ייבוא, רק CSV וטבלת MySQL שמחכה לזה עשיתי מה שכולם עושים בפעם הראשונה: פתחתי את הקובץ, כתבתי כמה הצהרות INSERT ביד כדי לקבל את הצורה הנכונה, והתחלתי להעתיק ערכים בשלוש שורות מאוחר יותר שם מוצר עם אפוסתרוף בתוכו, O'Brien's Tools, שבר את כל ההצהרה, כי הציטוט היחיד סגר את המחרוזת מוקדם ו-MySQL נחנק מהשאר. זה הרגע שבו אתה מבין ש-SQL בכתב יד מגיליון אלקטרוני אינו עבודה של חמש דקות, זה שדה מוקשים לציטוט עם 1,800 הזדמנויות לטעות. אז בניתי את ממיר CSV ל-SQL, והמדריך הזה הוא ההיגיון מאחורי כל אפשרות בו.

TL;DR: ממיר CSV ל-SQL קורא קובץ CSV ויוצר את CREATE TABLE ו INSERT הצהרות מסד נתונים צריך לאחסן אותו הכלי Toolz מנתח את הקובץ עם מכונת מצב RFC 4180, מסיק אם כל עמודה היא מספר שלם, עשרוני, בוליאני או טקסט, מצטט מזהים עבור MySQL, PostgreSQL או SQLite, בורח כל ערך בצורה נכונה, והופך תאים ריקים ל NULL. הדבק את ה-CSV, בחר את הניב שלך והעתק סקריפט שאתה יכול להפעיל מיד. הכל פועל בצד הלקוח: אין העלאה, אין הרשמה, עובד במצב לא מקוון.

אני בונה מוצרי SaaS על Laravel ו-React ואני שולח תוספים של וורדפרס, כך שטעינת נתונים טבלאיים למסד נתונים היא מטלה שבועית ולא חד פעמית לפעמים זה ייצוא לקוח, לפעמים קובץ seed לסביבה רעננה, לפעמים מתקן מהיר לבדיקה. CSV הוא תמיד הפורמט שבו הוא מגיע, ו-SQL הוא תמיד המקום שבו הוא צריך להגיע אליו. הגעה מאחד לשני ביד היא בדיוק סוג העבודה החוזרת ונוטה לשגיאות שכלי דפדפן צריך למחוק, אז זה המדריך שהלוואי שהיה לי כשהאפוסתרוף הזה שבר את הייבוא שלי.

מהו ממיר CSV ל-SQL?

ממיר CSV ל-SQL לוקח שורות של ערכים מופרדים בפסיקים ומייצר את הצהרות ה-SQL שיוצרות טבלה וטוענות לתוכה את הנתונים. CSV עצמו הוא סטנדרטי רק באופן רופף - RFC 4180 מתאר את הצורה הנפוצה ומפורש שהיא מתעדת את הפרקטיקה הקיימת במקום להכתיב אותה. הוא קורא את השורה הראשונה כשמות העמודות, מתייחס לכל שורה הבאה כאל רשומה ופולט שני דברים: א CREATE TABLE הצהרה המגדירה את העמודות עם סוגים הגיוניים, וקבוצה של INSERT הצהרות הנושאות את הערכים הפלט הוא סקריפט SQL רגיל שאתה יכול להדביק בלקוח מסד נתונים כגון TablePlus או DBeaver, ירידה לתוך הגירה או צינור לתוך mysql, psql, או sqlite3 שורת פקודה לבנות ולאכלס את הטבלה בשלב אחד.

הסיבה שהקטגוריה קיימת היא שהתרגום הוא fiddly בדרכים שקל לזלזל בהן כל ערך טקסט צריך להיות עטוף במרכאות בודדות כל ציטוט בודד בתוך ערך צריך להיות כפול כך שהוא לא יסיים את המחרוזת יש להשאיר מספרים ובוליאנים ללא ציטוט או שמאגר הנתונים יאחסן אותם כטקסט ושלך WHERE price > 100 יתנהג מוזר תאים ריקים בדרך כלל צריכים להפוך NULL במקום מחרוזת ריקה יש לצטט שמות עמודות עם רווחים או מילים שמורות עם התו המתאים למסד הנתונים שלך. מתגעגע לכל אחד מאלה על פני כמה אלפי שורות והאצווה כולה נכשלת, לעתים קרובות עם שגיאה שמצביעה על השורה הלא נכונה. ממיר מחיל את כל הכללים הללו באותו אופן בכל פעם, שהיא הצעת הערך כולה.

כיצד הכלי מחליט על כל עמודה 's סוג?

זה החלק שמפריד בין ממיר שימושי למטומטם כלי נאיבי עושה כל עמודה TEXT וקורא לזה בוצע, מה שטכנית עובד אבל נותן לך טבלה שבה מספרים ממוינים כמחרוזות ואי אפשר לעשות חשבון בלי ליהוק. The Toolz כלי CSV ל-SQL במקום זאת סורק כל ערך בעמודה ובוחר את הסוג הצר ביותר שמתאים לכולם.

הלוגיקה פשוטה ברגע שרואים אותה אם כל ערך לא ריק בעמודה הוא מספר שלם, העמודה הופכת לסוג שלם אם כל ערך הוא מספרי אבל לחלקם יש נקודות עשרוניות, הוא הופך לסוג צף אם כל ערך הוא המילה true או false, זה הופך לבוליאני כל דבר אחר, כולל תוכן מעורב וכל דבר עם אותיות, נופל בחזרה לטקסט יש חריג מכוון אחד שתופס אנשים בצורה טובה: ערך כמו 007 או 00123 נשאר טקסט, לא מספר שלם, כי אפס מוביל כמעט תמיד אומר שהעמודה היא מזהה, מיקוד או מספר טלפון שבו האפס משמעותי ויאבד אם יאוחסן כמספר. הכלל היחיד הזה הציל אותי מהשחתה שקטה של יותר מעמודת מקט של מוצר אחת.

אתה יכול לכבות את ההסקה אם אתה מעדיף שכל עמודה תהיה טקסט, שזו הבחירה הבטוחה כאשר אתה מתכנן לשנות את הסוגים בעצמך לאחר הטעינה. אבל במקרה הנפוץ, לתת לכלי להסיק סוגים פירושו שנוצר CREATE TABLE תואם את הנתונים במקום לשטח אותם, והטבלה ניתנת לשימוש ברגע שהיא קיימת.

באיזה ניב SQL עלי לבחור?

SQL הוא תקן הדרך שבה איות באנגלית הוא תקן, כלומר לכל מסד נתונים יש מבטא משלו. הניב שאתה בוחר משנה שלושה דברים בפלט: איך מצוטטים מזהים, איך קוראים לשמות הטיפוסים וכמה נכון ושקרי כתובים. הנה איך ארבע האפשרויות שונות.

דאגה	MySQL	PostgreSQL	SQLite	SQL סטנדרטי
ציטוט מזהה	בקטיקים `col`	ציטוטים כפולים "col"	ציטוטים כפולים "col"	ציטוטים כפולים "col"
סוג שלם	INT	INTEGER	INTEGER	INTEGER
סוג עשרוני	DOUBLE	DOUBLE PRECISION	REAL	REAL
סוג בוליאני	TINYINT(1)	BOOLEAN	INTEGER	BOOLEAN
סוג טקסט	VARCHAR(255)	TEXT	TEXT	TEXT
ערכים בוליאניים	1 / 0	TRUE / FALSE	1 / 0	TRUE / FALSE

ההנחיה המעשית היא להתאים את הניב למסד הנתונים שאתה בעצם טוען לתוכו, כי ההבדלים אינם קוסמטיים.MySQL ידחה את המזהים המצוטטים כפול ש-PostgreSQL רוצה אלא אם כן אתה במצב ANSI, ול-PostgreSQL אין TINYINT. ל-SQLite אין סוג בוליאני אמיתי בכלל, אז בוליאנים הופכים למספרים שלמים, וזו הסיבה שהכלי כותב 1 ו 0 גם ל-MySQL וגם ל-SQLite אבל TRUE ו FALSE עבור PostgreSQL ו-SQL סטנדרטי. אם אינך בטוח או שאתה כותב משהו נייד, SQL סטנדרטי הוא הבחירה השמרנית ביותר. ברגע שההצהרות נוצרות, אתה תמיד יכול להפעיל אותן דרך פורמט SQL כדי להדפיס יפה את הפלט לפני שהוא נכנס לקובץ הגירה.

כיצד אוכל להמיר CSV ל-SQL עם הכלי?

הזרימה קצרה בכוונה. הדבק את ה-CSV שלך בתיבת הקלט, או לחץ על טען מדגם כדי לראות דוגמה עובדת עם מזהה, שם, תפקיד, בוליאני ועמודת שכר.

הגדר את שם הטבלה לכל מה שצריך לקרוא לטבלת היעד, ובחר את ניב ה-SQL שלך. לאחר מכן החליטו על אפשרויות הפלט. השאר " כלול צור טבלה" אם הטבלה עדיין לא קיימת, או כבה אותה כדי ליצור רק את INSERT הצהרות כאשר אתה טוען לתוך טבלה שכבר שם. Keep "Infer colled types" on for a typed table, or off to make everything text.Choose "Multi-row INSERT" for one compact statement with many value tuples, which loads fastest, or turn it off to get one INSERT לכל שורה, שהיא ידידותית יותר לשליטה בגרסאות ומאפשרת לך להריץ שורות בנפרד. השאר " תאים ריקים בתור NULL" פועל אלא אם כן אתה רוצה ספציפית מחרוזות ריקות מאוחסנות.

ניתן לזהות אוטומטית את המפריד או להגדיר ביד. זיהוי אוטומטי של ציונים פסיק, נקודה-פסיק, לשונית וצינור לפי מידת העקביות של כל אחד מהם מפצל את מספר השורות הראשונות לאותו מספר עמודות, המטפל נכון בקבצי נקודה-פסיק אירופיים וביצוא מופרד כרטיסיות. לחץ על המר ל-SQL והפלט מופיע עם ספירה של שורות ועמודות, בתוספת כל אזהרות לגבי שמות עמודות כפולים או שורות מרופטות. העתק אותו והפעל אותו. אם הייצוא הגיע ללא שורת כותרת, כבה את " השורה הראשונה היא header" והכלי נותן שמות לעמודות column_1, column_2, וכן הלאה, אז מתייחס לכל שורה כאל נתונים.

למה בריחת ערך נכון חשובה כל כך?

מכיוון שבריחה שגויה היא לא רק באג, היא סוג של פגיעות אבטחה. האפוסתרוף ששבר את הייבוא הראשון שלי ביד הוא אותו מנגנון מאחורי הזרקת SQL: ציטוט בודד בתוך ערך, אם לא נמלט, מסיים את המחרוזת מוקדם ונותן לכל הבא להתפרש כ-SQL. באפליקציה אתה פותר זאת באמצעות שאילתות עם פרמטרים, כאשר מנהל ההתקן של מסד הנתונים שומר ערכים וקוד נפרדים לחלוטין. כאשר אתה מייצר סקריפט SQL סטטי מ-CSV, אין לך את ההפרדה הזו, כך שהבריחה צריכה להיות נכונה בטקסט שנוצר עצמו.

הכלי פועל לפי כלל ANSI SQL: ערך טקסט עטוף במרכאות בודדות, וכל ציטוט בודד בתוך הערך מוכפל. כך O'Brien's Tools הופך 'O''Brien''s Tools', שכל אחד ממאגרי המידע הנתמכים קורא בחזרה כמחרוזת המקורית. מספרים ובוליאנים נפלטים ללא ציטוטים כך שהם מאוחסנים כסוג הנכון, ומזהים מצוטטים עם הניב ' תו משלו אז עמודה שנקראת order או select לא מתנגש עם מילה שמורה זה בדיוק סוג של נכונות מייגעת שבני אדם טועים בלחץ זמן וכלי מסתדר בכל פעם, וזה כל העניין של אוטומציה שלו אם אתה סקרן איך הבריחה משתווה בין פורמטים, ה ממיר CSV ל-JSON מתמודד עם אותה בעיה מהצד של JSON, שבו דמות הבריחה היא קו נטוי אחורי ולא ציטוט כפול.

האם זה בטוח להמיר נתוני CSV רגישים באינטרנט?

עבור כלי זה, כן, והסיבה היא אדריכלית ולא הבטחה בדף כל שלב, ניתוח ה-CSV, הסקת סוגי עמודות, בריחה מערכים ובניית ההצהרות, פועל כ-JavaScript בתוך לשונית הדפדפן שלך. אין העלאה, אין שרת הלוך ושוב, ושום דבר לא נרשם או מאוחסן. אתה יכול להוכיח זאת על ידי פתיחת הדפדפן שלך's לשונית רשת ולחיצה על המר: אין בקשה עוזבת את הדף. לאחר טעינת הדף תוכל להתנתק לחלוטין מהאינטרנט והוא ממשיך לייצר SQL.

זה משנה כי אנשי CSVs להמיר ל SQL הם לעתים קרובות הקבצים הרגישים ביותר שיש לעסק טבלאות לקוחות, היסטוריות הזמנות, רשומות משתמש, ונתוני תמחור כל נסיעות כמו CSV לפני שהם נוחתים במסד נתונים ממיר שמעלה את הקובץ שלך לשרת, עם זאת התכוון היטב, הופך גיליון אלקטרוני פרטי למישהו אחר & #39;s רישום יומן עיבוד בצד הלקוח מסיר את השאלה לחלוטין: הנתונים לעולם לא עוזב את המכונה זה התחיל על ברירת המחדל היא מכוונת על פני כל כלי על Toolz.dev, וכתבתי את הארגומנט הארוך יותר ב מדריך פרטיות נתונים לכלים מקוונים לכל מי שרוצה את הנימוק המלא.

היכן מתאים CSV ל-SQL בזרימת עבודה אמיתית?

ההמרה היא לעתים רחוקות כל העבודה, היא תחנה אחת בצנרת. הדפוס שפגעתי בו לרוב הוא זריעה: לקוח שולח גיליון אלקטרוני, אני ממיר אותו לסקריפט SQL, ואני מריץ את הסקריפט הזה כדי לאכלס מסד נתונים של בימוי או פיתוח כך שלאפליקציה יש נתונים מציאותיים לעבוד מולם. מכיוון שהפלט הוא סקריפט רגיל ולא חיבור חי, קל לבדוק ב-repo כקובץ seed, לסקור בבקשת משיכה ולהפעיל מחדש בכל סביבה.

הכלים סביבו תלויים במה שהנתונים עושים הלאה. אם אני צריך לגלגל את הקובץ כרשת ניתנת למיון לפני ההמרה, ה מציג CSV מעבד את אותו ניתוח RFC 4180 כמו טבלה כדי שאוכל לזהות שורה פגומה לפני שהיא הופכת ל-malformed INSERT. אם היעד הוא API או קובץ תצורה ולא מסד נתונים, ה ממיר CSV ל-JSON הוא ההופ הנכון במקום, ואחיו JSON ל-CSV מטפל הפוך כאשר אני צריך למסור נתונים בחזרה כגיליון אלקטרוני. וברגע שה-SQL קיים, ה פורמט SQL מסדר את זה למשהו קריא להעברה אף אחד מאלה לא מעלה את הנתונים שלך, אז אתה יכול לשרשר אותם על אותו קובץ פרטי בלי לחשוב פעמיים אם אתה מרכיב ערכה לעבודת נתונים מהסוג הזה, שלי מדריך ערכת כלים למפתחי אינטרנט עובר דרך איך החלקים מתחברים.

מהם הגבולות שכדאי לדעת עליהם?

כנות לגבי גבולות היא חלק מאמון בכלי הממיר מייצר סטנדרט CREATE TABLE ו INSERT הצהרות, מה שאומר שהוא לא מסיק מפתחות ראשיים, מפתחות זרים, אינדקסים או אילוצי עמודות, מכיוון שאף אחד מהמידע הזה לא קיים ב-CSV שטוח. הוא בוחר סוג סביר עבור כל עמודה, אבל VARCHAR(255) עבור MySQL טקסט הוא ברירת מחדל, לא מדידה; אם יש לך עמודה של תיאורים ארוכים, ייתכן שתרצה להרחיב אותה TEXT לאחר הטעינה, ואם יש לך עמודה שאמורה להיות DATE או DATETIME תרצה לשנות אותו, מכיוון שהכלי מתייחס לתאריכים כאל טקסט כדי למנוע ניחוש שגוי של פורמט.

הרב-שורה INSERT הוא קומפקטי ומהיר, אבל קבצים גדולים מאוד לייצר משפט אחד ארוך מאוד, וכמה מסדי נתונים מכסה את הגודל של משפט אחד או מספר מצייני מיקום אם אתה טוען עשרות אלפי שורות ופגע מגבלה, לעבור לאחד INSERT לכל שורה, אשר נסחרת סקריפט גדול יותר עבור הצהרות מסד נתונים תמיד יקבל לבסוף, הכלי הוא עבור יצירת סקריפטים עומס, לא עבור הזרמת קובץ מרובה גיגה-בייט, כי כל הקלט חי בזיכרון הדפדפן עבור יצוא יומיומי, קבצי seed, ו מתקנים, וזה מה שרוב האנשים למעשה יש, אף אחד הגבולות האלה נושך. לדעת שהם קיימים זה רק ההבדל בין שימוש טוב בכלי לבין להיות מופתע ממנו.

שאלות נפוצות

כיצד אוכל להמיר קובץ CSV ל-SQL? הדבק את ה-CSV לתוך ממיר CSV ל-SQL, הגדר את שם הטבלה ובחר את ניב ה-SQL שלך. הוא קורא את שורת הכותרת כשמות עמודות, מסיק כל סוג עמודה ויוצר א CREATE TABLE פלוס INSERT הצהרות שאתה יכול להעתיק ולהפעיל במסד הנתונים שלך הכל קורה בדפדפן שלך, כך שהקובץ לעולם לא מועלה.

באילו מסדי נתונים של SQL הוא תומך? הממיר מוציא הצהרות עבור MySQL, PostgreSQL, SQLite ו-SQL סטנדרטי. הניב שאתה בוחר שולט בציטוט מזהה ובסוג והתחביר הבוליאני, כך שהסקריפט פועל במסד הנתונים הזה ללא עריכות. MySQL משתמש ב-backticks ו TINYINT(1) בוליאנים, בעוד PostgreSQL ו-SQL סטנדרטי משתמשים במרכאות כפולות ו TRUE או FALSE.

איך זה מחליט על סוגי העמודות? כל עמודה נבדקת מול כל ערך בנתונים אם כל הערכים שאינם ריקים הם מספרים שלמים העמודה הופכת לסוג מספר שלם, כל המספרים הופכים לסוג עשרוני, כל הערכים האמיתיים או הכוזבים הופכים לסוג בוליאני וכל דבר אחר הופך לטקסט ערך עם אפס מוביל כגון 007 נשאר טקסט כי זה בדרך כלל מזהה משמעותי אתה יכול לכבות את ההסקה כדי להפוך כל טקסט בעמודה.

האם זה יוצר את הטבלה או רק את התוספות? שניהם כברירת מחדל. זה פולט א CREATE TABLE הצהרה עם סוגי העמודות המשוערים ואחריה ה INSERT הצהרות.אתה יכול להפוך את CREATE TABLE כבוי כאשר הטבלה כבר קיימת ואתה צריך רק לטעון שורות.

כיצד מטפלים במרכאות ובתווים מיוחדים? ערכי טקסט עטופים במרכאות בודדות וכל ציטוט בודד בתוך ערך מוכפל, שזו הבריחה הסטנדרטית של SQL, אז O'Brien הופך 'O''Brien'. מספרים ובוליאנים נותרים ללא ציטוט, ומזהים מובאים עם סימנים אחוריים עבור MySQL או ציטוטים כפולים עבור הניבים האחרים, אז מילה שמורה כמו order לא מפר את ההצהרה.

מה קורה לתאים ריקים? כברירת מחדל תא ריק הופך NULL, שזה בדרך כלל מה שאתה רוצה עבור ערך חסר. אם אתה מעדיף לאחסן מחרוזת ריקה במקום זאת, כבה את האפשרות empty-as-NULL ותאים ריקים הופכים לשני מרכאות בודדות.

האם אוכל להמיר CSV שאין לו שורת כותרת? כן. כבה את אפשרות הכותרת והכלי נותן שמות לעמודות column_1, column_2, וכן הלאה, אז מתייחס לכל שורה כאל נתונים. זה שימושי עבור יצוא גולמי שנשלח ללא שורת כותרת.

האם קבצי ה-CSV שלי מועלים לכל מקום? מס 'ניתוח ויצירת SQL לרוץ כמו JavaScript בדפדפן שלך שום דבר לא מועבר, נרשם, או מאוחסן, והכלי ממשיך לעבוד במצב לא מקוון לאחר הטעינה, אשר אתה יכול לאשר על ידי צפייה בכרטיסייה הרשת בזמן שאתה להמיר.