Best way to use Excel Data

Hi,

To load csv data, I use a modified version of csv2rec for which the
data type of each column is specified explicitly in the data file.
By removing the dtype guessing you get a speedup and you also avoid
potential mess-ups.

Alessio: sadly you right about it not being possible to trust Excel with data.
Could you please give more details on the sqlite method you suggest ?

···

--

by Alessio Civ Oct 10, 2010; 09:04am:

Hi,

a strong advice from someone who is using excel format with tons of
data is to save them in csv and then import in Sqlite.

Excel messes up the data types and gives a lot of troubles with
numbers. Sqlite is fast and data are secure.
The power of this system is that you can query your data and plot what
you need for example.

I can share with you my script to import from csv to sqlite if you want.

--
thanks,
peter butterworth

Hi Butterw,

first of all, if you are working on data a lot, we could get in contact. I
need to work better on my scripts and we could help each other.

I've uploaded my script that imports from csv to sqlite. It's not a totally
clean script, as I am working on something else now I couldn't clean what I
did. Anyway, you should get an idea of what it does.

Basically, the script cleans up the messed data from excel (take out the
comas, puts point, convert numbers to floats). Then, the scripts writes the
resulting tuple in an sqlite database.
This script import over 200000 records in around 3 seconds.

After this, you can access your data connecting to sqlite. Accessing sqlite
data is extremely easy and efficient. I'm uploading a small script to plot
from Sqlite and getting the values for a linear regression, it just works
fine and smoothly.

Let me know if are interested in helping each other.

  http://old.nabble.com/file/p29928938/SqlitePlot_GDF.py SqlitePlot_GDF.py

http://old.nabble.com/file/p29928938/SqliteImportCsv.py SqliteImportCsv.py

butterw wrote:

···

Hi,

To load csv data, I use a modified version of csv2rec for which the
data type of each column is specified explicitly in the data file.
By removing the dtype guessing you get a speedup and you also avoid
potential mess-ups.

Alessio: sadly you right about it not being possible to trust Excel with
data.
Could you please give more details on the sqlite method you suggest ?

--

by Alessio Civ Oct 10, 2010; 09:04am:

Hi,

a strong advice from someone who is using excel format with tons of
data is to save them in csv and then import in Sqlite.

Excel messes up the data types and gives a lot of troubles with
numbers. Sqlite is fast and data are secure.
The power of this system is that you can query your data and plot what
you need for example.

I can share with you my script to import from csv to sqlite if you want.

--
thanks,
peter butterworth

------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
Spend less time writing and rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
Matplotlib-users mailing list
Matplotlib-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/matplotlib-users

--
View this message in context: http://old.nabble.com/Best-way-to-use-Excel-Data-tp29908079p29928938.html
Sent from the matplotlib - users mailing list archive at Nabble.com.

Hi Alessio,

Thank you for the sqlite code example.

What have been the key advantages of using a Database over a
structured array for your applications ?

http://docs.python.org/library/sqlite3.html :
SQLite is a C library that provides a lightweight disk-based database
that doesn’t require a separate server process and allows accessing
the database using a nonstandard variant of the SQL query language.

···

On Sun, Oct 10, 2010 at 12:56 PM, Peter Butterworth <butterw@...287...> wrote:

Hi,

To load csv data, I use a modified version of csv2rec for which the
data type of each column is specified explicitly in the data file.
By removing the dtype guessing you get a speedup and you also avoid
potential mess-ups.

Alessio: sadly you right about it not being possible to trust Excel with data.
Could you please give more details on the sqlite method you suggest ?

--

by Alessio Civ Oct 10, 2010; 09:04am:

Hi,

a strong advice from someone who is using excel format with tons of
data is to save them in csv and then import in Sqlite.

Excel messes up the data types and gives a lot of troubles with
numbers. Sqlite is fast and data are secure.
The power of this system is that you can query your data and plot what
you need for example.

I can share with you my script to import from csv to sqlite if you want.

--
thanks,
peter butterworth

--
thanks,
peter butterworth

Let' put things this way: if you have to work with many records, it is
better if you have a database.

With a database you can query what you need and only this is worthed the
effort of using a DB.

butterw wrote:

···

Hi Alessio,

Thank you for the sqlite code example.

What have been the key advantages of using a Database over a
structured array for your applications ?

http://docs.python.org/library/sqlite3.html :
SQLite is a C library that provides a lightweight disk-based database
that doesn’t require a separate server process and allows accessing
the database using a nonstandard variant of the SQL query language.

On Sun, Oct 10, 2010 at 12:56 PM, Peter Butterworth <butterw@...287...> > wrote:

Hi,

To load csv data, I use a modified version of csv2rec for which the
data type of each column is specified explicitly in the data file.
By removing the dtype guessing you get a speedup and you also avoid
potential mess-ups.

Alessio: sadly you right about it not being possible to trust Excel with
data.
Could you please give more details on the sqlite method you suggest ?

--

by Alessio Civ Oct 10, 2010; 09:04am:

Hi,

a strong advice from someone who is using excel format with tons of
data is to save them in csv and then import in Sqlite.

Excel messes up the data types and gives a lot of troubles with
numbers. Sqlite is fast and data are secure.
The power of this system is that you can query your data and plot what
you need for example.

I can share with you my script to import from csv to sqlite if you want.

--
thanks,
peter butterworth

--
thanks,
peter butterworth

------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
Spend less time writing and rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
Matplotlib-users mailing list
Matplotlib-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/matplotlib-users

--
View this message in context: http://old.nabble.com/Best-way-to-use-Excel-Data-tp29908079p29968676.html
Sent from the matplotlib - users mailing list archive at Nabble.com.

pyTables is worth a look, too"

http://www.pytables.org/moin

-Chris

···

On 10/14/10 9:52 PM, Alessio Civ wrote:

Let' put things this way: if you have to work with many records, it is
better if you have a database.

--
Christopher Barker, Ph.D.
Oceanographer

Emergency Response Division
NOAA/NOS/OR&R (206) 526-6959 voice
7600 Sand Point Way NE (206) 526-6329 fax
Seattle, WA 98115 (206) 526-6317 main reception

Chris.Barker@...259...