0

While importing a csv file into mongo database, I get an error : exception : CSV file ends while inside quoted field and the import operation aborts. I checked the actual records it was referring to in the exception description. I was able to import that record independently into the mongodb. When I try to import the whole file, it does not work.

What could be the reason for this issue ? I did check that record manually and was also able to import that record independently into the mongodb. Could it still be an issue with quotes/commas orother special characters that might cause a problem ?

According to the exception : error is with Row 3 Here are some more rows before and after that row. I am using mongoimport with parameters

mongoimport -h 127.0.0.1 -d databasename -c collectionname --type csv --file filelocation --headerline --ignoreBlanks
connected to: 127.0.0.1
exception:CSV file ends while inside quoted field
2014-07-17T22:39:29.629-0700 check 9 6307
2014-07-17T22:39:29.631-0700 imported 6306 objects
encountered 1 error(s)

Row 1:

"1555502","149","2012-07-26 17:13:10","iantist","2014-05-02 17:39:48","http://twitter.com/@iantist",,N,"<p>Neuroscientist,, rabid R user at work and home. </p>&#xA;","21","8","0",,N,,N,,N,"1442157"

Row 2:

"1555662","794","2012-07-26 18:18:37","olivecoder","2014-05-02 16:09:38","http://www.linkedin.com/in/olivecoder","Brazil","<p>I am a software engineer focused on developing solutions for the telecommunications industry since 1990. I have a huge background in R&amp;D projects,, developing firmware for telecom equipment,, CTI applications,, softswitches,, IVR and others telecom solutions that resulted in patent registration. I founded Mares Telecom to provide value-added services to carriers and to create new technologic solutions for the telecom market.</p>&#xA;&#xA;<p><a href=,"http://linkedin.com/in/olivecoder," rel=,"nofollow,">http://linkedin.com/in/olivecoder</a></p>&#xA;","135","75","8",,N,,N,"42","1693823"

Row 3:

"1556310","15","2012-07-27 00:07:09","Volodymyr Zhabiuk","2013-05-14 01:13:28","http://www.linkedin.com/in/volodymyrzhabiuk",,N,,N,"9","0","0",,N,,N,,N,"1694670"

Row 4:

"1557339","35","2012-07-27 10:09:27","Brice","2014-05-03 06:00:33","http://fr.linkedin.com/in/bleporini",,N,,N,"7","1","0",,N,,N,,N,"1695972"

Row 5:

"1558307","253","2012-07-27 17:06:29","PurpleDiane","2014-04-03 22:39:43","http://www.linkedin.com/in/dianedemerschen","San Diego,, CA","<p>20+ years programming experience,, primarily in Computer-Aided Design and Manufacturing. Early Fortran,, migrated to C. Took time off to care for my mom when she had Alzheimer's. Now updating my skills in C++,, Java,, Python and Android programming. At least that's what I've studied so far!</p>&#xA;&#xA;<p>I got a lot of information from StackOverflow while I was taking my C++ classes,, now I hope to learn more and perhaps even be of some help.</p>&#xA;","25","6","1",,N,,N,,N,"1697171"
Shruti
  • 107
  • 2
  • 14
  • Can you post that section of the CSV file? How exactly are you importing the file? – Tim Pietzcker Jul 18 '14 at 05:30
  • to my experience, when there are several \\n inside the quote, I get this error. Don't just look for the rows it reported, it's not accurate. You can look for the last few lines imported with `db.coll.find().sort({$natural: -1}).limit(5)`. It will help you find the problematic lines. – yaoxing Jul 18 '14 at 06:18

1 Answers1

0

Thanks for the help!! Turns out that the issue was with the fields which have null values in various lines throughout the file! A similar issue is explained here.

I actually created the CSV files from Mysql data. So I used this command to export into csv(but without any escape/enclose conditions)

select 'column1','column2'
UNION ALL
SELECT *
    FROM Mysqltable
    into outfile 'Filename.csv';

This got exported as a csv file ( from the Mysql db ) but has no commas between column entries or enclosing double quotes.

For importing into mongo: used same command as above but imported as TSV(tab separated values).

mongoimport -h 127.0.0.1 -d databasename -c collectionname --type tsv --file filelocation --headerline --ignoreBlanks

Works perfectly now!

Community
  • 1
  • 1
Shruti
  • 107
  • 2
  • 14