Csv import - comma acting as text delimiter

[shujath36]

I don’t have excel on my machine. Trying to import data from csv file to add values to parameters in family.

I am facing two issues. The comma in a sentence is acting as text delimiter and increasing the list numbers and some quotations showing up in the text as shown below.

The only solution I find in the forum is to replace comma by full stop which is not giving me desired results.

In the second image attached, text delimiter feature is removing the quotations in openoffice.Need something similar in Dynamo.

[Draxl_Andreas]

Can you save it as CSV(MS-Dos)…
i got rid of some trouble during import it!

[Mark.Ackerley]

Hey, so, I got this suggestion from Marcel…

Why not use a different delimiter like * or _ at the beginning and end of every string so you can skip those stray comma’s and breaks?

I googled how you might do that, and got this…

How to change the delimiter when exporting CSV from Excel?

Perhaps that is useful?

Mark

[Andrew_Hannell]

answer 1: avoid CSV format wherever possible- it is a poor format for the reasons you’ve identified
answer 2: the Python CSV importer is more tolerant- refer below. I’ve only used ‘Python Script from String’ so you can see it on the screenshot

import sys
sys.path.append('C:\Program Files (x86)\IronPython 2.7\Lib')
sys.path.append('C:\Program Files\IronPython 2.7\Lib')
import csv
import StringIO

file_in = IN[0]
output = []
source = StringIO.StringIO(file_in)
csvsource = csv.reader(source)
OUT = [line for line in csvsource]

[Marcel_Rijsmus]

You can open the CSV in any text editor, and use find and replace.
Much easier

[truevis]

@shujath36

I don’t have excel on my machine.

How about use Google Sheets? https://howchoo.com/google/create-a-csv-from-a-google-spreadsheet

There are also some strong free CSV editors around like [CSVed (Freeware Software)].

[Andrew_Hannell]

yes, but it depends on the data

For complex or inconsistent data, you would need to use regular expressions in the find & replace which can be hit & miss. A data pattern that you haven’t thought of will cause it to break

[shujath36]

Thanks Andrew for the script. It worked flawlessly.

@Draxl_Andreas and @Mark.Ackerley pipe character is also working fine.