Excel > CSV Files > MySQL on a Mac

[So_Long]

Sorry for asking so many questions, but I'm totally snowed. I've long used the Microsoft Works spreadsheet program on my PC. I would organize data, export it a CSV file (to Notepad), then import the CSV into a MySQL database table.

Now I'm trying to repeat that process on a Mac, using Excel (Microsoft Office for Mac) and a phpMyAdmin upgrade. So I'm not sure where the problems are, but they probably begin with the radically different CSV file.

First, I open an Excel spreadsheet. Then I click SAVE AS and, under Format, choose Comma Separated Values (.csv). The resulting file looks nothing like the Notepad CSV files I'm used to. Instead, it looks like a watered down spreadsheet - with no visible commas between table cells/fields.

When I try to import the CSV file into a database table using phpMyAdmin's default settings...

Fields terminated by... ;
Fields enclosed by... "
Fields escaped by... \
Lines terminated by... auto

....I get this error message:

"Invalid field count in CSV input on line 1."

When I change Format of Imported File to "CSV using LOAD DATA," it works - but it only imports the first row. I changed "Fields terminated by" to , (comma), since that's what's SUPPOSED to be in a CSV file. I also tried changing "Lines terminated by" from auto to \r\n, as that's what I use in my spreadsheets.

I also tried deleting the entries for Fields Enclosed/Ecaped by, which I don't really understand. I think I either used the default settings or slightly modified one of them on my PC.

It's hard to troubleshoot when I'm getting used to a radically different CSV file and a phpMyAdmin upgrade both. But I think I need to figure out the CSV file first.

Can anyone tell me how YOU get data from Excel for Mac into a MySQL database table? To give you an idea of what sort of data I'm working with, a typical row might include the following:

Cell 1: 5
Cell 2: <http://www.sharks.com>
Cell 3: This website's all about sharks. You should check it out!
Cell 4: January 14, 2007

In other words, I'm simply using my spreadsheet to store numerals, dates, URL's and brief amounts of text. I just need to know how to get it into my database.

Thanks.

 

[CyberTaz]

Have you tried looking further down in the list of Save As file formats? -
There are 2 more .csv options: Windows Comma Separated & MS-DOS Comma
Separated. Perhaps one of them will satisfy what you're looking for.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[So_Long]

Thanks for the tip. I tried both of those options with the same results; they produce what looks like a spreadsheet with no commas. I haven't tried to import them into a database yet; maybe I'll get better results with them.

Ideally, I'd like to find a tutorial that explains the whole process, including Mac/Excel, the CSV files and phpMyAdmin, because they're all part of what appears to be an increasingly complex story.

Thanks for the tip.

 

[JE McGimpsey]

Thanks for the tip. I tried both of those options with the same results; they
produce what looks like a spreadsheet with no commas.

Are you sure? What if you open with TextEdit or another text editor?

If you try to open the file with XL, XL will read it back into cells.

 

[CyberTaz]

What program are you opening the .csv with? If you're just reopening it in
Excel it *will* open as a spreadsheet - but without the formatting... that's
part of what csvs are all about If you open the file in a text editor or
something other than a spreadsheet program you should see that it is more
like what you expect.

Regards |:>)
Bob Jones
MVP Office:Mac

 

[So_Long]

Ah, great tip! I opened the CSV file with TextEdit, and it looks like the Notepad CSV files I'm used to working with.

In the meantime, I think I figured everything else out, too. First, I changed the row endings in my spreadsheet from \r\n to \r. I thought the former was a universal symbol, but now I understand that it doesn't work on a Mac (I guess).

I can now import CSV files using the "Load Data" option as long as I change the first phpMyAdmin value from a semi-colon ( to a comma (. I can leave all the other settings on default, and it seems to work.

Thanks so much for the tips.

 

[JE McGimpsey]

In the meantime, I think I figured everything else out, too. First, I changed
the row endings in my spreadsheet from \r\n to \r. I thought the former was a
universal symbol, but now I understand that it doesn't work on a Mac (I
guess).

No universals. In general, tho:

System EOL

Mac: \r
Unix: \n
Windows: \r\n