Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.soft-sys.math.mathematica > #2457 > unrolled thread

unable to import csv-Data

Back to article view | Back to comp.soft-sys.math.mathematica

Contents

  unable to import csv-Data Andre Koppel <akoppel@akso.de> - 2011-05-18 11:17 +0000
    Re: unable to import csv-Data Albert Retey <awnl@gmx-topmail.de> - 2011-05-19 11:44 +0000
    Re: unable to import csv-Data David Bailey <dave@removedbailey.co.uk> - 2011-05-19 11:42 +0000

#2457 — unable to import csv-Data

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

-- 
Andre Koppel Software GmbH
Prinz-Handjery-Str. 38
14167 Berlin
Tel.: (+4930) 810 09 190
Fax: (+4930) 326 01 046
www.invep.de
www.akso.de

Eingetragen beim Amtsgericht
Berlin Charlottenburg HRB92600
Geschäftsführer Andre Koppel

[toc] | [next] | [standalone]

#2486

Hi,

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

> For me it looks like the CSV-Importer is unable to detect
> NULL-Values  (;;)?!?

I think that the option:

"RepeatedSeparators" -> False

would get rid of that problem, but I'm not sure whether it will make 
everything go right. Alternatively you could also try to StringSplit bz 
hand where a ; appears. This would also have the advantage that you 
could read and process the data in junks with ReadList, which might or 
might not be necessary with 200000 lines of data.

hth,

albert

PS: considering the high standard of data privacy in Europe and 
especially Germany I would be very careful about sending personal data 
of any kind to a newsgroup...

[toc] | [prev] | [next] | [standalone]

#2493

On 18/05/2011 12: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
>
I think one approach may be to:

1) Read the file into Mathematica as a list of strings with 
ReadList[file,String]

2) Merge in "\n" newlines into the list with Riffle.

3) Use StringReplace to "Anglicise" the file. You may need to use 
several nested calls to this function to make sure semicolons don't get 
replaced by commas before the original commas get replaced.

4)  Use ImportString to import the end result.

String operations are remarkably fast, so this may work more efficiently 
than you expect!

David Bailey
http://www.dbaileyconsultancy.co.uk

[toc] | [prev] | [standalone]

Back to top | Article view | comp.soft-sys.math.mathematica

csiph-web