Re: unable to import csv-Data
- To: mathgroup at smc.vnet.net
- Subject: [mg118998] Re: unable to import csv-Data
- From: David Annetts <david.annetts at iinet.net.au>
- Date: Thu, 19 May 2011 07:42:36 -0400 (EDT)
Hi Andre,
You might be better off with something older ....
inpu = OpenRead["test.csv"];
rawd = ReadList[inpu, Record]; (* read everything into single records *)
Close[inpu];
rawd = StringReplace[#, {"," -> "."}] & /@ rawd; (* account for euro
decimals & other stuff *)
spl = StringSplit[#, ";"] & /@ rawd (* ... before splitting on ;'s *)
TableForm[spl] (* just everything aligns .. *)
which looks OK, although I suspect an error in the second record after
the EUR.
So now you're left with the task of converting dates & times, but that's
straightforward.
D.
On 18/05/2011 19:17, Andre Koppel wrote:
> Hello to all,
>
> I am trying to import some data from a csv-file. But I am absolutely
> unable to get any usefull result.
> I have tried several options to do formating during input, but in every
> case Mathematica 8 puts several csv-columns
> into one result-column.
> Because the csv-data contains germany encoding, I have tried several
> conversion options, but nothing helps.
> Here is a snapshot of the csv-data (one headline two datalines):
> --------------------- cut here ------------------------
> ID;KONTO_NR;KONTO_BEZ;BELEG_DAT;BELEG_NR;GKTO_NR;GKTO_BEZ;BU_TEXT;SOLL;HABEN;Buchsaldo;WAEHRUNG;Faelligkeit;Anfangsbestand;Ausgeblendet;Changed;InsoBaseUser;BuJahr
> 1;;;;;;;;7807477,41;6986382,79;,00;;;False;False;2010-11-01
> 10:24:09.997;KDLB\Conrad;
> 2;D_60004;Jeske, Norbert 23966 Hof
> Triwalk;2008-01-01;;S_09008;Vortrag;EB-Werte durch AIS TaxAudit
> berechnet und erstellt;387,37;,00;387,37;EUR;;True;True;2010-11-01
> 10:24:09.997;KDLB\Conrad;
> --------------------- cut here ------------------------
> I have tried the following import-command (and several versions of it),
> but did not get useful import-result:
> imp = Import["test.csv", "Table", "FieldSeparators" -> ";",
> "DateStringFormat" -> { "Year", "-", "Month", "-", "Day"},
> "CharacterEncoding" -> "ASCII", "HeaderLines" -> 1] ;
>
> For me it looks like the CSV-Importer is unable to detect NULL-Values (;;)?!?
>
> By the way reading the data into excel, writing a resulting xls-file and
> importing the xls-file into mathematica works out of the box,
> but I can't go this way because there are more than 200000 datalines,
> and Excel did not support such a great amount and Mathematica
> was unable to import Excel-2010-Formated xlsx-Data (ods didn't works
> also because of the great amount of data).
>
> Any help would be highly appreciated
> Kind regards
> Andre
>