How to import EXCEL into mySQL

This is a workaround for (mostly) non-programmers, found in a forum for ‘os Commerce’ – but I thought, it might be helpful here, too. 🙂


Original article is at: http://www.aspfree.com/c/a/Database/Converting-Your-Excel-Worksheet-into-a-Working-MySQL-Database/

You can only export one worksheet at a time to your MySQL database.  Your worksheet should be free from images, colors, wrapping text, and fancy fonts.  The first row is considered your Heading row starting in A1.

Steps to import a CSV file created in Excel into a MySQL table for a MySQL database stored on a server (not XAMPP localhost):

1.     Create your Excel file using the EXACT field names as in your MySQL table.  If you are importing into a table with many field names, you may want to EXPORT the table first, open in Excel and save it as your template.  If you are adding new data, make sure you delete any data that might have exported with your field names.  If your table has a Primary Key, it should be the first field listed.  If you have it set to auto_increments, leave it blank in your Excel file.

2.     Save it as a CSV file (SUGGESTION:  you may want to save the Workbook the same name as your table in the same format.  Ex:  products_attributes).

  • Go to File -> save as “CSV (Comma delimited).” If there is a warning that says “Do you want to keep the workbook in this format?” Click “YES” and close the workbook. Click “NO” if there is a warning that asks “Do you want to save the changes made to yourworkbook.csv?”

3.     Then go to http://csv2sql.evandavey.com/. This will convert csv data into MySQL insert statements.

  • Table Name: Type the EXACT table name you are importing to.
  • CSV File: Browse to the location of the CSV file you have saved in the above steps.
  • Mode: “Insert Statements”
    • If you are updating existing data, choose Updating Statement.
  • Primary Key Field (Update Only): leave blank.
  • After all are set, click “GO”
  • Copy the created statement(s).  You can leave off the following: “– Generated by http://csv2sql.com online CSV to SQL converter”

4.     Go to phpMyAdmin >> select your database

5.     Click on the “SQL” tab at the top

6.     Paste your statement(s) in the “Run SQL query/queries…” box

7.     Click “GO”

[amazon_image id=”3836218771″ link=”true” target=”_blank” size=”medium” ]Einstieg in PHP 5.4 und MySQL 5.5: Für Programmieranfänger geeignet (Galileo Computing)[/amazon_image] •  [amazon_image id=”3815828201″ link=”true” target=”_blank” size=”medium” ]Das große Buch: MySQL[/amazon_image] • [amazon_image id=”3826675185″ link=”true” target=”_blank” size=”medium” ]MySQL 5 (bhv Einsteigerseminar)[/amazon_image] • [amazon_image id=”389721525X” link=”true” target=”_blank” size=”medium” ]MySQL kurz & gut[/amazon_image]

4D v12 Docs – PDF !!

Frisch vom 4D-Ticker – wird ja auch langsam mal Zeit:


January 20, 2011

Due to popular demand, we are pleased to announce that 4D documentation is now available in PDF format. You can download PDF versions from the Doc Center homepage or directly on our FTP site.

For the time being, only the international English, French and German versions of the 4D v12 documentation are available in PDF. The procedure used to extract and convert the data into PDF manuals is still in the development stage and may need some additional improvement.

Please keep in mind that PDF versions are static by nature, and that any information they contain may have already been updated in the live HTML version, which remains the reference documentation.

You can download PDF versions from the 4D Doc Center at:

http://doc.4d.com/

For the time being, only the international English, French and German versions of the 4D v12 documentation are available in PDF.

For more information, please see:

http://www.4d.com/blog/pdf-documentation.html

Enjoy!

The 4D Documentation team


Es geschehen noch Zeichen und Wunder! 🙂

[ANN] Win32API v4.1

Hi everyone,

Win32API Version 4.1 is now available on our web site. This version adds a few new commands:

sys_IsAppRunningAsService

This command takes no parameters and returns 1 if the application is running as a service and 0 if it is not.

sys_SetRegText
sys_SetRegLongint
sys_SetRegArray
sys_SetRegBLOB

These are the inverse of the sys_GetReg commands. Parameters are the same as with the Get commands, where the 4th parameter is the new value to assign. The key/value will be updated if it already exists, and created if it does not exist.

Download:

http://www.orchardsoft.com/4Dapps/win32api.htm

Jeff