tag:blogger.com,1999:blog-2191510691414981665.post3129147660899148760..comments2011-01-22T14:53:14.168-05:00Dan McGhanhttps://profiles.google.com/116126534075611873104noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-2191510691414981665.post-88159476389693471482009-08-05T10:59:17.140-04:002009-08-05T10:59:17.140-04:00Great! I am sometimes getting an error where the enclosing character at the end of a line is included at the end of the last value....<br /><br />I was trying to get my head around your code but it gets a bit complex at this point! for now maybe I will just deal with this when I select from csv_temp...but if you&#39;ve fixed this...or anything else...I would be very interested. I am using the code and it&#39;s been a great start! :)Stirlhttp://www.blogger.com/profile/09485916734322753245noreply@blogger.comtag:blogger.com,1999:blog-2191510691414981665.post-26931048446545875612009-07-14T09:17:08.461-04:002009-07-14T09:17:08.461-04:00Stirl,<br /><br />I have some updated code for this. I&#39;ll try to post it this weekend.<br /><br />Regards,<br />Dandmcghanhttp://www.blogger.com/profile/13971112000913341546noreply@blogger.comtag:blogger.com,1999:blog-2191510691414981665.post-66293884017864116672009-07-14T02:19:34.950-04:002009-07-14T02:19:34.950-04:00might use this myself!Stirlhttp://www.blogger.com/profile/09485916734322753245noreply@blogger.comtag:blogger.com,1999:blog-2191510691414981665.post-61077427171002506102009-04-24T07:44:00.000-04:002009-04-24T07:44:00.000-04:00Hello,<br /><br />I like your solution of loading data, but still got error ORA-06502: PL/SQL: numeric or value error when have some national characters (czech) in the file. Do you have an ideas what should I do to correct this issue pls?<br /><br />thanks in advance<br />Robert, CZrobertnoreply@blogger.comtag:blogger.com,1999:blog-2191510691414981665.post-37667292786798256882009-03-29T22:00:00.000-04:002009-03-29T22:00:00.000-04:00Dan, I built a similar system early 2007 using external tables, but built the external tables dynamically as I had to handle more than 50 different formats. I had created a' data dictionary' so the admins will be able to define their own structures. Also created was a validation engine that would validate the attributes of a file definition based. For example you could have a file made up of 6 cols, each could be of a different type, size, mandatory, etc. The results of the failure was written to another error structure I had defined. Some of the files has over 150 cols. All of these were in packages and an APEX frontend tied them together. I allowed up to 250 cols and even had an edit screen with dynamic labels (had to work around the 100 col limitation) for simple edits that could be made and the file run through the validation engine. It was a very successful implementation. Of course, I wanted to add tons more features but had to dive into a different project (where I used part of what I built in 07).I have been meaning to package that for APEX community but just could not find time. Hopefully I will be able to find some time to package this and share.<BR/><BR/>Good luck<BR/>NNeelesh Shahhttp://www.blogger.com/profile/00197008550447011993noreply@blogger.comtag:blogger.com,1999:blog-2191510691414981665.post-3949639728236308442009-02-05T00:20:00.000-05:002009-02-05T00:20:00.000-05:00WTF is up with those commas? Didn't I say that was a no-no? ;)<BR/><BR/>External tables are a fine suggestion, however, you can't assume the end user has access to the server. What if this is part of an app that you've deployed to the business? Can't very well give them access the the database filesystem can you?<BR/><BR/>And by "we gave a presentation" you mean I made you do the whole thing? Or do you mean you ran well over the allotted time not allowing me to participate? ;)<BR/><BR/>For those of you that don't know, Dan was the biggest PITA at my very first presenation. A good one though. He very easily could have been a jerk yet he wasn't. He knew far more than I did about some of the internals of APEX (what is this, a LinkedIn recommendation?) and I think we really hit it off. If only I could get him to stop putting commas at the beginning of lines where they don't belong!oraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-2191510691414981665.post-36836338724931676762009-02-04T14:45:00.000-05:002009-02-04T14:45:00.000-05:00Sujay,<BR/><BR/>That's an interesting suggestion I hadn't considered. Funny because I'm very familiar with external tables... :(<BR/><BR/>I wonder how flexible it would be though... For example, say you wanted to change the delimiter or enclosure character. That may require dynamic SQL to create the external table definition. <BR/><BR/>It's worth exploring though. Thanks!<BR/><BR/>Regards,<BR/>Dandmcghanhttp://www.blogger.com/profile/13971112000913341546noreply@blogger.comtag:blogger.com,1999:blog-2191510691414981665.post-41200386971222778292009-02-04T14:12:00.000-05:002009-02-04T14:12:00.000-05:00Dan,<BR/><BR/>This seems too complicated, you can always use an External table to load csv data. It consists of just 2-3 steps, setting up a directory, granting necessary privileges and then creating the external table mapped to a file. <BR/><BR/>Check this link for a quick reference:<BR/><BR/><A HREF="www.psoug.org/reference/externaltab.html" REL="nofollow">www.psoug.org/reference/externaltab.htm</A><BR/><BR/>Not sure if external tables are available in Apex or not, haven't thought about the mechanism to integrate with Apex yet, but wanted to point this out.<BR/><BR/>Thanks<BR/>SujaySujay Dutta, PMPhttp://www.blogger.com/profile/02851895474849752728noreply@blogger.comtag:blogger.com,1999:blog-2191510691414981665.post-7034003110451484292009-02-04T11:09:00.000-05:002009-02-04T11:09:00.000-05:00Anonymous,<BR/><BR/>Could you please send me a file that you're having problems with? Chances are it's something small that could be easily fixed.<BR/><BR/>Regards,<BR/>Dandmcghanhttp://www.blogger.com/profile/13971112000913341546noreply@blogger.comtag:blogger.com,1999:blog-2191510691414981665.post-32973508486464911462009-02-04T11:03:00.000-05:002009-02-04T11:03:00.000-05:00I tried it with a number of csv files and it was hit or miss, as you stated this is NOT production ode, but a good starting point..Anonymousnoreply@blogger.com