Had the same problem, did it all front-end, takes a while. Purpose was
for a synchronization tool between an offline legacy application and an
online intranet. Reasoning was that I didn't have time to set up a DNS
for the .csv files. Look forward to hearing how non-best-practice it
is.
I do a customer table and a contact table (customer example shown).
Hopefully, I've removed enough incriminating info...
Process is:
1. Extract all customer and contact records that have been modified
since the last update into .csv file
2. For fields containing null values, replace null values with
"NULLVALUE" (and hope there's no city, state or country called
NULLVALUE)
2. Upload to db/ directory
(shown below)
3. Delete temporary table
4. Insert .csv into temporary table
5. Loop through temporary table. If the record exists in the master
online table, update, otherwise, insert.
Only thing it doesn't do yet is reflect deleting from the off-line
application.
<cfquery name="ClearQuery">
DELETE
FROM CustomerTableTemp
</cfquery>
<!--- Customer --->
<cfset IndexNo = 1>
<CFSET
thisPathFile="#ExpandPath("../db/CustomerTable.txt")#">
<cffile action="READ" file="#thisPathFile#"
variable="csvFile">
<cfset delim = Chr(10) & Chr(13)>
<cfset delim1 = "|">
<cfoutput>
<!--- Loop each row --->
<span class="Content">
<cfloop index="RowElement" list="#csvFile#"
delimiters="#delim#">
<!--- Loop through each column and define the
variables to enter into the DB --->
<!--- On the last column, insert the info into
the database and reset the Index --->
<cfloop index="ColumnElement"
list="#RowElement#" delimiters="#delim1#">
<cfif IndexNo EQ 1>
<cfset CustomerIDReturn =
ColumnElement>
<cfset IndexNo = 2>
<cfelseif IndexNo EQ 2>
<cfset CustomerNameReturn =
ColumnElement>
<cfset IndexNo = 3>
<cfelseif IndexNo EQ 3>
<cfif ColumnElement EQ
"NullValue">
<cfset
CustomerCityReturn = "">
<cfelse>
<cfset
CustomerCityReturn = ColumnElement>
</cfif>
<cfset IndexNo = 4>
<cfelseif IndexNo EQ 4>
<cfif ColumnElement EQ
"NullValue">
<cfset
CustomerStateReturn = "">
<cfelse>
<cfset
CustomerStateReturn = ColumnElement>
</cfif>
<cfset IndexNo = 5>
<cfelseif IndexNo EQ 5>
<cfif ColumnElement EQ
"NullValue">
<cfset
CustomerCountryReturn = "">
<cfelse>
<cfset
CustomerCountryReturn = ColumnElement>
</cfif>
<cfset IndexNo = 1>
<cfquery name="TypeQuery">
INSERT INTO
CustomerTableTemp(CustomerTempID, CustomerName, City, State, Country)
VALUEs
('#CustomerIDReturn#', '#CustomerNameReturn#', '#CustomerCityReturn#',
'#CustomerStateReturn#', '#CustomerCountryReturn#')
</cfquery>
</cfif>
</cfloop>
</cfloop>
</span>
</cfoutput>
<!--- (move data from temporary tables to final tables) --->
<cfquery name="CustomerTempReturn">
SELECT CustomerTempID, CustomerName, City,
State, Country
FROM CustomerTableTemp
ORDER BY CustomerTempID
</cfquery>
<!--- Loop through temporary table. If record exists,
update, otherwise, add --->
<cfset CustomerAddIndex = 0>
<cfset CustomerEditIndex = 0>
<cfoutput>
<cfloop query="CustomerTempReturn">
<cfquery name="CustomerReturn">
SELECT CustomerTable.CustomerID,
CustomerTable.CustomerTempID, CustomerTable.CustomerName
FROM CustomerTable
WHERE
CustomerTable.CustomerTempID='#CustomerTempID#'
</cfquery>
<cfif CustomerReturn.RecordCount GT 0>
UPDATE #CustomerTempID# | #CustomerName#
| #City# | #State# | #Country#<br>
<cfquery name="ModifyCustomer">
UPDATE CustomerTable
SET CustomerName =
'#CustomerName#'
, City = '#City#'
, State = '#State#'
, Country = '#Country#'
, DateEdited =
#CreateODBCDateTIme(now())#
, EditedBy =
#SelectedAdmin#
WHERE CustomerTempID =
'#CustomerTempID#'
</cfquery>
<cfset CustomerEditIndex =
CustomerEditIndex + 1>
<cfelse>
INSERT #CustomerTempID# | #CustomerName#
| #City# | #State# | #Country#<br>
<cfquery name="ModifyCustomer">
INSERT INTO CustomerTable
(CustomerTempID, CustomerName, Hidden, City, State, Country,
DateCreated, DateEdited, EditedBy)
VALUES ('#CustomerTempID#',
'#CustomerName#', 0, '#City#', '#State#', '#Country#',
#CreateODBCDateTIme(now())#, #CreateODBCDateTIme(now())#,
#SelectedAdmin#)
</cfquery>
<cfset CustomerAddIndex =
CustomerAddIndex + 1>
</cfif>
</cfloop>
</cfoutput>
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Peter
Tilbrook
Sent: Tuesday, 18 January 2005 6:16 PM
To: CFAussie Mailing List
Cc: [EMAIL PROTECTED]
Subject: [cfaussie] Advice appreciated on data conversion
Any ideas on parsing this from a flat text file (.txt) into a database
(assuming row one in file is column name?).
Sample data:
SUPPLIER|CAT.|PRODUCT-CODE|DESCRIPTIONS|LQ|MQ|HQ|LQ-PRICE|MQ-PRICE|HQ-PR
SUPPLIER|ICE|
RRP(INC-TAX)|STOCK|WEIGHT|G-CODE
4KUS |CD|CCD-52X6D |52X CD-ROM DRIVE RETAIL PACK
|9|19|999|16.7|16.6|16.5|23.99|YES|1|09013
4KUS |MO|CDR-7S52
|INT.CRW,52xCDR,32xCDRW,52xCD-READ,NERO5.5,SMART-BURN/X,VAS
|9|19|49|31|30.75|30.5|44.99|YES|1|14392
As you can see the end of the row is not "terminated" and the usual
delimiter of the comma "," is used within certain fields so bugger the
easy parsing using the CFML default comma.
I am assuming the the COLUMN NAMES are not valid and would have to be
tweaked on the fly (that is easy).
I am also assuming that ALL fields are populated although NULL data
could be accepted.
I have done similar conversions only recently but with a known
delimiter. Any ideas on the most efficient way to do this using
ColdFusion MX 6.x.? Please?
Regards,
Peter Tilbrook
Director, ColdGen Internet Solutions
Manager, ACT and Region ColdFusion Users Group
Director of Technology, Global Information Exchange
4/73 Tharwa Road
Queanbeyan, NSW, 2620
AUSTRALIA
WWW 1: http://www.coldgen.com/
WWW 2: http://www.actcfug.com/
Telephone: +61-2-6284-2727
E-mail: [EMAIL PROTECTED]
>>
All of my external emails are scanned for viruses using the latest
available Norton AV signatures. Also I do NOT maintain an Address book
or Contact list to minimise the risk of infecting recipients of my
messages for viruses. I also prefer "plain text" emails for speed and
efficiency.
Powered by Lookout:
Lookout is lightning-fast search for your email, files, and desktop
works with Microsoft Outlook.
http://www.lookoutsoft.com/Lookout/
>>
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To
unsubscribe send a blank email to
[EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
---
You are currently subscribed to cfaussie as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/
