CSV creation

[musosdev]

Hi

I've written some code to generate a CSV file from a database table.
However, some colums are getting broken up into 2 fields, or truncated - I
think cos they have comma in, and of course commas are used as field
seperators.

How can I ensure that my fields stay in tact?!

Thanks

 

[Fred]

Dans : musosdev écrivait :

Hi
Hello,

I've written some code to generate a CSV file from a database table.
However, some colums are getting broken up into 2 fields, or
truncated - I think cos they have comma in, and of course commas are
used as field seperators.

How can I ensure that my fields stay in tact?!

According to the specifications I found (seems there is not a real
standard (?)) I use this little program in vb to escape the values.
separator is the chosen separator for the CSV file : a comma most of the
time
a value should be enclosed with quotes when :
- containing the separator
- containing line breaks
- containing quotes
- starting or ending with space

If strvalue.Contains(separator) OrElse _
strvalue.Contains(vbCrLf) OrElse _
strvalue.Contains(vbCr) OrElse _
strvalue.Contains(vbLf) OrElse _
strvalue.Contains("""") OrElse _
strvalue.StartsWith(" ") OrElse _
strvalue.EndsWith(" ") Then
strvalue = strvalue.Replace("""", """""")
strvalue = """" & strvalue & """"
End If

 

[Cowboy \(Gregory A. Beamer\)]

The norm is to surround text fields with quotes, so they do not break on
commas inside the field.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

********************************************
| Think outside the box! |
********************************************

 

[musosdev]

Thank you both - problem solved!

The norm is to surround text fields with quotes, so they do not break on
commas inside the field.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

********************************************
| Think outside the box! |
********************************************