Highlighted

CSV into MYSQL database

New Here ,
Dec 29, 2015

Copy link to clipboard

Copied

I am attempting to take a lot of data from a csv file and import it into a mysql database.

My Code

<cffile action="read"file="http://website.com/testtmk.csv" variable="datacsv">

<cfloop index="index" list="#datacsv#" delimiters="#chr(10)##chr(13)#">

<cfquery datasource="X" username="Y" password="Z!" name="putproperty">

INSERT INTO property

   (Property_id, Amenities, Association, Association_Fee, Association_includes, Bath_full, Baths_half, Beds, Building, City, Easement, FloodZone,

   Floor, Flooring, Furnished, Inclusion, Tenure, ListPrice, ListSvc, LotFeature, LotSize, Maintenance, MLSID, Neighbourhood, Parking, ParkingTotal,

   Permitaddress, PotoCount, PoolFeatures, Postal, PropertyCondition, PropertyType, PublicRemarks, Restrictions, SQFTBuilding`, SQFTGarage`, SQFTLanai,

   SQFTRoof, SQFTRoofO, SQFTTotal, State, Status, Stories, StreetName, StreetNumber, StreetSuff, UnitFeatures, UnitNumber, Utilities, View,

   VirtualTour, YearRemodeled)

  

VALUES

   ('#Amenities#',

   '#form.Association#', '#form.Association_Fee#', '#form.Association_includes#', '#form.Bath_full#', '#form.Baths_half#', '#form.Beds#', '#form.Building#', '#form.City#', '#form.Easement#', '#form.FloodZone#',

   '#form.Floor#', '#form.Flooring#', '#form.Furnished#', '#form.Inclusion#', '#form.Tenure#', '#form.ListPrice#', '#form.ListSvc#', '#form.LotFeature#', '#form.LotSize#', '#form.Maintenance#', '#form.MLSID#', '#form.Neighbourhood#', '#form.Parking#', '#form.ParkingTotal#',

   '#form.Permitaddress#', '#form.PotoCount#', '#form.PoolFeatures#', '#form.Postal#', '#form.PropertyCondition#', '#form.PropertyType#', '#form.PublicRemarks#', '#form.Restrictions#', '#form.SQFTBuilding#', '#form.SQFTGarage#', '#form.SQFTLanai#',

   '#form.SQFTRoof#', '#form.SQFTRoofO#', '#form.SQFTTotal#', '#form.State#', '#form.Status#', '#form.Stories#', '#form.StreetName#', '#form.StreetNumber#', '#form.StreetSuff#', '#form.UnitFeatures#', '#form.UnitNumber#', '#form.Utilities#', '#form.View#',

   '#form.VirtualTour#', '#form.YearRemodeled#')

</cfquery>

</cfloop>

<strong><cfquery datasource="X" username="Y" password="Z!" name="getproperty">

SELECT * FROM Property

</cfquery>

<cfdump var="#getproperty#">

My Error Code

Variable AMENITIES is undefined.

The error occurred in D:/home/manpcs.com/wwwroot/Untitled_cfm.cfm: line 17
15 : 16 :  VALUES 17 : ('#Amenities#',  18 :    '#form.Association#', '#form.Association_Fee#', '#form.Association_includes#', '#form.Bath_full#', '#form.Baths_half#', '#form.Beds#', '#form.Building#', '#form.City#', '#form.Easement#', '#form.FloodZone#', 19 :    '#form.Floor#', '#form.Flooring#', '#form.Furnished#', '#form.Inclusion#', '#form.Tenure#', '#form.ListPrice#', '#form.ListSvc#', '#form.LotFeature#', '#form.LotSize#', '#form.Maintenance#', '#form.MLSID#', '#form.Neighbourhood#', '#form.Parking#', '#form.ParkingTotal#', 

My Question.

How do I designate the Values of each column and row to be specific?

Thank you for your help in advance

Views

148

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

CSV into MYSQL database

New Here ,
Dec 29, 2015

Copy link to clipboard

Copied

I am attempting to take a lot of data from a csv file and import it into a mysql database.

My Code

<cffile action="read"file="http://website.com/testtmk.csv" variable="datacsv">

<cfloop index="index" list="#datacsv#" delimiters="#chr(10)##chr(13)#">

<cfquery datasource="X" username="Y" password="Z!" name="putproperty">

INSERT INTO property

   (Property_id, Amenities, Association, Association_Fee, Association_includes, Bath_full, Baths_half, Beds, Building, City, Easement, FloodZone,

   Floor, Flooring, Furnished, Inclusion, Tenure, ListPrice, ListSvc, LotFeature, LotSize, Maintenance, MLSID, Neighbourhood, Parking, ParkingTotal,

   Permitaddress, PotoCount, PoolFeatures, Postal, PropertyCondition, PropertyType, PublicRemarks, Restrictions, SQFTBuilding`, SQFTGarage`, SQFTLanai,

   SQFTRoof, SQFTRoofO, SQFTTotal, State, Status, Stories, StreetName, StreetNumber, StreetSuff, UnitFeatures, UnitNumber, Utilities, View,

   VirtualTour, YearRemodeled)

  

VALUES

   ('#Amenities#',

   '#form.Association#', '#form.Association_Fee#', '#form.Association_includes#', '#form.Bath_full#', '#form.Baths_half#', '#form.Beds#', '#form.Building#', '#form.City#', '#form.Easement#', '#form.FloodZone#',

   '#form.Floor#', '#form.Flooring#', '#form.Furnished#', '#form.Inclusion#', '#form.Tenure#', '#form.ListPrice#', '#form.ListSvc#', '#form.LotFeature#', '#form.LotSize#', '#form.Maintenance#', '#form.MLSID#', '#form.Neighbourhood#', '#form.Parking#', '#form.ParkingTotal#',

   '#form.Permitaddress#', '#form.PotoCount#', '#form.PoolFeatures#', '#form.Postal#', '#form.PropertyCondition#', '#form.PropertyType#', '#form.PublicRemarks#', '#form.Restrictions#', '#form.SQFTBuilding#', '#form.SQFTGarage#', '#form.SQFTLanai#',

   '#form.SQFTRoof#', '#form.SQFTRoofO#', '#form.SQFTTotal#', '#form.State#', '#form.Status#', '#form.Stories#', '#form.StreetName#', '#form.StreetNumber#', '#form.StreetSuff#', '#form.UnitFeatures#', '#form.UnitNumber#', '#form.Utilities#', '#form.View#',

   '#form.VirtualTour#', '#form.YearRemodeled#')

</cfquery>

</cfloop>

<strong><cfquery datasource="X" username="Y" password="Z!" name="getproperty">

SELECT * FROM Property

</cfquery>

<cfdump var="#getproperty#">

My Error Code

Variable AMENITIES is undefined.

The error occurred in D:/home/manpcs.com/wwwroot/Untitled_cfm.cfm: line 17
15 : 16 :  VALUES 17 : ('#Amenities#',  18 :    '#form.Association#', '#form.Association_Fee#', '#form.Association_includes#', '#form.Bath_full#', '#form.Baths_half#', '#form.Beds#', '#form.Building#', '#form.City#', '#form.Easement#', '#form.FloodZone#', 19 :    '#form.Floor#', '#form.Flooring#', '#form.Furnished#', '#form.Inclusion#', '#form.Tenure#', '#form.ListPrice#', '#form.ListSvc#', '#form.LotFeature#', '#form.LotSize#', '#form.Maintenance#', '#form.MLSID#', '#form.Neighbourhood#', '#form.Parking#', '#form.ParkingTotal#', 

My Question.

How do I designate the Values of each column and row to be specific?

Thank you for your help in advance

Views

149

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Dec 29, 2015 0
Most Valuable Participant ,
Dec 29, 2015

Copy link to clipboard

Copied

Where is "Amenities" supposed to be coming from?  And why are all the values that appear to be coming from your CSV file prefixed with "FORM."?

-Carl V.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Dec 29, 2015 1
BKBK LATEST
Adobe Community Professional ,
Jan 03, 2016

Copy link to clipboard

Copied

Carl has suggested what caused the error. You asked a separate question.

Alan Koenig_920 wrote:

My Question.

How do I designate the Values of each column and row to be specific?

Simple: just match column-names and respective values by eye. Then you will immediately see that your matching is wrong, right from the start:

Property_id <-> '#Amenities#'

Amenities <-> '#form.Association#'

Association <-> '#form.Association_Fee#'

etc.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 03, 2016 0