Formatowanie CSV ograniczonego przecinkami, aby zmusić Excela do interpretowania wartości jako ciąg znaków

Dla tych, którzy mają kontrolę nad danymi źródłowymi, najwyraźniej Excel będzie automatycznie wykrywał format pola CSV, chyba że kolumna CSV jest w tym formacie:

"=""Data Here"""

np.

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

Działa to również w Google Spreadsheet, ale nie jestem pewien, czy inne aplikacje arkuszy kalkulacyjnych obsługują tę notację.

Jeśli podejrzewasz, że dane mogą zawierać same cudzysłowy, musisz je podwoić, tak jak w tym przypadku…

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

(EDIT: Zaktualizowane z poprawkami, dzięki DMA57361!)

Podobnie jak wielu, borykałem się z tymi samymi decyzjami, które podejmuje Microsoft i próbowałem różnych sugerowanych rozwiązań.

Dla Excela 2007 idą następujące:

• Umieszczenie wszystkich wartości w podwójnych cudzysłowach NIE pomaga
• Umieszczenie = przed wszystkimi wartościami po umieszczeniu ich w podwójnych cudzysłowach POMAGA, ALE sprawia, że plik csv jest bezużyteczny dla większości innych aplikacji
• Umieszczenie nawiasów wokół podwójnych cudzysłowów wokół wszystkich wartości jest śmieciem
• Umieszczenie spacji przed wszystkimi wartościami przed umieszczeniem wokół nich podwójnych cudzysłowów NIE zapobiega konwersji na daty, ale NIE zapobiega przycinaniu wiodących lub ciągnących zer.
• Umieszczanie pojedynczego cudzysłowu przed wartością działa tylko podczas wprowadzania danych w Excelu.

Jednak:

Umieszczenie tabulatora przed wszystkimi wartościami przed umieszczeniem wokół nich podwójnych cudzysłowów NIE zapobiega konwersji na daty I NIE zapobiega przycinaniu wiodących lub ciągnących się zer, a arkusz nie pokazuje nawet paskudnych znaczników ostrzegawczych w lewym górnym rogu każdej komórki.

Np:

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

Zauważ, że znak tabulacji musi znajdować się wewnątrz podwójnych cudzysłowów. Edit: okazuje się, że podwójne cudzysłowy nie są nawet konieczne.

Podwójne kliknięcie pliku csv może otworzyć plik jako arkusz kalkulacyjny w Excelu, pokazując wszystkie wartości, które są traktowane jak wyżej, jak dane tekstowe. Upewnij się, że Excel używa ‘.’ jako kropki dziesiętnej, a nie ‘,’ lub każdy wiersz pliku csv będzie kończył się jako jeden tekst w pierwszej komórce każdego wiersza. Najwyraźniej Microsoft uważa, że CSV oznacza “Nie przecinek dziesiętny” Separated Value.

Użycie funkcji importu w Excelu pozwala na określenie formatu (auto, tekst lub data), w jakim mają być interpretowane poszczególne kolumny i nie wymaga modyfikacji plików z danymi.

W Excelu 2007/2010 można to znaleźć jako Data → Get External Data → From Text.
lub Data → Import External Data → Import Data w Excelu 2003.

Oto obraz Kreatora importu tekstu w Excelu 2003 w akcji na podanym przykładzie danych, pokazujący, że importuję dwie ostatnie kolumny jako tekst:

Przykład od Simona nie zadziałał dla mnie i podejrzewam, że jest to różnica językowa. W C# oto jak wygląda mój roboczy ciąg formatu:

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);

a tak wygląda plik wyjściowy:

="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,=""

Jak widać, format w pliku wyjściowym to ="VALUE", a nie "=""VALUE""",, co moim zdaniem może być konwencją Visual Basic.

Używam programu Excel 2010. Nawiasem mówiąc, Google Sheets nie otworzy/konwertuje pliku sformatowanego w ten sposób. Będzie działać, jeśli usuniesz znak równości, a więc "VALUE", - Excel nadal będzie otwierał plik, ale zignoruje fakt, że chcesz, aby twoje kolumny były ciągami znaków.

Prostym sposobem na zmuszenie Excela do interpretowania daty jako tekstu jest umieszczenie pojedynczego cudzysłowu przed datą, zamiast używania pełnych cudzysłowów, jak w przypadku:

‘10/11/2002

Jeśli możesz zaimportować CSV zamiast go otwierać, możesz powiedzieć Excelowi, jaki format powinna mieć każda kolumna. Spójrz na to pytanie, które zadałem .