Need Help Removing quotes and double quotes from my Excel Spreadsheet

[deankeen]

Hello! Been trying to figure out how to solve this problem for days now. I have data with many special characters like commas, quotations marks, etc. Excel is randomly putting unwanted quotation marks in some of my cells which is apparently the way Excel saves csv files. I've tried saving in all different types of formats but to no avail.

Excel is putting quotes at the beginning and end of some of my cells as well as double quotes ("") where there should just be single quotes (").

If anyone has a VB script I can use to fix this, I would be incredibly grateful!

I've attached a sample cell so you can see the problem I'm experiencing. You can see the extra quotes in the data that shouldn't be there, if you copy the cell to Notepad.

Thank you for any help!
Dan

[Ago]

I'm writing this from my phone so I can't give you actual code.
But use a code that creates a textfile and just name the file .csv instead.
This way you have total control of what will be written to the file

[Kenneth Hobs]

When you have special characters like vblf in a cell, a copy from Excel to Notepad will do that. If you want to replace vblf with another or none, use Replace().

Here is how I replaced the tab character.


Option Explicit

Sub t()
Dim rc As Variant
Dim s As String, s2 As String

s = ActiveWorkbook.Path & "\Fruits.txt"

[A1] = "Fruit"
[A2] = "Apple"
[A3] = "Grape"
[A4] = "Orange"
[B1] = "Color"
[B2] = "Red/Golden"
[B3] = "Red/Green"
[B4] = "Orange"

Range("A1:B4").Copy
s2 = Replace(getClipboard(), vbTab, ",")
Application.CutCopyMode = False
MakeTXTFile s, s2

rc = Shell("notepad " & s, vbNormalFocus)
'Kill s
End Sub

Sub InsertTxtFileContents()
Dim s As String, a() As String, b() As String
Dim i As Long, i2 As Long, r As Range

s = ActiveWorkbook.Path & "\Fruits.txt"
Set r = Range("C5")

FileLoadToArray a(), s
i2 = 0
For i = LBound(a) To UBound(a)
b() = Split(a(i), ",")
If UBound(b) > 0 Then r.Offset(i2, 0).Resize(1, UBound(b) + 1).Value = b()
i2 = i2 + 1
Next i

'Kill s
End Sub

Sub MakeTXTFile(filePath As String, str As String)
Dim hFile As Integer
If Dir(FolderPart(filePath), vbDirectory) = "" Then
MsgBox filePath, vbCritical, "Missing Folder"
Exit Sub
End If

hFile = FreeFile
Open filePath For Output As #hFile
If str <> "" Then Print #hFile, str
Close hFile
End Sub

Function FolderPart(sPath As String) As String
FolderPart = Left(sPath, InStrRev(sPath, "\"))
End Function


Function getClipboard()
'Add Reference: 'Reference: Microsoft Forms xx Object
Dim MyData As DataObject

On Error Resume Next
Set MyData = New DataObject
MyData.GetFromClipboard
getClipboard = MyData.GetText
End Function


'http://www.visualbasic.happycodings.com/Files_Directories_Drives/code54.html
'Purpose : Reads a file into a string array.
'Inputs : asLines() A string array (see Outputs)
' sFileName The path and file name of the file to open and read
'Outputs : Returns an empty string on success, else returns the error decription
' asLines(1 to NumLines) String array containing the file
'Notes : Usually used for text files, but will load any file type.



Function FileLoadToArray(ByRef asLines() As String, ByVal sFileName As String) As String
Dim iFileNum As Long, lFileLen As Long
Dim sBuffer As String

'Initialise Variables
On Error GoTo ErrFailed

'Open File
iFileNum = FreeFile
Open sFileName For Binary Access Read As #iFileNum
'Get the size of the file
lFileLen = LOF(iFileNum)
If lFileLen Then
'Create output buffer
sBuffer = String(lFileLen, " ")
'Read contents of file
Get iFileNum, 1, sBuffer
'Split the file contents
asLines = Split(sBuffer, vbNewLine)
End If

Close #iFileNum
'Return success
FileLoadToArray = ""

Exit Function

ErrFailed:
Debug.Assert False
Debug.Print Err.Description
FileLoadToArray = Err.Description
'Close file
If iFileNum Then
Close #iFileNum
End If
End Function

[deankeen]

Ago, If you're able to send me some code when you're not on mobile, I'd really appreciate that! Not exactly sure what to search for to find that.

Kenneth Hobs, apologies for being an amateur with VB, but how would I go about applying that code to my situation? Thanks so much for your time!

[Kenneth Hobs]

What you do with my code depends on what you are trying to do. I would have to know that before I can give advice.

[deankeen]

What you do with my code depends on what you are trying to do. I would have to know that before I can give advice.

I would like to save my Excel file as a CSV without having all the extra quotation marks in it like it has now. I have many product descriptions with this problem and need to remove the quotes before uploading it to my ecommerce system.

[Paul_Hossler]

I would like to save my Excel file as a CSV without having all the extra quotation marks in it like it has now. I have many product descriptions with this problem and need to remove the quotes before uploading it to my ecommerce system.


Basically that what Excel does when it generates a CSV from a worksheet

If a cell contains [Now is the time for, etc. etc.] it will generate a csv file line like [.....,"Now is the time for, etc. etc.", ... ] otherwise the embedded comma will make that look like two fields: [Now is the time for] in one and [etc. etc.] in the next field.

Likewise, if there's a quote in the text string as in 11" (eleven inches), the Excel VBA convention is to double up the quote (ASCII 34) as in [The paper size is 8.5"" by 11"" and is parchment]

I have seen Excel do some other things to 'help' out


How does your ecommerce system handle a field with embedded spaces and commas? What does it use as a field separator? what about unprintable characters (line feed, carriage returns, tabs)?

I had a hard time with your attachment, but it looks like CSS (see screen shot) and in a single cell which is why there is a leading quote in front of the <p> and a trailing quote after the </p> as well as having linefeed and carriage returns

The easiest thing might be to up upload a small workbook with 3-4 typical items, AND what you want the output to be




Excel is randomly putting unwanted quotation marks in some of my cells which is apparently the way Excel saves csv files. ... Excel is putting quotes at the beginning and end of some of my cells as well as double quotes ("") where there should just be single quotes (").


It's not random. Unwanted maybe, not understood maybe, but not random.
Also there is a difference between [a single quote] = one ASCII 34 and a [single quote] = ASCII 39 ... " or '

I assuming from your sample that you wanted [1/8"] instead of [1/8""] that is one double quote instead of the two double quotes


Paul

[Ago]

Ago, If you're able to send me some code when you're not on mobile, I'd really appreciate that! Not exactly sure what to search for to find that.

Kenneth Hobs, apologies for being an amateur with VB, but how would I go about applying that code to my situation? Thanks so much for your time!

I couldn't open the attached file for some reason but I made a code that generates a csv-file with column A and B.
And if you need more you can just add that to the code.



Sub CreateCSVFile()
Dim fnum As Integer
Dim MyFile As String


MyFile = "Filename.csv" 'filename of csv-file
fnum = FreeFile()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
i = 1 'Startrow

Open MyFile For Output As #fnum
While i <= LastRow

Print #fnum, Range("A" & i).Value & "," & Range("B" & i).Value
i = i + 1
Wend

Close #fnum

End Sub

[deankeen]

Ago, thanks for your reply... But unfortunately I originally had data in just the first column or two but your script is dividing up my data. Is that supposed to happen?
11464

Paul_Hossler, you have my situation exactly correct. I'm using Woocommerce and their Product CSV Import Suite to upload my data. I have HTML in my product descriptions which is necessary because my data is quite different for each product so I use Excel to add in HTML paragraph tags, bold tags, etc.

Do you know of some way to fix the issue? I don't know for sure that Woocommerce will see all of the unwanted quotes, but I assume it will. I'll try what you suggested and upload a small CSV with the unwanted quotes and see how Woocommerce deals with it. Would you recommend using a different program instead of Excel that might handle these special characters differently?

[deankeen]

Wow, I feel like a real fool...I tried uploading the csv to my system and it didn't have the extra quotation marks! So sorry to waste everyone's time...I foolishly assumed Woocommerce would see the unwanted quotes, but it did not. Case closed.

Thanks everyone!

[Kenneth Hobs]

Glad all is well now. I suspected that might be the case which is why I asked how you were using it. For the CSV format, see this for some information why they need quote delimitation sometimes. http://en.wikipedia.org/wiki/Comma-separated_values

Excel CSV files are really CSV files but give you a bit more power when opened in Excel.

I usually prefer "quote-tab" delimited files when I am working with import/export to various applications when standard database formats are not feasible.

[Ago]

Ago, thanks for your reply... But unfortunately I originally had data in just the first column or two but your script is dividing up my data. Is that supposed to happen?
11464




I couldn't even open the file you attached, probably a site-filetype-webbrowser thing.
But I assumed the csv should be seperated with commas, as csv is normally comma seperated. Hence the name: Comma Seperated Values.

Good you found a solution..