Re: Removing new line from CSV?
On Wed, Sep 15, 2010 at 9:29 AM, Rick Root wrote:
Unfortunately, using your code (except using createObject instead of
javaloader, as I have the library in CF's classpath), I get the same
error.
Here's a function that uses opencsv http://opencsv.sourceforge.net/ 2.1,
another CSV http://en.wikipedia.org/wiki/Comma-separated_values parsing
deal (looks like there's a newer version, too!). Works fer me on Railo.
Nasty CSV file in, clean CSV file out.
Don't get me started on poorly
formattedhttp://en.wikipedia.org/wiki/Escape_characterCSV files.
cffunction name=cleanCsvFile output=false
cfargument name=csvFileIn required=true /
cfargument name=csvFileOut required=true /
cfargument name=separator default= /
cfargument name=quotechar default= /
cfargument name=escapechar default= /
cfargument name=lineseparator default= /
cfscript
var fileReader =
createObject(java,java.io.FileReader).init(arguments.csvFileIn);
var reader =
createObject(java,au.com.bytecode.opencsv.CSVReader);
var myEntries = ;
var fileWriter =
createObject(java,java.io.FileWriter).init(arguments.csvFileOut);
var writer =
createObject(java,au.com.bytecode.opencsv.CSVWriter);
var csvstr =
if(separator eq ) {
separator = writer.DEFAULT_SEPARATOR;
}
if(quotechar eq ) {
quotechar = writer.DEFAULT_QUOTE_CHARACTER;
}
if(escapechar eq ) {
escapechar = writer.DEFAULT_ESCAPE_CHARACTER;
}
if(lineseparator eq ) {
lineseparator = writer.DEFAULT_LINE_END;
}
writer.init(fileWriter,
javacast(char,separator),javacast(char,quotechar),javacast(char,escapechar),
lineseparator);
if(quotechar NEQ ) {
reader =
reader.init(fileReader,javacast(char,separator),javacast(char,quotechar),true);
} else {
reader = reader.init(fileReader,javacast(char,separator));
}
myEntries = reader.readAll();
writer.writeAll(myEntries);
writer.flush();
fileReader.close();
fileWriter.close();
reader.close();
writer.close();
/cfscript
cfreturn true /
/cffunction
--
In most things success depends on knowing how long it takes to succeed.
Charles de Montesquieu
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337104
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV? [spamtrap bayes][spamtrap heur]
On Thu, Sep 16, 2010 at 12:04 AM, Paul Hastings p...@sustainablegis.com wrote: On 9/16/2010 2:45 AM, Rick Root wrote: http://www.opensourcecf.com/1/2010/09/Example-of-using-JavaCSV-CSVReader-class-to-read-CSV-files.cfm pretty sure the numeric columns for csvReader are 0 based (ie 1st column is referenced as 0 not 1). yes, i'm too lazy to register on your blog. I like it that way ;) You're right, this line: cfset val = fileInput.get(javacast(int,col)) should be cfset val = fileInput.get(javacast(int,col-1)) Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337106 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV?
Use the JavaCSV library to read and writing CSV files with coldfusion. You'll NEVER have to worry about parsing CSV files again =) This java library handles all kinds of things like this. Dealing with CSV files in coldfusion can be a real pain in the ass once you start dealing with files where fields contain carriage returns or commas. I don't have any examples of reading a CSV but here's some code for writing one. http://www.opensourcecf.com/1/2008/06/JavaCSV-for-creating-large-CSV-and-other-delmiited-files-with-Coldfusion.cfm Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337056 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV?
BTW in my own interest, having never actually used the CsvReader class (only the CsvWriter class), I started to attempt to write some sample code... Unfortunately, this is as far as I got :) cfset filename = test.csv cfset fileInput = createObject(java,com.csvreader.CsvReader) cfset fileInput.init(filename) cfset fileInput.close() The third line errors with the following error: An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Which I don't get because the CsvReader class is neither an abstract class nor an interface. Oh well, I tried =) Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337064 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV?
If I recall correctly, I experienced problems when using just a file name as well. But was successful by using constructor using the filename, delimeter, and charset. I hope you can see what changes you're need from the code below which works within the context of my app but doesn't work on its own. cffunction name=getReader output=false access=public returntype=any hint=Returns the reader object cfargument name=fileName required=true type=string hint=The full path to the filename cfargument name=delimiter required=true type=string hint=The delimiter cfargument name=charset required=false type=string default=Cp1252 hint=The text encoding. Default is Cp1252. The same default for coldfusion. !--- Create reader --- cfset var csvreader = controller.getPlugin(JavaLoader).create(com.csvreader.CsvReader) !--- Create a charset object to pass to the function --- cfset var charsetObject = createObject(java,java.nio.charset.Charset).forName(arguments.charset) !--- Readobject --- cfset var readObject = csvreader.init(arguments.fileName,arguments.delimiter,charsetObject) cfreturn readObject /cffunction - Gabriel On Wed, Sep 15, 2010 at 10:33 AM, Rick Root rick.r...@gmail.com wrote: BTW in my own interest, having never actually used the CsvReader class (only the CsvWriter class), I started to attempt to write some sample code... Unfortunately, this is as far as I got :) cfset filename = test.csv cfset fileInput = createObject(java,com.csvreader.CsvReader) cfset fileInput.init(filename) cfset fileInput.close() The third line errors with the following error: An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Which I don't get because the CsvReader class is neither an abstract class nor an interface. Oh well, I tried =) Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337067 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV?
Unfortunately, using your code (except using createObject instead of javaloader, as I have the library in CF's classpath), I get the same error. On Wed, Sep 15, 2010 at 10:52 AM, Dorioo dor...@gmail.com wrote: If I recall correctly, I experienced problems when using just a file name as well. But was successful by using constructor using the filename, delimeter, and charset. I hope you can see what changes you're need from the code below which works within the context of my app but doesn't work on its own. cffunction name=getReader output=false access=public returntype=any hint=Returns the reader object cfargument name=fileName required=true type=string hint=The full path to the filename cfargument name=delimiter required=true type=string hint=The delimiter cfargument name=charset required=false type=string default=Cp1252 hint=The text encoding. Default is Cp1252. The same default for coldfusion. !--- Create reader --- cfset var csvreader = controller.getPlugin(JavaLoader).create(com.csvreader.CsvReader) !--- Create a charset object to pass to the function --- cfset var charsetObject = createObject(java,java.nio.charset.Charset).forName(arguments.charset) !--- Readobject --- cfset var readObject = csvreader.init(arguments.fileName,arguments.delimiter,charsetObject) cfreturn readObject /cffunction - Gabriel On Wed, Sep 15, 2010 at 10:33 AM, Rick Root rick.r...@gmail.com wrote: BTW in my own interest, having never actually used the CsvReader class (only the CsvWriter class), I started to attempt to write some sample code... Unfortunately, this is as far as I got :) cfset filename = test.csv cfset fileInput = createObject(java,com.csvreader.CsvReader) cfset fileInput.init(filename) cfset fileInput.close() The third line errors with the following error: An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. Which I don't get because the CsvReader class is neither an abstract class nor an interface. Oh well, I tried =) Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337073 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV? [spamtrap bayes][spamtrap heur]
On 9/15/2010 9:33 PM, Rick Root wrote: The third line errors with the following error: An exception occurred when instantiating a Java object. The class must not be an interface or an abstract class. out of curiosity i gave that lib a spin, very nice. your error looks like a poorly worded file not found message. try full path to CSV file. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337083 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV? [spamtrap bayes][spamtrap heur]
On Wed, Sep 15, 2010 at 1:01 PM, Paul Hastings p...@sustainablegis.com wrote: your error looks like a poorly worded file not found message. try full path to CSV file. Yeah, that was it. And yes, VERY poorly worded =) Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337093 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV? [spamtrap bayes][spamtrap heur]
Okay, I wrote some sample code and tested it for using JavaCSV to read a CSV file. http://www.opensourcecf.com/1/2010/09/Example-of-using-JavaCSV-CSVReader-class-to-read-CSV-files.cfm My test file didn't have headers so this just generically dumps the csv into a very generic query. Rick ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337095 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV? [spamtrap bayes][spamtrap heur]
On 9/16/2010 2:45 AM, Rick Root wrote: http://www.opensourcecf.com/1/2010/09/Example-of-using-JavaCSV-CSVReader-class-to-read-CSV-files.cfm pretty sure the numeric columns for csvReader are 0 based (ie 1st column is referenced as 0 not 1). yes, i'm too lazy to register on your blog. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337103 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV?
In Windows a line feed is actually #chr(10)##chr(13)#. However, if you remove those you will probably remove ALL line feeds in your file instead of just the double line feed that is causing you problems. This can be tricky, but you will probably want to look for #chr(10)##chr(13)##chr(10)##chr(13)#, or something like that. Line feeds can be a real pain in the butt because different software will give you different line feed combinations. Sent with my Android phone On Sep 14, 2010 11:19 AM, Phillip Vector vec...@mostdeadlygame.com wrote: I have the following data... bb0a0933-85d4-44be-a72c-25aa759c603f,3782049,DirectMail,63933-8188,The form ProgramForms has been rejected because of the following reason: bb0a0933-85d4-44be-a72c-25aa759c603f,3782049,DirectMail,63933-8188,The form (Application Form) has been rejected because of the following reason: 0002 -Missing proof of program participation 1023 -SSN not provided 70d4eeca-047d-42e5-8648-25abe1579c96,3784291,DirectMail,07055-2931, The issue I have is that on the second record (ID: bb0a0933-85d4-44be-a72c-25aa759c603f), they use 2 lines when there is more then one reason why the app was rejected. I suspect that they are using CTRL-Return or some such to put it in. The question is.. How do I remove it and make it one line again with a cfreplace command? I use commands like cfset newcsvfile = replace(newcsvfile, ',,', ',0,', ALL) to basically scrub the data. So how would I do it for a situation like this? When I try cfset newcsvfile = replace(csvfile, '#chr(13)#', '', ALL) It doesn't remove it.. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337003 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV?
Try Chr(10) also like: cfset newcsvfile = replace(csvfile, '#chr(10)#', '', ALL) I just had to do that with a message digest that wasn't being verified correctly, Phil On Tue, Sep 14, 2010 at 1:17 PM, Phillip Vector vec...@mostdeadlygame.comwrote: I have the following data... bb0a0933-85d4-44be-a72c-25aa759c603f,3782049,DirectMail,63933-8188,The form ProgramForms has been rejected because of the following reason: bb0a0933-85d4-44be-a72c-25aa759c603f,3782049,DirectMail,63933-8188,The form (Application Form) has been rejected because of the following reason: 0002 -Missing proof of program participation 1023 -SSN not provided 70d4eeca-047d-42e5-8648-25abe1579c96,3784291,DirectMail,07055-2931, The issue I have is that on the second record (ID: bb0a0933-85d4-44be-a72c-25aa759c603f), they use 2 lines when there is more then one reason why the app was rejected. I suspect that they are using CTRL-Return or some such to put it in. The question is.. How do I remove it and make it one line again with a cfreplace command? I use commands like cfset newcsvfile = replace(newcsvfile, ',,', ',0,', ALL) to basically scrub the data. So how would I do it for a situation like this? When I try cfset newcsvfile = replace(csvfile, '#chr(13)#', '', ALL) It doesn't remove it.. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337004 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Removing new line from CSV?
No need for the extraneous pound signs. cfset newcsvfile = replace(csvfile, chr(10), '', ALL) On Tue, Sep 14, 2010 at 1:26 PM, Phillip Duba phild...@gmail.com wrote: Try Chr(10) also like: cfset newcsvfile = replace(csvfile, '#chr(10)#', '', ALL) I just had to do that with a message digest that wasn't being verified correctly, Phil On Tue, Sep 14, 2010 at 1:17 PM, Phillip Vector vec...@mostdeadlygame.comwrote: I have the following data... bb0a0933-85d4-44be-a72c-25aa759c603f,3782049,DirectMail,63933-8188,The form ProgramForms has been rejected because of the following reason: bb0a0933-85d4-44be-a72c-25aa759c603f,3782049,DirectMail,63933-8188,The form (Application Form) has been rejected because of the following reason: 0002 -Missing proof of program participation 1023 -SSN not provided 70d4eeca-047d-42e5-8648-25abe1579c96,3784291,DirectMail,07055-2931, The issue I have is that on the second record (ID: bb0a0933-85d4-44be-a72c-25aa759c603f), they use 2 lines when there is more then one reason why the app was rejected. I suspect that they are using CTRL-Return or some such to put it in. The question is.. How do I remove it and make it one line again with a cfreplace command? I use commands like cfset newcsvfile = replace(newcsvfile, ',,', ',0,', ALL) to basically scrub the data. So how would I do it for a situation like this? When I try cfset newcsvfile = replace(csvfile, '#chr(13)#', '', ALL) It doesn't remove it.. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:337005 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
