Artikel
Varför Excel förvanskar dina CSV-filer
Ett patient-ID som börjar med tre nollor. En gen som heter SEPT2. Ett kontonummer på sexton siffror. Öppna något av dessa i ett kalkylblad som en vanlig CSV-fil och Excels importfunktion visar dem inte bara, den skriver om dem: nollorna försvinner, gennamnet blir ett datum, och kontonumret tappar sina sista siffror. Inget av detta är egentligen ett fel, det är vad som händer när en allmän gissare av tal och datum möter text som bara ser ut som ett tal eller ett datum. Här är vad som faktiskt händer med en CSV-fil i samma stund Excel öppnar den, och hur du stoppar det innan det sker.
Inledande nollor försvinner
Skriv 02134 i en cell och Excels standardkolumntyp, Allmänt, läser det som ett tal och tappar den inledande nollan: 2134. Det drabbar varje kod som lagras som siffror men egentligen inte är en mängd, amerikanska postnummer, franska postnummer, anställnings- eller fakturanummer, telefonnummer. Rent tekniskt går inget förlorat i den underliggande CSV-filen, texten intill cellen förblir oförändrad, ända tills du sparar arbetsboken, då Excel skriver tillbaka vad det visade, och nollorna är borta för gott. Lösningen är att tala om för Excel att kolumnen är text innan den hinner gissa: via Data > Från text/CSV (eller Hämta data > Från fil) kan du ställa in varje kolumns datatyp explicit, eller lägga till en apostrof före ett värde i en enskild cell för att tvinga fram text.
Text blir till datum utan att fråga
Samma gissare letar också efter datumformad text. En cell som innehåller MAR1 eller SEPT2, tolkad som 1 mars eller 2 september, konverteras i tysthet till ett datumvärde och formateras om, utan att någon varning visas. Det här är inte hypotetiskt: 2020 döpte HUGO Gene Nomenclature Committee om ungefär 27 mänskliga gensymboler, däribland SEPT2 och MARCH1, specifikt eftersom kalkylprogram fortsatte att omvandla dem till datum varje gång ett dataset öppnades, ett problem allvarligt nog att en genomgång av genomikartiklar från 2016 hittade autokorrigeringsfel av det här slaget i ungefär en av fem publikationer som levererade Excel-bilagor. Lärdomen gäller bortom gennamn: vilken kort alfanumerisk kod som helst som råkar se ut som en dag och en månad riskerar det här i samma stund någon öppnar din CSV i ett kalkylblad, inte bara du.
Mycket långa tal tappar sina sista siffror
Excel lagrar varje tal som ett flyttalsvärde med 15 signifikanta siffrors precision, en gräns Microsoft dokumenterar direkt. Ett kort- eller kontonummer på sexton siffror som 4111111111111111 avrundas för att passa, så den sista siffran eller de två sista blir i tysthet noll och värdet är inte längre det du skrev. Att formatera cellen efteråt tar inte tillbaka de förlorade siffrorna, eftersom det underliggande lagrade talet redan har avrundats; enda lösningen är att importera kolumnen som text, eller föregå värdet med en apostrof, innan Excel någonsin behandlar det som ett tal.
Text med accenttecken blir till trasiga tecken
Teckenkodning är en separat feltyp skild från autokorrigering. En CSV-fil utan byteordningsmärke är rena byte; om den sparades som UTF-8 kommer Excel på Windows oftast att öppna den och i stället anta systemets egen kodsida, så bokstäver med accenttecken, valutasymboler och emoji kommer ut som trasiga tecken, en sträng som café visas som något närmare café. Den säkra vägen när du skapar en CSV för Excel är att spara den som CSV UTF-8 eller lägga till ett UTF-8-byteordningsmärke; den säkra vägen när du tar emot en är att importera via Data > Från text/CSV, vilket låter dig välja källkodningen explicit i stället för att lita på att en dubbelklickning gissar rätt.
Att flytta data mellan format utan att öppna den i Excel
Inget av detta är egentligen ett Excel-fel, det är vad ett kalkylblads gissare av tal och datum är byggd för att göra, och det gäller oavsett hur filen öppnas, inte bara i Excel. Det säkraste sättet att omforma en CSV-fil eller kontrollera att en kolumn verkligen förblir text innan den når något kalkylblad är att bearbeta den direkt: vår datakonverterare tolkar CSV med en standardtolkare som förstår citattecken och omtolkar aldrig ett värdes typ, så ett postnummer eller gennamn förblir exakt den text du gav den. Den körs helt i din webbläsare, ingenting du klistrar in laddas upp någonstans. Det skyddar dock bara konverteringssteget: ska resultatet ändå öppnas i ett kalkylblad efteråt, markera de känsliga kolumnerna som text där också.
Verktyg i den här artikeln
Vanliga frågor
Hur stoppar jag Excel från att ta bort inledande nollor från en CSV?
Öppna inte filen genom att dubbelklicka på den. Importera den i stället via Data > Från text/CSV (eller Hämta data), som visar en datatypväljare kolumn för kolumn; ställ in kolumnen för postnummer, ID eller telefonnummer på Text innan du slutför importen. En snabb lösning per cell är att skriva en apostrof före värdet, som '02134, vilket tvingar fram text utan att ändra vad som visas.
Varför gjorde Excel om mitt ID eller min kod till ett datum?
Excels kolumntyp Allmänt letar aktivt efter datumformad text, mönster som ett kort ord plus ett tal, eller två tal separerade med bindestreck eller snedstreck, och konverterar allt som matchar, utan att fråga om bekräftelse. Det är exakt vad som tvingade fram omdöpningen av gensymboler som SEPT2 och MARCH1 2020. Importera kolumnen som Text, på samma sätt som för inledande nollor, för att stoppa gissningen innan den sker.
Undviker jag de här problemen helt om jag konverterar min CSV på ett annat sätt?
Det undviker dem för det konverteringssteget, inte för alltid. En tolkare som behandlar varje fält som ren text, som vår datakonverterare, skriver inte om ett postnummer eller ett gennamn när den gör om en CSV till JSON. Men om resultatet ändå senare öppnas i ett kalkylblad, av dig eller av den som tar emot det, får det programmets egen importfunktion en ny chans att omtolka värdena, så de känsliga kolumnerna behöver markeras som text där också.