Jak mogę powstrzymać Excela przed zjedzeniem moich pysznych plików CSV i wydaleniem bezużytecznych danych?

Mam bazę danych, która śledzi sprzedaż widżetów według numeru seryjnego. Użytkownicy wprowadzają dane i liczbę nabywców oraz skanują każdy widżet do niestandardowego programu klienckiego. Następnie finalizują zamówienie. To wszystko działa bezbłędnie.

Niektórzy klienci chcą arkusza kalkulacyjnego zgodnego z Excelem zakupionych widżetów. Generujemy to za pomocą skryptu PHP, który wysyła zapytanie do bazy danych i wyprowadza wynik jako plik CSV z nazwą sklepu i powiązanymi danymi. Działa to również doskonale.

Po otwarciu w edytorze tekstu, takim jak Notatnik lub vi, plik wygląda następująco:

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

Jak widać, numery seryjne są obecne (w tym przypadku dwa razy, nie wszystkie kolejne numery seryjne są takie same) i są długimi ciągami liczb. Po otwarciu tego pliku w programie Excel wynik staje się:

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

Jak można zauważyć, numery seryjne są ujęte w podwójne cudzysłowy. Wydaje się, że Excel nie szanuje kwalifikatorów tekstowych w plikach .csv. Podczas importowania tych plików do programu Access mamy zerową trudność. Podczas otwierania ich jako tekstu nie ma żadnych problemów. Ale Excel bez wątpienia konwertuje te pliki na bezużyteczne śmieci. Próba instruowania użytkowników końcowych w sztuce otwierania pliku CSV za pomocą aplikacji innej niż domyślna staje się, powiedzmy, męcząca. Czy jest nadzieja? Czy istnieje ustawienie, którego nie mogłem znaleźć? Wydaje się, że tak jest w przypadku programów Excel 2003, 2007 i 2010.

Ale Excel bez wątpienia konwertuje te pliki na bezużyteczne śmieci.

Excel jest bezużytecznym śmieciem.

Rozwiązanie

Byłbym trochę zaskoczony, gdyby jakikolwiek klient chcący danych w formacie Excel nie był w stanie zmienić widocznego formatowania tych trzech kolumn na „Liczba” z zerowymi miejscami dziesiętnymi lub na „tekst”. Załóżmy jednak, że krótki dokument instruktażowy nie wchodzi w rachubę.

Twoje opcje to:

1. Wrzuć nieliczbowy, a nie biały znak do numerów seryjnych.
2. Zapisz plik xls lub xlsx z domyślnym formatowaniem.
3. Oszukuj i wyprowadzaj te liczby jako formuły ="268435459705526269","",="268435459705526269"(możesz także ="268435459705526269",,="268435459705526269"zaoszczędzić sobie 2 znaki). Ma to tę zaletę, że wyświetla się poprawnie i prawdopodobnie jest ogólnie przydatne, ale subtelnie zepsute (ponieważ są to formuły).

Uważaj na opcję 3, ponieważ niektóre programy (w tym Excel i Open Office Calc) nie będą już traktować przecinków wewnątrz =""pól jako znaków ucieczki. Oznacza to, że ="abc,xyz"obejmie dwie kolumny i przerwie import.

Użycie formatu "=""abc,xy"""rozwiązuje ten problem, ale ta metoda nadal ogranicza Cię do 255 znaków z powodu limitu długości formuły programu Excel.

Mieliśmy podobny problem, gdy mieliśmy pliki CSV z kolumnami zawierającymi zakresy, takie jak 3-5, a Excel zawsze konwertuje je na daty, np. 3-5 to 3 marca, po czym przełączenie z powrotem na numeryczne daje nam bezużyteczną liczbę całkowitą daty. Obejdziemy to

1. Zmiana nazwy rozszerzenia CSV na TXT
2. Następnie, gdy otworzyliśmy go w programie Excel, uruchomiłoby się kreator importu tekstu
3. W kroku 3 z 3 w kreatorze powiedzieliśmy, że kolumny, o których mowa, to tekst i zostały one poprawnie zaimportowane.

Myślę, że możesz zrobić to samo tutaj.

Twoje zdrowie

Lepszym rozwiązaniem jest wygenerowanie skoroszytu XML. Lubię to:

<?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>

Plik musi mieć rozszerzenie .xml. Excel i OpenOffice otwierają go poprawnie.

Moje rozwiązanie: Mam ten sam problem z importowaniem numerów seryjnych. Nie muszą być traktowane jak liczby, tzn. Nie są na nim wykonywane żadne funkcje matematyczne, ale potrzebujemy tam całej liczby. Najprostszą rzeczą jest wstawienie spacji do numeru seryjnego. np. „12345678 90123456 1234”. Kiedy Excel go importuje, będzie traktowany jako tekst zamiast cyfr.

Miałem długie numery kont, które były zniekształcone.

Tak to naprawiłem:

Otwórz plik.csv w pakiecie Libre Office / Open Office (może być konieczne określenie ograniczników itp.), A następnie zapisz plik jako plik Excel Excel.

Następnie otwórz ten plik w programie Excel, a zobaczysz, że kolumny nie są już zmieniane w format naukowy lub cokolwiek innego. Aby być bezpiecznym, kliknij kolumnę prawym przyciskiem myszy i jawnie ustaw format na Tekst, a następnie zapisz jako format pliku Excel.

Otwórz plik w formacie Excel, a kolumna nadal powinna być w porządku!

Kreator importu to najlepsze rozwiązanie dla zwykłych użytkowników i sytuacji jednorazowych. Jeśli potrzebujesz rozwiązania programowego, możesz użyć metody QueryTables.Add (która jest tym, czego używa Kreator importu za scenami).

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