[cfaussie] Re: Problems Uploading CSV data into SQL table
Are you 1. importing the CSV to SQL Server via ColdFusion? or 2. generating the CSV with ColdFusion then using DTS tools to import the CSV to SQL Server? If 2, then perhaps the problem is with the DTS tools. Apparently when importing excel or csv, it uses the first 8 rows of your file to determine the datatype for each column. Perhaps it's thinking that your first column is numeric data so then it inserts null for all columns that have a character in them?? We had a similar problem with a project, when rows further down in our CSV had large amounts of text, but because the first 8 rows were short, the datatype was determined in advance and our long text got truncated. We ended up creating a first row with dummy data that reflected the column types and sizes we needed, then removed this row after import. It's a messy work-around though. Perhaps you could also try putting quotes around your first column data. ie. 095,2 Bdrm S,Clean House,,WRIGHT x2 095A,Hotel,Svc Daily,18/02/2009,ANGELL x1 Ryan On Feb 18, 10:51 am, SAMARIS Software rai...@ozemail.com.au wrote: The following is an extract from a txt / csv file I import into my sql database 095,2 Bdrm S,Clean House,,WRIGHT x2 095A,Hotel,Svc Daily,18/02/2009,ANGELL x1 096,2 Bdrm S,Dirty Departs,,EVANS x4 096A,Hotel,Svc Daily,18/02/2009,SOUCE x2 097,2 Bdrm S,Dirty Departs,,WENCESLAUS x2 097,2 Bdrm S,Dirty Arrives,,. x2 097A,Hotel,Svc Daily,18/02/2009,BLACKLEY x2 098,2 Bdrm S,Clean Vacant,, 098A,Hotel,Svc Daily,18/02/2009,MOIR x2 099,2 Bdrm S,Clean House,,MAGUIRE x4 100,2 Bdrm S,Clean Vacant,, 101,2 Bdrm S,Clean Arrives,,COOKE x2 the first column is unit number all the data inserts into the table row by row however when there is a letter at the end of the unit number that field remains blank in the database even though its present in the data file if there is a letter at the front of the unit number eg w1255 then the value is recorded any idea what causes the value to record blank when there is a charcter at the end of the unit number but if there is no character in the number or the character is at the beginning it records correctly the field type for unit number in the sql table is nvchar I reassign each unit value using cfset unit_number=#ToString(daily_housekeeping_report.unit)# to force before the insert query for each record being uploaded Regards Claude Raiola B.Econ (Acc), B.Hot.Mngt. Websites:www.AustralianAccommodation.comwww.SAMARIS.NETwww.WebSiteSolutions.com.au Mobile: 0414 228 948 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CF8 bug with CFDOCUMENT
I get the same error as you. I noticed that outputting #structKeyList(arguments)# within cfdocumentitem reveals a single key called attributes which appears to be a blank struct. Seems whatever code is behind cfdocumentitem is operating in a whole other scope. Seems fine for output in the document body though. On Feb 18, 11:45 am, Matthew matthewbchamb...@gmail.com wrote: Someone must be running CF8 and can try the code? Anyone? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: CFFTP Put files to sub directory
You can put the directory name as part of the remotefile attribute of the cfftp tag. eg. remotefile=/pages/#building_name#_#unit#_gallery.txt The directory attribute is for other cfftp actions On Jan 28, 12:49 pm, SAMARIS Software rai...@ozemail.com.au wrote: I am using the following code to ftp files from my cf app to another server cfftp connection = imediatestconn username = #un# password = #pw# passive = yes timeout=180 server = #server# directory=Pages action = putfile localfile=D:\FTP\Austaccomm\Htdocs\samaris\pnr\pages\bca_#building_name#_#u nit#_gallery.txt remotefile=#building_name#_#unit#_gallery.txt The files are uploading to the server however they are being placed in the root directory when I want to have them placed into a specific sub directory called /pages can someone enlighten me as to what I need to do to be able to upload to the sub directory rather than the room directory Regards Claude Raiola B.Econ (Acc), B.Hot.Mngt. Websites:www.AustralianAccommodation.comwww.SAMARIS.NETwww.WebSiteSolutions.com.au Mobile: 0414 228 948 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Using stored procedure exclusively to control database access
Hey Felix, * Faster execution is definitely a benefit of stored procs for intensive or repetitive tasks * Centralised place for logic is definitely a good thing, but I would suggest that it's better to have in ColdFusion not stored procs. In some cases, not all business logic can be represented in T-SQL so you end up with a half-half solution anyway. Depends what you define as business logic but perhaps writing to a file, accessing a web service or emailing an administrator on a certain event may be part of your business logic which needs CF. * Not sure if you implement unit testing or use other debugging tools, but I find T-SQL is not as easy to test or debug as ColdFusion. * Do you have version control for your stored procedures? I would think it pretty important that your app's business logic should be under version control and that's far easier to do with your CF code than stored procs which have to be copied to files. * Database portability may not be an issue on a single app, but it may be if you reuse the same code on other projects cheers Ryan On Jan 12, 12:19 pm, felixt cem...@gmail.com wrote: Hi all, It has been suggested by someone at work that we should only allow access to database via stored procedures. This was proposed to fix the current situation where we have hundreds of similar SQL statements scattered around the system. For example if the business logic has changed in one place that affects a table, one needs to do a keyword search on all files to make sure all the related files are updated. I am aware of the benefits of going the stored procs way, like: 1. Centralized place for logic 2. Faster execution 3. It's very unlikely that we will go with different database system other than MSSQL so portability is not an issue for us. But I feel a bit uneasy about this, I don't feel business logic should be in the database also I think debugging stored procedure will be more difficult (adding one more place to check). But this is just my feel, I might be wrong. Any thoughts, is this a normal/recommended practice? Also what are the best practices that you guys use to combat this scattered SQL statements? I thought of using CFCs (gateways and/or DAOs) should be sufficient: CFM - CFC - query rather than: CFM - CFC - stored proc Cheers, Felix --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
[cfaussie] Re: Grouping Output By Date Not working
I think the CAST to DATE in query of queries gives a timestamp which still has the time part, so here's another suggestion: It's not the most efficient way, but you could try pre-processing the query data like this so that your startdate value only contains the date part: cfset QueryAddColumn(mycalendar1,startdate,ArrayNew(1)) cfloop query=mycalendar1 cfset QuerySetCell(mycalendar1,startdate,DateFormat(starttime,-mm- dd)) /cfloop However that might not be so efficient if the query recordcount is high, and there may be other ways you could group records as you are outputting them instead of using a Query of Queries. Hope that helps --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~--~~~~--~~--~--~---
