RE: Newbie question: importing cvs settings
am 28.09.2009 20:41:38 von Gavin Towey
1. Try opening up the csv file in a text editor, viewing it in a spreadshee=
t looks like it's hiding some extra formatting or lines that may be causing=
problems.
2. Try importing through the mysql CLI. From the screenshot you posted, it=
looks like PMA is parsing the file and creating an insert statement for ea=
ch line. It may be incorrectly handling some of that data. Using the CLI =
you'll get better feedback about what, if any, the error is.
Regards,
Gavin Towey
-----Original Message-----
From: Patrice Olivier-Wilson [mailto:biz@biz-comm.com]
Sent: Saturday, September 26, 2009 11:02 AM
To: 'mysql'
Subject: Re: Newbie question: importing cvs settings
Yep, typo ...:-(
I did some screen shots of 2 tests. A workaround solution is to make a
final entry in the csv file that I don't really need. Then everything up
to that point gets imported ok using CSV method. The LOAD DATA method
did not enter anything.
My earlier assumption about line 17 was false. It was dependent on how
many rows, and I had been using tests with same amount of data.
As I said, very beginner level, so thanks for the patience.
screenshots at
http://biz-comm.com/mysqlprojects/
thank you....
John wrote:
> I assume you mean csv not cvs!
>
> What is the error you get when the import fails? What version of MySQL ar=
e
> you using? Can you post the output of SHOW CREATE TABLE for the table you
> are trying to load the file in to and a sample of the csv which is failin=
g
> to load?
>
> Do you get the same error if you try and load the files using MySQL clien=
t
> and the LOAD DATA INFILE command? (See this link for details on how to us=
e
> LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html)
>
> Regards
>
> John Daisley
> MySQL & Cognos Contractor
>
> Certified MySQL 5 Database Administrator (CMDBA)
> Certified MySQL 5 Developer (CMDEV)
> IBM Cognos BI Developer
>
> Telephone +44 (0)7812 451238
> Email john@butterflysystems.co.uk
>
> -----Original Message-----
> From: Patrice Olivier-Wilson [mailto:biz@biz-comm.com]
> Sent: 26 September 2009 17:08
> To: mysql
> Subject: Newbie question: importing cvs settings
>
> Greetings:
>
> I have a project for which need to import cvs files into db.
>
> I can do so up to a point. The import will only do 16 lines,
> consistently. Error is failing at line 17.
>
> Steps:
>
> create table fields in Excel document, where they all match database fiel=
ds
> enter information in several of the columns, but not all as client will
> be filling it in online (leaving ID blank)
> save excel to .cvs
> log into phpMyAdmin
> import cvs
>
>
> I've experimented with several settings in the import, but consistently,
> it fails at line 17, even with different .cvs files.
>
>
> Any guidance, most appreciated.
>
>
--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com
The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
RE: Newbie question: importing cvs settings
am 29.09.2009 17:49:02 von Jerry Schwartz
>-----Original Message-----
>From: Patrice Olivier-Wilson [mailto:biz@biz-comm.com]
>Sent: Sunday, September 27, 2009 10:19 AM
>Cc: 'mysql'
>Subject: Re: Newbie question: importing cvs settings
>
>Back again... I have 192 records to import, and tried my extra line at
>the end hoping for a work around, but nope, it failed at line 17 again.
>
>
>Invalid field count in CSV input on line 17.
>
>Anyone have an idea why this might be happening?
>
[JS] This is just a shot in the dark, but Excel can be rather surprising when
it puts out a CSV file. Depending upon the data, and exactly how you've
specified the export, it can put double-quotes in unexpected places.
If you leave out the 17th line of data what happens?
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
>
>
>Patrice Olivier-Wilson wrote:
>> Yep, typo ...:-(
>>
>> I did some screen shots of 2 tests. A workaround solution is to make a
>> final entry in the csv file that I don't really need. Then everything
>> up to that point gets imported ok using CSV method. The LOAD DATA
>> method did not enter anything.
>>
>> My earlier assumption about line 17 was false. It was dependent on how
>> many rows, and I had been using tests with same amount of data.
>> As I said, very beginner level, so thanks for the patience.
>>
>> screenshots at
>> http://biz-comm.com/mysqlprojects/
>>
>> thank you....
>>
>>>
>>>
>>
>>
>
>
>--
>Patrice Olivier-Wilson
>888-385-7217
>http://biz-comm.com
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Re: Newbie question: importing cvs settings
am 29.09.2009 18:10:08 von Patrice Olivier-Wilson
Jerry Schwartz wrote:
>>
> [JS] This is just a shot in the dark, but Excel can be rather surprising when
> it puts out a CSV file. Depending upon the data, and exactly how you've
> specified the export, it can put double-quotes in unexpected places.
>
> If you leave out the 17th line of data what happens?
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
>
>
>
>
Thanks Jerry, Gavin and John:
Sorry for not an immediate response to all of your suggestions. Other
demands were pulling at me since I first asked for assistance.
I opened the .csv file with Text Wrangler, and the commas are missing at
about line 17
portfolio_ID,portfolio_sort_ID,portfolio_title,portfolio_bod ycopy,portfolio_image,portfolio_before
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg,
,1,Kitchens,,123.jpg
,1,Kitchens,,123.jpg
,1,Kitchens,,123.jpg
So not sure why that is happening. I'm on a Mac, using Excel 2008. But
at least you all have helped me find what it is doing, so now, I can at
least pull into a txt file and make corrections manually.
My thanks to all of you for your help and patience.
(above represents an empty portfolio_ID, a filled in sort_ID, title,
empty bodycopy, image, empty before)
Thank you.
--
Patrice Olivier-Wilson
888-385-7217
http://biz-comm.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org