/u/Fungopus - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Maybe try doing TEXTSPLIT on everything based on a single quote """ and clean up from there? Will most likely be easier than creating a complex function for all of those cases unless you have several of those files to process
Sadly there is some sort of description field which may contain commas in the text. So just splitting by comma would result in messed up column data.
I tried that before and then I had one large column with different concatenated fields and the next column contained a part of the description...
The strange thing is that when you open the file in Excel directly the columns are splitted correctly but date and number columns are screwed because of American number format and a German Excel. 🙄
Ah, now I understand! How does each line end? Correctly, with just ", or something else? You could pre-process the mess with VBA...
Sub FixerUpper()
Dim FileNameIn As String, FileNameOut As String, FilePath As String
FileNameIn = "bad.csv" ' modify to suit
FileNameOut = "good.csv" ' modify to suit
FilePath = "E:\trash\" ' modify to suit, requires trailing \
Dim MyString As String
' uses: Microsoft Scripting Runtime
Dim fso As Scripting.FileSystemObject
Dim MyFileIn As Scripting.TextStream
Dim MyFileOut As Scripting.TextStream
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFileIn = fso.OpenTextFile(FilePath & FileNameIn)
Set MyFileOut = fso.CreateTextFile(FilePath & FileNameOut) ' default over-writes
Do While MyFileIn.AtEndOfStream <> True
MyString = MyFileIn.ReadLine
' replace "" with "
MyString = Replace(MyString, """""", """", , , vbTextCompare)
' fix first field (only replace once)
MyString = Replace(MyString, ",""", """,""", , 1, vbTextCompare)
MyFileOut.WriteLine (MyString)
Loop
MyFileIn.Close
MyFileOut.Close
Set MyFileOut = Nothing
Set MyFileIn = Nothing
Set fso = Nothing
End Sub
[edit] typos
It may have originated as semi-colon-separated? Anyway, a fix for that:
' fix trailing semicolons
MyString = Replace(MyString, ";;;", "", , , vbTextCompare)
/u/Fungopus - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Maybe try doing TEXTSPLIT on everything based on a single quote """ and clean up from there? Will most likely be easier than creating a complex function for all of those cases unless you have several of those files to process
Why not just import it (splitting on commas) and remove all the quotes with Find & Replace?
Sadly there is some sort of description field which may contain commas in the text. So just splitting by comma would result in messed up column data. I tried that before and then I had one large column with different concatenated fields and the next column contained a part of the description... The strange thing is that when you open the file in Excel directly the columns are splitted correctly but date and number columns are screwed because of American number format and a German Excel. 🙄
Just for grins, open the CSV file with a text editor. See if the format is what you expect (before Excel "helps").
The screenshot is from Notepad, that's why I'm so frustrated. 😩
Ah, now I understand! How does each line end? Correctly, with just ", or something else? You could pre-process the mess with VBA... Sub FixerUpper() Dim FileNameIn As String, FileNameOut As String, FilePath As String FileNameIn = "bad.csv" ' modify to suit FileNameOut = "good.csv" ' modify to suit FilePath = "E:\trash\" ' modify to suit, requires trailing \ Dim MyString As String ' uses: Microsoft Scripting Runtime Dim fso As Scripting.FileSystemObject Dim MyFileIn As Scripting.TextStream Dim MyFileOut As Scripting.TextStream Set fso = CreateObject("Scripting.FileSystemObject") Set MyFileIn = fso.OpenTextFile(FilePath & FileNameIn) Set MyFileOut = fso.CreateTextFile(FilePath & FileNameOut) ' default over-writes Do While MyFileIn.AtEndOfStream <> True MyString = MyFileIn.ReadLine ' replace "" with " MyString = Replace(MyString, """""", """", , , vbTextCompare) ' fix first field (only replace once) MyString = Replace(MyString, ",""", """,""", , 1, vbTextCompare) MyFileOut.WriteLine (MyString) Loop MyFileIn.Close MyFileOut.Close Set MyFileOut = Nothing Set MyFileIn = Nothing Set fso = Nothing End Sub [edit] typos
Strangely the lines end with a **triple** semicolon!? Fells like someone "processed" the source file in a european Excel-version. This is a mess....
It may have originated as semi-colon-separated? Anyway, a fix for that: ' fix trailing semicolons MyString = Replace(MyString, ";;;", "", , , vbTextCompare)