Text File not Reading in Correctly with Delimiter

Most text files I have no issue with reading in with the delimiter appropriately and pulling in my data. However, there is one source where, while it has the same delimiter as usual ("|"), isn't being correctly read. When I load the data, it only reads the first line. The details do not load and I get the warning "Too many fields in record 1". Other things to note:

1. I can do a text to column in excel with no issue. So while I suspect there is something different with this text file, it isn't crazy different than usual.

2. I attempted to load without the delimiter and that time I could get my rows. However, when I did a text to column flow, it didn't appropriately separate.

3. I also attempted to copy the exact delimiter that was in the file just in case the "|" was a little different. That didn't help.

I have attached the config and error I am getting.

Error message.xlsx

Input

Accepted answers

kjvan026

I figured out the solution myself. The problem stemmed from there being an extra delimiter at the end of my rows. The header row did not have the extra delimiter, but the rest did. As a solution, I simply started the import from the second row and unchecked the box for "First row contains field names". I have to name the field names myself, but that is fairly straight forward.

All comments

OTrieger

@kjvan026
When you are getting this error it means that you assigned the wrong delimiter.
Try to set it with \0 which means no delimiter and try to see what is the delimiter

kjvan026

I have attempted this as well. The delimiter is definitely the "|" but it is still not breaking it out.

kjvan026

Here is a highly modified version of my data (as in I deleted most of the columns and rows and changed some words). I think it has to do with the extra pipe at the end. How can I deal with that?

Data Modified version 2.txt

kjvan026

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335