parse a delimited file by quotes and tabs

[ChrisBair]

I need to parse a delimited file into a 2D array so I can pull stuff out of it and do fun things (Salesforce.com Knowledge article translation import/export). I'm running into a problem that all of the examples I've found here in the forum generally will split on a tab and do a new row on a CR or LF but this particular data set has some funky messed up data. The first line has the header, the subsequent lines start with the primary key that is not in quotes and then the rest of the fields have a @TAB between them and quotes surrounding them.  That might be manageable but there is one field that has line feeds in it (Current_Change_Notes__c), though none of the notes appear to have a quote in it, I can't rule out that someone won't put one in there. I would assume that splitting on "@TAB" (with actual quotes as part of the delimiting) might workexcept for the first field that doesn't end with a quote (plus there would be a trailing quote left over for the last field. 

It's also fun that even though it's actually tab delimited with quotes, the files are names .csv 

I looked into  but couldn't see how to adapt it to this.

[kylomas]

Hi Chris,

You might be further ahead posting a representive example of your input and your expected output.

kylomas

[DicatoroftheUSA]

Upload a sample of your data's problem area. Replace text with garbage if confidential.

Also check out this tool. And read the helpfile on how to use regex.

Edited February 18, 2014 by DicatoroftheUSA

[BrewManNH]

'?do=embed' frameborder='0' data-embedContent>>

[czardas]

You could also try this: '?do=embed' frameborder='0' data-embedContent>>

;

#include 'CSVSplit.au3' ; see link above

Local $sFilePath = @ScriptDir & "\test.csv" ; Change this to you own file path

Local $hFile = FileOpen($sFilePath)
If $hFile = -1 Then
    MsgBox(0, "", "Unable to open file")
    Exit
EndIf

Local $sString = FileRead($hFile)
If @error Then
    MsgBox(0, "", "Unable to read file")
    FileClose($hFile)
    Exit
EndIf
FileClose($hFile)

Local $aTSV = _CSVSplit($sString, @TAB) ; Parse TAB Separated Values (TSV)
_ArrayDisplay($aTSV)

;

When you say quotes, do you mean double quotes? - that would make more sense to me. If you mean single quotes, then the above example will not work. Make sure you put the CSVSplit.au3 (from the link) in the same folder as the above script, and change the file path in the code to your own file path.

Edited February 19, 2014 by czardas

[ChrisBair]

That's odd, I uploaded the input data, not sure what happened to it. OK, here it is in dropbox: https://dl.dropboxusercontent.com/u/6943630/Answer__kav.csv

[ChrisBair]

You could also try this: '?do=embed' frameborder='0' data-embedContent>>

;

When you say quotes, do you mean double quotes? - that would make more sense to me. If you mean single quotes, then the above example will not work. Make sure you put the CSVSplit.au3 (from the link) in the same folder as the above script, and change the file path in the code to your own file path.

 

czardas - that worked perfectly! Thanks to everyone that looked at this.

[czardas]

I'm happy it worked. You're the first person to test this on a TSV file from out in the wild. I'm curious which software created it. Good luck with your project/s.

Edited February 19, 2014 by czardas

[ChrisBair]

When you export stuff from Salesforce this is the format it creates it in. This one happens to be knowledge base articles. Here's the next project    '?do=embed' frameborder='0' data-embedContent>>

[ChrisBair]

Also check out this tool. And read the helpfile on how to use regex.

 

That thing is sweet! I already put it to use.

[DicatoroftheUSA]

That thing is sweet! I already put it to use.

Yeah, this site is littered with gems. I use that one almost daily.

Edited February 19, 2014 by DicatoroftheUSA