0

I want to upload an excel spreadsheet to phpmyadmin.

I convert it to a .csv, changed the max_allowed_packet to 512M in both client and server side. But I keep getting a mySQL #2006 error.

I am not exporting anything to a machine other than mine, both client and server are running locally.

Any ideas?

sqlbuddy
  • 79
  • 1
  • 10
  • Does the approach work for smaller files? 512M seems quite large... – andy Nov 20 '14 at 19:54
  • the approach works with smaller files but I need it to work with even larger files than 512M – sqlbuddy Nov 23 '14 at 18:43
  • Does it work if you load the csv from the command line: http://stackoverflow.com/questions/3635166/how-to-import-csv-file-to-mysql-table ? If not, I assume your mysql server has trouble handling the file either due to errors in the file or because of its size. – andy Nov 24 '14 at 08:18

1 Answers1

0

How to add a EXCEL sheet to MySQL?

It is very easy ...

1) You have to add in your excel sheet before your first column a new column
2) You populate this column with " for every row that you want to export

3) You have to add in your excel sheet after your last column a new column
4) You populate this column with " for every row that you want to export

5) you select the data including the new columns and use STRG + C to copy it

6) you open your notepad and paste the data with STRG + V

7) Excel seperate the columns with TAB. You select a TAB and STRG + C

8) You press STRG + H for replace and fill the Find What field with this tab

9) You fill the Replace With field with "," and then you click on replace all

10) You replace all fields ,"" with nothing.

10) You save this file with the extension .CSV

11) You use in phpmyadmin the import function and import your data

Important: Because you are not giving field informations to the phpmyadmin, you should start from the first field. And navigate to the correct table!

Example:

your MySql table have the structure:

ID (auto increment) Name City TIMESTAMP (CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP)

You need to prepare the timestamp.

Add in EXCEL a column that match the field order from your table. In this column insert following formula:

=(NOW()-xxx)+ROW()

Replace xxx with the number of rows of your data. This back counts the date and avoid trouble with the timestamp.

Your pasted data from EXCEL look like this:

" John Atlantis 2015-44-04 18:44:19 " " Bill Los Angeles 2015-44-05 18:44:19 " " Maria Chicago 2015-44-06 18:44:19 " " Josef New York 2015-44-07 18:44:19 " " Kaspar Boston 2015-44-08 18:44:19 "

and your data CSV look (after you used the replace action two times) like this:

"","John","Atlantis" "","Bill","Los Angeles" "","Maria","Chicago" "","Josef","New York" "","Kaspar","Boston"

The trick is to add in excel the " at the outer left column and at the outer right column ...

... and to use copy and paste and replace. It is the fastest way without using a script.

Scoobeedo Cool
  • 101
  • 9