Inserting data from csv file to database

diecasthft
diecasthft used Ask the Experts™
on
Good Morning!! I was hoping someone could help me with something....I have a small piece of code that allows users to load a csv file into my database through my Coldfusion interface. Everythnig worked well, until I added a new field, and the database column that the data is being inserted into is a number field, versus a varchar2 field. My code is below. I konw my code is wrong, becasue I keep getting an invalid number error when I try to insert the number 3 index. Before I added that, everythnig worked fine. Thanks Alot!!!

<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
    <cfquery name="importcsv" datasource="MCA_Oracle">
         INSERT INTO MCATEST.SOMARDS_TEST (JONO,MIPR_NUMBER,A_COMMIT)
         VALUES
                  ('#listgetAt('#index#',1, ',')#',
                   '#listgetAt('#index#',2, ',')#',
                   '#listgetAt('#index#',3, ',')#'
                  )
   </cfquery>
</cfloop>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
     
>> invalid number error when I try to insert the number 3 index

What's the value of #index# when the error occurs? If you're trying to insert a space, or some value that isn't a number, that would explain the error.  


>> '#listgetAt('#index#',3, ',')#'

Normally, don't use quotes when inserting numbers. Either use val() to convert the string to a number of cfqueryparam with the correct data type (integer, decimal, ..?)

                  VALUES
                  ('#listgetAt(index,1, ',')#',
                   '#listgetAt( index,2, ',')#',
                   #val( listgetAt(index,3, ',') )#
                  )

Author

Commented:
That did it!! Thanks for your help!!!
Most Valuable Expert 2015

Commented:
Ok. Just be sure listgetAt(index,3, ',') contains a number.  val() automatically converts anything not numeric to 0.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial