Export-CSV - Why does it put quotes around *everything*?
Kevin Buchan
Sure, this is a valid CSV, technically, but it feels like a clunky
implmentation. Only values with delimiters within them should have to
be quoted.
Is there a way to turn this off and make it more RFC4180 compliant?
Thanks.
--
Kevin Buchan
kevin.buchan@troutman[nospam]sanders.com
Bob Landau
Because its easier to quote everything rather than not. You can have
whitespace, embedded quotes, etc. C/C++ has taken this same approach when it
comes to several concepts one would be enums and the trailing ',' which is
ignored because many people would build these at compile time.
CVF files are not for human consumption; these are to be fed to applications.
Please point to the part that states adding non-essential quotes breaks
compatibility with this RFC. I'm not seeing it based on the section 2)
"Definition of the CSV format"
http://tools.ietf.org/html/rfc4180
5. Each field may or may not be enclosed in double quotes (however
some programs, such as Microsoft Excel, do not use double quotes
at all). If fields are not enclosed with double quotes, then
double quotes may not appear inside the fields. For example:
"aaa","bbb","ccc" CRLF
zzz,yyy,xxx
6. Fields containing line breaks (CRLF), double quotes, and commas
should be enclosed in double-quotes. For example:
"aaa","b CRLF
bb","ccc" CRLF
zzz,yyy,xxx
7. If double-quotes are used to enclose fields, then a double-quote
appearing inside a field must be escaped by preceding it with
another double quote. For example:
"aaa","b""bb","ccc"
"Kevin Buchan" wrote:
> .
>
Kevin Buchan
breaks compatibility with this RFC."
Of course, it does not have such a section. As I pointed out,
export-csv does, in fact, produce valid CSV.
My point was: Sure, it's valid, but I require more from the
developers on my Team than meeting "minimum expectations". I had my
most junior develper write a PoSh 2 function to accomplish the same
thing and his results in a (slightly) smaller file and executes in
less than 1/2 the time as export-csv. Sure, he spent way too much
time optimizing it for speed (we'll never get that day back) but it
was a good learning exercise for him.
For "middle of the night" operations that just transfer CSV data from
one system to another, we use the built in export-csv so that we have
no external dependencies but for time sensitive operations or when I
am producing a CSV file to be sent to an external partner, we use our
improved, home-grown version.
Perhaps I raised your ire by saying "make it more RFC4180 compliant"
because clealy it is fully compliant. I'm not sure why you seemed to
react emotionally to what was simply a request to the community.
-KB
Karl Mitschke
If it's IS in fact RFC4180 compliant (and it IS), how can it possible be
"more compliant"?
You say "Sure, it's valid, but I require more from the developers on my Team
than meeting "minimum expectations"."
The RFC has no "minimum expectations" - it's either RFC compliant or it's
not ;)
You should have left the red herring of RFC compliance out of this and just
asked "Can I prevent double quotes in an export-csv"
Karl
http://unlockpowershell.wordpress.com/
-join("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})
Larry__Weiss
On 6/8/2010 12:41 PM, Karl Mitschke wrote:
> ... just asked "Can I prevent double quotes in an export-csv"
>
Now that it is narrowed down to that, allow me to ask
Can I prevent double quotes in an export-csv?
and also
Is there a published PowerShell script that will remove the maximum
number of double quotes from a CSV file, yet retain the integrity of
that CSV file?
- Larry
Karl Mitschke
Hello Larry__Weiss,
Larry;
Was it you asking the original question, under an alias? ;)
> Can I prevent double quotes in an export-csv?
No, but you can use ConvertTo-Csv and strip the quotes.
I use ConvertTo-Csv to remove the header here:
http://unlockpowershell.wordpress.com/2009/12/15/powershell-export-csv-with-no-header/
> Is there a published PowerShell script that will remove the maximum
> number of double quotes from a CSV file, yet retain the integrity of
> that CSV file?
I haven't seen one.
Karl
http://unlockpowershell.wordpress.com/
[string]([regex]::Matches("6B61726C6D69747363686B65406D742E6E6574",'(.{2})')|%{[char][Convert]::ToInt32($_.value,16)})
Larry__Weiss
On 6/8/2010 2:43 PM, Karl Mitschke wrote:
> Hello Larry__Weiss,
>> On 6/8/2010 12:41 PM, Karl Mitschke wrote:
>>> ... just asked "Can I prevent double quotes in an export-csv"
>>
>> Now that it is narrowed down to that, allow me to ask
>> Can I prevent double quotes in an export-csv?
>> and also
>> Is there a published PowerShell script that will remove the maximum
>> number of double quotes from a CSV file, yet retain the integrity of
>> that CSV file?
>>
> Was it you asking the original question, under an alias? ;)
>
Naw, I'm just seizing the moment to resolve some of my own curiosities.
<grin>
- Larry
Karl Mitschke
I hoped so :)
Larry__Weiss
On 6/8/2010 2:43 PM, Karl Mitschke wrote:
> ... you can use ConvertTo-Csv and strip the quotes.
>
But you have to be smart about it.
The details of all the conditions where double quotes must be preserved
is what I hope to find in a tested implementation.
- Larry
Larry__Weiss
> http://unlockpowershell.wordpress.com/2009/12/15/powershell-export-csv-with-no-header/
>
Thanks for bringing my attention to ConvertTo-Csv.
Here is some code I derived starting with the example from
your webpage:
Get-WmiObject Win32_OperatingSystem |
Select-Object CSName,Caption,Version |
ConvertTo-Csv -NoTypeInformation |
Select-Object -Skip 1 |
Set-Content OSInfo.csv
Get-Content OSInfo.csv
I noticed that even when your example code used the -OutVariable
parameter that ConvertTo-Csv produced pipeline output, so I just
added on some more fixtures to the pipe to skip the header.
Also, that way, the output file does not have to be removed before the
main pipeline is started.
- Larry
Larry__Weiss
And one more modification to simplify it
<##> Get-WmiObject Win32_OperatingSystem |
<##> Select-Object CSName,Caption,Version |
<##> ConvertTo-Csv |
<##> Select-Object -Skip 2 |
<##> Set-Content OSInfo.csv
<##>
<##> Get-Content OSInfo.csv
(I added the <##> to see if that way it would preserve my indentation
which seemed to get lost on my earlier post.)
- Larry
Karl Mitschke
Yes, indeed
Notice *I* didn't provide a soloution ;)
Karl Mitschke
I just removed the file as it was leftover from the previous example ;)
I likle your version too
Larry__Weiss
On 6/8/2010 5:04 PM, Karl Mitschke wrote:
>> On 6/8/2010 2:43 PM, Karl Mitschke wrote:
>>> I use ConvertTo-Csv to remove the header here:
>>> http://unlockpowershell.wordpress.com/2009/12/15/powershell-export-cs
>>> v-with-no-header/
>> Thanks for bringing my attention to ConvertTo-Csv.
>>
>> Here is some code I derived starting with the example from your
>> webpage:
>>
>> Get-WmiObject Win32_OperatingSystem |
>> Select-Object CSName,Caption,Version |
>> ConvertTo-Csv -NoTypeInformation |
>> Select-Object -Skip 1 |
>> Set-Content OSInfo.csv
>> Get-Content OSInfo.csv
>>
>> I noticed that even when your example code used the -OutVariable
>> parameter that ConvertTo-Csv produced pipeline output, so I just added
>> on some more fixtures to the pipe to skip the header.
>>
>> Also, that way, the output file does not have to be removed before the
>> main pipeline is started.
>
>
Your version had to remove it if it exists because yours appends
to the file.
My version overwrites an earlier file if it exists.
I got in at the V2 level of PowerShell, and sometimes I wonder if
some techniques I find in examples are because certain cmdlets or
parameters were not available in V1.
Was the -Skip parameter to Select-Object available in PS V1?
- Larry