سؤال كيف يمكنني إيقاف Excel عن تناول ملفات CSV اللذيذة وإفراز البيانات غير المفيدة؟


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

يرغب بعض العملاء في الحصول على جدول بيانات متوافق مع Excel للأدوات التي قاموا بشرائها. نحن ننتج هذا باستخدام برنامج PHP النصي الذي يستعلم عن قاعدة البيانات ويخرج النتيجة كملف CSV مع اسم المتجر والبيانات المرتبطة به. هذا يعمل بشكل جيد للغاية أيضا.

عند فتحه في محرر نصي مثل Notepad أو vi ، يبدو الملف كما يلي:

"Account Number","Store Name","S1","S2","S3","Widget Type","Date"
"4173","SpeedyCorp","268435459705526269","","268435459705526269","848 Model Widget","2011-01-17"

كما ترون ، الأرقام التسلسلية موجودة (في هذه الحالة مرتين ، وليس كل المسلسلات الثانوية هي نفسها) وهي سلاسل طويلة من الأرقام. عندما يتم فتح هذا الملف في Excel ، تصبح النتيجة:

Account Number  Store Name  S1  S2  S3  Widget Type Date 
4173    SpeedyCorp  2.68435E+17     2.68435E+17 848 Model Widget    2011-01-17

كما قد تكون لاحظت ، يتم إحاطة الأرقام المسلسلة بواسطة علامات اقتباس مزدوجة. لا يبدو أن Excel يحترم مؤهلات النصوص في ملفات .csv. عند استيراد هذه الملفات إلى Access ، ليس لدينا أي صعوبة. عند فتحها كنص ، لا مشكلة على الإطلاق. لكن Excel ، بدون فشل ، يقوم بتحويل هذه الملفات إلى بيانات غير مفيدة. إن محاولة توجيه المستخدمين النهائيين في فن فتح ملف CSV باستخدام تطبيق غير افتراضي أصبح ، كما نقول ، مملًا. هل هناك أمل؟ هل هناك إعداد لم أتمكن من العثور عليه؟ يبدو أن هذا هو الحال مع Excel 2003 و 2007 و 2010.


123
2018-01-19 01:08


الأصل


يمكنني اعطاء +1 للاسم فقط؟ - tombull89
Excel does not seem to respect text qualifiers in .csv files - الاقتباسات المزدوجة هي ليس المؤهلات النصية ، فهي ببساطة تسمح بفواصل في بياناتك ، إذا كنت لا تستخدم الفواصل في بياناتك ، فهي كذلك لا معنى له. جميع البيانات في ملف CSV غير معدلة ، بحيث لا يستطيع برنامج Excel إلا أن يخمن أن الرقم التسلسلي الكبير الخاص بك هو رقم، وهذا عند تشغيل لفي Excel أقصى قدر من الدقة من 15 رقما ، وهو ما هو اقتطاع الأرقام الخاصة بك. - DMA57361
لا يبدو أن Excel يحترم كل الفواصل ضمن علامات اقتباس مزدوجة. خذ بعين الاعتبار "12 أغسطس 2012" يحول Excel ذلك إلى القمامة أيضًا. - zundarz
أريد أن أذكر هذا سؤال سو. فهو يشرح الخيارات التي لديك عند التعامل مع CSV في Excel. - nixda
ردًا علىnixda هذه اقتراحات مفيدة ، خاصة للمستخدمين الأكثر خبرة. مشكلتي هي مشكلة إنسانية تقريبًا ، في حين أن Excel يربط نفسه بملفات csv ، والناس يشاهدون الرمز ، والنقر المزدوج (لأن هذه هي الطريقة التي تفتح بها الأشياء) ، وعادةً ما تضغط على Save (لأننا دائمًا نقول لهم لإنقاذ!) ، وفقدت كل شيء. لكنني بالتأكيد سأستخدم أساليبك عندما يكون ذلك ممكنًا. - atroon


الأجوبة:


لكن Excel ، بدون فشل ، يقوم بتحويل هذه الملفات إلى بيانات غير مفيدة.

التفوق هو القمامة غير مجدية.

حل

سأكون مندهشا بعض الشيء إذا كان أي عميل يريد البيانات الخاصة بك في تنسيق Excel غير قادر على تغيير التنسيق المرئي على تلك الأعمدة الثلاثة إلى "رقم" مع صفر المنازل العشرية أو "النص". لكن لنفترض أن وثيقة "كيف" قصيرة غير واردة.

خياراتك هي:

  1. أجمِد أحرفًا غير رقمية وليست بيضاء في الأرقام المسلسلة.
  2. اكتب ملف xls أو ملف xlsx مع بعض التنسيق الافتراضي.
  3. الغش وإخراج تلك الأرقام والصيغ ="268435459705526269","",="268435459705526269" (يمكنك أيضا القيام به ="268435459705526269",,="268435459705526269" إنقاذ نفسك 2 حرفا). هذا له ميزة عرض بشكل صحيح ، وربما تكون مفيدة بشكل عام ، ولكن مكسورة بمهارة (كما هي الصيغ).

كن حذرا مع الخيار 3 ، لأن بعض البرامج (بما في ذلك Excel & Open Office Calc) لن تعالج الفواصل بداخلك ="" الحقول كما هرب. هذا يعني ="abc,xyz" سوف تمتد عمودين وكسر الاستيراد.

باستخدام صيغة "=""abc,xy""" يحل هذه المشكلة ، ولكن لا تزال هذه الطريقة تحدّ من 255 حرفًا بسبب حد طول صيغة Excel.


57
2018-01-19 01:57



في الواقع ، هذا ليس قاسيا. قم بنسخ أحد الأرقام أعلاه ولصقها في Excel ، ثم قم بتغيير تنسيق الرقم كما هو مقترح. يتغير Excel القيمة ، ينتج عن القمامة. - Joe Internet
Joe ، كنت سريعًا جدًا في النظرة العامة الأولية. يقوم Excel بالفعل بإنتاج القمامة ، وهو في حد ذاته نفايات. لقد قمت بتحديث إجابتي لتعكس ذلك. قد يكون الخيار هو وجود "csv Excel" وأيضًا وجود "csv جدير بالاهتمام والاستحقاق" - Tyler
Tyler - لا أعتقد أن Excel عبارة عن قمامة ، فقط أقول إن OP كانت على صواب بأنها تنتج القمامة في هذه الحالة. إنه في الواقع سؤال جيد للغاية ، بدون حل يبدو أنيقًا. - Joe Internet
تم اقتراح الخيار تنسيق خلايا ... ، وقد حاولت استخدامه. في هذه الحالة ، في اللحظة التي تفتح فيها الملف ، يبدو أن Excel يقوم بتحويل المسلسلات إلى كتابة علمية (متفق عليها ، غير متوقعة) وقذف الدقة. عند تغييرها إلى رقم أو إلى نص ، لا يعود السلسلة. أن حقا هو جوهر المشكلة. الإخراج كما الصيغ قد تفعل ذلك على الرغم من ... لم أفكر في ذلك. - atroon
@ DMA57361 لا يتوقع السلوك ، فهو قابل للتحديد. دقة رقمية موثقة بشكل جيد ، كيف يقرأ التفوق CSVs ليست كذلك. إن عدم وجود تحذير وإهمال البيانات بشكل سخيف أمر سخيف. حقيقة أنه لا يمكنك حتى إخبار Excel كيفية استيراد البيانات سخيفة بنفس القدر. هي السلبية بحاجة؟ لا ، لكن الصدق هو أفضل سياسة وهذا ما أشعر به. - Tyler


واجهتنا مشكلة مشابهة حيث كان لدينا ملفات CSV ذات أعمدة تحتوي على نطاقات مثل 3-5 و Excel سيحولها دائمًا إلى تواريخ مثل. 3-5 سيكون 3 مارس ، وبعد ذلك العودة إلى أرقام رقمية أعطانا عددًا صحيحًا من التاريخ بدون فائدة. حصلنا عليه من قبل

  1. إعادة تسمية ملف CSV إلى امتداد TXT
  2. ثم عندما فتحناها في Excel ، فإن هذا من شأنه أن يعالج معالج استيراد النص
  3. في الخطوة 3 من 3 في المعالج قلنا لها أن الأعمدة في السؤال كانت نصًا واستوردوا بشكل صحيح.

هل يمكن أن تفعل الشيء نفسه هنا أعتقد.

text import wizard

في صحتك


42
2018-01-31 18:30



+1 لكونها الطريقة الصحيحة للقيام بذلك. (تحرير: آسف كان لا بد من تحرير قليلا لتوضيح الحل) - Jay
ليس عليك إعادة تسمية ملفك. ما عليك سوى استخدام معالج الاستيراد Shift-select كل الأعمدة واختيار نص. - nixda
معالج استيراد النص هو الجواب. جميع الحلول الأخرى عبارة عن قرصان لا داعٍ ناتج عن عدم فهم كيفية استخدام برنامج Excel لعرض ملفات CSV وتعديلها. - Excellll
Excellll ، يفعل ذلك ملف واحد في وقت متأكد. عند أتمتة هذه العملية ، غالبًا ما يوفر "الهاكر غير الضروري" اليوم. - Parrish Husband
هذا هو عديم الفائدة تماما عندما يتم استخدام التفوق من قبل المستخدمين القياسيين لعرض ملفات CSV. قبل أن أحاول شرح كيفية استخدام معالج استيراد النص إلى ~ 15 مستخدمًا لمهارات المبتدئين ، أود بدلاً من ذلك كتابة شفرة مصدر مستند excel بنفسي. - northkildonan


الحل الأفضل هو إنشاء مصنف XML. مثله:

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  </OfficeDocumentSettings>

  <ss:Worksheet ss:Name="Sheet 1">
    <Table>
    <Column ss:Width="100"/>
    <Column ss:Width="100"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="80"/>
    <Column/>

    <Row>
      <Cell><Data ss:Type="String">Account Number</Data></Cell>
      <Cell><Data ss:Type="String">Store Name</Data></Cell>
      <Cell><Data ss:Type="String">S1</Data></Cell>
      <Cell><Data ss:Type="String">S2</Data></Cell>
      <Cell><Data ss:Type="String">S3</Data></Cell>
      <Cell><Data ss:Type="String">Widget Type</Data></Cell>
      <Cell><Data ss:Type="String">Date</Data></Cell>
    </Row>

    <Row>
      <Cell><Data ss:Type="String">4173</Data></Cell>
      <Cell><Data ss:Type="String">SpeedyCorp</Data></Cell>
      <Cell><Data ss:Type="String">268435459705526269</Data></Cell>
      <Cell><Data ss:Type="String">x</Data></Cell>
      <Cell><Data ss:Type="String">268435459705526269</Data></Cell>
      <Cell><Data ss:Type="String">848 Model Widget</Data></Cell>
      <Cell><Data ss:Type="String">2011-01-17</Data></Cell>
    </Row>


    </Table>
    <x:WorksheetOptions/>
  </ss:Worksheet>
</Workbook>

يجب أن يكون الملف بامتداد .xml. يقوم Excel و OpenOffice بفتحه بشكل صحيح.


9
2018-02-17 11:13



هل تعني أن OP يجب أن يستخدم برنامج PHP النصي لتحويل قاعدة البيانات إلى تنسيق XML؟ - Prasanna
أكثر نظافة بكثير من فتح المستخدمين لـ .csv في Excel أو إجراء نسخ احتياطي لـ CSV حتى يتمكن Excel فقط من فهم ملف CSV الخاص بك. إنه ليس هذا المركب من المخطط. - binki
أين يتم توثيق هذا المعيار؟ أود معرفة المزيد عن أنواع البيانات المتاحة. - John Doherty


بلدي الحل: لقد حصلت على نفس المشكلة مع استيراد الأرقام التسلسلية. لا يتعين معاملتهم كأرقام ، أي أنه لا توجد وظائف حسابية على ذلك ، ولكننا نحتاج إلى العدد الكامل هناك. أبسط شيء لدي هو إدراج مسافة في الرقم التسلسلي. على سبيل المثال "12345678 90123456 1234". عندما يقوم Excel باستيرادها ، سيتم التعامل معه كنص بدلاً من رقم.


1
2018-05-08 08:08





كان لدي أرقام حساب طويلة يجري مشوه.

هذه هي الطريقة التي أصلحتها:

افتح ملفك file.csv في Libre Office / Open Office (قد تضطر إلى تحديد محدّدات إلخ.) ثم احفظ الملف كملف XML Excel.

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

افتح ملف تنسيق Excel وسيظل العمود على ما يرام!


0
2018-05-17 14:53



في حين هذا سيكون العمل ، في محاولة لشرح لشخص يتحدث فقط الإنجليزية المكسورة لماذا يحتاج إلى استخدام مجموعة مكتب مختلفة يخلق مشاكل كثيرة كما يحل. البرامج البديلة إلى M $ Office هي كلها جيدة في رأيي الشخصي ، لكنني أدرك أنني لا أستطيع تحويل الجميع. - atroon


معالج الاستيراد هو أفضل حل للمستخدمين العرضيين وحالات لمرة واحدة. إذا كنت بحاجة إلى حل برمجي ، فيمكنك استخدام الأسلوب QueryTables.Add (وهو ما يستخدمه معالج الاستيراد من خلف الكواليس).

Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & "C:\myfile.csv", Destination:=Range("$A$1"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 2, 2) 'Edit this line. Add a number for each column, 1 is general, 2 is text. Search the internet for other formats.
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

0
2017-07-23 19:48