Python Forum
Problem with importing a CSV file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Problem with importing a CSV file
#1
I have a problem importing a CSV file. I have tried with r' and singel / and dubbel \\ both ways. The same problem.
But I can easily import the same file in R-studio with

"df <- read.csv2(file="G:\\Analyser\\2019 OS\\test.csv",head=TRUE)"

Anybody have a clue what I´m doing wrong? I´m using Anaconda Spyder and I can also import the file directly.


import pandas as pd
pd.read_csv("G:\\Analyser\\2019 OS\\test.csv", header=None)
My error message is:

Traceback (most recent call last):

  File "<ipython-input-2-90f87efa86d2>", line 3, in <module>
    pd.read_csv("G:\\Analyser\\2019 OS\\test.csv", header=None)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers.py", line 702, in parser_f
    return _read(filepath_or_buffer, kwds)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers.py", line 435, in _read
    data = parser.read(nrows)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers.py", line 1139, in read
    ret = self._engine.read(nrows)
  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers.py", line 1995, in read
    data = self._reader.read(nrows)
  File "pandas/_libs/parsers.pyx", line 899, in pandas._libs.parsers.TextReader.read
  File "pandas/_libs/parsers.pyx", line 914, in pandas._libs.parsers.TextReader._read_low_memory
  File "pandas/_libs/parsers.pyx", line 968, in pandas._libs.parsers.TextReader._read_rows
  File "pandas/_libs/parsers.pyx", line 955, in pandas._libs.parsers.TextReader._tokenize_rows
  File "pandas/_libs/parsers.pyx", line 2172, in pandas._libs.parsers.raise_parser_error

ParserError: Error tokenizing data. C error: Expected 1 fields in line 4, saw 2 
Reply
#2
how does your csv file looks like? especially line 4. can you provide let say top 10 lines
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
try

pd.read_csv("G:\\Analyser\\2019 OS\\test.csv", header=None, error_bad_lines=False)
Reply
#4
[Image: view?usp=drivesdk]

I have tried but I still get an error message.
pd.read_csv("G:\\Analyser\\2019 OS\\test.csv", header=None, error_bad_lines=False)
the error message:
pd.read_csv(""G:\\Analyser\\2019 OS\\test.csv"", header=None, error_bad_lines=False)
b'Skipping line 4: expected 1 fields, saw 2\nSkipping line 7: expected 1 fields, saw 2\nSkipping line 44: expected 1 fields, saw 2\nSkipping line 47: expected 1 fields, saw 2\nSkipping line 48: expected 1 fields, saw 2\nSkipping line 62: expected 1 fields, saw 2\nSkipping line 68: expected 1 fields, saw 2\nSkipping line 83: expected 1 fields, saw 2\nSkipping line 98: expected 1 fields, saw 2\nSkipping line 101: expected 1 fields, saw 2\nSkipping line 103: expected 1 fields, saw 2\nSkipping line 181: expected 1 fields, saw 2\nSkipping line 245: expected 1 fields, saw 2\nSkipping line 314: expected 1 fields, saw 2\n'
Traceback (most recent call last):

  File "<ipython-input-1-aef5c76db53b>", line 3, in <module>
    pd.read_csv("G:\\Analyser\\2019 OS\\test.csv", header=None, error_bad_lines=False)

  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers.py", line 702, in parser_f
    return _read(filepath_or_buffer, kwds)

  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers.py", line 435, in _read
    data = parser.read(nrows)

  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers.py", line 1139, in read
    ret = self._engine.read(nrows)

  File "C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\parsers.py", line 1995, in read
    data = self._reader.read(nrows)

  File "pandas/_libs/parsers.pyx", line 899, in pandas._libs.parsers.TextReader.read

  File "pandas/_libs/parsers.pyx", line 914, in pandas._libs.parsers.TextReader._read_low_memory

  File "pandas/_libs/parsers.pyx", line 991, in pandas._libs.parsers.TextReader._read_rows

  File "pandas/_libs/parsers.pyx", line 1123, in pandas._libs.parsers.TextReader._convert_column_data

  File "pandas/_libs/parsers.pyx", line 1176, in pandas._libs.parsers.TextReader._convert_tokens

  File "pandas/_libs/parsers.pyx", line 1299, in pandas._libs.parsers.TextReader._convert_with_dtype

  File "pandas/_libs/parsers.pyx", line 1315, in pandas._libs.parsers.TextReader._string_convert

  File "pandas/_libs/parsers.pyx", line 1553, in pandas._libs.parsers._string_box_utf8

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe5 in position 95: invalid continuation byte
Reply
#5
Unfortunately that image does not help. You need to open the file in text editing program like Notepad or Notepad++
I understand these are confidential data (client LEI, transactions), so look at the data on lines 4, 7, 44, etc. - probably you have comma in some of the cells. If that is the case, at least this cell should be quoted. Is this the case?, e.g,

Output:
id,name 1,"some, value"
as you can see here there are 2 columns, but line 2 has separator inside the value
it's possible that all values will be quoted
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#6
I made it to an excel file instead and that worked in Spyder. I do not know what the problem was because I could not see any strange cells or something similar. I easily imported the csv file in R-studio also. But anyways thank you all for the help.
Reply
#7
UnicodeDecodeError occurs, if the source file can't be decoded from utf8, which is the default encoding.
The function pd.read_csv does not seem to have a kwarg to ignore encoding errors.

One way could be to open the file in TextMode and pass the fd to pandas.
with open("G:\\Analyser\\2019 OS\\test.csv", errors='ignore' ) as fd:
    data = pd.read_csv(fd, header=None, error_bad_lines=False)
Take a look into the documentation about pd.read_csv.

This is the constructor:
 pandas.read_csv(filepath_or_buffer, sep=', ', delimiter=None, header='infer', names=None, index_col=None, usecols=None, squeeze=False, prefix=None, mangle_dupe_cols=True, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skipinitialspace=False, skiprows=None, nrows=None, na_values=None, keep_default_na=True, na_filter=True, verbose=False, skip_blank_lines=True, parse_dates=False, infer_datetime_format=False, keep_date_col=False, date_parser=None, dayfirst=False, iterator=False, chunksize=None, compression='infer', thousands=None, decimal=b'.', lineterminator=None, quotechar='"', quoting=0, escapechar=None, comment=None, encoding=None, dialect=None, tupleize_cols=None, error_bad_lines=True, warn_bad_lines=True, skipfooter=0, doublequote=True, delim_whitespace=False, low_memory=True, memory_map=False, float_precision=None)[source]
The first argument filepath_or_buffer is described as:
Quote:filepath_or_buffer : str, pathlib.Path, py._path.local.LocalPath or any \

object with a read() method (such as a file handle or StringIO)

The string could be a URL. Valid URL schemes include http, ftp, s3, and file. For file URLs, a host is expected. For instance, a local file could be file://localhost/path/to/table.csv

I haven't tested the upper example, but it should work. In this case errors are ignored.
I guess the file you have, is in a different encoding as utf8.
It could be:
  • latin1 (ISO/IEC 8859-1)
  • latin9 (ISO/IEC 8859-15)
  • Windows-1252 (CP 1252 / (Western European) / ANSI)

There is also a modules called ftfy which can solve bad encoding errors.

import ftfy


with open('file_with_bad_encoding.txt', errors='ignore') src:
    fixed_text = ftfy.fix_text(src.read())
with open('file_with_fixed_encoding.txt', 'w') as dst:
    dst.write(fixed_text)
After this, the file is using utf8 as encoding and the most errors from wrong encoding/decoding should be fixed.
To know the right encoding of an input file is better.
Almost dead, but too lazy to die: https://sourceserver.info
All humans together. We don't need politicians!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  New2Python: Help with Importing/Mapping Image Src to Image Code in File CluelessITguy 0 698 Nov-17-2022, 04:46 PM
Last Post: CluelessITguy
  Problem with importing python-telegram library into the project gandonio 1 1,515 Nov-01-2022, 02:19 AM
Last Post: deanhystad
  Problem with importing Python file in Visual Studio Code DXav 7 4,903 Jun-15-2022, 12:54 PM
Last Post: snippsat
  importing functions from a separate python file in a separate directory Scordomaniac 3 1,329 May-17-2022, 07:49 AM
Last Post: Pedroski55
  Importing a function from another file runs the old lines also dedesssse 6 2,478 Jul-06-2021, 07:04 PM
Last Post: deanhystad
  Importing text file into excel spreadsheet with formatting david_dsmn 1 3,548 Apr-05-2021, 10:21 PM
Last Post: david_dsmn
  importing a CSV file into Python russoj5 1 2,917 Aug-02-2020, 12:03 AM
Last Post: scidam
  Importing data from a text file into an SQLite database with Python macieju1974 7 4,011 Jun-29-2020, 08:51 PM
Last Post: buran
  importing CSV file into a OOP Class table using Python faruk61 1 2,906 Apr-15-2020, 12:00 PM
Last Post: faruk61
  importing CSV file into a HTML table using Python trybakov 1 2,247 Feb-22-2020, 09:47 PM
Last Post: scidam

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020