CSV to MyDB

--Apple-Mail-1-364144602
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=US-ASCII;
delsp=yes;
format=flowed

Hello everyone,
Hope your day is going well.
I have a (hopefully) quick question. What is the best way to store a
CSV file in a MySQL database?
As a varchar, blob, longtext or other?
I would like to minimize the amount of fields in the table, so I was
leaning towards a longtext or blob. But I don't know the advantages or
disadvantages to using these. The text is a excel price list that is
exported to a
csv and the data will be sent to repopulate a html table that has the
same amount of fields.
the only catch is some of the fields are descriptions (text) and some
are just prices (numbers).
Hope this is not an overload of info, just want to inform complete.

TIA,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


--Apple-Mail-1-364144602--

Re: CSV to MyDB

Hey friend!

You can create a DB table with the same structure of your CSV and
store it like a normal table, using the mysqlimport command:
http://dev.mysql.com/doc/refman/5.5/en/mysqlimport.html

In this way, you can do SELECT's and all DML commands.

If you want to store the file as you receive it, that'd be good to
create three columns: an ID (AUTONUM), a "date_load" and a BLOB field
called "content", where I'd store the CSV. And I guess you should use
BLOB. A quote from http://dev.mysql.com/doc/refman/5.0/en/blob.html:
"BLOB values are treated as binary strings (byte strings). They have
no character set, and sorting and comparison are based on the numeric
values of the bytes in column values. TEXT values are treated as
nonbinary strings (character strings). They have a character set, and
values are sorted and compared based on the collation of the character
set."


My $0.02,
Adriano Laranjeira.
São Bernardo do Campo - Brazil.
> > > > > > > >
> On Fri, 25 Feb 2011 09:06:47 -0600
> Karl DeSaulniers wrote:
> Hello everyone,
> Hope your day is going well.
> I have a (hopefully) quick question. What is the best way to store a
> CSV file in a MySQL database?
> As a varchar, blob, longtext or other?
> I would like to minimize the amount of fields in the table, so I was
> leaning towards a longtext or blob. But I don't know the advantages
>or
> disadvantages to using these. The text is a excel price list that is
> exported to a
> csv and the data will be sent to repopulate a html table that has
>the same amount of fields.
> the only catch is some of the fields are descriptions (text) and
>some are just prices (numbers).
> Hope this is not an overload of info, just want to inform complete.
>
> TIA,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: CSV to MyDB

I see. Thank you for that and thank you for the link.

Best,
Karl

Sent from losPhone

On Feb 25, 2011, at 10:57 AM, "Adriano Rodrigo Guerreiro Laranjeira" =
> wrote:

> Hey friend!
>
> You can create a DB table with the same structure of your CSV and =20
> store it like a normal table, using the mysqlimport command:
> http://dev.mysql.com/doc/refman/5.5/en/mysqlimport.html
>
> In this way, you can do SELECT's and all DML commands.
>
> If you want to store the file as you receive it, that'd be good to =20
> create three columns: an ID (AUTONUM), a "date_load" and a BLOB =20
> field called "content", where I'd store the CSV. And I guess you =20
> should use BLOB. A quote from =
http://dev.mysql.com/doc/refman/5.0/en/blob.html:
> "BLOB values are treated as binary strings (byte strings). They have =20=

> no character set, and sorting and comparison are based on the =20
> numeric values of the bytes in column values. TEXT values are =20
> treated as nonbinary strings (character strings). They have a =20
> character set, and values are sorted and compared based on the =20
> collation of the character set."
>
>
> My $0.02,
> Adriano Laranjeira.
> São Bernardo do Campo - Brazil.
>> > > > > > > > On Fri, 25 Feb 2011 09:06:47 -0600
>> Karl DeSaulniers wrote:
>> Hello everyone,
>> Hope your day is going well.
>> I have a (hopefully) quick question. What is the best way to store =20=

>> a CSV file in a MySQL database?
>> As a varchar, blob, longtext or other?
>> I would like to minimize the amount of fields in the table, so I was
>> leaning towards a longtext or blob. But I don't know the advantages =20=

>> or
>> disadvantages to using these. The text is a excel price list that =20
>> is exported to a
>> csv and the data will be sent to repopulate a html table that has =20
>> the same amount of fields.
>> the only catch is some of the fields are descriptions (text) and =20
>> some are just prices (numbers).
>> Hope this is not an overload of info, just want to inform complete.
>> TIA,
>> Karl DeSaulniers
>> Design Drumm
>> http://designdrumm.com
>
>
> --=20
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php