axessJosh
asked on
export mysql data into multiple rows in excel
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.
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.
ASKER
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.
I'll look at re-exporting with those changes.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.