Opmaak van een door komma's gescheiden CSV om Excel te dwingen de waarde als een string te interpreteren

Voor degenen die controle hebben over de brongegevens, blijkbaar zal Excel het formaat van een CSV-veld automatisch detecteren, tenzij de CSV-kolom dit formaat heeft:

"=""Data Here"""

bv…

20, 5.5%, "0404 123 351", "3-6", "=""123"""
[number] [percent] [number] [date] [string] <-- how Excel interprets

Het werkt ook in Google Spreadsheet, maar ik weet niet zeker of andere spreadsheet apps deze notatie ondersteunen.

Als je vermoedt dat de gegevens zelf aanhalingstekens kunnen bevatten, moet je ze dubbel-escapen, zoals dit…

"=""She said """"Hello"""" to him"""

(EDIT: bijgewerkt met correcties, bedankt DMA57361!)

Zoals velen heb ik geworsteld met dezelfde beslissingen die Microsoft neemt en heb ik verschillende voorgestelde oplossingen geprobeerd.

Voor Excel 2007 geldt het volgende:

• Alle waarden tussen dubbele aanhalingstekens zetten helpt NIET
• Een = voor alle waarden zetten na ze tussen dubbele aanhalingstekens te hebben gezet helpt WEL, MAAR maakt het csv-bestand onbruikbaar voor de meeste andere toepassingen
• Haakjes zetten rond de dubbele aanhalingstekens rond alle waarden is onzin
• Een spatie zetten voor alle waarden alvorens er dubbele aanhalingstekens rond te zetten voorkomt conversies naar datums, maar voorkomt NIET het wegsnoeien van voorloop- of naloopnullen.
• Het plaatsen van een enkel aanhalingsteken voor een waarde werkt alleen bij het invoeren van gegevens in Excel.

Echter:

Het plaatsen van een tab vóór alle waarden en vervolgens dubbele aanhalingstekens eromheen voorkomt conversies naar datums EN voorkomt het trimmen van voorloop- of naloopnullen en het blad toont niet eens vervelende waarschuwingstekens in de linkerbovenhoek van elke cel.

Bijv:

"<tab character><some value>","<tab character><some other value>"

Merk op dat het tab-teken binnen de dubbele aanhalingstekens moet staan. Edit: het blijkt dat de dubbele aanhalingstekens niet eens nodig zijn.

Dubbelklikken op het csv bestand kan het bestand openen als een spreadsheet in Excel met alle waarden die worden behandeld als net boven, als tekst gegevens. Zorg ervoor dat je Excel instelt om de ‘.’ als decimaalteken te gebruiken en niet de ‘,’ of elke regel van het csv bestand zal eindigen als één tekst in de eerste cel van elke rij. Blijkbaar denkt Microsoft dat CSV betekent “Niet de decimale punt” Gescheiden Waarde.

Met behulp van de importfunctionaliteit van Excel kunt u opgeven in welk formaat (auto, tekst of datum) elke kolom moet worden geïnterpreteerd en hoeven de gegevensbestanden niet te worden gewijzigd.

U kunt het vinden als Data → Get External Data → From Text in Excel 2007/2010.
Of Data → Import External Data → Import Data in Excel 2003.

Hier is een afbeelding van de Excel 2003 Tekst Import Wizard in actie op de gegeven voorbeeld gegevens, waarbij ik de laatste twee kolommen als tekst heb geïmporteerd:

Het voorbeeld van Simon werkte niet voor mij, en ik vermoed dat het een taalverschil is. In C# ziet mijn werkende format string er zo uit:

var linebreak = (i++ == list.Count) ? "" : "\r\n";

csv += String.Format("=\"{0}\",{1},{2},{3},=\"{4}\"{5}",
    item.Value, item.Status, item.NewStatus, item.Carrier, c.Status, linebreak);

en zo ziet het uitvoerbestand er uit:

="abababababab",INVALID,INVALID,USPS,="",
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9500100030492359000149",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9400110200793482982812",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9400110200793482982812",UNKNOWNSTATUS,DELIVERED,USPS,="3"
="9400110200793000216184",UNKNOWNSTATUS,INVALID,USPS,=""

Zoals te zien is, is het formaat in het uitvoerbestand ="VALUE", en niet "=""VALUE""",, waarvan ik denk dat het een Visual Basic conventie is.

Ik gebruik Excel 2010. Overigens, Google Sheets zal een bestand dat op deze manier is geformatteerd niet openen/converteren. Het zal werken als je het gelijkheidsteken verwijdert dus "VALUE", - Excel zal nog steeds het bestand openen maar het feit negeren dat je wilt dat je kolommen strings zijn.

Een eenvoudige manier om Excel te dwingen de datum als tekst te interpreteren is om een enkel aanhalingsteken voor de datum te plaatsen, in plaats van volledige aanhalingstekens te gebruiken, zoals in:

‘10/11/2002

Als je de CSV kunt importeren in plaats van hem te openen, kun je Excel vertellen welk formaat elke kolom moet hebben. Kijk eens naar deze vraag die ik stelde .