Read csv file with text qualifier

Help Studio
#1

I have a CSV file with text qualifier as double quotes ("). Please advise.

Ex:

  1. Numbers with thousand separator will be surrounded by double quoted(").
  2. If name has space then it will be surrounded by double quoted(").

Note: I need to start parse from 4th line onwards only.
PFA - sample file.sample.txt (493 Bytes)

#2

Hi,

Hope the following helps you.

Sample20200511-2.zip (16.9 KB)

Regards,

#3

Thanks for the help. Working for the given scenario.
But in normal scenario we may have multiple comma in amount field values.

Ex:
input:
1,234,1,Singh,“4,44,44,000”,16.00%,“30, months”,163,128,34,01-04-2020,paid,
expected:
1,234,1,Singh,44444000,16.00%,30 months,163,128,34,01-04-2020,paid,

Thanks in advance.

#4

Hi,

How about the following?

Sample20200511-2v2.zip (14.7 KB)

Regards,

#5

Thanks @Yoichi working fine. :+1:

Can you explain following
Assignment-1 expression:

System.Text.RegularExpressions.Regex.Match(strContent,"(?<=^(.\n){3})[\s\S]").Value

Assignment-2 expression:

System.Text.RegularExpressions.Regex.Replace(strContent,"(?<=,"").+?(?="",)",function(m) m.Value.Replace(",",""))

#6

Hi,

(?<= ) is lookbehind in regex. For example, (?<=A)Bmeans B will be matched if there is A before B.
(?= ) is lookahead in regex. For example, A(?=B) means A will be matched if there is B after A.

System.Text.RegularExpressions.Regex.Match(strContent,"(?<=^(.*\n){3})[\s\S]*").Value

(?<=^(.*\n){3})[\s\S]*") means [\s\S]* will be matched before ^(.*\n){3}.
[\s\S]* means all characters will be matched, and ^(.*\n){3} means first 3 lines will be matched.
As a result, it returns all the characters after 3rd line.

System.Text.RegularExpressions.Regex.Replace(strContent,"(?<=,"").+?(?="",)",function(m) m.Value.Replace(",",""))

(?<=,"").+?(?="",) will match characters between ," and ",
and function(m) means anonymous function. In this case it replaces “,” to “” for matched strings.
As a result it can remove double quote and comma if input is like “xxx,xx,x” for example.

Regards,

1 Like