Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

export mysql data into multiple rows in excel

Posted on 2013-11-04
4
Medium Priority
?
333 Views
Last Modified: 2013-12-04
I have a plugin in wordpress that sets up addresses for a google map.

I'd like to export the data into separate columns in Excel.  My first attempt didn't work as it puts everything into 1 column in Excel.

I can get the information exported out of phpMyAdmin correctly, but need to split what's exported into name, address, city, state, zip.

Here's how it exports

"310 E. Argonne Dr
Kirkwood, MO 63122
Mail to:  PO Box 515134
314-984-9224
Pastor: Thomas Lovis"

Is there something I'm missing on the export or a tool in Excel that I'm overlooking?  I tried data to columns but it only grabbed the address.
0
Comment
Question by:axessJosh
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39622722
axessJosh,

When you export from phpMyAdmin, did you setup the export options?

Fields terminated by  ,
Fields enclosed by    "
Fields escaped by     \
Lines terminated by   AUTO
Replace NULL by       NULL

Instead of using ,(Comma) in the Fields Terminated By, I usually use some Ascii symbols that does not normally exist in the data. For example, ¤ (Alt-1231). That way, when I open the CSV somewhere else, I can delimit it with ¤ instead of comma. That way, there is no field mix.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 39622790
I didn't set any export options.  I wasn't sure there was rhyme or reason to how the fields were saved in the DB and didn't know there were options like that.

I'll look at re-exporting with those changes.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 39628492
I can see where that will work, but when I tried, all the info is contained into one cell in the DB.  I can't see where there's anything to select in the "text to columns" wizard that'll let me distinguish where to separate.
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 2000 total points
ID: 39631790
axessJosh,

In your new export do you see the Fields termination symbols in the data?

If you do, you can now separate the data into column by doing Text-To-Column in Excel.

1.

Highlight you data in Excel.

2.

Open up Text to Columns Wizard in Excel. Do that by going to Data Ribbon Menu, then Text to Columns.

3.

Select Delimited as the separation method and click next.

4.

In the Delimiters section, uncheck everything, then check Other:. In the provided text box, type in Fields Termination Symbol you have choose to use when you exported your data. As you type in the Field Termination Symbol, you should be able to see the separation line in the Data Preview Pane. Click Next to continue.

5.

This final step is optional. In this step, you can choose to assign different format to the data columns and you can also choose to not importing some of the columns. Make changes if needed and click Finish when done.
You should now have everything broken into columns for you.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question