I am a small, zero budget, small business owner who needs to know how to
convert Excel files into MySQL files that I can upload to my web host. We have
more than 2000 product items that we need to publish on our web store. Part of
the data is already in SQL, things like item number, description, size, and
price. Excel does a dutiful job of importing these files. But I need to add
to the SQL table my POS software provides, the "picture name" for instance.
Some other things I need to be created in a new table, like the "long
description," and "long description number." (Numerous items get the same long
description.)

I can't be the first person to have faced this problem, so I throw it out
there. Please help.

Background: I am an old school programmer (think punch cards and FORTRAN)
that learned Dreamweaver CS3 from "no experience' and built a 50-some page
website in less than two weeks. I also know relational databases well enough.
But, being away from computers for so long, a lot of the lingo gets lost on me.
Don't be afraid to challenge me, but don't assume I know the buzz words you
know.

PS: A mentor is ABSOLUTELY welcome. Volunteers?

~ Scott
http://oldcountrypeddler.com

..oO(Squonk64)

>I am a small, zero budget, small business owner who needs to know how to
>convert Excel files into MySQL files that I can upload to my web host.

CSV (comma separated values) might be an option. In Excel export as CSV,
in MySQL you can import it.

Micha

Michael Fesser wrote:
> .oO(Squonk64)
>
>> I am a small, zero budget, small business owner who needs to know how to
>> convert Excel files into MySQL files that I can upload to my web host.
>
> CSV (comma separated values) might be an option. In Excel export as CSV,
> in MySQL you can import it.
>
> Micha

Yes, that's how I do it. I used the Microsoft Works (sub-Excel)
spreadsheet on my PC and just recently switched to a Mac and installed
Microsoft Office for Mac so I can use Excel.

It's a pretty simple process, though I had to iron out a few small kinks
on both my PC and my Mac. Using phpMyAdmin on my Mac, I click Import,
then choose "CSV using LOAD DATA where it says "Format aof imported
file." (I don't remember doing that on my PC, but it might just be a new
feature on the newer versions of phpMyAdmin.

Under "Options for CSV import using LOAD DATA, I change the first
setting from a semi-colon to a comma, then change the last setting from
"auto" to \r, which is what I end my rows with on my spreadsheets - on a
Mac. On a PC, it would be \r\n, I believe.

When I saved a spreadsheet as a CSV file on my PC, it saved it as in
Notepad, which I could easily open and edit. On my Mac, the CSV file
looks like a stripped down spreadsheet. I have to close it, then open it
again with a text editor, like TextEdit.

Then I import it into my database table, after which I export the table
as a SQL file and import the SQL file to my online database.

Thank you, Michael. What I knew as "comma delimited text" can be exported from
Excel, but I still don't have a program to save it as a MySQL file. I must be
overlooking something you younger people understand.

When I goto MySQL.com and look at the "downloads" page, I don't see anything
related to a program called "MySQL" that can import a CSV file. I know, this
might be a stupid, simple question. But I was able to figure our how to
download a free Apache server software that can understand PHP and SQL. I just
can't find this answer by myself.

That's what I need. That program. Please
help.http://mysql.comhttp://mysql.comhttp://mysql.com

> but I still don't have a program to save it as a MySQL file.

You don't. You save it as a CSV file. Then you feed it to MySQL using
either the MySQL client, Navicat, or PHPMyAdmin.

--
Murray --- ICQ 71997575
Adobe Community Expert
(If you *MUST* email me, don't LAUGH when you do so!)
==================
http://www.projectseven.com/go - DW FAQs, Tutorials & Resources
http://www.dwfaq.com - DW FAQs, Tutorials & Resources
==================


"Squonk64" <webforumsuser@macromedia.com> wrote in message
news:fqhrg8$p6v$1@forums.macromedia.com...
> Thank you, Michael. What I knew as "comma delimited text" can be exported
> from
> Excel, but I still don't have a program to save it as a MySQL file. I
> must be
> overlooking something you younger people understand.
>
> When I goto MySQL.com and look at the "downloads" page, I don't see
> anything
> related to a program called "MySQL" that can import a CSV file. I know,
> this
> might be a stupid, simple question. But I was able to figure our how to
> download a free Apache server software that can understand PHP and SQL. I
> just
> can't find this answer by myself.
>
> That's what I need. That program. Please
> help.http://mysql.comhttp://mysql.comhttp://mysql.com
>

..oO(Squonk64)

>Thank you, Michael. What I knew as "comma delimited text" can be exported from
>Excel, but I still don't have a program to save it as a MySQL file. I must be
>overlooking something you younger people understand.

You don't need an extra tool, MySQL can understand the CSV format. David
gave a solution with phpMyAdmin, but you could also do the same command
on the MySQL command line.

Micha

Squonk64 escreveu:
> I am a small, zero budget, small business owner who needs to know how to
> convert Excel files into MySQL files that I can upload to my web host. We have
> more than 2000 product items that we need to publish on our web store. Part of
> the data is already in SQL, things like item number, description, size, and
> price. Excel does a dutiful job of importing these files. But I need to add
> to the SQL table my POS software provides, the "picture name" for instance.
> Some other things I need to be created in a new table, like the "long
> description," and "long description number." (Numerous items get the same long
> description.)
>
.............................................
>
> ~ Scott
> http://oldcountrypeddler.com
>
------
Take a look at: http://www.educar.pro.br/i_en/GeoIP/ , where I've
developed a simple method to convert CSV to MySQL using MSExcel or
OpenOfficeCalc, using a CSV from MaxMind as example.
----
zerof

Thank you all. I knew I didn't need to rebuild the wheel or plunk down $200-$500 for database software. I'll give it a try in a couple of days, after I become proficient enough with PHP.

"Squonk64" <webforumsuser@macromedia.com> wrote in message
news:fqhplc$n6k$1@forums.macromedia.com...

Just one thing you might bare in mind, excel has a will of its own with csv
files in formatting the columns. You might avoid some grief by using a tab
rather than a comma but make sure your text to import doesn't have any tabs
in it or you will get some errors.

Thank you folks for bearing with me. I am surprised how simple the PHP part of
all this is. Now if the database side would just be explained a bit better....

I filtered out all the unnessary data from the Excel file and added a few new
columns that I need to make the PHP work. All in all, my "Inventory.csv" sheet
is 13 columns by 2053 rows, including the header row.

I followed David's instructions about importing the file, but when I look at
the "Format of imported file" area, the only button available is for SQL. I
don't see a button called "CSV using LOAD DATA" I am using version 2.11.4 of
phpMyAdmin, a January 2008 release, so I don't think that's the problem.

When I tab over to "engines" in phpMyAdmin, I see CSV listed, but apparently
it is not active because "This MySQL server does not support the CSV storage
engine." I am running MySQL version 5.0.51a, which sounds current enough.
That leads me to think maybe I am either running the wrong Apache software or
the emulation is pretty poor. Can anyone suggest a better AMP program to use
that will allow me to load me CSV data? That will at least get me to the next
step without a few days worth of trial and error.

Again, that you for bearing with the pathetically basic questions I have.
Until next time, from zero budget/DIY-land...