Jump to content
Sign in to follow this  
ChrisBair

parse a delimited file by quotes and tabs

Recommended Posts

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  :wacko:

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

Share this post


Link to post
Share on other sites
kylomas

Hi Chris,

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

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
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 by DicatoroftheUSA

Share this post


Link to post
Share on other sites
BrewManNH

If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
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 by czardas

Share this post


Link to post
Share on other sites
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.

  • Like 1

Share this post


Link to post
Share on other sites
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 by czardas

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×