How to Fix Columns That Shift in a CSV File

What 'shifted columns' really means

A CSV file looks plain at first glance. Each line is one row. Each row holds fields in a set order. A comma marks where one field ends.

The head row names each field. It might say name, town, state, and ZIP. The rows that come next must match it. Each row needs the same count of fields.

That rule comes from RFC 4180. It is the main rule set for CSV files. The rule is clear and strict. Each row should hold the same count of fields.

When a row breaks that rule, things shift. One row has one field too much. The next field lands in the wrong place. Then the next one does too.

Say a town name lands in the ZIP box. Or a price lands in the name box. Your tool now reads the wrong facts. It may save them that way too.

The shift may show in just one row. Or it may show in all rows past a point. It all turns on where the flaw is. One bad row can spoil a whole load.

This is a top cause of bad loads. It helps to know why CRM imports fail in the first place. Shifted rows sit near the top of that list.

The good news is plain. The flaw is most times small. It is one comma or one quote. You can find it and fix it.

Do not fix the file by eye at scale. A file can hold ten thousand rows. Your eye will miss the one bad line. Use a check that counts each row.

Think of each row as a train. Each car must sit in its own slot. If one car takes two slots, all cars shift. Your job is to find that car and fix it.

Cause 1: commas inside a value

The most plain cause is a comma in a value. Names and firms hold commas all the time. A firm name may be Acme, Inc. A town line may hold a street and a suite.

Look at this short line for a clue. Acme, Inc.,00501 looks like two fields to you. A tool reads it as three fields. The comma in the name splits it in two.

The tool does not know your plan. It sees a comma and cuts there. It has no way to guess your thought. It just does what the mark tells it.

The fix is to wrap the field in quotes. Write it like this in the file. Put the full name in double quotes. Then the comma sits safe in the field.

RFC 4180 gives this rule too. A field with a comma must wear quotes. The same holds for quotes and line breaks. If a field holds one, wrap it up.

Here is how the line should look. The name sits in quotes as one field. The ZIP code stays in its own field. Now the row has the right field count.

You will see this flaw in firm names most. Last name first names cause it too. Think of Smith, John in a list. That comma will split the name in two.

Street lines cause it a lot as well. A line like 12 Main St, Apt 4 has a comma. If it has no quotes, it splits. The suite lands in the town field.

Job names can hold commas too. So can notes and tags in your file. Any free text field is at risk. Free text is where commas like to hide.

The fix takes just a few ticks. Add quotes to the field with the comma. Then count the fields in that row. It should now match the head row.

Most tools that write CSV files do this for you. But hand edits can break it fast. A quick fix in a text tool can strip quotes. That is when the shift sneaks back in.

Cause 2: missing or extra quote marks

Quote marks have a job to do. They wrap fields that need care. They tell the tool where a field starts. They tell it where the field ends.

If one quote is lost, the field runs on. It may eat the next comma as text. It may eat the next field too. Then the whole row shifts out of line.

If one quote is added by chance, harm comes too. The tool may end the field too soon. The rest of the text then stands bare. Bare text with a comma will split at once.

Quotes in a field need care as well. RFC 4180 says to double them. If a quote sits in a quoted field, write two. One quote on its own will break the parse.

Say a note says she said hi in quotes. Those quote marks must be doubled up. If they are not, the tool gets lost. It can shift the rest of the row.

Smart quotes are a sneaky cause too. Word tools may swap straight quotes for curved ones. A CSV tool wants straight double quotes. Curved marks do not count as quotes at all.

So check the type of mark you use. Use plain straight double quotes in files. Do not paste text from a word file blind. That paste can bring the wrong marks with it.

Look for rows where quotes show in the data. That is a sign the quotes did not parse. The tool read them as plain text. That row most times has a shift as well.

How do you spot a lost quote fast? Count the quotes in the row by eye. A quoted field needs one at each end. An odd count is a clear red flag.

Some rows have more than one such field. Count with care in those rows. Each quoted field must start and end clean. One slip in one field shifts all the rest.

Do not try to fix quotes in a sheet view. The sheet may hide the true marks. It shows you the parsed view, not the raw text. Work in the raw file for this fix.

Once the quotes are right, save the file. Keep it as plain CSV text. Then run a field count check on all rows. Rows that still fail need one more look.

Cause 3: the wrong delimiter

A delimiter is the mark that splits fields. In most CSV files, that mark is a comma. But some files use a tab or a pipe. Some use a semicolon in place of a comma.

The mark lets a tool tell fields apart. That point comes from the U.S. National Archives. If the tool picks the wrong mark, it fails. It can not find where each field ends.

If the mark is wrong, the file looks odd at once. The whole file may show as one wide column. Each row sits in one big cell. No field splits where it should.

That sign is good news in a way. The data may be fine as it stands. The tool just read it the wrong way. Set the right mark and the file snaps back.

The Archives note one more key fact. Excel may need the mark set by hand. It does not all the time guess it right. You may have to tell it which mark to use.

To learn more, read why Excel breaks CSV files in real use. Wrong marks on import are a big part of it. A hand set step can save a whole file.

How do you know which mark is right? Open the raw file and look at line one. See what mark sits between the head words. That mark is the one the file used.

If you see tabs, the file is tab split. If you see pipes, it is pipe split. Name it right when you load the file. Do not let the tool guess if you can help it.

Mix marks can cause a shift too. One part of a file may use commas. A pasted part may use tabs in its place. That mix will shift the pasted rows at once.

Keep one mark for the whole file. Pick the mark your next tool wants most. For most CRM loads, that mark is a comma. Then make sure each row uses that same mark.

Watch out for marks inside the data too. A tab in a note can act like a split mark. That is true if the file is tab split. Clean such marks out of free text fields.

The wrong mark is fast to fix once seen. It is also fast to miss if you rush. Take ten ticks to check line one first. That small check can save hours of rework.

How to find the bad rows

Start with the head row of the file. Count how many fields it holds. Write that count down and keep it near. That count is your truth for all rows.

Now count the fields in each row. Rows that match the head are fine. Rows that do not match need a fix. Flag each bad row so you can find it fast.

RowSift can help with this first check. It runs in your browser on your own screen. It checks that each row has the same field count. It flags rows that do not line up.

Do the check on the raw file first. Do not open it in a sheet and save it yet. A sheet can change the file as it saves. Work on a copy so your source stays safe.

Use the table as a quick guide. Match the sign you see to a cause. Try that fix first on the flagged row. Then count the fields in that row again.

Look at the first bad row with care. Read it in raw text form, mark by mark. Find the first place where it goes wrong. The rest of the shift flows from that spot.

Fix that first flaw and count once more. Some rows have just one flaw in them. Some rows have two or three flaws. Go step by step till the count is right.

Check the rows just past the bad one too. A lost quote can eat a line break. That can merge two rows into one long row. If counts jump by a lot, look for that.

Keep a short list as you work. Write the row count and the flaw you found. This list helps if the same flaw comes back. You will spot the trend much more fast.

Do not trust a quick glance in a sheet. A sheet may trim or hide some marks. It may show a clean view of a bad file. The raw count is the check that counts.

When all rows match the head count, pause. Your file is now in line at a base level. It can still hold other flaws, like bad dates. But the shift flaw is gone at that point.

How to fix the file and stop the next shift

Now fix each flagged row one by one. Add quotes where a field holds a comma. Double quotes that sit inside a quoted field. Take out stray quotes that do not belong.

Keep your edits small and plain. Change just the marks that are wrong. Do not retype whole rows by hand if you can. Hand typing can add new flaws of its own.

When the fix is done, you can check your file with RowSift once more. It will flag rows that still do not line up. It then lets you export a clean CSV file.

Save the fixed file with a new name. Keep the old file in a safe place. If the load still fails, you can diff them. You can also roll back with no loss.

Next, stop the flaw at its source. Ask where this file came from each time. Was it typed by hand or built by a tool? Was it pasted from mail or a word file?

Hand work is where most shifts start. A quick edit can drop a pair of quotes. A paste can bring in the wrong marks. A save in the wrong form can swap marks.

Set a few plain rules for your team. Save files as CSV in plain text form. Do not edit raw CSV files in word tools. Check the field count each time you export.

If a tool builds the file for you, test it. Export a small test file from that tool. Count the fields in each row of the test. If a test row fails, fix the source tool first.

Make one last check a habit each time. Count fields per row before each load. It takes a short time and saves long pain. A clean count is your green light to load.

Teach the team to spot the top signs. One row wide, a name split in two, quotes in data. When they see a sign, they stop the load. They fix the row, then they start again.

Keep your routine the same each month. Same source, same marks, same final check. A plain routine beats a big one time fix. It keeps columns that shift in a CSV from coming back.

You do not need a large tool set for this. You need a raw text view and a count check. You need care with commas, quotes, and marks. With those, most shifts end for good.