أوقف Excel من تحويل قيم نصية معينة إلى تواريخ تلقائيًا


Answers

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

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

  • الرمز البريدي / البريدية
  • أرقام الهاتف
  • أرقام الهوية الحكومية

والتي يمكن أن تبدأ أحيانًا بصفر واحد أو أكثر (0) ، والتي يتم التخلص منها عند تحويلها إلى أرقام رقمية. أو تحتوي القيمة على أحرف يمكن الخلط بينها وبين العوامل الرياضية (كما في التواريخ).

حالتين يمكن أن أفكر في أن الحل "prepending =" ، كما ذكر سابقا ، قد لا يكون مثاليا

  • حيث قد يتم استيراد الملف إلى برنامج آخر غير MS Excel (يتبادر إلى الذهن وظيفة دمج المراسلات الخاصة بـ MS Word) ،
  • حيث يمكن أن تكون القراءة البشرية مهمة.

الاختراق لي للتغلب على هذا

إذا قام أحد ما قبل / إلحاق حرف غير رقمي و / أو غير تاريخ في القيمة ، فسيتم التعرف على القيمة كنص ولا يتم تحويلها. قد تكون الأحرف غير المطبوعة جيدة لأنها لن تغير القيمة المعروضة. ومع ذلك ، لا يعمل حرف الفضاء القديم العادي (\ s ، ASCII 32) لهذا لأنه يتم قصه بواسطة Excel ومن ثم لا يزال يتم تحويل القيمة. ولكن هناك العديد من الشخصيات الأخرى للطباعة والفضاء التي ستعمل بشكل جيد. ولكن الأسهل هو إلحاق (إضافة بعد) حرف علامة التبويب البسيطة (\ t ، ASCII 9).

فوائد هذا النهج:

  • متوفر من لوحة المفاتيح أو برمز ASCII سهل التذكر (9) ،
  • لا يزعج الاستيراد ،
  • عادة لا تضايق نتائج دمج المراسلات (حسب تخطيط القالب - ولكن عادة ما يضيف مساحة كبيرة في نهاية السطر). (إذا كانت هذه مشكلة ، فابحث عن أحرف أخرى مثل المساحة الخالية من العرض (ZWSP ، Unicode U + 200B)
  • ليس عائقا كبيرا عند عرض CSV في المفكرة (الخ) ،
  • ويمكن إزالتها عن طريق البحث عن / استبدال في Excel (أو المفكرة وغيرها).
  • لا يلزمك استيراد ملف CSV ، ولكن يمكنك النقر نقرًا مزدوجًا لفتح ملف CSV في Excel.

خيار اخر

قد يكون لإنشاء ملفات XML ، والتي يتم قبول تنسيق معين لها أيضًا للاستيراد بواسطة إصدارات MS Excel الأحدث ، والتي تسمح بالكثير من الخيارات المشابهة لصيغة .XLS ، ولكن ليس لدي خبرة بهذا.

لذلك هناك العديد من الخيارات. اعتمادا على الاحتياجات الخاصة بك / التطبيق ، قد يكون واحد أفضل من الآخر.

إضافة

يجب أن يقال أن الإصدارات الأحدث (2013 +) من MS Excel لا تفتح CSV في تنسيق جدول البيانات أي أكثر - واحد أكثر من speedbump في سير عمل واحد مما يجعل Excel أقل فائدة ... على الأقل ، توجد تعليمات للالتفاف حوله. انظر على سبيل المثال Stackoverflow: كيفية عرض ملفات .csv بشكل صحيح في Excel 2013؟ .

Question

هل يعرف أحد ما إذا كان هناك رمز مميز يمكنني إضافته إلى csv الخاص بي لحقل معين بحيث لا يحاول Excel تحويله إلى تاريخ؟

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




لا تزال مشكلة في إصدار Microsoft Office 2016 ، مزعجة إلى حد ما بالنسبة لنا نحن نعمل مع أسماء الجينات مثل MARC1 و MARCH1 و SEPT1 الخ. الحل وجدته الأكثر عملية بعد إنشاء ملف ".csv" في R ، التي سيتم فتحها / مشاركتها مع مستخدمي Excel:

  1. افتح ملف CSV كنص (مفكرة)
  2. انسخه (ctrl + a ، ctrl + c).
  3. ألصقها في ورقة إكسل جديدة - ستتم لصقها كلها في عمود واحد كسلسلة نصية طويلة.
  4. اختر / حدد هذا العمود.
  5. انتقل إلى البيانات- "النص إلى الأعمدة ..." ، على فتح نافذة اختيار "محدد" (المقبل). تحقق من وضع علامة "فاصلة" (وضع علامة تشير إلى أنه سيتم بالفعل إظهار فصل البيانات إلى أعمدة أدناه) (التالي) ، في هذه النافذة يمكنك اختيار العمود الذي تريده ووضع علامة عليه كنص (بدلاً من عام) (إنهاء).

HTH




كان لدي مشكلة مشابهة وهذا هو الحل الذي ساعدني دون الحاجة إلى تحرير محتويات ملف CSV:

إذا كان لديك المرونة لتسمية الملف شيء آخر غير ".csv" ، يمكنك تسميته بامتداد ".txt" ، مثل "Myfile.txt" أو "Myfile.csv.txt". ثم عند فتحه في Excel (وليس عن طريق السحب والإسقاط ، ولكن باستخدام File-> Open أو قائمة الملفات الأكثر استخدامًا مؤخرًا) ، سوف يوفر لك Excel "معالج استيراد النص".

في الصفحة الأولى من المعالج ، اختر "محدد" لنوع الملف.

في الصفحة الثانية من المعالج ، اختر "،" كمحدد واختر أيضًا مؤهل النص إذا كنت قد أحاطت بقيمك بعلامات اقتباس

في الصفحة الثالثة ، حدد كل عمود على حدة وقم بتعيين كل نوع "نص" بدلاً من "عام" لإيقاف Excel من العبث ببياناتك.

آمل أن يساعدك هذا أو أي شخص لديه مشكلة مماثلة!




ما قمت به لهذه المشكلة نفسها هو إضافة ما يلي قبل كل قيمة csv: "=" "" واقتباس مزدوج واحد بعد كل قيمة CSV ، قبل فتح الملف في Excel. خذ القيم التالية على سبيل المثال:

012345,00198475

يجب تغيير هذه قبل فتح في Excel إلى:

"="""012345","="""00198475"

بعد القيام بذلك ، تظهر كل قيمة خلية كصيغة في Excel ومن ثم لن يتم تنسيقها كرقم وتاريخ وما إلى ذلك. على سبيل المثال ، تظهر قيمة 012345 كما يلي:

="012345"



مرحبا لدي نفس المشكلة ،

أكتب هذا vbscipt لإنشاء ملف CSV آخر. سيحتوي ملف CSV الجديد على مساحة في كل حقل ، لذلك سيفهم Excel كأنه نص.

لذلك قمت بإنشاء ملف .vbs مع التعليمات البرمجية أدناه (على سبيل المثال Modify_CSV.vbs) ، حفظ وإغلاقه. قم بسحب وإسقاط الملف الأصلي إلى ملف vbscript الخاص بك. سيقوم بإنشاء ملف جديد مع "SPACE_ADDED" إلى اسم الملف في نفس الموقع.

Set objArgs = WScript.Arguments

Set objFso = createobject("scripting.filesystemobject")

dim objTextFile
dim arrStr ' an array to hold the text content
dim sLine  ' holding text to write to new file

'Looping through all dropped file
For t = 0 to objArgs.Count - 1
    ' Input Path
    inPath = objFso.GetFile(wscript.arguments.item(t))

    ' OutPut Path
    outPath = replace(inPath, objFso.GetFileName(inPath), left(objFso.GetFileName(inPath), InStrRev(objFso.GetFileName(inPath),".") - 1) & "_SPACE_ADDED.csv")

    ' Read the file
    set objTextFile = objFso.OpenTextFile(inPath)


    'Now Creating the file can overwrite exiting file
    set aNewFile = objFso.CreateTextFile(outPath, True) 
    aNewFile.Close  

    'Open the file to appending data
    set aNewFile = objFso.OpenTextFile(outPath, 8) '2=Open for writing 8 for appending

    ' Reading data and writing it to new file
    Do while NOT objTextFile.AtEndOfStream
        arrStr = split(objTextFile.ReadLine,",")

        sLine = ""  'Clear previous data

        For i=lbound(arrStr) to ubound(arrStr)
            sLine = sLine + " " + arrStr(i) + ","
        Next

        'Writing data to new file
        aNewFile.WriteLine left(sLine, len(sLine)-1) 'Get rid of that extra comma from the loop


    Loop

    'Closing new file
    aNewFile.Close  

Next ' This is for next file

set aNewFile=nothing
set objFso = nothing
set objArgs = nothing



لقد صنعت هذا الماكرو vba الذي يقوم بشكل أساسي بتنسيق نطاق الإخراج كنص قبل لصق الأرقام. يعمل بشكل مثالي بالنسبة لي عندما أريد لصق قيم مثل 8/11 و 23/6 و 1/3 وما إلى ذلك دون أن يفسرها Excel كتواريخ.

Sub PasteAsText()
' Created by Lars-Erik Sørbotten, 2017-09-17
Call CreateSheetBackup

Columns(ActiveCell.Column).NumberFormat = "@"

Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

ActiveCell.PasteSpecial

End Sub

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




لا تزال هذه المشكلة موجودة في Mac Office 2011 و Office 2013 ، ولا يمكنني منع حدوثها. يبدو هذا شيء أساسي.

في حالتي كان لدي قيم مثل "1 - 2" و "7 - 12" داخل ملف CSV مغلق بشكل صحيح داخل الفواصل المقلوبة ، وهذا تلقائيا يحول إلى تاريخ ضمن التفوق ، إذا حاولت لاحقا تحويله إلى نص عادي فقط ستحصل عليه تمثيل رقم للتاريخ مثل 43768. بالإضافة إلى ذلك ، يقوم بإعادة تنسيق الأرقام الكبيرة الموجودة في الباركود وأرقام EAN إلى 123E + الأرقام مرة أخرى والتي لا يمكن تحويلها مرة أخرى.

لقد اكتشفت أن جداول بيانات Google Drive من Google لا تحول الأرقام إلى التواريخ. تحتوي الباركود على فواصل فيها كل 3 أحرف ولكن يمكن إزالتها بسهولة. إنه يعالج ملفات CSV بشكل جيد خاصة عند التعامل مع ملفات CSV الخاصة بـ MAC / Windows.

قد احفظ شخص ما في وقت ما.




طريقة بديلة:

قم بتحويل تنسيق العمود الذي تريد تغييره إلى "نص". حدد جميع الخلايا التي تريد الحفاظ عليها ، انسخها. بدون إلغاء تحديد هذه الأعمدة ، انقر فوق "تحرير> لصق خاص> قيم"

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




لصق الجدول في الكلمة. هل بحث / استبدال وتغيير جميع - (شرطات) في - (شرطة مزدوجة). قم بنسخ ولصق في Excel. يمكن القيام بنفس الشيء بالنسبة للرموز الأخرى (/) ، إلخ. إذا احتجت إلى التغيير مرة أخرى إلى شرطة مرة واحدة في Excel ، فقط قم بتنسيق العمود إلى نص ، ثم قم بإجراء التغيير. أتمنى أن يساعدك هذا.




وجدت طريقة بسيطة للقيام بذلك في Excel 2003 خلال 2007. افتح مصنف XLS فارغ. ثم انتقل إلى قائمة البيانات ، وقم باستيراد البيانات الخارجية. حدد ملف CSV الخاص بك. انتقل من خلال المعالج ثم في "تنسيق بيانات الأعمدة" حدد أي عمود يحتاج إلى إرغامه على "نص". سيؤدي ذلك إلى استيراد هذا العمود بأكمله كتنسيق نصي يمنع Excel من محاولة التعامل مع أي خلايا محددة كتاريخ.




أفعل ذلك لأرقام بطاقات الائتمان التي تحافظ على تحويلها إلى رموز علمية: أقوم في نهاية المطاف باستيراد ملفي بتنسيق .csv إلى جداول بيانات Google. تسمح خيارات الاستيراد الآن بتعطيل التنسيق التلقائي للقيم الرقمية. أقوم بتعيين أي أعمدة حساسة إلى نص عادي وتنزيلها كـ xlsx.

إنه سير عمل رهيب ، لكن على الأقل تترك قيمي كما ينبغي أن تكون.




(بافتراض Excel 2003 ...)

عند استخدام معالج Text-to-Columns ، يمكنك في الخطوة 3 إملاء نوع البيانات لكل عمود من الأعمدة. انقر فوق العمود في المعاينة وقم بتغيير عمود misbehaving من "عام" إلى "نص".




أعرف أن هذا هو موضوع قديم. بالنسبة لأولئك مثلي ، الذين لا يزال لديهم هذه المشكلة باستخدام Office 2013 عبر كائن powershell com يمكن استخدام الأسلوب opentext . المشكلة هي أن هذه الطريقة لها العديد من الحجج ، والتي تكون في بعض الأحيان حصرية متبادلة. لحل هذه المشكلة ، يمكنك استخدام طريقة استدعاء - namedparameter المقدمة في هذه المشاركة . على سبيل المثال سيكون

$ex = New-Object -com "Excel.Application"
$ex.visible = $true
$csv = "path\to\your\csv.csv"
Invoke-NamedParameter ($ex.workbooks) "opentext" @{"filename"=$csv; "Semicolon"= $true}

للأسف ، اكتشفت للتو أن هذه الطريقة تقطع بطريقة أو بأخرى تحليل csv عندما تحتوي الخلايا على فواصل خطية. هذا مدعوم من csv ولكن يبدو أن تطبيق microsofts يتم التنصت عليه. كما أنها لم تكتشف بطريقة أو بأخرى الأحرف الألمانية المحددة. إعطاء الثقافة الصحيحة لم يغير هذا السلوك. يتم حفظ جميع الملفات (csv و script) مع ترميز utf8. أولاً كتبت التعليمة البرمجية التالية لإدراج الخلية csv حسب الخلية.

$ex = New-Object -com "Excel.Application"
$ex.visible = $true;
$csv = "path\to\your\csv.csv";
$ex.workbooks.add();
$ex.activeWorkbook.activeSheet.Cells.NumberFormat = "@";
$data = import-csv $csv -encoding utf8 -delimiter ";"; 
$row = 1; 
$data | %{ $obj = $_; $col = 1; $_.psobject.properties.Name |%{if($row -eq1){$ex.ActiveWorkbook.activeSheet.Cells.item($row,$col).Value2= $_ };$ex.ActiveWorkbook.activeSheet.Cells.item($row+1,$col).Value2 =$obj.$_; $col++ }; $row++;}

لكن هذا بطئ شديد ، ولهذا بحثت عن بديل. يتيح لك Excel إمكانية تعيين قيم نطاق من الخلايا ذات المصفوفة. لذا استخدمت الخوارزمية في هذا المدون لتحويل csv في multiarray.

function csvToExcel($csv,$delimiter){
     $a = New-Object -com "Excel.Application"
     $a.visible = $true

    $a.workbooks.add()
     $a.activeWorkbook.activeSheet.Cells.NumberFormat = "@"
     $data = import-csv -delimiter $delimiter $csv; 
     $array = ($data |ConvertTo-MultiArray).Value
     $starta = [int][char]'a' - 1
     if ($array.GetLength(1) -gt 26) {
         $col = [char]([int][math]::Floor($array.GetLength(1)/26) + $starta) + [char](($array.GetLength(1)%26) + $Starta)
     } else {
         $col = [char]($array.GetLength(1) + $starta)
     }
     $range = $a.activeWorkbook.activeSheet.Range("a1:"+$col+""+$array.GetLength(0))
     $range.value2 = $array;
     $range.Columns.AutoFit();
     $range.Rows.AutoFit();
     $range.Cells.HorizontalAlignment = -4131
     $range.Cells.VerticalAlignment = -4160
}

 function ConvertTo-MultiArray {
     param(
         [Parameter(Mandatory=$true, Position=1, ValueFromPipeline=$true)]
         [PSObject[]]$InputObject
     )
     BEGIN {
         $objects = @()
         [ref]$array = [ref]$null
     }
     Process {
         $objects += $InputObject
     }
     END {
         $properties = $objects[0].psobject.properties |%{$_.name}
         $array.Value = New-Object 'object[,]' ($objects.Count+1),$properties.count
         # i = row and j = column
         $j = 0
         $properties |%{
             $array.Value[0,$j] = $_.tostring()
             $j++
         }
         $i = 1
         $objects |% {
             $item = $_
             $j = 0
             $properties | % {
                 if ($item.($_) -eq $null) {
                     $array.value[$i,$j] = ""
                 }
                 else {
                     $array.value[$i,$j] = $item.($_).tostring()
                 }
                 $j++
             }
             $i++
         }
         $array
     } 
} 
csvToExcel "storage_stats.csv" ";"

يمكنك استخدام التعليمات البرمجية أعلاه كما يجب أن تقوم بتحويل أي csvs إلى excel. ما عليك سوى تغيير المسار إلى csv وحرف المحدد في الأسفل.




أثناء إنشاء السلسلة المراد كتابتها إلى ملف CSV في C # ، اضطررت إلى تنسيقها بهذه الطريقة:

"=\"" + myVariable + "\""



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

مثال 1: يتم عرض = ("012345678905") كـ 012345678905

مثال 2: = ("1954-12-12") يعرض كـ 1954-12-12 ، وليس 12/12/1954 .




Related