CSV إلى SQL: قم بتحويل جدول البيانات إلى عبارات INSERT بدون أخطاء الاقتباس

المهمة التي جعلتني أبني هذا أخيرًا كانت مملة وكلفتني ساعة لم تكن لدي. سلمني أحد العملاء جدول بيانات يتكون من 1800 صف منتج وطلب مني تحميله في قاعدة بيانات التدريج الخاصة به قبل العرض التوضيحي في صباح اليوم التالي. لا توجد واجهة برمجة تطبيقات، ولا شاشة استيراد، فقط ملف CSV وجدول MySQL في انتظاره. لقد فعلت ما يفعله الجميع في المرة الأولى: فتحت الملف، وكتبت بعض عبارات INSERT يدويًا للحصول على الشكل الصحيح، وبدأت في نسخ القيم. بعد ثلاثة صفوف، اسم المنتج مع فاصلة عليا فيه O'Brien's Toolsلقد كسرت العبارة بأكملها، لأن الاقتباس الفردي أغلق السلسلة مبكرًا واختنق MySQL في الباقي. هذه هي اللحظة التي تدرك فيها أن الكتابة اليدوية لـ SQL من جدول بيانات ليست مهمة مدتها خمس دقائق، بل هي حقل ألغام مع 1800 فرصة للخطأ. لذلك قمت ببناء محول CSV إلى SQLوهذا الدليل هو السبب وراء كل خيار فيه.

ليرة تركية؛DR: يقرأ محول CSV إلى SQL ملف CSV ويقوم بإنشاء ملف CREATE TABLE و INSERT البيانات التي تحتاج قاعدة البيانات إلى تخزينها. تقوم أداة Toolz بتحليل الملف باستخدام جهاز حالة RFC 4180، وتستنتج ما إذا كان كل عمود عددًا صحيحًا أو عشريًا أو منطقيًا أو نصيًا، وتقتبس معرفات MySQL أو PostgreSQL أو SQLite، وتفلت من كل قيمة بشكل صحيح، وتحول الخلايا الفارغة إلى NULL. الصق ملف CSV، واختر لهجتك، وانسخ برنامجًا نصيًا يمكنك تشغيله على الفور. كل شيء يعمل من جانب العميل: لا يوجد تحميل، ولا تسجيل، ويعمل دون اتصال بالإنترنت.

أقوم ببناء منتجات SaaS على Laravel وReact وأقوم بشحن مكونات WordPress الإضافية، لذا فإن تحميل البيانات الجدولية في قاعدة بيانات يعد عملاً روتينيًا أسبوعيًا وليس لمرة واحدة. في بعض الأحيان يكون تصديرًا للعميل، وأحيانًا ملفًا أوليًا لبيئة جديدة، وأحيانًا تثبيتًا سريعًا للاختبار. CSV هو دائمًا التنسيق الذي يصل إليه، وSQL دائمًا هو المكان الذي يجب أن ينتهي فيه الأمر. إن الانتقال من واحد إلى آخر يدويًا هو بالضبط نوع العمل المتكرر والمعرض للأخطاء الذي يجب أن تمحوه أداة المتصفح، لذلك هذا هو الدليل الذي أتمنى لو حصلت عليه عندما كسرت تلك الفاصلة العليا استوردتي.

ما هو محول CSV إلى SQL؟

يأخذ محول CSV إلى SQL صفوفًا من القيم المفصولة بفواصل وينتج عبارات SQL التي تنشئ جدولًا وتحميل البيانات إليه. CSV نفسه موحد بشكل فضفاض فقط - RFC 4180 يصف النموذج الشائع ويوضح أنه يوثق الممارسة الحالية بدلاً من إملاءها. يقرأ الصف الأول كأسماء أعمدة، ويعامل كل صف تالي كسجل، ويصدر شيئين: أ CREATE TABLE عبارة تحدد الأعمدة بأنواع معقولة، ومجموعة من INSERT العبارات التي تحمل القيم. الإخراج عبارة عن برنامج نصي SQL عادي يمكنك لصقه في عميل قاعدة بيانات مثل TablePlus أو DBeaver، أو الانتقال إلى الترحيل، أو توصيله إلى mysql، psql، أو sqlite3 سطر الأوامر لإنشاء الجدول وملئه في خطوة واحدة.

سبب وجود هذه الفئة هو أن الترجمة تتم بطريقة سهلة الاستهانة بها. يجب تغليف كل قيمة نصية بعلامات اقتباس مفردة. يجب مضاعفة كل اقتباس داخل القيمة حتى لا يتم إنهاء السلسلة. يجب ترك الأرقام والمنطقية بدون علامات اقتباس وإلا ستقوم قاعدة البيانات بتخزينها كنص ونص WHERE price > 100 سوف تتصرف بغرابة. عادة ما تحتاج الخلايا الفارغة إلى أن تصبح NULL بدلا من سلسلة فارغة. يجب اقتباس أسماء الأعمدة ذات المسافات أو الكلمات المحجوزة بالحرف المناسب لقاعدة البيانات الخاصة بك. افتقد أيًا من هذه العناصر عبر بضعة آلاف من الصفوف وتفشل الدفعة بأكملها، غالبًا مع وجود خطأ يشير إلى السطر الخطأ. يطبق المحول كل هذه القواعد بنفس الطريقة في كل مرة، وهو عرض القيمة بأكمله.

كيف تحدد الأداة نوع كل عمود &#39؛s؟

هذا هو الجزء الذي يفصل المحول المفيد عن المحول الغبي. أداة ساذجة تصنع كل عمود TEXT ويستدعي ذلك، وهو ما يعمل من الناحية الفنية ولكنه يمنحك جدولًا حيث يتم فرز الأرقام كسلاسل ولا يمكنك إجراء العمليات الحسابية دون الإرسال. تولز أداة CSV إلى SQL وبدلاً من ذلك، يقوم بمسح كل قيمة في العمود واختيار النوع الأضيق الذي يناسبها جميعًا.

المنطق واضح ومباشر بمجرد رؤيته. إذا كانت كل قيمة غير فارغة في العمود عبارة عن رقم صحيح، يصبح العمود نوعًا صحيحًا. إذا كانت كل قيمة رقمية ولكن بعضها يحتوي على نقاط عشرية، فإنها تصبح نوعًا عائمًا. إذا كانت كل قيمة هي الكلمة true أو false، يصبح منطقيا. أي شيء آخر، بما في ذلك المحتوى المختلط وأي شيء يحتوي على أحرف، يعود إلى النص. هناك استثناء واحد متعمد يجذب الناس بطريقة جيدة: قيمة مثل 007 أو 00123 يبقى نصًا، وليس عددًا صحيحًا، لأن الصفر البادئ يعني دائمًا تقريبًا أن العمود عبارة عن معرف أو رمز بريدي أو رقم هاتف حيث يكون الصفر مهمًا وسيتم فقده إذا تم تخزينه كرقم. لقد أنقذتني هذه القاعدة الفردية من إتلاف أكثر من عمود SKU للمنتج بصمت.

يمكنك إيقاف تشغيل الاستدلال إذا كنت تفضل أن يكون كل عمود نصًا، وهو الخيار الآمن عندما تخطط لتغيير الأنواع بنفسك بعد التحميل. ولكن بالنسبة للحالة الشائعة، فإن السماح للأداة باستنتاج الأنواع يعني إنشاءها CREATE TABLE يطابق البيانات بدلاً من تسطيحها، ويكون الجدول قابلاً للاستخدام لحظة وجوده.

ما هي لهجة SQL التي يجب أن أختارها؟

SQL هو معيار بالطريقة التي تعتبر بها التهجئة الإنجليزية معيارًا، وهذا يعني أن كل قاعدة بيانات لها لهجتها الخاصة. تتغير اللهجة التي تختارها ثلاثة أشياء في الإخراج: كيفية اقتباس المعرفات، وما تسمى أسماء الأنواع، ومدى صحة وكتابة الخطأ. وإليك كيف تختلف الخيارات الأربعة.

قلق	MySQL	PostgreSQL	سكليت	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 البيانات عند التحميل في جدول موجود بالفعل. احتفظ واقتبس؛ استنتج أنواع الأعمدة واقتباسها؛ تشغيل لجدول مكتوب، أو إيقاف تشغيل لجعل كل شيء نصًا. اختر واقتبس؛ إدراج واقتباس متعدد الصفوف؛ لبيان واحد مضغوط يحتوي على العديد من صفوف القيمة، والتي يتم تحميلها بشكل أسرع، أو قم بإيقاف تشغيلها للحصول على واحدة INSERT لكل صف، وهو أكثر سهولة للتحكم في الإصدار ويتيح لك تشغيل الصفوف بشكل فردي. اترك & اقتبس؛ الخلايا الفارغة مثل NULL&quot؛ ما لم تكن تريد تخزين السلاسل الفارغة على وجه التحديد.

يمكن اكتشاف المحدد تلقائيًا أو ضبطه يدويًا. اكتشاف تلقائي لفاصلة الدرجات والفاصلة المنقوطة وعلامة التبويب والأنبوب من خلال مدى اتساق كل سطر في تقسيم الأسطر العديدة الأولى إلى نفس عدد الأعمدة، مما يتعامل بشكل صحيح مع ملفات الفاصلة المنقوطة الأوروبية والصادرات المفصولة بعلامات التبويب. انقر فوق تحويل إلى SQL ويظهر الإخراج مع عدد الصفوف والأعمدة، بالإضافة إلى أي تحذيرات حول أسماء الأعمدة المكررة أو الصفوف الممزقة. انسخه وقم بتشغيله. إذا وصل التصدير بدون سطر رأس، فأوقف التشغيل &quot؛ الصف الأول هو header&quot؛ وتقوم الأداة بتسمية الأعمدة column_1، column_2وهكذا، ثم يتم التعامل مع كل صف كبيانات.

لماذا يعتبر الهروب الصحيح من القيمة مهمًا جدًا؟

نظرًا لأن الهروب غير الصحيح ليس مجرد خطأ، فهو فئة من الثغرات الأمنية. الفاصلة العليا التي كسرت أول استيراد لي يدويًا هي نفس الآلية وراء حقن SQL: علامة اقتباس واحدة داخل القيمة، إذا لم يتم الهروب منها، تنهي السلسلة مبكرًا وتسمح بتفسير كل ما يلي على أنه SQL. في أحد التطبيقات، يمكنك حل هذه المشكلة باستخدام استعلامات ذات معلمات، حيث يحافظ برنامج تشغيل قاعدة البيانات على القيم والتعليمات البرمجية منفصلة تمامًا. عندما تقوم بإنشاء برنامج نصي SQL ثابت من ملف CSV، ليس لديك هذا الفصل، لذلك يجب أن يكون الهروب صحيحًا في النص الذي تم إنشاؤه نفسه.

تتبع الأداة قاعدة ANSI SQL: يتم تغليف قيمة النص بعلامات اقتباس مفردة، ويتم مضاعفة أي علامة اقتباس مفردة داخل القيمة. لذا O'Brien's Tools يصبح 'O''Brien''s Tools'، والتي تقرأها كل قاعدة بيانات مدعومة كسلسلة أصلية. يتم إصدار الأرقام والقيم المنطقية بدون علامات اقتباس بحيث يتم تخزينها كنوع صحيح، ويتم اقتباس المعرفات باستخدام الحرف الخاص باللهجة &#39؛ لذلك يتم استدعاء عمود order أو select لا يصطدم بكلمة محجوزة. هذا هو بالضبط نوع الصواب الممل الذي يخطئ فيه البشر تحت ضغط الوقت وتتحقق الأداة بشكل صحيح في كل مرة، وهذا هو الهدف الأساسي من أتمتتها. إذا كنت مهتمًا بكيفية مقارنة الهروب عبر التنسيقات، فإن محول CSV إلى JSON يواجه نفس المشكلة من جانب JSON، حيث يكون حرف الهروب عبارة عن شرطة مائلة عكسية وليس اقتباسًا مضاعفًا.

هل من الآمن تحويل بيانات CSV الحساسة عبر الإنترنت؟

بالنسبة لهذه الأداة، نعم، والسبب معماري وليس وعدًا على الصفحة. كل خطوة، تحليل ملف CSV، واستنتاج أنواع الأعمدة، والهروب من القيم، وبناء البيانات، يتم تشغيلها كجافا سكريبت داخل علامة تبويب المتصفح الخاصة بك. لا يوجد تحميل، ولا خادم ذهابًا وإيابًا، ولا يتم تسجيل أو تخزين أي شيء. يمكنك إثبات ذلك عن طريق فتح علامة تبويب الشبكة الخاصة بالمتصفح &#39؛ والنقر فوق تحويل: لا يوجد طلب يغادر الصفحة. بمجرد تحميل الصفحة، يمكنك قطع الاتصال بالإنترنت بالكامل وتستمر في إنشاء SQL.

وهذا مهم لأن الأشخاص الذين يتحولون إلى CSVs إلى SQL غالبًا ما يكونون أكثر الملفات حساسية لدى الشركة. تنتقل جداول العملاء وسجلات الطلبات وسجلات المستخدم وبيانات التسعير جميعها على شكل ملف CSV قبل أن تصل إلى قاعدة البيانات. يقوم المحول الذي يقوم بتحميل ملفك إلى الخادم، مهما كان حسن النية، بتحويل جدول بيانات خاص إلى شخص آخر &#39؛ إدخال سجل. تؤدي المعالجة من جانب العميل إلى إزالة السؤال تمامًا: البيانات لا تترك الجهاز الذي بدأ عليه أبدًا. هذا الإعداد الافتراضي متعمد عبر كل أداة على Toolz.dev، وقد كتبت الوسيطة الأطول في دليل خصوصية البيانات للأدوات عبر الإنترنت لمن يريد المنطق الكامل.

أين يتناسب CSV إلى SQL مع سير العمل الحقيقي؟

نادرًا ما يكون التحويل هو المهمة بأكملها، فهو عبارة عن محطة واحدة في طور الإعداد. النمط الذي أضربه غالبًا هو البذر: يرسل العميل جدول بيانات، وأقوم بتحويله إلى برنامج نصي SQL، وأقوم بتشغيل هذا البرنامج النصي لملء قاعدة بيانات مرحلية أو تطويرية بحيث يحتوي التطبيق على بيانات واقعية للعمل ضدها. نظرًا لأن الإخراج عبارة عن برنامج نصي عادي وليس اتصالًا مباشرًا، فمن السهل التحقق من إعادة الشراء كملف أولي، والمراجعة في طلب السحب، وإعادة التشغيل في أي بيئة.

تعتمد الأدوات المحيطة به على ما تفعله البيانات بعد ذلك. إذا كنت بحاجة إلى النظر إلى الملف كشبكة قابلة للفرز قبل التحويل، فإن عارض CSV يعرض نفس تحليل RFC 4180 كجدول حتى أتمكن من اكتشاف صف مشوه قبل أن يصبح مشوهًا INSERT. إذا كانت الوجهة عبارة عن واجهة برمجة تطبيقات أو ملف تكوين وليس قاعدة بيانات، فإن محول CSV إلى JSON هي القفزة الصحيحة بدلا من ذلك، وشقيقها json إلى CSV يعالج العكس عندما أحتاج إلى إعادة البيانات كجدول بيانات. وبمجرد وجود SQL، فإن منسق SQL يرتبها إلى شيء يمكن قراءته للترحيل. لا يقوم أي من هذه بتحميل بياناتك، لذا يمكنك ربطها على نفس الملف الخاص دون تفكير ثانٍ. إذا كنت تقوم بتجميع مجموعة لهذا النوع من عمل البيانات، فأنا دليل مجموعة أدوات مطور الويب يمشي عبر كيفية اتصال القطع.

ما هي الحدود التي تستحق المعرفة عنها؟

الصدق بشأن الحدود هو جزء من الثقة في الأداة. المحول يولد المعيار CREATE TABLE و INSERT العبارات، مما يعني أنها لا تستنتج المفاتيح الأساسية أو المفاتيح الخارجية أو الفهارس أو قيود الأعمدة، لأنه لا توجد أي من هذه المعلومات في ملف CSV مسطح. فهو يختار نوعًا معقولًا لكل عمود، ولكن VARCHAR(255) بالنسبة لـ MySQL، يعد النص افتراضيًا وليس قياسًا؛ إذا كان لديك عمود من الأوصاف الطويلة، فقد ترغب في توسيعه إلى TEXT بعد التحميل، وإذا كان لديك عمود ينبغي أن يكون DATE أو DATETIME سوف ترغب في تغييره، حيث أن الأداة تتعامل مع التواريخ كنص لتجنب تخمين التنسيق بشكل خاطئ.

متعدد الصفوف INSERT صغير الحجم وسريع، لكن الملفات الكبيرة جدًا تنتج عبارة واحدة طويلة جدًا، وبعض قواعد البيانات تحدد حجم عبارة واحدة أو عدد العناصر النائبة. إذا كنت تقوم بتحميل عشرات الآلاف من الصفوف وتصل إلى الحد الأقصى، فقم بالتبديل إلى واحد INSERT لكل صف، والذي يتداول برنامجًا نصيًا أكبر للبيانات التي ستقبلها قاعدة البيانات دائمًا. وأخيرًا، الأداة مخصصة لإنشاء نصوص برمجية للتحميل، وليس لبث ملف متعدد الجيجابايت، لأن الإدخال بأكمله موجود في ذاكرة المتصفح. بالنسبة للصادرات اليومية والملفات الأولية والتركيبات، وهو ما يمتلكه معظم الأشخاص بالفعل، لا يوجد أي من هذه الحدود. إن معرفتها بوجودها هي مجرد الفرق بين استخدام الأداة بشكل جيد والتفاجأ بها.

الأسئلة المتداولة

كيف يمكنني تحويل ملف CSV إلى SQL؟ لصق CSV في محول CSV إلى SQL، قم بتعيين اسم الجدول، واختر لهجة SQL الخاصة بك. يقرأ صف الرأس كأسماء أعمدة، ويستنتج كل نوع عمود، وينشئ أ CREATE TABLE زائد INSERT البيانات التي يمكنك نسخها وتشغيلها في قاعدة البيانات الخاصة بك. كل شيء يحدث في متصفحك، لذلك لا يتم تحميل الملف أبدًا.

ما هي قواعد بيانات SQL التي تدعمها؟ يقوم المحول بإخراج عبارات MySQL وPostgreSQL وSQLite وSQL القياسية. تتحكم اللهجة التي تختارها في اقتباس المعرف والنوع وبناء الجملة المنطقي، لذلك يعمل البرنامج النصي في قاعدة البيانات هذه دون تعديلات. يستخدم MySQL علامات خلفية و TINYINT(1) القيم المنطقية، بينما يستخدم PostgreSQL وSQL القياسي علامات الاقتباس المزدوجة و TRUE أو FALSE.

كيف يقرر أنواع الأعمدة؟ يتم فحص كل عمود مقابل كل قيمة في البيانات. إذا كانت جميع القيم غير الفارغة عبارة عن أرقام صحيحة، يصبح العمود نوعًا صحيحًا، وتصبح جميع الأرقام نوعًا عشريًا، وتصبح جميع القيم الصحيحة أو الخاطئة نوعًا منطقيًا، ويصبح أي شيء آخر نصًا. قيمة ذات صفر بادئ مثل 007 يبقى نصًا لأنه عادةً ما يكون معرفًا مهمًا. يمكنك إيقاف تشغيل الاستدلال لإنشاء كل نص عمود.

هل يقوم بإنشاء الجدول أم الإدخالات فقط؟ كلاهما بشكل افتراضي. ينبعث أ CREATE TABLE بيان مع أنواع الأعمدة المستنتجة متبوعة بـ INSERT تصريحات. يمكنك قلب CREATE TABLE يتم إيقاف التشغيل عندما يكون الجدول موجودًا بالفعل وتحتاج فقط إلى تحميل الصفوف.

كيف يتم التعامل مع الاقتباسات والأحرف الخاصة؟ يتم تغليف قيم النص بعلامات اقتباس مفردة ويتم مضاعفة أي علامة اقتباس مفردة داخل القيمة، وهو هروب SQL القياسي، لذلك O'Brien يصبح 'O''Brien'. يتم ترك الأرقام والمنطقات المنطقية بدون علامات اقتباس، ويتم اقتباس المعرفات بعلامات خلفية لـ MySQL أو علامات اقتباس مزدوجة للهجات الأخرى، لذا فإن الكلمة المحجوزة مثل order لا يكسر البيان.

ماذا يحدث للخلايا الفارغة؟ افتراضيًا تصبح الخلية فارغة NULL، وهو عادة ما تريده لقيمة مفقودة. إذا كنت تفضل تخزين سلسلة فارغة بدلاً من ذلك، فأوقف تشغيل خيار فارغ كـ NULL وستصبح الخلايا الفارغة علامتي اقتباس مفردتين.

هل يمكنني تحويل ملف CSV لا يحتوي على صف رأس؟ نعم. قم بإيقاف تشغيل خيار الرأس وتقوم الأداة بتسمية الأعمدة column_1، column_2وهكذا، يتم التعامل مع كل صف كبيانات. وهذا مفيد للصادرات الخام التي يتم شحنها بدون خط رأس.

هل يتم تحميل ملفات CSV الخاصة بي في أي مكان؟ رقم. يتم تشغيل التحليل وإنشاء SQL كجافا سكريبت في متصفحك. لا يتم نقل أي شيء أو تسجيله أو تخزينه، وتستمر الأداة في العمل دون اتصال بالإنترنت بمجرد تحميلها، وهو ما يمكنك تأكيده من خلال مشاهدة علامة تبويب الشبكة أثناء التحويل.