Hello,
I am reading a CSV file and inserting it into a database. All is good with the
exception that one field has commas within it, therefore when it runs, it
thinks the commas inside the "" are delimeters also. Any ideas on how I can
get around it or take the commas out of that field before I start processing
the CSV file? The commas in the field are not necessary. For example in my code
below the varible Description may have several commas in it when I am trying to
insert it into the database, therefore creating a insertion error.
Thanks in advanced.
Here is my code:
<cfset aFileNames = ArrayNew(1)>
<cfset index = 1>
<cfquery name="getImportPath" datasource="sqlbuffers">
SELECT tblSettings.CSVExportPath
FROM tblSettings
WHERE tblSettings.ExportType = 'FullfillmentOrders'
</cfquery>
<cfdirectory action="list" directory="#getImportPath.CSVExportPath#"
name="getFullfillmentOrders" recurse="yes" sort="Name">
<cfloop query="getFullfillmentOrders">
<cfset aFileNames[index] = #getFullfillmentOrders.Name#>
<cfset index = index + 1>
</cfloop>
<cfloop from="1" to="#ArrayLen(aFileNames)#" index="i">
<!---reads the file in to a variable called the FileNames--->
<cffile action="read"
file="#getImportPath.CSVExportPath##aFileNames[i]#" variable="FileNames">
<!---loop through the file and insert each row into the WIP demand
table--->
<cfloop list="#FileNames#" index="i" delimiters="#chr(13)#">
<cfset counter = 1>
<cfloop list="#i#" index="j" delimiters=",">
<!---defines each column in the database--->
<cfif counter eq 1>
<cfset OrderNumber = #trim(j)#>
<cfelseif counter eq 2 >
<cfset SalesOrderNumber = #trim(j)#>
<cfelseif counter eq 3>
<cfset WorkOrderNumber = #trim(j)#>
<cfelseif counter eq 4>
<cfset PartNumber = #trim(j)#>
<cfelseif counter eq 5>
<cfset Description = #trim(j)#>
<cfelseif counter eq 6>
<cfset Revision = #trim(j)#>
<cfelseif counter eq 7>
<cfset UnitOfMeasure = #trim(j)#>
<cfelseif counter eq 8>
<cfset PartType = #trim(j)#>
<cfelseif counter eq 9>
<cfset InventoryPlanning = #trim(j)#>
<cfelseif counter eq 10>
<cfset PlannerCode = #trim(j)#>
<cfelseif counter eq 11>
<cfset OrderDate = #trim(j)#>
<cfelseif counter eq 12>
<cfset RequestDate = #trim(j)#>
<cfelseif counter eq 13>
<cfset OrderQuantity = #trim(j)#>
<cfelseif counter eq 14>
<cfset RecommendedQuantity = #trim(j)#>
<cfelseif counter eq 15>
<cfset Priority = #trim(j)#>
<cfelseif counter eq 16>
<cfset Expedite = #trim(j)#>
<cfelseif counter eq 17>
<cfset Vendor = #trim(j)#>
<cfelseif counter eq 18>
<cfset VendorIdentifier = #trim(j)#>
<cfelseif counter eq 19>
<cfset UnitCost = #trim(j)#>
<cfelseif counter eq 20>
<cfset ExtendedCost = #trim(j)#>
<cfelseif counter eq 21>
<cfset VarianceCost = #trim(j)#>
<cfelseif counter eq 22>
<cfset Customer = #trim(j)#>
<cfelseif counter eq 23>
<cfset Notes = #trim(j)#>
<cfelseif counter eq 24>
<cfset VarianceNotes = #trim(j)#>
</cfif>
<cfset counter = counter + 1>
</cfloop>
<!---If partnumber is blank or the average per week is 0 then
don't insert the record--->
<cfif orderNumber NEQ "OrderNumber" AND orderNumber NEQ "">
<cfquery name="insertFullfillmentOrders"
datasource="sqlbuffers">
INSERT INTO tblFullfillmentOrders
([OrderNumber], [SalesOrderNumber], [WorkOrderNumber], [PartNumber],
[Description], [Revision], [UnitOfMeasure], [PartType], [InventoryPlanning],
[PlannerCode], [OrderDate], [RequestDate], [OrderQuantity],
[RecommendedQuantity], [Priority], [Expedite], [Vendor], [VendorIdentifier],
[UnitCost], [ExtendedCost], [VarianceCost], [Customer], [Notes],
[VarianceNotes])
SELECT '#OrderNumber#', '#SalesOrderNumber#',
'#WorkOrderNumber#', '#PartNumber#', '#Description#', '#Revision#',
'#UnitOfMeasure#', '#PartType#', '#InventoryPlanning#', '#PlannerCode#',
'#OrderDate#', '#RequestDate#', #OrderQuantity#, #RecommendedQuantity#,
#Priority#, #Expedite#, '#Vendor#', '#VendorIdentifier#', #UnitCost#,
#ExtendedCost#, #VarianceCost#, '#Customer#', '#Notes#', '#VarianceNotes#'
</cfquery>
</cfif>
</cfloop>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create Web Applications With ColdFusion MX7 & Flex 2.
Build powerful, scalable RIAs. Free Trial
http://www.adobe.com/products/coldfusion/flex2/
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270713
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
