Search code examples
excelcsvexport-to-excel

Csv file is not aligned correctly in excel

I have a CSV file, when I open Excel then Add from File, I choose 'comma' as delimiter because it is like that, so when I see then the result it is not good.

Basically the "description" field contains "breaks / enters" and they are shown in new row although all that text is shown in " ", i don't know how to resolve this, anyone has any idea?

Regards

Here is the example of csv

ID,Type,SKU,Naam,Gepubliceerd,Uitgelicht?,"Zichtbaarheid in catalogus","Korte omschrijving",Beschrijving,"Startdatum actieprijs","Einddatum actieprijs","Btw status",Belastingklasse,"Op voorraad?",Voorraad,"Lage voorraad","Nabestellingen toestaan?","Wordt individueel verkocht?","Gewicht (kg)","Lengte (cm)","Breedte (cm)","Hoogte (cm)","Klantbeoordelingen toestaan?",Aankoopnotitie,Actieprijs,"Reguliere prijs",Categorieën,Tags,Verzendklasse,Afbeeldingen,Downloadlimiet,"Dagen vervaltijd download",Hoofd,"Gegroepeerde producten",Upsells,Cross-sells,"Externe URL","Knop tekst",Positie,"Naam eigenschap 1","Waarde eigenschap 1","Zichtbare eigenschap 1","Globale eigenschap 1"
341,simple,DM1R,"Title-DM1",1,0,visible,,"<h2>TITLE</h2>
<strong>this is only what i'm expecting</strong>


This is the csv file

Hey hey hey
Tralalal

Eén afmeting: 50 x 40
<em>*de afmetingen zijn in cm en hoogte x lengte.</em>
<h2>Some text!</h2>
more txt text text

Opmerkingen:
<ul>
    <li>Test 1</li>
    <li>Test 2.</li>
</ul>",,,taxable,,1,32,5,1,0,,,,,1,,,32,"Diamond Paintings",,,,,,,,,,,,0,,,,
Solution

  • Same here when I import the given CSV as you describe on a Dutch machine. It seems Excel has a bug there.

    What DOES work is to use a small piece of PowerShell to convert the delimiters in the CSV to the machine's default delimiter.
    On a Dutch machine that would be the semi-colon ;

    # import the CSV with the comma as delimiter
$csv = Import-Csv -Path '<PATH-TO-THE-ORIGINAL-CSV-FILE>' -Delimiter ','
# export it again with a new name using either `-UseCulture` or `-Delimiter ';'`
$csv | Export-Csv -Path '<PATH-FOR-THE-OUTPUT-CSV-FILE>' -UseCulture -Force -NoTypeInformation -Encoding UTF8

    Next, you can simply double-click the new file and Excel will recognize the newlines inside the field.

    Of course, you will still have to set the Wrap text option on the cell alignment afterwards. (In Dutch Terugloop)

    Cell alignment

    Hope that helps