csv file problem (help!?)
Hi, i have a csv file. Here is an example. (it is taken from messages stored on a phone). 1,status,,[EMAIL PROTECTED],here is a messagemessage, 2001/10/16 (Tue) 19:34,status ,status,,[EMAIL PROTECTED],here is a messagemessage, 2001/10/16 (Tue) 19:34,status here are the csv rules. 1. comma is the primary delimeter 2. doulbe quotes () is the text qualifier (but for some reason it isnt always present for all fields, like date. 3. if there is a doulbe quote in the text it is escaped () 4. line breaks may be in the message, as well as commas I can open this in excel or access just fine, but i am having serious problems writing an intelligent script that will be ok if there are commas or doulbe quotes in a block of text. I tried using CFX_CSVToQuery however it produced errors on the first row and wouldnt continue (no matter what the settings). I need to automate the process for the client and have an upload form. The horrible thing is that i am a week overdue, the whole site interface has been done - i am just stuck on the csv to database part. If i dont get it done the whole project will be in a bit of trouble. anyhelp would be really appreciated. thanks chad ~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: csv file problem (help!?)
i have a csv file. Here is an example. (it is taken from messages stored on a phone). 1,status,,[EMAIL PROTECTED],here is a messagemessage, 2001/10/16 (Tue) 19:34,status ,status,,[EMAIL PROTECTED],here is a messagemessage, 2001/10/16 (Tue) 19:34,status here are the csv rules. 1. comma is the primary delimeter 2. doulbe quotes () is the text qualifier (but for some reason it isnt always present for all fields, like date. 3. if there is a doulbe quote in the text it is escaped () 4. line breaks may be in the message, as well as commas I can open this in excel or access just fine, but i am having serious problems writing an intelligent script that will be ok if there are commas or doulbe quotes in a block of text. I tried using CFX_CSVToQuery however it produced errors on the first row and wouldnt continue (no matter what the settings). I need to automate the process for the client and have an upload form. The horrible thing is that i am a week overdue, the whole site interface has been done - i am just stuck on the csv to database part. If i dont get it done the whole project will be in a bit of trouble. anyhelp would be really appreciated. Have you tried using the Text ODBC driver? It usually opens the files as normal CSVs and you don't have to worry about using CFFile (or CSX_ tags) as you can use it as a datasource Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 Websites for the real world ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: csv file problem (help!?)
Have you tried using the Text ODBC driver?
It usually opens the files as normal CSVs and you don't have to worry
about
using CFFile (or CSX_ tags) as you can use it as a datasource
hi,
i have tried, but i couldnt figure out how to use it.
it has to be all automatic, so i thought about using the connectionstring
attribute in cfquery for dsn-less connections.
if i did something like
cfset constr = Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=c:\somepath\;Extensions=asc,csv,tab,txt;Persist Security
Info=False;
cfquery name=myquery connectionstring=#constr# dbtype=ODBC
but i am not sure how to reference the variables. NO headers, just 7
values.
how could i run a query?
select val1
from textfile
etc etc
thanks
chad
~~
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: csv file problem (help!?)
Chad, i have a csv file. Here is an example. (it is taken from messages stored on a phone). 1,status,,[EMAIL PROTECTED],here is a messagemessage, 2001/10/16 (Tue) 19:34,status ,status,,[EMAIL PROTECTED],here is a messagemessage, 2001/10/16 (Tue) 19:34,status anyhelp would be really appreciated. Can you change the delimiters that are being used to output this file? Having done a substantial amount of work with newspaper content output from Quark pages. I found that about the only way to deal with the unknowns that you'll have in your message text is to change the delimiters to something that you are highly unlikely to find in the text. For me these delimiters eventually became, and have been ever since, | (pipe) for column/field delimiters and ¬ (chr(172)) for text delimiters. Once you have sensible delimiters then the whole task becomes infinitely easier to handle. (400+ news stories plus images processed from Quark pages, inserted into a text retrieval database, additional information added to _every_ story and imported into a website in the space of approximately 1.5hours, a minimum of twice a day :o) ) If you can't change the delimiters, can you get all the other fields surrounded by (double quotes) as well? (Especially as you have text in your date/times and text status fields) If you can do that, then you have a better idea of the column breaks, because you are unlikely to get , in the middle of your message text. Have you tried using the Text ODBC driver? It usually opens the files as normal CSVs and you don't have to worry about using CFFile (or CSX_ tags) as you can use it as a datasource Ewww.. Phil! You sick puppy! ;o) Regards Stephen ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: csv file problem (help!?)
Have you tried using the Text ODBC driver? It usually opens the files as normal CSVs and you don't have to worry about using CFFile (or CSX_ tags) as you can use it as a datasource Ewww.. Phil! You sick puppy! ;o) Hey, I like the CSV drivers for simple things - they make some things really simple :P Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 Websites for the real world ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** -Original Message- From: Stephen Moretti [mailto:[EMAIL PROTECTED]] Sent: 14 November 2001 13:19 To: CF-Talk Subject: Re: csv file problem (help!?) Chad, i have a csv file. Here is an example. (it is taken from messages stored on a phone). 1,status,,[EMAIL PROTECTED],here is a messagemessage, 2001/10/16 (Tue) 19:34,status ,status,,[EMAIL PROTECTED],here is a messagemessage, 2001/10/16 (Tue) 19:34,status anyhelp would be really appreciated. Can you change the delimiters that are being used to output this file? Having done a substantial amount of work with newspaper content output from Quark pages. I found that about the only way to deal with the unknowns that you'll have in your message text is to change the delimiters to something that you are highly unlikely to find in the text. For me these delimiters eventually became, and have been ever since, | (pipe) for column/field delimiters and ¬ (chr(172)) for text delimiters. Once you have sensible delimiters then the whole task becomes infinitely easier to handle. (400+ news stories plus images processed from Quark pages, inserted into a text retrieval database, additional information added to _every_ story and imported into a website in the space of approximately 1.5hours, a minimum of twice a day :o) ) If you can't change the delimiters, can you get all the other fields surrounded by (double quotes) as well? (Especially as you have text in your date/times and text status fields) If you can do that, then you have a better idea of the column breaks, because you are unlikely to get , in the middle of your message text. Regards Stephen ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
