`fields cannot be identical: ' ' and ' '` mongoimport error

Question

i'm trying to import a csv into mongodb on my local machine. I used the following commmand from the shell:

mongoimport -d mydb -c things --type csv --file /Users/..../agentsFullOutput.csv --headerline

I get the following error:

Failed: fields cannot be identical: '' and ''

I can't find anything on what that means. What am I doing wrong? The csv file, by the way, is the result of a mongoexport.

here are the column headers and some data:

_id build_profile   company_address company_name    company_website created_at  device _token   downloaded_app  email   first_name  last_name   is_proapp   modified_at mobile_phone    terms_accepted_at   license_number  broker_id   join_unique_url linkedin_profile_id billing_customer_id billing_zip mobile_phone    office_phone    vendors_count   clients_count   app_client
ObjectID(52ab245b763f4aec448b6763)  0   California  Lateral test    2014-01-01T08:19:05.470Z            test    test    test        2015-04-18T05:16:37.155Z    [phone]                          zip [phone]      10  5   
ObjectID(52b46bfc763f4ad9198b45ab)      7928    test    test    2014-01-01T08:19:05.470Z                Jennifer    Chase       2015-04-15T17:05:17.114Z            5551112     jennifer-chase      test    7071            22  64  

Widower

Looks like there are two empty fields, like foo, , , bar . – Tray

@MarkusWMahlberg I'm not sure I understand. There are tons of empty fields in the csv as some items are blank. There are, however, no empty column headings. Is that what you are referring to? – Widower

yes, that was what I was referring to. Was just a wild guess. Sorry. – Tray

I just tested a simple csv file using excel and there were no errors, but it imported zero documents. is there an issue using excel csv files? – Widower

Might be a line ending problem. Other than that, I have no idea. – Tray

Can you isolate a few lines of the csv that cause the problem? Can you post them as part of the question? – Exponent

The only issue I can see is that there is a space between the device and _token, in your headers. is this present in the import file? – Seismic

Does an import with just the two lines in your question work? – Seismic

@AlistairNelson I tried after removing the space in `device_token' and still have the problem. – Widower

Do you have any empty lines at the bottom of the csv file? – Seismic

Just tried importing your data and it went in fine. Badly formatted but fine. Then I tried to import the data in the format below with commas and I got a duplicate field error. You have mobile_phone in your data twice. I doubt this is the issue but it would be worth fixing this and trying again. Also, can you add a couple of lines from the actual csv other than copying them from excel? – Seismic

Answer 1

I had the exact same issue. I was opening up a CSV file in Excel to massage it and saving it back out again. While trying to import it into Mongo using "mongoimport" command I was getting the same error message that I had identical values. I checked the columns headings over and over to make sure there weren't any identical values.

I eventually tried re-saving the file from Excel using the "Windows Comma Separated (.csv)" option from the Format dropdown menu instead of the default "Comma Separated Values (.csv)" found in the "Common Format" section.

Worked perfectly.

Rubric

Great! Thanks a ton. Looks like Mongo was not able to read newline character of default CSV file exported from Excel. – Harleigh

Didn't find "Windows Comma Separated (.csv)". For me, saving it as "CSV UTF-8 (Comma Delimited)" did the job. – Rafael

Answer 2

The error: Mongo-tools is checking that the fields in your headerline are unique. This is because MongoDB doesn't support duplicated field names in documents.

From mongo-tools repo:

  // NOTE: this means we will not support imports that have fields like
  // a, a - since this is invalid in MongoDB
  if field == latterField {
    return fmt.Errorf("fields cannot be identical: '%v' and '%v'", field, latterField)
  }

EDIT

I was able to reproduce this error message by creating a csv file with duplicate field names in the headerline. It seems that your csv file has duplicate field names in the headerline '' and ''. Without seeing the actual file I imagine there is something like: field1,field2,,field3,,field4 in the headerline.

Telfer

Answer 3

While I was following Adhil Maujahid's blog post on "Interactive Data Visualization with D3.js, DC.js, Python, and MongoDB" I encountered with this issue. After spending nearly an hour, I changed the command --headerline to -f 1,2,3,....44 (all the way untill "44"). Here 44 is the number of attributes in the file. So if you ever encounter with this issue, try out the work around. Please do let me know the logic behind this if you know the underlying reason.

Muddler

Answer 4

I had a similar issue. I created an Excel spreadsheet, and in one column I had a linearized XML string. It seems that the save function of Excel did not handle one of the XML strings well, and created additional fields (columns) from it. Naturally I did not have any column headers for those additional columns, so when I tried importing to MongoDB, I got this error.

I was able to correct it by finding the problem-child XML string. Turns out that some of the spaces were actually tabs, and Excel used the tabs to separate the string across multiple cells. Once I replaced the tabs with a single space, the CSV saved correctly and mongoimport worked.

Deipnosophist

Answer 5

Got an identical error while using mongoimport as mongoimport --db test --collection transactions --type csv --headerline --file ~/test.csv

The error I got is Failed: fields cannot be identical: '100' and '100'

I had exported a csv using mongoexport. Then did some changes using Microsoft Excel on Mac and tried mongoimport, which resulted in error. I think the MS Excel saving as csv made unexpected changes making the file useless for mongoimport.

Then, I exported the file again using mongoexport, and now did the changes I required using Sublime editor. The mongoimport works now without issue.

Hulse

Answer 6

Typically this is because your file doesn't have "\n" (newline character) and just has "\r"( carriage return). This usually happens if you create the file from "Mac" and not on latest *nix or Windows.

*So when MongoDB is trying to read the CSV, it's reading the entire file as a single-line and throwing the error. See this bug report *

Solution:

Windows –> NIX: 
tr -d '\r' < windowsfile > nixfile // delete the carriage returns

Mac –> NIX: 
tr '\r' '\n' < macfile > nixfile // translate carriage returns into newlines

NIX –> Mac: 
tr '\n' '\r' < macfile > nixfile // translate newlines into carriage returns

Yet another option is to do this from within vi like so:

:set fileformat = unix 
:w

Credit: https://danielmiessler.com/study/crlf/#gs.bJ39VzA

There are other CLI tools like dos2Unix and unix2dos and so on that will help like below:

awk '{ sub("\r$", ""); print }' dos.txt > unix.txt


perl -pe 's/\r$//' < dos.txt > unix.txt

Occasionally, your CSV file may not have headerline at all!

HTH

Roede

Answer 7

Found the solution here - I opened the file in notepad++ and replaced all my CR (\r) with LF (\n). Did a find a replace - did my import again, and it worked!

Clause

Answer 8

Same issue, turned out the file had some data with no column header.

Grapher

Answer 9

Save CSV file as Windows comma separated (.csv) file. steps:

1. Open CSV or Excel file.
2. Save As Option
3. Select Formate (Windows Comma Separated (.csv))

Run Command: ./mongoimport --db betahrprocesses --collection employee --type csv --headerline  --file employee.csv 

Mackler

Answer 10

I have found replacing CR with CR+LF or CR with LF in the CSV file resolves the issue running mongoimport in Windows, and mongodb 4.0.12 .

Anthropogenesis

Answer 11

In my case, I just opened the CSV file, marked the first empty column and dragged to collect a few more empty columns and pressed delete then saved. That got rid of the problem. Those columns didn't have any data in my case so it was safe to delete these two phantom columns.

Irritated

Answer 12

In my case I get the error because the headers in the CSV file were missing, I added the headers and re-imported which worked without any issues. I hope this will help some of you.

Quixote

Answer 13

In the format csv, each field must be separated by a comma. In the example Is it tabs or spaces?.

You would try with a file like this:

_id,build_profile,company_address,company_name,company_website,created_at,device,_token,downloaded_app,email,first_name,last_name,is_proapp,modified_at,mobile_phone,terms_accepted_at,license_number,broker_id,join_unique_url,linkedin_profile_id,billing_customer_id,billing_zip,mobile_phone,office_phone,vendors_count,clients_count,app_client
ObjectID(52ab245b763f4aec448b6763),0,California,Lateral,test,2014-01-01T08:19:05.470Z,,test,test,test,2015-04-18T05:16:37.155Z,(123),123-1234,,,,zip,(123),123-1234,10,5,
ObjectID(52b46bfc763f4ad9198b45ab),7928,test,test,2014-01-01T08:19:05.470Z,,Jennifer,Chase,2015-04-15T17:05:17.114Z,,5551112,jennifer-chase,test,7071,,22,64,

Stentor

My example is just a copy and paste from an excel file. I'm assuming an excel csv is separated by commas, as mongoimport is working just find for other excel csv files. – Widower