Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
convert comma separated values (CSV) to tab-separated values (TSV)
while preserving commas in qualified strings
<cffunction name="CSVtoTSV" output="no">
<cfargument name="csv" required="yes" type="string">
<cfset csv = replace(csv, ',', ', ', 'all')> <!--- make sure to trim() values when you use them later --->
<!--- find qualified strings --->
<cfset qualified = reMatch('[\b ,](".*?")',csv)>
<!--- /find qualified strings --->
<cfdump var="#csv#">
<cfloop array="#qualified#" index="x">
<cfset originalPhrase = x>
<cfset phrase = x>
<cfif mid(phrase,1,1) eq ','>
<cfset phrase = right(phrase,len(phrase)-1)>
<!--- replace the commas in qualified values with chr(30) --->
<cfset phrase = replaceNoCase(phrase,',',chr(30),'all')>
<!--- /replace the commas in qualified values with chr(30) --->
<cfset phrase = replaceNoCase(phrase,'"','','all')>
Original: #originalPhrase#<br>
Phrase: #phrase#<br>
<cfif mid(originalPhrase,1,1) eq ','>
<cfset phrase = ",#phrase#">
<cfset csv = replace(csv,originalPhrase,phrase,'all')>
<!--- convert all the commas to tabs --->
<cfset csv = replace(csv,',',chr(9),'all')>
<!--- /convert all the commas to tabs --->
<!--- convert the chr(30)'s back into commas --->
<cfset csv = replace(csv,chr(30),',','all')>
<!--- /convert the chr(30)'s back into commas --->
<cfdump var="#csv#" label="new">
<cfreturn csv>
Copy link

lesirvin commented Jan 12, 2019

Thanks for this. Does this preserve a number of empty fields in a row? Ex... if I have:
will the empty fields convert to the similar number of tabs?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment