0

Export MySql data in following command.

SELECT id,documentfile
FROM documentuploads
INTO OUTFILE '/home/home2/agri_doc.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

CSV file data

"3","%PDF-1.7\
%����\
1 0 obj\
<<\
/Type /Catalog\
/Pages 2 0 R\
>>\"

I am currently using mongodb shell version v3.6.3. i want to import my csv file in mongo database, and I have created a collection documentupload where only two field id, docs. data is latin1_swedish_ci encoded. I tried following command.

mongorestore -d agrilicense2 -c documentupload  --type csv --file /home/shiva/home/agri_doc3.csv  --fields id, docs --headerline

Show output
error parsing command line options: unknown option "type"
Thank you

  • Are you sure you want to restore (.csv) file. As i am able to see in your statement you want to import the (.csv) file. – Md Haidar Ali Khan Apr 11 '19 at 7:49
  • @MdHaidarAliKhan my complete file CSV file path /home/shiva/home/agri_doc3.csv – Shiva Manhar Apr 11 '19 at 7:51
  • I have been updated the answer with respect to import of (.csv) file. – Md Haidar Ali Khan Apr 11 '19 at 8:12
  • If you want to import only the two fields (i.e., id,docs) then why not delete rest of the field from the (.csv) file . To avoid to write down more arguments in the mongo shell command. – Md Haidar Ali Khan Apr 11 '19 at 8:25
  • As i understood you have been imported some MySQL data in (.csv) format and want to export in MongoDB. Right – Md Haidar Ali Khan Apr 11 '19 at 8:35
1

You can import the (.csv) file through the following commands

mongoimport -d databaseName -c collectionName --type csv --file locationsOftheCSVFile.csv --headerline

For example here I have created the database name test and collection name StackExchange and going to import the LocarionReq.csv file.

> use test
switched to db test
> db.createCollection("StackExchange");
{ "ok" : 1 }
> show collections
StackExchange

C:\Program Files\MongoDB\Server\4.0\bin>mongoimport -d test -c StackExchange --type csv --file C:\test\LocationReq.csv --headerline
2019-04-11T10:58:53.147+0300    connected to: localhost
2019-04-11T10:58:53.263+0300    imported 1000 documents

Now you can check the total document records through the mongo shell command

>db.StackExchange.find().count();
 1000

If you want to find the document records through mongo shell then run the command such as

> db.StackExchange.find().pretty(); -- an easy-to-read attractive format.

Or

> db.StackExchange.find();

Note : Run mongoimport or mongoexport from the system command line, not the mongo shell.

For your further ref here

| improve this answer | |
  • error validating settings: only one positional argument is allowed – Shiva Manhar Apr 11 '19 at 8:17
  • @shivaManhar, did you try this >mongoimport -d agrilicense2 -c documentupload --type csv --file /home/shiva/home/agri_doc3.csv --headerline . I mean to say that after the location file of .csv only write down the --headerline. – Md Haidar Ali Khan Apr 11 '19 at 8:21
  • yes -- file /home/shiva/home/agri_doc.csv --headerline output Failed: line 111, column 14: extraneous " in field – Shiva Manhar Apr 11 '19 at 8:24
  • 1
    @ShivaManhar In your agri_doc.csv file, check the value present in row number 111 and column number 14 for any special characters. This link can help you with correcting this issue stackoverflow.com/questions/31573851/… – Mani Apr 11 '19 at 15:10
1

Before going to answer i want to say thanks Md Haidar Ali Khan, Mani and all. 1. First change MySQL Export(dump) query. documentfile not is base64 format so before export change documentfile ot base65 through TO_BASE64 method.

SELECT id,TO_BASE64(documentfile)
FROM documentuploads
INTO OUTFILE 'agri_doc64.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

mongoimport command

mongoimport --db agrilicense2 --collection documentupload  --type csv --columnsHaveTypes --fields "id.int32(),documentfile.binary(base64)" --file /home/shiva/home/agri_doc64.csv

I hope this code help other and save lot of time.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.