I will have data to routinely import into SQL Server from Excel files
constructed with goofy column headers like "Business Classification"
and "Owner's Name" that may contain spaces and/or single quotes. I
will need to be doing these imports fairly frequently so I am trying
to avoid the seemingly easy solution of renaming the Excel columns
before importing so that I can make the process almost automatic
(saving lots of time in the long run). Surprisingly the data get
imported into SQL Server just fine with such goofy names as field
names. Since the imported data won't necessarily always have the
exact same fieldnames or even the same order in the spreadsheet, I
need to build a dynamic import process that will take imported Excel
data from its first table in SQL Server and allow me to assign its
somewhat random and goofy fieldnames to standard fieldnames in a
second table in the same database where the data are to be actually
used and then INSERT the data into the second table. This takes a few steps:
Since the fieldnames may vary from import to import, I am getting
them in a two-step process this way:
<CFQUERY NAME="GetTables" DATASOURCE="YadaYada">
SELECT Name, id
FROM sysobjects
WHERE (xtype = 'U') AND (name LIKE 'Import%')
</CFQUERY>
This gives me the name and id for all of my import tables that I
output on a web page as a series of links with a URL variable
referencing the id so that I can then use another query to get the
fieldnames of any of my import tables like so:
<CFQUERY NAME="GetFields" DATASOURCE="YadaYada">
SELECT name
FROM syscolumns
WHERE id = #URL.id#
</CFQUERY>
I then output the fieldnames from the GetFields query like so, in
order to use them in a third query, protecting goofy fieldnames with
brackets so SQL Server doesn't choke:
<CFSET FieldList = ''>
<CFOUTPUT QUERY="GetFields">
<CFSET FieldList = FieldList & '[#Name#], '>
</CFOUTPUT>
<CFSET FieldList = FieldList & '3.1416 AS Pi'> (this is just to dodge
the trailing comma from the last CFSET above)
So far so good, as I am able to get the data out of each Import table
by referencing its fieldnames (yes I could use SELECT * but then I'd
still have the same problem as below and this does work fine):
<CFQUERY NAME="GetData" DATASOURCE="YadaYada">
SELECT #PreserveSingleQuotes(Variables.FieldList)#
FROM #URL.TableName#
</CFQUERY>
PreserveSingleQuotes is needed above to protect against bombing when
fieldnames have single quotes in them, and the resulting dynamic
query above can come out looking like this (keep in mind that
fieldnames will vary slightly between different Import tables):
SELECT [Business], [Business Classification], [Location Address],
[Mail City], [Mailing Address], [Owner's Name]
FROM ImportTable27
This ugly hack works fine up to here. Unfortunately I am hung up
trying to get ColdFusion to read the goofy fieldnames without
throwing errors. Trying to output the data in ColdFusion I get this:
Invalid CFML construct found on line 1 at column 10. (CF gets the
line wrong, not unusual)
ColdFusion was looking at the following text:
Classification
The error occurred in C:\htdocs\DevTesting\GetTables.cfm: line 55
53 : <CFLOOP LIST="#ValueList(GetFields.Name)#" INDEX="ii">
54 : <CFSET FieldName = '#ii#'>
55 : #Evaluate('#FieldName#')#
56 : </CFLOOP>
57 :
This error happens where the above CFLOOP is contained in this CFOUTPUT QUERY:
<CFOUTPUT QUERY="GetData">
<CFLOOP LIST="#ValueList(GetFields.Name)#" INDEX="ii">
<CFSET FieldName = '#ii#'>
#Evaluate('#FieldName#')#
</CFLOOP>
<br />
</CFOUTPUT>
What appears to be happening in this case is that the first field,
Business, passes okay (the first field in the first record is output
on the page), but Business Classification is not being read properly
by ColdFusion since the error indicates that ColdFusion was looking
at Classification, missing or skipping the preceding Business in the
Business Classification field. I have already tried several
strategies like <CFSET FieldName = '[#ii#]'> in the CFLOOP but then
ColdFusion chokes on the brackets. Also problems occur with variants
of #Evaluate('#FieldName#')# like #Evaluate(#FieldName#)# and
#Evaluate(FieldName)#. (I am using the latest MX 7 developer's
edition and SQL Server 2000 trial edition on WinXP).
What I want to do next is output all the data onto a web page from an
Import table so that I can look at it and decide which of the
random/goofy fieldnames from Excel imports in SQL Server belong to
which of my standard fieldnames in the second and final table which
the data will be moved to. I intend to finish this little utility so
that I can select in dropdown boxes which fields from an Import table
will correspond with which fields in my final data table in order to
dynamically construct the necessary INSERT query. All that I need to
get past this hurdle is to figure out how to get ColdFusion to output
goofy fieldnames dynamically as I am attempting but failing above.
Thanks for your help,
Karl S.
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.15.20/588 - Release Date: 12/15/2006
10:02 AM
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264168
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
