Menu
I try to import a CSV file in Excel, using ; as delimiters, but some columns contains
; and/or quotes.
My problem is : I can use double quotes to ignore the delimiters for a specific string, but if there is a double quote inside the string, it ignores delimiters until the first double quote, but not after. I don't know if it's clear, it's not that easy to explain.
I will try to explain with a example :
Suppose I have this string this is a;test : I use double quotes around the string, to ignore the delimiter => It works.
Now if this string contains delimiters AND double quotes : my trick doesn't work anymore. For example if I have the string this; is" a;test : My added double quotes around the string ignore delimiters for the first part (the delimiter in the part this; is is correctly ignored, but since there is a double quote after, Excel doesn't ignore the next delimiter in the a;test part.
I tried my best to be as clear as possible, I hope you'll understand what is the problem.
514
By default, the escape character is a " (double quote) for CSV-formatted files. If you want to use a different escape character, use the ESCAPE clause of COPY , CREATE EXTERNAL TABLE or gpload to declare a different escape character.
csv file. Use the Foreach-Object cmdlet (% is an alias) to read each line as it comes from the file. Inside the script block for the Foreach-Object command, use the $_ automatic variable to reference the current line and the replace operator to replace a quotation mark with nothing.
Since CSV files use the comma character "," to separate columns, values that contain commas must be handled as a special case. These fields are wrapped within double quotation marks. The first double quote signifies the beginning of the column data, and the last double quote marks the end.
When reading in a quoted string in a csv file, Excel will interpret all pairs of double-quotes ("") with single double-quotes(").
so "this; is"" a;test" will be converted to one cell containing this; is" a;test
So replace all double-quotes in your strings with pairs of double quotes.
Excel will reverse this process when exporting as CSV.
Here is some CSV
a,b,c,d,e
"""test1""",""",te"st2,"test,3",test"4,test5
And this is how it looks after importing into Excel:
117
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
