Re: Importing Excel/CVS into Database

[Boris Samorodov]

On Fri, 24 Aug 2007 22:27:13 - robo wrote:

> Wow, your tip works like a charm.

Glad to be helpful!

> Do you personally know of a good tutorial that teaches things like

Sorry, I don't. :-(

> this? I'd like to read them.


WBR
-- 
bsam

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[robo]

Wow, your tip works like a charm.

Do you personally know of a good tutorial that teaches things like
this? I'd like to read them.

Thanks,

- robo

On Aug 24, 2:54 pm, Boris Samorodov <[EMAIL PROTECTED]> wrote:
> Hi, All!
>
> On Fri, 24 Aug 2007 21:05:07 - robo wrote:
>
> > How would I catch a blank/empty line?
> > I've googled and tried:
> > if not line.strip():
> >   print "skipping blank line"
> >   continue
> > if line.strip() == '':
> >   print "skipping blank line"
> >   continue
>
> I used to do:
> -
> line = line.strip()
> if len(line) == 0:
>   print "skipping blank line"
> -
>
> > the script keeps spewing data into the DB, but doesn't seem like its
> > catching blank/empty lines because the print statements aren't
> > executed.
> > What is the appropriate way to catch blank/empty lines?
>
> HTH & WBR
> --
> bsam


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[Boris Samorodov]

Hi, All!


On Fri, 24 Aug 2007 21:05:07 - robo wrote:

> How would I catch a blank/empty line?

> I've googled and tried:

> if not line.strip():
>   print "skipping blank line"
>   continue

> if line.strip() == '':
>   print "skipping blank line"
>   continue

I used to do:
-
line = line.strip()
if len(line) == 0:
  print "skipping blank line"
-

> the script keeps spewing data into the DB, but doesn't seem like its
> catching blank/empty lines because the print statements aren't
> executed.

> What is the appropriate way to catch blank/empty lines?


HTH & WBR
-- 
bsam

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[robo]

How would I catch a blank/empty line?

I've googled and tried:

if not line.strip():
  print "skipping blank line"
  continue

if line.strip() == '':
  print "skipping blank line"
  continue

the script keeps spewing data into the DB, but doesn't seem like its
catching blank/empty lines because the print statements aren't
executed.

What is the appropriate way to catch blank/empty lines?

Thanks,

- robo


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[robo]

Good news guys, this code is working for me:

import MySQLdb
import csv

def imports():
  db = MySQLdb.connect(user='...', db='...', passwd='...', host='...')
  cursor = db.cursor()
  file = "C:\\test_import.txt"
  for i, line in enumerate(open(file)):
if i == 0:
  continue
data_first, data_last, data_phone, data_email =
line.rstrip('\n').split('\t')
statement = "Inserting " + data_first + ", " + data_last + ", " +
data_phone + ", " + data_email
print statement
cursor.execute("""
  INSERT INTO people
  (first, last, phone, email)
  VALUES
  (%s, %s, %s, %s)
  """, (data_first, data_last, data_phone, data_email)
)
  return "Job Finished"

This method seems faster than having to deal with Django models.  And
to think, I was about to buy Navicat :P.

Thank you so much for all your help!

-robo


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[Amirouche]

## data.csv ##
"name","symbol","value","change","mktcap","volume","open","high","low"
"Google Inc. ","GOOG ","512.19","-0.56 (-0.11%) ","159.87B ","3.08M ",
516.13,516.13,507
"Novell, Inc. ","NOVL ","6.84","-0.05 (-0.73%) ","2.39B ","4.49M ",
6.95,6.96,6.76
"Intl Business Machines Corp. ","IBM ","111.45","+1.45 (1.32%)
","151.62B ","8.27M ",110.4,111.68,110.01
"Microsoft Corporation ","MSFT ","28.30Â Â  ","+0.08 (0.28%)
","265.33B ","33.89M ",28.3,28.33,28.1
"Yahoo! Inc. ","YHOO ","23.13","-0.10 (-0.43%) ","30.98B ","15.59M ",
23.35,23.36,22.95
"Apple Inc. ","AAPL ","131.07","-1.44 (-1.09%) ","113.98B ","30.93M ",
133.26,133.34,129.76
"Red Hat, Inc. ","RHT ","19.24","-0.32 (-1.64%) ","3.72B ","2.30M ",
19.86,19.86,19.08
"Sun Microsystems, Inc. ","SUNW ","4.93","+0.07 (1.44%) ","17.60B
","92.71M ",4.87,4.93,4.8
"Adobe Systems Incorporated ","ADBE ","40.60","-0.17 (-0.42%)
","23.89B ","3.60M ",40.82,40.99,40.33

well now, we want to feed some database with this... have a look at
models + FinanceParser, a class which inherit from csv.DictReader
http://dpaste.com/17586/

how to use it ?

simple

f = open('data.csv')
parser = FinancerParser(f)
for row in parser:
o = PortfolioEntry()
data = parser.next()
o.__dict__.update(data)
o.save()

hope it helps.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[Tim Chase]

> Tim, in your code:
> 
>filename = 'foo.tab'
>for i, line in enumerate(open(filename)):
>  if i == 0: continue # skip the header row
>  (fieldname1, fieldname2, fieldname3 ...
>   ) = line.rstrip('\n').split('\t')
>  cursor.execute("""
>INSERT INTO app_foo
>(f1, f3, f2)
>VALUES
>(%s, %s, %s)
>""", (fieldname1, fieldname3, fieldname2)
>)
> 
> f1, f2, and f3 would be my "first", "last", and "phone", and the
> fieldname1 to fieldname3 would be the data from "first", "last", and
> "phone"?

The (fieldname1, fieldname2, fieldname3) assignment at the top 
pulls the values from the line into those particular variables. 
In your case you might want to write the above as

   filename = 'foo.tab'
   for i, line in enumerate(open(filename)):
 if i == 0: continue # skip the header row

 # these are the columns in the given row
 (last_name, first_name, phonenumber, email, dob, ...
  ) = line.rstrip('\n').split('\t')
 cursor.execute("""
   INSERT INTO app_foo
   (first, last, phone)
   VALUES
   (%s, %s, %s)
   """, (first_name, last_name, phonenumber)
   )

If you happen to know the order of the columns in your 
tab-separated file, and they don't need any further manipulation 
(such as changing case, stripping whitespace, calculating or 
dropping columns, etc), you can even simplify the above to


   for i, line in enumerate(open(filename)):
 if i == 0: continue # skip the header row
 # assumes the line is "first\tlast\tphone"
 row = line.rstrip('\n').split('\t')
 cursor.execute("""
   INSERT INTO app_foo
   (first, last, phone)
   VALUES
   (%s, %s, %s)
   """, row
   )

If you know your files aren't all that big (swamping memory), you 
can even get grotesque with it, doing things like

  cursor.executemany("""
   INSERT INTO app_foo
   (first, last, phone)
   VALUES
   (%s, %s, %s)
   """,
[row.rstrip('\n').split('\t')
 for i, row in enumerate(open(filename))
 if i > 0])

Huzzah for list comprehensions ;)  If executemany() supports 
iterators rather than requiring lists, you could omit the square 
brackets, and it wouldn't matter how large your file-size was. 
The executemany() call is considerably more efficient if you have 
large volumes of preparable statements.

-tim




--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[robo]

**Ben Ford, can you explain a bit more about your code?:

for row in reader:
   obj = MyDjangoModel()
   obj.__dict__ = row
   obj.save()

Correct my interpretation if it's wrong. If my CSV file has columns
"first", "last", "phone", and "email", then MyDjangoModel would
contain those 4 fields. Then, obj.__dict__ = row would contain data
from those 4 CSV column types? I was thinking of something like
obj.first = first_CSV, obj.last = last_CSV, and so on. But the most
important thing first, before I mock your code, I need to import csv
right?

**Aidas Bendoraitis and **Tim Chase, your methods look promising.
Tim, in your code:

   filename = 'foo.tab'
   for i, line in enumerate(open(filename)):
 if i == 0: continue # skip the header row
 (fieldname1, fieldname2, fieldname3 ...
  ) = line.rstrip('\n').split('\t')
 cursor.execute("""
   INSERT INTO app_foo
   (f1, f3, f2)
   VALUES
   (%s, %s, %s)
   """, (fieldname1, fieldname3, fieldname2)
   )

f1, f2, and f3 would be my "first", "last", and "phone", and the
fieldname1 to fieldname3 would be the data from "first", "last", and
"phone"?

**Amirouche, your method is similar to Ben's, could you give me some
examples?

Thanks

-Robo


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[Ben Ford]

It worked? Sweet ;-)

On 23/08/07, Amirouche <[EMAIL PROTECTED]> wrote:
>
>
>
>
> On 22 août, 09:51, "Ben Ford" <[EMAIL PROTECTED]> wrote:
> > A quick hack that should work would be to create a DictReader object to
> read
> > the csv with (have a look in the python docs
> > here).
>
> That's actullay what I did but I didn't know about the obj.__dict__ =
> MyDict trick :)
>
> thanks
>
>
> >
>


-- 
Regards,
Ben Ford
[EMAIL PROTECTED]
+628111880346

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[Amirouche]

On 22 août, 09:51, "Ben Ford" <[EMAIL PROTECTED]> wrote:
> A quick hack that should work would be to create a DictReader object to read
> the csv with (have a look in the python docs
> here).

That's actullay what I did but I didn't know about the obj.__dict__ =
MyDict trick :)

thanks


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[Ben Ford]

A quick hack that should work would be to create a DictReader object to read
the csv with (have a look in the python docs
here).
For each row you need to write do:

for row in reader:
   obj = MyDjangoModel()
   obj.__dict__ = row
   obj.save()

I haven't tested this, but I've used  the csv module a fair bit and th
syntax should be close enough.
Ben

On 22/08/07, Amirouche <[EMAIL PROTECTED]> wrote:
>
>
>
>
> On Aug 21, 8:37 pm, robo <[EMAIL PROTECTED]> wrote:
> > Have any of you guys imported excel/cvs by using Python/Django? I need
> > to import 1000 products for a shopping site and I'd like to learn the
> > fastest and easiest way to do so.
>
> I already did this, but in another way.
> 1. I write Models,
> 2. I parse line by line the csv file (using python library)
> 3. create for each line a Model Instance with the data extracted from
> the file
>
> I prefer, because I had to  change some values, and I prefer to do
> this in python :)
>
>
> >
>


-- 
Regards,
Ben Ford
[EMAIL PROTECTED]
+628111880346

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[Amirouche]

On Aug 21, 8:37 pm, robo <[EMAIL PROTECTED]> wrote:
> Have any of you guys imported excel/cvs by using Python/Django? I need
> to import 1000 products for a shopping site and I'd like to learn the
> fastest and easiest way to do so.

I already did this, but in another way.
1. I write Models,
2. I parse line by line the csv file (using python library)
3. create for each line a Model Instance with the data extracted from
the file

I prefer, because I had to  change some values, and I prefer to do
this in python :)


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[Aidas Bendoraitis]

Yes, I've done that. The following function read_excel_csv works with
Excel CSV files saved on Mac (I can't remember exactly, but it might
be that MS Office for Mac OS X saves CSV files using different
separators than on Windows). The function reads the rows line by line,
parses the cells of each row and passes the values in a list to your
custom function.

###
import csv

def test(l):
print l

def read_excel_csv(filepath, function):
f = open(filepath, "rb")
line_list = "".join(f.readlines()).split("\r")
reader = csv.reader(line_list, delimiter=";", doublequote=True)
for value_list in reader:
function(value_list)

>>> read_excel_csv("/some/path/table.csv", test)
###

Good luck!
Aidas Bendoraitis aka Archatas


On 8/21/07, olivier <[EMAIL PROTECTED]> wrote:
>
> > Have any of you guys imported excel/cvs by using Python/Django? I need
> > to import 1000 products for a shopping site and I'd like to learn the
> > fastest and easiest way to do so.
>
>
> If you need to load data straight from the Excel, you can use xlrd,
> which works great.
>
> http://www.lexicon.net/sjmachin/xlrd.htm
>
> Regards,
>
> Olivier
>
>
>
>
> >
>

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[olivier]

> Have any of you guys imported excel/cvs by using Python/Django? I need
> to import 1000 products for a shopping site and I'd like to learn the
> fastest and easiest way to do so.


If you need to load data straight from the Excel, you can use xlrd,
which works great.

http://www.lexicon.net/sjmachin/xlrd.htm

Regards,

Olivier




--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[Tim Chase]

> Have any of you guys imported excel/cvs by using
> Python/Django? I need to import 1000 products for a shopping
> site and I'd like to learn the fastest and easiest way to do
> so.

I do this day in and day out :)

I prefer tab-delimited because it's easy and clean, though 
Python's built-in "csv" module can ease the pain of working with 
csv (which I presume you mean, rather than CVS, which is the 
Concurrent Versioning System)

I usually just write INSERT statements by hand, directly using 
the DB interface for my backend (PostgreSQL in this case) and 
then run a little Python code to iterate over the file and spew 
the data into the system:

   filename = 'foo.tab'
   for i, line in enumerate(open(filename)):
 if i == 0: continue # skip the header row
 (fieldname1, fieldname2, fieldname3 ...
  ) = line.rstrip('\n').split('\t')
 cursor.execute("""
   INSERT INTO app_foo
   (f1, f3, f2)
   VALUES
   (%s, %s, %s)
   """, (fieldname1, fieldname3, fieldname2)
   )

You may have to do lookups into other tables, etc, but this is 
generally how I do it.  Things can also get fancier if your input 
file can have the columns in any arbitrary order.  The above simply

 From within a running Django app, you can make use of the ORM to 
ease some of the annoyances, but when bulk-loading the data 
repeatedly (several thousand rows of data, multiple times per 
day), I personally like the control of raw SQL to eke out the 
last bit of performance from my DB connection.

I've seen some tools that try to ease this for you, but I've 
never been impressed by them, as I end up spending as much time 
fiddling with them as it would take me to bang out some python 
code to do the same thing.

-tim





--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: Importing Excel/CVS into Database

[Kirk Strauser]

On Tuesday 21 August 2007, robo wrote:

> Have any of you guys imported excel/cvs by using Python/Django?

Check out "pydoc csv".  Python ships with a nice CSV-reader module.
-- 
Kirk Strauser


signature.asc
Description: This is a digitally signed message part.