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: cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] 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" <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 -~----------~----~----~----~------~----~------~--~---
