Hey Claude, Can't say I've come across your problem before but do you know where is the "bad casting" is occurring? Is it the SQL statement or before? If it is at the SQL are you using cfqueryparam?
Cheers, Ross On 18/02/2009, at 1:47 PM, SAMARIS Software wrote: > > Hi, > > No I am simply taking a csv file that has been created by an external > application and uploading the data file via a cold fusion page which > uploads > the file to the server space and then I read the file and import > data into > the sql server table via traditional cfquery insert. > > Regards > > Claude Raiola > B.Econ (Acc), B.Hot.Mngt. > > Websites: > www.AustralianAccommodation.com > www.SAMARIS.NET > www.WebSiteSolutions.com.au > Mobile: 0414 228 948 > > > -----Original Message----- > From: [email protected] [mailto:[email protected]] > On Behalf > Of ryanhoppitt > Sent: Wednesday, 18 February 2009 10:37 AM > To: cfaussie > Subject: [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" <[email protected]> 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 [email protected] 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 -~----------~----~----~----~------~----~------~--~---
