Cheap copy and paste (note: you still need to find someone who writes
excel macros):
First, get your data into a spreadsheet.
Next, separate your columns by adding new columns, with "supposedly" unique
letter combinations (such as aaabbb, bbbccc, cccddd) of characters.
Next, in your last column, concatenate everything - then copy and paste
that column into a textarea box.
In a perfect world, a basic VB macro does it. In my world, I just put it
in a few times, manually.
In the actual code:
Basically, I just loop over the data, and use the unique column values to
measure the start and end of my record. Not perfect, IF the column values
aren't unique. But, it is a HELL of a lot better than individual entry.
Optionally, put the data into a temporary table in your DB, and then manipulate
it.
RESULTS OF CONCATENATING COLUMNS:
aaabbbSmithbbbcccJohncccddddddeee2006-07-01eeefff
aaabbbJohnsonbbbcccMichaelcccddd1234432dddeee2004-07-01eeefff
aaabbbBushbbbcccGeorgecccddd100006732dddeee2004-07-01eeefff
COLDFUSION:
Basically, I just loop over the data, and use the unique column values to
measure the start and end of my record. Not perfect, IF the column values
aren't unique. But, it is a HELL of a lot better than individual entry.
Optionally, put the data into a temporary table in your DB, and then manipulate
it.
Actual CODE for COLDFUSION:
<cfset vyn_End = "NO">
<cfloop condition="#vyn_End# EQ 'NO'">
<cfset vnu_RecordStart = find("aaabbb",vtx_BulkInsert, vnu_RecordStart)>
<cfset vnu_RecordEnd = find("fffggg",vtx_BulkInsert,vnu_RecordStart + 1)>
<cfif vnu_RecordStart EQ 0 AND vyn_End EQ "NO">
<cfset vyn_End = "YES">
</cfif>
<cfif vyn_End EQ "NO">
<!--- Get variables that measure each record length --->
<cfset vnu_Length = vnu_RecordEnd - vnu_RecordStart>
<cfset vtx_Record = mid(vtx_BulkInsert,vnu_RecordStart + 6,vnu_Length)>
<!--- Get temporary variables that contain the individual record
elements. --->
<cfset vnu_SelectLength = find("bbbccc",vtx_BulkInsert,
vnu_RecordStart) - find("aaabbb",vtx_BulkInsert, vnu_RecordStart) -6 >
<cfset vCurtx_LastName = #mid(vtx_BulkInsert,6 +
find("aaabbb",vtx_BulkInsert, vnu_RecordStart) ,vnu_SelectLength)#>
<cfset vnu_SelectLength = find("cccddd",vtx_BulkInsert,
vnu_RecordStart) - find("bbbccc",vtx_BulkInsert, vnu_RecordStart) -6 >
<cfset vCurtx_FirstName = #mid(vtx_BulkInsert,6 +
find("bbbccc",vtx_BulkInsert, vnu_RecordStart),vnu_SelectLength)#>
<cfset vnu_SelectLength = find("dddeee",vtx_BulkInsert,
vnu_RecordStart) - find("cccddd",vtx_BulkInsert, vnu_RecordStart) -6 >
<cfset vCurtx_EmpNo = #mid(vtx_BulkInsert,6 +
find("cccddd",vtx_BulkInsert, vnu_RecordStart) ,vnu_SelectLength)#>
<!--- Dates can be funky - in my format, the data comes in yyyy-mm-dd
--->
<cfset vnu_SelectLength = find("eeefff",vtx_BulkInsert,
vnu_RecordStart) - find("dddeee",vtx_BulkInsert, vnu_RecordStart) -6 >
<cfset vCurtx_Start = #mid(vtx_BulkInsert,6 +
find("dddeee",vtx_BulkInsert, vnu_RecordStart) ,vnu_SelectLength)#>
<cfset vCurdt_Start =
createdate(mid(vCurtx_Start,1,4),mid(vCurtx_Start,6,2),mid(vCurtx_Start,9,2))>
<cfset vODBC_Curtx_Start = createodbcdate(vCurdt_Start)>
<cfset vnu_RecordStart = vnu_RecordEnd>
<CFQUERY DATASOURCE="#DS#" NAME="q_Insert">
INSERT into t_Data
(tx_EmployeeNumber,tx_LastName,tx_FirstName,dt_DateStart)
VALUES
('#vCurtx_EmpNo#',
'#vCurtx_LastName#','#vCurtx_FirstName#',#vODBC_Curtx_Start#)
</CFQUERY>
<li>#vCurtx_LastName#/#vCurtx_FirstName#/#vCurtx_EmpNo#/#vODBC_Curtx_Start#/#vODBC_Curtx_End#</li>
</cfif>
</cfloop>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:244876
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
