RE: Revised Thread - Uploading CSV and Validation
Treat it as two lists. The first using Chr(13) or Chr(10) as a delimiter, the second using ,: The only problem you'll get with that is if the CSV contains a record like the following (which is perfectly valid): 1,Banana,Long and yellow, with a slight bend As CF list functions would see it, there are 4 list items; in CSV terms, there are actually only three. Tim. -- --- Badpen Tech - CF and web-tech: http://tech.badpen.com/ --- RAWNET LTD - independent digital media agency We are big, we are funny and we are clever! New site launched at http://www.rawnet.com/ --- This message may contain information which is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. Such notification notwithstanding, any comments, opinions, information or conclusions expressed in this message are those of the originator, not of rawnet limited, unless otherwise explicitly and independently indicated by an authorised representative of rawnet limited. --- ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189889 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Revised Thread - Uploading CSV and Validation
On Mon, 10 Jan 2005 20:06:12 -0600, Aaron Rouse [EMAIL PROTECTED] wrote: And if using Oracle you could use SQLLDR. I have the need to do this just with CF and been using a CFC I got off this list for uploading it but still have not found the time to really progress with it. On Mon, 10 Jan 2005 16:53:15 -0700, Paul Malan [EMAIL PROTECTED] wrote: If you're using SQL Server, you can create a DTS package then call it from CF. I couldn't believe the difference in speed when I switched to DTS. (If you need to use CF to validate the incoming data, use DTS to populate a temp table, CF to pull it out and insert the validated data into its permanent home. I'd wager still much quicker than using a loop over cffile contents, though I'd be curious to know if you wind up trying both...) On Mon, 10 Jan 2005 17:36:09 -0500, Ken [EMAIL PROTECTED] wrote: Hi. I want to enable the user to upload a csv file, then the data should be validated against pre-defined criteria for different columns in the csv file. Once that is done, I want to write the validated rows from the csv file to the DB. CSVs are much trickier than a lot of people realize to handle in my experience. For example, there are two separate CSV formats -- one the world uses and one Microsoft uses. How do you know which type you're going to get? Secondly, did you know that you can have carriage returns within a given field, and it's perfectly valid? There goes the #Chr(13)##Chr(10)# delimiter check. Lastly, ColdFusion by itself is going to be pretty slow and chew up lots of RAM as you increase the size of the CSV file. You might be able to plan for current requirements (basic validation, small files, etc.), but what about down the road? I just finished a project that needed to take multiple CSV files and do all sorts of validation on the data before inserting into the database. We wound up going with a multi-tiered solution: 1) First upload the file and do a basic file extension check via ColdFusion to make sure it's a CSV file 2) Use the awesome Ostermiller utilities at http://ostermiller.org/utils/CSV.html to actually parse the CSV file, which returns a clean array with the data that you can then use ColdFusion to loop over (i.e., to check file headers to make sure they match what you're expecting). Simply download the jar file from the site I mentioned, drop it in your lib directory, restart ColdFusion, and off you go. Here's the CF syntax needed to use it if you get stuck: cffile action=READ file=#fileNameToRead# variable=fileContents / cfset parser = createObject(java, com.Ostermiller.util.ExcelCSVParser) / cfset parsedFile = parser.parse(fileContents) / cfdump var=#parsedFile# / 3) At this point, you can do your specific data validation. This part I'll leave to you because we bought a third party product that cost us a ton of cash but has been worth every penny (we needed to geocode, do a point-in-polygon analysis, and validate data ranges and stuff on potentially tens of thousands of records at a time, and this product is lightning quick at it...better than ColdFusion could ever be). Hope this helps get you on your way. Regards, Dave. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189909 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Revised Thread - Uploading CSV and Validation
2) Use the awesome Ostermiller utilities at http://ostermiller.org/utils/CSV.html to actually parse the CSV file Hmm, excellent resource. One to bookmark I think! Cheers Dave. Tim. -- --- Badpen Tech - CF and web-tech: http://tech.badpen.com/ --- RAWNET LTD - independent digital media agency We are big, we are funny and we are clever! New site launched at http://www.rawnet.com/ --- This message may contain information which is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. Such notification notwithstanding, any comments, opinions, information or conclusions expressed in this message are those of the originator, not of rawnet limited, unless otherwise explicitly and independently indicated by an authorised representative of rawnet limited. --- ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189911 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Revised Thread - Uploading CSV and Validation
CSVs are much trickier than a lot of people realize to handle in my experience. For example, there are two separate CSV formats -- one the world uses and one Microsoft uses. Actually, there is no standard for CSV. There are just about as many formats as there are implementations. Consequently, CSV is not a particularly good interchange format. It's my understanding that what we commonly refer to as the CSV format was popularized by Excel. Other applications, including those from Microsoft, have used formats similar. In many cases, the format is loosely based on the Excel format. For instance, I don't believe the Excel format allows embedded carriage returns and line feeds. Excel also trims leading zeros and white space. Many other implementations do not. They will also allow carriage returns and line feeds provided the data in question is enclosed in quotes. How do you know which type you're going to get? Secondly, did you know that you can have carriage returns within a given field, and it's perfectly valid? Be careful. By using the word valid you're suggesting that CSV can be validated against something akin to a DTD or that there's some overriding standard. This is not the case. The Excel format is the closest thing to a de facto standard, but it is also one of the most limiting. Consequently, what one application considers valid is not necessarily what another considers valid. You can have two CSV files which are perfectly valid in their own context but which can't be shared with other applications. There are quite a few articles on CSV formatted files and ways to parse them. I found this one in particular to be useful when I was writing a simple CSV parser: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm Ben Rogers http://www.c4.net v.508.240.0051 f.508.240.0057 ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189915 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Revised Thread - Uploading CSV and Validation
Ken wrote: Hi. I want to enable the user to upload a csv file, then the data should be validated against pre-defined criteria for different columns in the csv file. Once that is done, I want to write the validated rows from the csv file to the DB. I would change the process a bit. Don't bother validating the csv, just use whatever native import utility your database has to import the csv into a temp table of the database. Then validate the records in the table and insert them into the permanent table. Jochem ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189924 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Revised Thread - Uploading CSV and Validation
The carriage return within a cell is actually the problem I ran into and never looked back at what I will do to work past it. This requirement is kind of low on my list of things to get done so just keeps moving towards the back burner. The data I have is populated into an Excel spreadsheet and then exported to CSV format from within Excel. Unfortunately this project is hosted in a shared environment and the ability to put anything 3rd party is slim to none in actually happening. If I could just use SQLLDR then it would be a cake walk, but that is not going to be an option. The last time I looked into this I was using java.io.BufferedReader and ran into it not liking those carriage returns, had some suggestions on work arounds and will look into those whenever I get back to this need. -- Aaron Rouse http://www.happyhacker.com/ On Tue, 11 Jan 2005 10:48:11 -0500, Dave Carabetta [EMAIL PROTECTED] wrote: CSVs are much trickier than a lot of people realize to handle in my experience. For example, there are two separate CSV formats -- one the world uses and one Microsoft uses. How do you know which type you're going to get? Secondly, did you know that you can have carriage returns within a given field, and it's perfectly valid? There goes the #Chr(13)##Chr(10)# delimiter check. Lastly, ColdFusion by itself is going to be pretty slow and chew up lots of RAM as you increase the size of the CSV file. You might be able to plan for current requirements (basic validation, small files, etc.), but what about down the road? I just finished a project that needed to take multiple CSV files and do all sorts of validation on the data before inserting into the database. We wound up going with a multi-tiered solution: 1) First upload the file and do a basic file extension check via ColdFusion to make sure it's a CSV file 2) Use the awesome Ostermiller utilities at http://ostermiller.org/utils/CSV.html to actually parse the CSV file, which returns a clean array with the data that you can then use ColdFusion to loop over (i.e., to check file headers to make sure they match what you're expecting). Simply download the jar file from the site I mentioned, drop it in your lib directory, restart ColdFusion, and off you go. Here's the CF syntax needed to use it if you get stuck: cffile action=READ file=#fileNameToRead# variable=fileContents / cfset parser = createObject(java, com.Ostermiller.util.ExcelCSVParser) / cfset parsedFile = parser.parse(fileContents) / cfdump var=#parsedFile# / 3) At this point, you can do your specific data validation. This part I'll leave to you because we bought a third party product that cost us a ton of cash but has been worth every penny (we needed to geocode, do a point-in-polygon analysis, and validate data ranges and stuff on potentially tens of thousands of records at a time, and this product is lightning quick at it...better than ColdFusion could ever be). ~| Protect your mail server with built in anti-virus protection. It's not only good for you, it's good for everybody. http://www.houseoffusion.com/banners/view.cfm?bannerid=39 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189927 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Revised Thread - Uploading CSV and Validation
On Tue, 11 Jan 2005 11:11:27 -0500, Ben Rogers [EMAIL PROTECTED] wrote: CSVs are much trickier than a lot of people realize to handle in my experience. For example, there are two separate CSV formats -- one the world uses and one Microsoft uses. Actually, there is no standard for CSV. There are just about as many formats as there are implementations. Consequently, CSV is not a particularly good interchange format. Which further emphasizes my point that basic ColdFusion solutions of looking for carriage returns and the like is not a robust way to solve this problem. It's my understanding that what we commonly refer to as the CSV format was popularized by Excel. Other applications, including those from Microsoft, have used formats similar. In many cases, the format is loosely based on the Excel format. For instance, I don't believe the Excel format allows embedded carriage returns and line feeds. Excel also trims leading zeros and white space. Many other implementations do not. They will also allow carriage returns and line feeds provided the data in question is enclosed in quotes. How do you know which type you're going to get? Secondly, did you know that you can have carriage returns within a given field, and it's perfectly valid? Be careful. By using the word valid you're suggesting that CSV can be validated against something akin to a DTD or that there's some overriding standard. This is not the case. The Excel format is the closest thing to a de facto standard, but it is also one of the most limiting. Well, I would have said validated if I meant to a DTD or XSD or some such, and I've never even heard of any validation mechanisms for CSV files other than parsing the file itself. Consequently, what one application considers valid is not necessarily what another considers valid. You can have two CSV files which are perfectly valid in their own context but which can't be shared with other applications. Sure, but the same could be said of almost any transport medium. For example, an XML file might be valid on one system, but not necessarily on another if the DTD/XSD it validates against is different. There are quite a few articles on CSV formatted files and ways to parse them. I found this one in particular to be useful when I was writing a simple CSV parser: http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm That's a good one. I'm particularly interested in the CSV to XML converter they posted. Thanks for the link. Regards, Dave. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189930 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Revised Thread - Uploading CSV and Validation
Which further emphasizes my point that basic ColdFusion solutions of looking for carriage returns and the like is not a robust way to solve this problem. I wasn't debating that point. However, it's worth noting that this is entirely dependent on the application which generated the CSV file. If the files are all coming from Excel -- which I believe does not allow embedded carriage returns -- then scanning for carriage returns is perfectly fine. Well, I would have said validated if I meant to a DTD or XSD or some such, and I've never even heard of any validation mechanisms for CSV files other than parsing the file itself. But you did use the word valid. That is a loaded term on a ColdFusion mailing list filled with Web developers. I was merely trying to clarify the statement. Your messages seemed to suggest that there was a right and wrong way to parse a CSV file. I was simply trying to convey that there are as many correct ways to parse CSV files as there are different flavors of CSV because CSV is not a standard. Sure, but the same could be said of almost any transport medium. For example, an XML file might be valid on one system, but not necessarily on another if the DTD/XSD it validates against is different. I think you're conflating the issue (possibly because I mentioned DTDs in the previous message). There is an XML standard. Consequently, I can validate the XML document to ensure that it's properly *formatted*. I can be reasonably sure that, if I create a standards compliant XML document, that other applications will be able to parse it. What they do with it from there is up to them. It's worth noting that, if they can't, then it's probably their fault. In rare cases, it may be an ambiguity in the standard. This is not the case with CSV. The format of a CSV file can be perfectly valid for one application but invalid for another. XML also supports DTDs, which allow you to validate the *structure* of the document (as opposed to the format). If a DTD uses an absolute URI (or is otherwise available on both systems), then the structure of the document can be validated on both systems. Since there is no CSV standard, the format cannot be validated. The closest thing you get to a DTD in a CSV file is when some applications include the column names in the first line of the file. This tells you the number of columns that you can expect. That's a good one. I'm particularly interested in the CSV to XML converter they posted. Thanks for the link. BTW, I agree with the posts that suggest using DTS, SQLLDR and other such apps. They've already solved a good deal of the integration problems. DTS in particular provides a nifty wizard which lets the developer describe the CVS flavor that you have. Ben Rogers http://www.c4.net v.508.240.0051 f.508.240.0057 ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189936 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Revised Thread - Uploading CSV and Validation
On Tue, 11 Jan 2005 12:35:56 -0500, Ben Rogers [EMAIL PROTECTED] wrote: Which further emphasizes my point that basic ColdFusion solutions of looking for carriage returns and the like is not a robust way to solve this problem. I wasn't debating that point. However, it's worth noting that this is entirely dependent on the application which generated the CSV file. If the files are all coming from Excel -- which I believe does not allow embedded carriage returns -- then scanning for carriage returns is perfectly fine. Agreed. For the record, it looks like Excell (I'm using 2003) does indeed allow carriage returns. I just ran a quick test (when in the cell, hit Alt+Enter to start a new line within it) and it saved out with the carriage return. Well, I would have said validated if I meant to a DTD or XSD or some such, and I've never even heard of any validation mechanisms for CSV files other than parsing the file itself. But you did use the word valid. That is a loaded term on a ColdFusion mailing list filled with Web developers. I was merely trying to clarify the statement. Yeah, I guess I can see the confusion. Your messages seemed to suggest that there was a right and wrong way to parse a CSV file. I was simply trying to convey that there are as many correct ways to parse CSV files as there are different flavors of CSV because CSV is not a standard. That wasn't my point at all. I was actually pointing out the opposite -- that there were too many different ways to parse CSV files, so the usually common ColdFusion suggestion of looping over rows and looking for carriage returns won't necessarily suffice. Sure, but the same could be said of almost any transport medium. For example, an XML file might be valid on one system, but not necessarily on another if the DTD/XSD it validates against is different. I think you're conflating the issue (possibly because I mentioned DTDs in the previous message). There is an XML standard. Consequently, I can validate the XML document to ensure that it's properly *formatted*. I can be reasonably sure that, if I create a standards compliant XML document, that other applications will be able to parse it. What they do with it from there is up to them. It's worth noting that, if they can't, then it's probably their fault. In rare cases, it may be an ambiguity in the standard. This is not the case with CSV. The format of a CSV file can be perfectly valid for one application but invalid for another. I'm with you now. XML also supports DTDs, which allow you to validate the *structure* of the document (as opposed to the format). If a DTD uses an absolute URI (or is otherwise available on both systems), then the structure of the document can be validated on both systems. Since there is no CSV standard, the format cannot be validated. The closest thing you get to a DTD in a CSV file is when some applications include the column names in the first line of the file. This tells you the number of columns that you can expect. That's a good one. I'm particularly interested in the CSV to XML converter they posted. Thanks for the link. BTW, I agree with the posts that suggest using DTS, SQLLDR and other such apps. They've already solved a good deal of the integration problems. DTS in particular provides a nifty wizard which lets the developer describe the CVS flavor that you have. I use Oracle here at work, and my experience with SQLLDR is that, while it's an extremely powerful way of bulk loading data, the format of the file needs to be perfect for it to succeed. Hence the suggestion of going through the different steps to verify that you have a validly structured CSV file (with the Ostermiller utilities, etc.), particularly when I have external clients who will mess up their files from time to time. I've not used DTS personally, but by many accounts, it's an invaluable part of the SQL Server product and is a great fit for this sort of work. Regards, Dave. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189937 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Revised Thread - Uploading CSV and Validation
Agreed. For the record, it looks like Excell (I'm using 2003) does indeed allow carriage returns. I just ran a quick test (when in the cell, hit Alt+Enter to start a new line within it) and it saved out with the carriage return. Good to know. Thanks for testing. That wasn't my point at all. I was actually pointing out the opposite -- that there were too many different ways to parse CSV files, so the usually common ColdFusion suggestion of looping over rows and looking for carriage returns won't necessarily suffice. I understood your point and was generally agreeing. I was just trying to clarify because I thought it might be confusing. In other words, I could see how someone would read your message and think that, if they added support for embedded carriage returns and line breaks, they would somehow have a valid CSV parser. I use Oracle here at work, and my experience with SQLLDR is that, while it's an extremely powerful way of bulk loading data, the format of the file needs to be perfect for it to succeed. Hence the suggestion of going through the different steps to verify that you have a validly structured CSV file (with the Ostermiller utilities, etc.), particularly when I have external clients who will mess up their files from time to time. I've not used DTS personally, but by many accounts, it's an invaluable part of the SQL Server product and is a great fit for this sort of work. I have very little experience with Oracle and none (directly) with SQLLDR. I *ass*umed it was similar to DTS. :) DTS, though awkward in many ways, is a pretty incredible piece of technology. I've seen sites that use thousands of DTS packages and a simple VB scheduler to manage all sorts of operations (data migration, work flow, data management, data validation, etc). It surprises me how many of the daily operations at several Fortune 500 companies are handled this way. Ben Rogers http://www.c4.net v.508.240.0051 f.508.240.0057 ~| Sams Teach Yourself Regular Expressions in 10 Minutes by Ben Forta http://www.houseoffusion.com/banners/view.cfm?bannerid=40 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189939 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Revised Thread - Uploading CSV and Validation
I was just providing a method using carriage returns and lists, not the entire code. It's fairly simple to find out if the data delimiters are correctly interpreted, or embedded in the data since the list lengths (fields) will be wrong. Those that don't match the field count can be saved to another list for further analysis and cleanup. Obviously, a direct loading utility into the database is preferable over using CF. 2) Use the awesome Ostermiller utilities at http://ostermiller.org/utils/CSV.html to actually parse the CSV file Hmm, excellent resource. One to bookmark I think! Cheers Dave. Tim. -- --- Badpen Tech - CF and web-tech: http://tech.badpen.com/ --- RAWNET LTD - independent digital media agency We are big, we are funny and we are clever! New site launched at http://www.rawnet.com/ --- This message may contain information which is legally privileged and/or confidential. If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited. Such notification notwithstanding, any comments, opinions, information or conclusions expressed in this message are those of the originator, not of rawnet limited, unless otherwise explicitly and independently indicated by an authorised representative of rawnet limited. --- ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189944 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Revised Thread - Uploading CSV and Validation
Treat it as two lists. The first using Chr(13) or Chr(10) as a delimiter, the second using ,: cfset CSV = CSVFileData cfloop index=idx List=#CSV# delimiters=#chr(13)# cfset Firstcolumn = ListGetAt(idx,1) cfset SecondColumn = ListGetAt(idx,2) Continue for all columns and validate Insert into DB here /cfloop each loop of idx will provide the next row of data to be inserted. Hi. I want to enable the user to upload a csv file, then the data should be validated against pre-defined criteria for different columns in the csv file. Once that is done, I want to write the validated rows from the csv file to the DB. Please help. Thanks, Ken ~| Protect your mail server with built in anti-virus protection. It's not only good for you, it's good for everybody. http://www.houseoffusion.com/banners/view.cfm?bannerid=39 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189859 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Revised Thread - Uploading CSV and Validation
If you're using SQL Server, you can create a DTS package then call it from CF. I couldn't believe the difference in speed when I switched to DTS. (If you need to use CF to validate the incoming data, use DTS to populate a temp table, CF to pull it out and insert the validated data into its permanent home. I'd wager still much quicker than using a loop over cffile contents, though I'd be curious to know if you wind up trying both...) On Mon, 10 Jan 2005 17:36:09 -0500, Ken [EMAIL PROTECTED] wrote: Hi. I want to enable the user to upload a csv file, then the data should be validated against pre-defined criteria for different columns in the csv file. Once that is done, I want to write the validated rows from the csv file to the DB. Please help. Thanks, Ken ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189866 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Revised Thread - Uploading CSV and Validation
And if using Oracle you could use SQLLDR. I have the need to do this just with CF and been using a CFC I got off this list for uploading it but still have not found the time to really progress with it. On Mon, 10 Jan 2005 16:53:15 -0700, Paul Malan [EMAIL PROTECTED] wrote: If you're using SQL Server, you can create a DTS package then call it from CF. I couldn't believe the difference in speed when I switched to DTS. (If you need to use CF to validate the incoming data, use DTS to populate a temp table, CF to pull it out and insert the validated data into its permanent home. I'd wager still much quicker than using a loop over cffile contents, though I'd be curious to know if you wind up trying both...) On Mon, 10 Jan 2005 17:36:09 -0500, Ken [EMAIL PROTECTED] wrote: Hi. I want to enable the user to upload a csv file, then the data should be validated against pre-defined criteria for different columns in the csv file. Once that is done, I want to write the validated rows from the csv file to the DB. Please help. Thanks, Ken ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189881 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
