Importing Excel ->> MySQL

Importing Excel ->> MySQL

by M Y -
Number of replies: 6
Hi all,

Hope someone will be able to help me with this.
I am trying to import a list of categories for the SOBI Business Directory component for Joomla.

We have a list of categories that we would like to import into these tables:
catid int(11)

No
auto_increment Browse distinct values Change Drop Primary Unique Index Fulltext
name varchar(100) latin1_swedish_ci
Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
image char(100) latin1_swedish_ci
Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
image_position varchar(10) latin1_swedish_ci
Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
description text latin1_swedish_ci
Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
introtext varchar(100) latin1_swedish_ci
Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
published tinyint(1)

Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
checked_out int(11)

Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
checked_out_time datetime

Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
ordering int(11)

Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
access tinyint(3)

Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
count int(11)

Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
params text latin1_swedish_ci
Yes NULL
Browse distinct values Change Drop Primary Unique Index Fulltext
icon

I have tired exporting the Excel spreadsheet into CSV and importing, but I get this error:

Invalid field count in CSV input on line 1.

Does anyone have any advice, or know if/what I am doing wrong?

Many Thanks,
Martin
Average of ratings: -
In reply to M Y

Re: Importing Excel ->> MySQL

by Samuli Karevaara -
To begin I'd go with the obvious: do you have the same amount of columns in the CSV file than in the target table?
Average of ratings: -
In reply to Samuli Karevaara

Re: Importing Excel ->> MySQL

by M Y -
I exported the table in Excel Format from phpmyadmin, then added the new data into the 'name' column. So I would assume so, yes.

Average of ratings: -
In reply to M Y

Re: Importing Excel ->> MySQL

by Tariq Adel Al Ammadi -
Export to CSV, edit using open office (or a simple text editor -- CSV is just a file containing tuples of comma separated values), and then import.

I would bypass Excel as it doesn't support CSV files properly, as I have learned the hard way. Hope this helps! smile
Average of ratings: -
In reply to Tariq Adel Al Ammadi

Re: Importing Excel ->> MySQL

by M Y -

Tariq,

That worked perfectly.

Cheers,

Average of ratings: -
In reply to M Y

Re: Importing Excel ->> MySQL

by Samuli Karevaara -
I'm curious: if you open the original problematic csv file that was created with Excel in Notepad for example, are the values separated by commas? If you have regional settings that use a different separator, then that will "break" the file too. If Finland we use the comma as a decimal separator, so the values are separated by ; and that won't work in uploading users to Moodle with a CSV file, for example.
Average of ratings: -
In reply to Samuli Karevaara

Re: Importing Excel ->> MySQL

by M Y -
I think it used commas, I am using English UK.
Average of ratings: -