Why Your XML to CSV Conversion Loses Data — and How to Fix It

You convert a file. You open the new sheet. Some data is gone.

This is a common problem. Many people search for why an xml to csv loses data after they see blank cells, missing codes, or too few rows.

The good news is that most loss has a clear cause. And most loss can be fixed before you download the file.

This guide walks through the main causes, one by one. For each cause, you will see what goes wrong, why it happens, and how to fix it. At the end, you will also get a short check you can run on every file.

What XML and CSV do in different ways

First, it helps to know why the two formats clash.

XML is a format for storing data in a tree shape. A tree shape means data sits inside other data, like boxes inside boxes. One big tag can hold many small tags inside it.

CSV is a format for storing data in a flat table. A flat table means rows and columns, like a sheet of paper with a grid. Each row is one record. Each column is one field.

When you move from a tree to a flat table, you have to make choices. Where does a tag that sits inside another tag go? What do you do when the same tag shows up three times in one record? What do you do with extra data stored inside the tag itself?

If a converter makes those choices in a poor way, and does not tell you, data can vanish. The file still opens. The sheet still looks neat. But key values are missing.

That is why loss is hard to spot. You have to know where to look.

A short example to keep in mind

Use this small file as we go. It is a list of books. Each book has some normal tags. Each book also has data stored as attributes, and some tags repeat.

An attribute is a small piece of data stored inside the opening tag itself, not between two tags. For example, in <book id="101">, the word id is an attribute name, and 101 is its value.

<books>
  <book id="101" lang="en" currency="USD">
    <title>Simple Cooking</title>
    <price>24.99</price>
    <author>Ana Lee</author>
    <author>Ben Roy</author>
    <tag>food</tag>
    <tag>home</tag>
  </book>
  <book id="102" lang="fr" currency="EUR">
    <title>Garden Notes</title>
    <price>18.50</price>
    <author>Clara Kim</author>
    <tag>garden</tag>
  </book>
</books>

This file has two books. So a good table should help you see two books, with all their key data.

Here is a bad result first. This is what you get when a tool drops attributes and drops repeated values.

title	price	author	tag
Simple Cooking	24.99	Ana Lee	food
Garden Notes	18.50	Clara Kim	garden

At first glance, this looks fine. But look close. Where are the book IDs? Where are the language codes and the currency codes? Where is the second author, Ben Roy? Where is the second tag, home?

They are gone. The IDs 101 and 102 are gone. The codes en, fr, USD, and EUR are gone. One author and one tag were kept, and the rest were dropped.

Here is a good result for the same file. In this version, attributes are kept as columns, and repeated values are joined in one cell with ; .

book@id,book@lang,book@currency,title,price,author,tag
101,en,USD,Simple Cooking,24.99,Ana Lee; Ben Roy,food; home
102,fr,EUR,Garden Notes,18.50,Clara Kim,garden

Now nothing key is lost. You can see each book ID. You can see the language and the currency. You can see both authors for the first book. You can see both tags.

Keep this example in mind as you read the causes below. Each cause explains one part of the gap between the bad table and the good table.

If you are new to the whole process, start with the basic steps of converting XML to CSV and then come back to this guide to check your result.

Cause 1: Attributes are dropped

This is the most common cause of loss, and it is also the most quiet.

In XML, there are two main places to store a value. One place is between tags, like <price>24.99</price>. The other place is as an attribute, inside the opening tag, like <book id="101">.

Both places hold real data. But some converters only read the first place. They read what is between tags, and they skip what is inside the tag. The file converts with no error. No warning pops up. The attributes just do not show up in the sheet.

What kind of data lives in attributes?

A lot of key data lives in attributes. Here are some common cases:

IDs for products, books, users, or orders
Codes for currency, like USD or EUR
Codes for language, like en or fr
Codes for country or region
Type codes, like the kind of account or the kind of item
Dates or version numbers that a system adds to a tag
Links or keys that join one file to another file

If you lose an ID, you may not be able to match the row back to your main system. If you lose a currency code, a price means much less. Is 24.99 in dollars or in euros? The sheet will not tell you. If you lose a language code, you may not know which text is in which language.

In short, attributes often hold the small codes that make the big values make sense.

How to fix dropped attributes

The fix is simple in idea. Keep attributes as columns too.

On XmlToCsv, attributes are kept as columns, named like product@id. That name means the element name, then an @ sign, then the attribute name. So if a product tag has an id attribute, you get a column called product@id. If it has a currency attribute, you get a column called product@currency.

This naming is clear. You can tell at a glance if a column came from an attribute or from a normal tag. And you do not lose the link between the attribute and the tag it belongs to.

When you convert a file, check the preview first. The preview on XmlToCsv shows the first rows of the table before you download. Look for columns with @ in the name. If your source file uses attributes and you see no such columns, stop and check your tool or your settings. Do not download and hope for the best.

You can learn more about this fix when you keep XML attributes as columns. That guide goes deep on naming and on how to clean up attribute columns in your sheet after you download.

A quick test for this cause

Open your XML file in a text tool. Search for the = sign inside tags. Each time you see a pattern like name="value", that is an attribute. Write down two or three key ones, like an ID. Then open your CSV file and search for those values. If you cannot find them, attributes were dropped.

Cause 2: Repeated elements are collapsed

The second big cause is repeated tags.

In XML, the same tag can show up more than once inside one record. In the book example, <author> shows up twice in the first book. The <tag> tag also shows up twice. This is normal. A book can have two authors. A product can have three colors. An order can have five items.

A flat table has one cell for each field in a row. So what does a converter do when one field has two or three values, but there is only one cell for it?

Poor tools handle this in poor ways. Some keep only the first value and drop the rest. Some keep only the last value, so later values write over earlier ones. Both ways lose data, and both ways look fine at first glance because the cell is not blank. It has a value. It just does not have all the values.

How to fix repeated elements

You have two good choices, and the right choice depends on how you will use the sheet.

Choice one: Join the values in one cell.

Join the repeated values with ; in one cell. So Ana Lee and Ben Roy become Ana Lee; Ben Roy in the author cell. The row count stays the same. One row is still one book. This choice is best when you want a clean list, one row per record, and you just need to see or search all the values.

Choice two: Expand to one row per value.

Make a new row for each value. So a book with two authors becomes two rows. The book data repeats on each row, but each row has one author. This choice is best when you want to sort, count, or filter by the repeated field. For example, if you want to count books per author, one row per author makes the math easy.

On XmlToCsv, you can pick either way. Repeated child values can be joined with ; in one cell, or expanded to one row per value. Think about your end use before you pick. If you will build charts or counts by that field, expand. If you will read the sheet as a list, join.

For a full walk-through of the expand choice, see how to expand repeated values into one row per value.

A quick test for this cause

Find a record in your XML file that has a repeated tag. Count how many times the tag shows up. Then find that record in your CSV file. Can you find all those values? If you joined them, they should all be in the one cell, split by ; . If you expanded them, you should see that many rows for that record. If you see only one value and you did not choose to keep only one, values were collapsed and lost.

Cause 3: The wrong row element is chosen

The third cause can change the whole shape of your table.

A row element is the tag that repeats once per record. It is the tag that tells the converter, "start a new row here." In the book file, the row element is book, because the file has one <book> tag for each book.

If the wrong tag is picked as the row element, records merge or split in the wrong way.

Pick a tag that is too high up, like books, and all records merge into one giant row. You might get a sheet with just one row and very long cells. Pick a tag that is too low down, like author, and one book splits into many rows in a way you did not want. Or the main fields go blank, because they do not sit inside the low tag.

How to fix a wrong row element

On XmlToCsv, the row element is found for you. The tool looks at which tags repeat and picks the most likely one. In most files, this pick is right, and you do not need to do a thing.

But no auto pick is right for all files. So you can also pick a different row element yourself, or type a path by hand. A path is the chain of tags from the top of the tree down to the tag you want, like books/book.

Before you download, ask one plain question: "What is one row in my sheet meant to be?" If one row should be one book, the row element must be book. If one row should be one order, the row element must be order. Then check that the row count in the preview looks right for that answer.

For a deep guide on this choice, learn how to choose the right row element. Getting this one choice right fixes a whole class of loss at once.

A quick test for this cause

Count the records in your XML file. For the book file, count the <book> tags. That count is two. Then count the data rows in your CSV file, not counting the top header row. If you joined repeated values, the counts should match. If they do not match, and you did not choose to expand, the row element is likely wrong.

Cause 4: Mixed content loses the loose text

The fourth cause is less known, but it hits some files hard.

Mixed content means text and child tags are mixed in the same parent tag. Here is a short example:

<note>
  <text>Please pay <b>soon</b> and keep the receipt.</text>
</note>

In this case, the <text> tag holds loose text, "Please pay" and "and keep the receipt.", plus a child tag <b> with the word "soon". The loose text is not inside its own tag. It just sits inside the parent, next to the child tag.

Some converters only read child tags. They grab the word inside <b>, but they drop the loose text around it. Or they keep the first bit of loose text and drop the rest. The sense of the full line is lost.

You see this pattern a lot in notes, descriptions, help text, and any field where a person typed free text with some bold words or links inside.

How to fix mixed content loss

The fix is to keep the loose text in its own column, often named #text. The #text column holds the text that sits directly inside a tag, apart from the text inside its child tags.

When you check your preview, look at fields that should hold full sentences. If the sentences look cut up, or if only the bold words show up, mixed content loss is a likely cause. A tool that gives you a #text column helps you keep the whole line. You can then join the parts back up in your sheet if you need one clean text field.

The main point is awareness. If a field holds free text with tags inside it, check that field with extra care after every conversion.

Cause 5: Different shapes across records leave blank columns

The fifth cause is not always loss at all. But it scares many people, so it is worth a clear word.

In many real files, not all records have the same shape. One book has a subtitle. The next book does not. One product has a sale price. The next one does not. One user has a middle name field. The next one does not.

When the converter builds one table for all records, it makes a column for each field that shows up in any record. If a field shows up in only some records, the other rows will have a blank cell in that column.

Those blanks are not loss. They are true. That record just did not have that field in the source file. The data was not dropped by the converter. It was never there for that record.

How to tell true blanks from real loss

Ask two questions:

Does the column exist at all? If the column exists and only some cells are blank, that is likely a shape difference, not loss. If the column does not exist at all, but the field exists in the XML, that is loss.
Does the source record have that field? Open the XML file, find that one record, and look. If the tag is not there, a blank cell is correct. If the tag is there with a value, and the cell is blank, that is real loss.

This check takes a minute, but it saves a lot of worry. It also stops you from "fixing" a file that was fine, and making it worse.

Real files from shops, banks, and large systems are full of shape differences. Expect some blank columns. Check a few of them, confirm they are true blanks, and move on.

Other small causes worth a quick look

The five causes above cover most loss. But a few smaller issues can also bite.

Broken XML with no clear error. If an XML file has a flaw, like a tag that was not closed, a good tool should tell you where the flaw is. On XmlToCsv, broken XML gives a clear error with line and column numbers, so you can go to that spot and fix it. Some rival tools fail silently with no message, which leaves you guessing if the file was bad or the tool was bad. A clear error is not loss, but it stops silent loss, because you learn the job did not finish and you know why.

File size limits. Very large files need care. On XmlToCsv, large files are processed with streaming. Streaming means the tool reads the file in small parts, one part at a time, instead of loading the whole file at once. This helps large files finish without using too much memory. Know your plan limits too: Free up to 2 MB, Starter up to 25 MB, and Pro up to 100 MB. If a file is over your limit, the job cannot run as is. Split the file first, or use a plan with a higher limit.

Row limits on export. The Free plan allows 10 conversions per month, and export up to 5,000 rows. If your file has more rows than your plan allows for export, check what you got. A cut-off export can look like loss, when it is really a plan limit. Starter is $9 per month and Pro is $19 per month, with higher file-size limits. Pick the plan that fits your file size and row count, so limits do not cut your data.

Doing the same mapping work again and again. This one is not loss in one file, but loss of time and care across many files. If you convert the same kind of file each week, you may pick columns or rename fields the same way each time. If you rush, you may miss a step one week and lose a column. Paid plans on XmlToCsv can save field mapping templates. A template stores your field choices so you can reuse them. That makes repeat jobs more steady, and steady jobs lose less data. Exports are CSV and XLSX, so you can also pick the format that fits your next tool.

Your checking routine: a five-minute check for every file

You do not need a long test to catch most loss. You need a short routine that you run the same way each time. Here is one that works well.

Step 1: Count records in the XML

Before you convert, count how many records the file should have. Count the row element tags. In the book file, count the <book> tags. You can use the find tool in a text app and search for <book and <book>. Write the count down.

Also note the first record and the last record. Write down a key value for each, like the first book ID and the last book ID. You will look for them in step 3.

Step 2: Check the preview before you download

On XmlToCsv, use the table preview. It shows the first rows before download. Read the column names. Do you see attribute columns, like book@id, if your file uses attributes? Do repeated fields look joined or expanded the way you chose? Does the row count in view look sane, not one row and not tens of thousands for a small file?

If the preview looks wrong, fix the settings now. Change the row element, or change how repeated values are handled. Do not download a bad preview and try to fix it in the sheet. It is much faster to fix it at this step.

Step 3: Count rows in the CSV and compare

After download, open the CSV file and count the data rows. Do not count the header row at the top. Compare this count with the XML record count from step 1.

If you joined repeated values, the counts should match. If you expanded repeated values, the CSV will have more rows, and that is expected. In that case, pick one record and check that its extra rows add up to the right count for that record.

If the counts are far off with no clear reason, go back. The row element is the first thing to re-check.

Step 4: Spot-check the first and last records

Find the first record in the CSV file. Check its key fields against the XML file: ID, name or title, price, and any codes. Then do the same for the last record.

Why first and last? Because some flaws cut off the start or the end of a file. A check of only the middle rows can miss that. First and last checks catch cut-offs and also catch files where the last records have a different shape.

While you are there, spot-check one record in the middle that has repeated values or attributes, if you can find one fast. That one check covers causes 1 and 2 at once.

Step 5: Check blanks before you worry

Scan for blank columns and blank cells. For a few blanks, go back to the XML file and look at those records. If the field is not in the source record, the blank is true. If the field is in the source with a value, you have found real loss, and the cause list above will help you name it.

Write down what you found, even if all is well. A short note like "120 records in, 120 rows out, first and last IDs match" gives you trust in the file, and it helps the next person who uses it.

Putting it all together

Data loss in XML to CSV conversion is rarely random. It comes from a short list of causes:

Attributes dropped, so IDs and codes vanish
Repeated values collapsed, so only one value survives
The wrong row element, so records merge or split the wrong way
Mixed content, so loose text around child tags is dropped
Shape differences, which leave true blanks that only look like loss

Each cause has a fix, or a check that proves there is no loss. Keep attributes as columns. Join or expand repeated values on purpose. Pick the row element that matches what one row should be. Keep loose text in a #text column. And run the five-minute check: count records, check the preview, count rows, and spot-check the first and last records.

Do that, and your sheets will hold what your XML files held. That is the whole goal: same data, new shape, nothing key left behind.

References

https://www.w3.org/TR/xml/ — W3C XML specification — defines elements and attributes.
https://developer.mozilla.org/en-US/docs/Web/XML/Introduction — MDN's plain-language XML introduction.