Menu
I've recently created an application which adds items to a Database by CSV. After adding items I realized that lots of my values had extra quotes (") that weren't needed and this was messing up my ordering.
The problem is that when exporting to a CSV from Excel, Excel adds extra quotes to all of my values that already have a quote in them. I've shown the difference below:
Original Item: Drill Electric Reversible 1/2" 6.3A
Exported Item: "Drill Electric Reversible 1/2"" 6.3"
Note: the CSV export is adding three (3) extra quotes ("). Two on the ends, and one after the original intended quote.
Is there a setting I can change, or a formatting property I can set on the Excel File/Column? Or do I have to live with it and remove these quotes in my back-end code before adding them to the Database?
835
To do this, select the column of data that has the extra quote marks, then go to the “Data” tab and click “Text to Columns.” In the “Text to Columns” wizard, select “Delimited” and click “Next.” Then, uncheck the “Tab” option and check the “Other” option.
Quotation marks are used as text qualifiers Quotation marks appear in CSV files as text qualifiers. This means, they function to wrap together text that should be kept as one value, versus what are distinct values that should be separated out.
ISSUE: A CSV file contains data that consists of a bunch of fields separated by a comma and optionally enclosed by double-quotes, hence the name Comma-Separated-Values or CSV. Due to the lack of an actual standard for CSV formatting, some programs may opt to use semi-colons instead of commas as separators.
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.
This is entirely normal. The outer quotes are added because this is a string. The inner quote is doubled to escape it. Same kind of thing you'd see in a SQL query for example. Use the TextFieldParser class to have tried and true framework code care of the parsing of this for you automatically.
193
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
