0

I'm trying to import the following CSV data set into Excel. I'm using the Text Import Wizard to set the right delimiters. So far, I used the ";" and "," delimiters.

Here is the result so far. The data set seems to be looking fine, until row 493. From that point on, the data changes from the year 2014 to 2015. Somehow, the format of the data seems to have changed somewhat from that point on, resulting in a CSV file in which the columns are not aligned anymore.

Here's a snapshot of what happens:

enter image description here

Question: what should I do to format this CSV data set in such a way that its columns are neatly aligned in Excel?

3
  • Please note that I did not select the Comma delimiter in the picture, but that does not address the main issue anyway.
    – Max Muller
    Dec 3, 2020 at 14:38
  • See my answer to your second question in this context. Dec 5, 2020 at 10:00
  • Importing with Power Query, using UTF8, comma delimiter, and quotestyle.csv seems to result in a proper transformation. Dec 7, 2020 at 12:40

2 Answers 2

1

The file is malformed at line 494, where the record seems to be just a fragment.

This is how it looks in notepad++:

enter image description here

It seems like there is spurious line-feed that splits line 493 in two. Manually joining lines 493 and 494 might fix the problem for this line (I haven't tried to import it).

However, this line is not the only one. You should look for lines starting with ", as all of them seem to belong to the preceding lines.

Using notepad++, the following Replace All operation might fix all the lines:

  • Find what : \n"
  • Replace with : "
  • Search mode : Extended

enter image description here

3
  • Thank you for your advice. This particular operation did not seem to fix the whole problem. Notepad++ seems to be a useful program though to tackle these kinds of problems. If you happen to find out what works in this case, could you please let me know?
    – Max Muller
    Dec 4, 2020 at 12:42
  • It's possible that there are more errors in this dataset. Let me know which line number and I'll look (after joining the lines as described in my answer).
    – harrymc
    Dec 4, 2020 at 14:04
  • it is still many lines that contain the same error. I could only change one line when I did: Find what: \n", Replace with " and selected Replace All. The other lines did not change.
    – Max Muller
    Dec 4, 2020 at 14:14
1

There is no problem/error in the source file.

If you set properly the encoding and the field separator (it's comma) you should not have any problem. Here my example libreoffice open window (I do not have excel).

enter image description here

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.