AutoHotkey Homepage AutoHotkey Community
Let's help each other out
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

[Function] Convert String for CSV - Format4CSV() beta 1

 
Reply to topic    AutoHotkey Community Forum Index -> Scripts & Functions
View previous topic :: View next topic  
Author Message
Rhys



Joined: 17 Apr 2007
Posts: 761
Location: Florida
PostPosted: Fri Jan 04, 2008 4:47 pm    Post subject: [Function] Convert String for CSV - Format4CSV() beta 1 Reply with quote
Credits: CSV rules pulled from Creativyst Software

Usage: Converts a string for use in a single field of a CSV file.
Will handle strings with: commas, double quotes, linefeeds*
Issues:Multi-line fields do not display properly in Excel (box character at each end of line).
*While creating a CSV with fields that contain linefeeds will work, there may not be an easy way (using AHK) to read a CSV file with this structure. I do not use mutli-line values but wanted to include this ability.


v Beta 1 - 20080414:
Code:
TheString=
(Join|
Smith, John
Auto"Hot"Key
Hogan, "Hulk"
This "double" quote
Plain old string
$1,000,000
Multi`nLine`nField
)
Loop, Parse, TheString, |
{
   FormattedString:=Format4CSV(A_LoopField)
   Results.=A_LoopField . " -> " . FormattedString . "`n"
}
MsgBox,,Results,%Results%
Return

Format4CSV(F4C_String)
{
   Reformat:=False ;Assume String is OK
   IfInString, F4C_String,`n ;Check for linefeeds
      Reformat:=True ;String must be bracketed by double quotes
   IfInString, F4C_String,`r ;Check for linefeeds
      Reformat:=True
   IfInString, F4C_String,`, ;Check for commas
      Reformat:=True
   IfInString, F4C_String, `" ;Check for double quotes
   {   Reformat:=True
      StringReplace, F4C_String, F4C_String, `",`"`", All ;The original double quotes need to be double double quotes
   }
   If (Reformat)
      F4C_String=`"%F4C_String%`" ;If needed, bracket the string in double quotes
   Return, F4C_String
}

v Alpha 1 - 20080104:
Code:
Go:
Reformat:=False  ;Assume we don't need to reformat
InputBox, SearchTerm, CSV Field Formatter v alpha 1, It places the text into the field...
If Errorlevel
   Reload ;For easy, live updating of script
SearchTerm_append:=SearchTerm
IfInString, SearchTerm_append,`, ;Check for commas
   Reformat:=True ;String must be bracketed by double quotes
IfInString, SearchTerm_append, `" ;Check for double quotes
{   Reformat:=True ;String must be bracketed by double quotes
   StringReplace, SearchTerm_append, SearchTerm_append, `",`"`", All ;And the original double quotes need to be double double quotes
}
If Reformat
   SearchTerm_append=`"%SearchTerm_append%`" ;If needed, bracket the string in double quotes
MsgBox, %SearchTerm%`nWas reformatted to`n%SearchTerm_append%
GoSub, Go

_________________
[Join IRC!]
Back to top
View user's profile Send private message
Rhys



Joined: 17 Apr 2007
Posts: 761
Location: Florida
PostPosted: Mon Apr 14, 2008 2:41 pm    Post subject: Reply with quote
Bump for beta 1.
_________________
[Join IRC!]
Back to top
View user's profile Send private message
berban_



Joined: 16 Mar 2011
Posts: 150
Location: Worcester, Massachusetts
PostPosted: Fri Sep 30, 2011 8:47 pm    Post subject: Reply with quote
Hey! I love CSV and found this while looking for AutoHotkey stuff related to CSV Smile

Not to, uhhhh.... be that guy.... but this can be done a bit shorter. (I know this is 3½ years old so you probably already know/don't care.) Anyway, here's my function for doing this:
Code:
CSV(Text)
{
   If (SubStr(Text, 1, 1) = """") or InStr(Text, ",") or InStr(Text, "`n") or InStr(Text, "`r") {
      StringReplace, Text, Text, ", "", All
      Text := """" Text """"
   }
   Return Text
}

I think it's essentially the same as yours. Although there is one difference that I can see: see how I do (SubStr(Text, 1, 1) = """") instead of just InStr(Text, """") ? That's because, from my testing, I've found that only if the first character is a double quote is the value considered literal. For instance, cell 2 below contains a literal ":
Code:
Cell 1, "Cell 2


And another note (one which I am disappointed in!): It seems that AutoHotkey does not support newlines as value separators! (By "doesn't support" I mean not supported by Loop, Parse, String, CSV) For instance, if you create a test.csv file like the one shown below:
Code:
ab,cd
ef,gh
ij,kl
...and then run this code:
Code:
FileRead, string, test.csv
Loop, Parse, string, CSV
   MsgBox, %A_LoopField%
...it will display "ab" then "cd`nef" then "gh`nij" then "kl". No good!

I'm working on a solution to this but it's not quite done
_________________
★★★ Email me at berban at aim full stop com ★★★
Back to top
View user's profile Send private message Visit poster's website
[VxE]



Joined: 07 Oct 2006
Posts: 3254
Location: Simi Valley, CA
PostPosted: Sat Oct 01, 2011 1:45 am    Post subject: Reply with quote
In CSV, non-literal newlines delimit rows, not cells, that's why Loop, Parse, CSV doesn't do anything special with newlines.

If you haven't already seen it, my table manipulation library has functions to convert CSV to/from TSV.
_________________
Ternary (a ? b : c) guide     TSV Table Manipulation Library
Post code inside [code][/code] tags!
Back to top
View user's profile Send private message
berban_



Joined: 16 Mar 2011
Posts: 150
Location: Worcester, Massachusetts
PostPosted: Sat Oct 01, 2011 3:50 am    Post subject: Reply with quote
Ah, thanks for the tip Smile

Your lib looks great! An incredible variety of functions! Shocked

What exactly are you recommending I do: just use the tsv format instead; or read csv files and convert them to tsv and use your functions on them; or read a tsv and convert it to csv for better compatibility with Loop, Parse, CSV...?

Oh and by the way, personally, I like to put the url of the forum post in my code downloads, since that's where a lot of documentation is... which I don't see in your table lib. Just a thought
_________________
★★★ Email me at berban at aim full stop com ★★★
Back to top
View user's profile Send private message Visit poster's website
[VxE]



Joined: 07 Oct 2006
Posts: 3254
Location: Simi Valley, CA
PostPosted: Sat Oct 01, 2011 10:13 am    Post subject: Reply with quote
berban_ wrote:
Your lib looks great! An incredible variety of functions! Shocked
Glad to hear it! I guess I should underscore that the functions are standalone, which means you can cherry-pick the ones you need instead of including the whole 100K+ lib.

I don't know what task you wish to accomplish, only that it probably involves tabular data. So, I will only recommend that you review available resources for handling such data and determine which, if any, meet your requirements. I think it's very reasonable to store data tables in CSV files and have a script convert them to TSV for processing.

I admit I'm a slouch in the documentation department, but I have added the url to the forum topic to the library file Wink
_________________
Ternary (a ? b : c) guide     TSV Table Manipulation Library
Post code inside [code][/code] tags!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    AutoHotkey Community Forum Index -> Scripts & Functions All times are GMT
Page 1 of 1

 
Jump to:  
You can post new topics in this forum
You can reply to topics in this forum


Powered by phpBB © 2001, 2005 phpBB Group