Making Database

1
Making Database Spreadsheets Work Together

• Selene Bainum
• RiteTech LLC

2
About Me

• Doing ColdFusion SQL development for more than
  1/3 of my lifetime
• Chief Architect _at_ RiteTech
• RiteTech is my company completely independent
  for the first time!
• Likes
• SQL and databases!
• Love making money on the internet
• www.smartsupplies.net
• Dislikes
• DC Traffic
• Rude People

3
Agenda

• Intro
• Importing Data
• Tools
• Examples
• Exporting Data
• Tools
• Examples
• Resources
• Any custom tags/functions shown in examples are
  free!

4
Spreadsheets vs. Databases

• Similarities

• Differences

• Data stored in tabular format
• Both contains rows and columns
• Headers/column names denote data

• Spreadsheets often contain repeated rows of data
• Database tables should be normalized repeated
  data among rows kept to a minimum
• Spreadsheets used column delimiters not as
  reliable as database columns
• Spreadsheets often contain no unique key

5
Reasons to Import/Export

• Import

• Export

• Data only available as
• Web pages
• Text files
• Excel spreadsheets
• Etc
• Need data in your database!

• Export raw data so that others can
  view/manipulate it in Excel
• Create reports

6
Potential Import Issues

• Bad/non-standard data
• Spreadsheets may take hours and hours to clean up
  the data manually
• Ill-placed commas and quotes can wreak havoc
• Data types can get misread/lost
• Matching imported data can be daunting
• It helps to have good knowledge of SQL

7
Importing Tools

• Import CSV or Excel files via SQL Server
• Loop over file line by line
• CFHTTP
• Custom Tags
• cfx_text2Query
• cfx_Excel2Query
• cfx_Excel
• Problematic as CF doesnt recognize empty
  string elements

8
Examples
9
Grabbing Data from a Site

• Find a site that contains regularly formatted
  data
• Using list of US Representatives as an example
• Copy the data and paste into notepad
• Perform any formatting necessary

10
Import Data via Database

• May not work on all databases
• Using SQL Server 2005 in example
• Not all installs of databases may support this
  type of import
• May not have access to database

11
Get DataVIA CFHTTP
12
Get DataVIA CFHTTP
13
Get DataVIA CFHTTP
14
Get DataVIA CFHTTP
15
Get Data via cfx_text2Query
16
Get Data via cfx_text2Query
17
Get Data via cfx_text2Query
18
Get Data via cfx_text2Query
19
Importing Data

• Insert all rows first
• Start with query result set
• Create/modify table as needed in database
• Loop over query results
• Insert results
• Perform other database inserts/updates as needed

20
Import Code
21
Time to Normalize!
22
Normalize SQL

• -- Declare the variables
• DECLARE _at_RepID INT, _at_StateID INT, _at_DistrictID
  INT, _at_LastNameTx VARCHAR(50), _at_FirstNameTx
  VARCHAR(50),
• _at_PhoneTx VARCHAR(25), _at_RoomNb INT, _at_StateCd
  VARCHAR(2), _at_DistrictTx VARCHAR(10)
• -- Create a cursor
• DECLARE cRep CURSOR FOR SELECT FirstName,
  LastName, State, District, Phone, Room FROM
  RepListImport WITH (NOLOCK)
• -- Open the cursor
• OPEN cRep
• -- Loop over the cursor
• FETCH NEXT FROM cRep INTO _at_LastNameTx,
  _at_FirstNameTx, _at_StateCd, _at_DistrictTx, _at_PhoneTx,
  _at_RoomNb
• WHILE _at__at_FETCH_STATUS 0 BEGIN
• SET _at_StateID NULL SET _at_DistrictID NULL
• -- State exist?
• SELECT _at_StateID StateID FROM State WITH
  (NOLOCK) WHERE StateCd _at_StateCd
• IF _at_StateID IS NULL BEGIN
• SET NOCOUNT OFF
• INSERT INTO State (StateCd) VALUES (_at_StateCd)
  SELECT _at_StateID Scope_Identity()
• SET NOCOUNT ON
• END
• -- District exist?

23
Imports Automated Options

• Create table structures and import scripts
• Schedule retrieval of files
• Email attachments
• External files
• Process import when new files available
• Run additional database scripts
• ColdFusion
• Database scheduler

24
Exporting Data

• Much easier than imports
• Data is already cleaned and normalized
• Tricky part is formatting

25
Exporting Options

• Manually create a CSV file via ColdFusion
• Custom Tags/Functions
• generateExcel
• cf_Excel_XML
• cfx_Excel
• cfx_Query2Excel

26
Exporting CSV / Text Only

• Manually created CSV file
• Created by looping over the query results
• Can add custom column headings
• Can format data in cells date, number, etc
• generateExcel
• Easier than manual creation
• Cannot add custom column headings
• Column heading is the same as the column name
• Automatically formats numbers and dates

27
Manual CSV File Example

• ltcfsetting showdebugoutput"no" /gt
• lt!--- Query the reps. ---gt
• ltcfquery datasource"Mayhem_SQL"
  name"qryGetRepList"gt
• SELECT R.RepID, R.FirstNameTx, R.LastNameTx,
  R.PhoneTx, R.RoomNb, S.StateCd, D.DistrictTx
• FROM Preso..Rep R WITH (NOLOCK) INNER JOIN
  Preso..State S WITH (NOLOCK) ON R.StateID
  S.StateID
• INNER JOIN Preso..District D WITH (NOLOCK) ON
  R.DistrictID D.DistrictID
• ORDER BY S.StateCd, D.DistrictTx
• lt/cfquerygt
• ltcfheader name"Content-Disposition"
  value"inline filenameexport.csv"gt
• ltcfcontent reset"yes" type"text/comma-separated-
  values" /gt"State","District","LastName","FirstName
  ","Phone","Room"
• ltcfoutput query"qryGetRepList"gt"StateCd","Dist
  rictTx","LastNameTx","FirstNameTx","PhoneTx
  ","RoomNb"
• lt/cfoutputgt
• ltcfabort /gt

28
generateExcel Example

• ltcfsetting showdebugoutput"no" /gt
• lt!--- Query the reps. ---gt
• ltcfquery datasource"Mayhem_SQL"
  name"qryGetRepList"gt
• SELECT R.RepID, R.FirstNameTx, R.LastNameTx,
  R.PhoneTx, R.RoomNb, S.StateCd, D.DistrictTx
• FROM Preso..Rep R WITH (NOLOCK) INNER JOIN
  Preso..State S WITH (NOLOCK) ON R.StateID
  S.StateID
• INNER JOIN Preso..District D WITH (NOLOCK) ON
  R.DistrictID D.DistrictID
• ORDER BY S.StateCd, D.DistrictTx
• lt/cfquerygt
• ltcfinclude template"inc_fnGenerateExcel.cfm" /gt
• ltcfscriptgt
• generateExcel(qryGetRepList, "StateCd,DistrictTx,
  LastNameTx,FirstNameTx,PhoneTx,RoomNb")
• lt/cfscriptgt
• ltcfabort /gt

29
cf_Excel_XML

• Creates real Excel file
• Uses Excels XML format
• Custom Settings
• Header/column names
• Multiple data formatting options
• Multiple sheets per file
• Advanced Formatting
• Header color/text
• Alternating row colors
• Column width

30
cf_Excel_XML Example

• ltcfsetting showdebugoutput"no" /gt
• lt!--- Query the reps. ---gt
• ltcfquery datasource"Mayhem_SQL"
  name"qryGetRepList"gt
• SELECT R.RepID, R.FirstNameTx, R.LastNameTx,
  R.PhoneTx, R.RoomNb, S.StateCd, D.DistrictTx
• FROM Preso..Rep R WITH (NOLOCK) INNER JOIN
  Preso..State S WITH (NOLOCK) ON R.StateID
  S.StateID
• INNER JOIN Preso..District D WITH (NOLOCK) ON
  R.DistrictID D.DistrictID
• ORDER BY S.StateCd, D.DistrictTx
• lt/cfquerygt
• ltcf_excel_xml filename"export"
• sheets"1"
• collist1"StateCd,DistrictTx,LastNameTx,Firs
  tNameTx,PhoneTx,RoomNb"
• headerlist1"State,District,Last Name,First
  Name,Phone,Room"
• colwidths1"50,75,100,100,75,50"
• query1"qryGetRepList"gt
• ltcfabort /gt

31
cf_Excel_XML Advanced Example

• lt!--- Create the attribute collection. ---gt
• ltcfset ac StructNew() /gt ltcfset counter 0 /gt
• ltcfoutput query"qryGetRepList" group"StateCd"gt
  ltcfset counter counter 1 /gt
• ltcfquery dbtype"query" name"qryGetReps"gt
• SELECT FirstNameTx, LastNameTx, PhoneTx,
  RoomNb, DistrictTx, StateCd
• FROM qryGetRepList
• WHERE StateCd 'StateCd'
• ORDER BY DistrictTx
• lt/cfquerygt
• ltcfoutputgtltcfscriptgt
• ac"collistcounter" "StateCd,DistrictTx,
  LastNameTx,FirstNameTx,PhoneTx,RoomNb"
• ac"headerlistcounter"
  "State,DistrictTx,LastNameTx,FirstNameTx,PhoneTx,R
  oomNb"
• ac"colwidthscounter"
  "50,75,100,100,75,50"
• ac"querycounter" "qryGetReps"
• ac"sheetnamecounter" "StateCd"
• lt/cfscriptgtlt/cfoutputgt
• lt/cfoutputgt
• ltcf_excel_xml filename"export"
  sheets"counter" attributecollection"ac"gt

32
Wrap Up

• Importing and Exporting can be
• Easy
• Difficult
• Both
• Great tools exist use them!
• Search regularly for new tags code
• If you know ASP.NET, even better

33
Resources

• Adobe ColdFusion Exchange
• cf_Excel_XML
• http//www.adobe.com/cfusion/exchange
• Ryan Emerle
• cfx_text2query
• cfx_excel2query
• cfx_query2excel
• http//www.emerle.net/old/programming/
• cfTopper
• Generate Excel Function
• http//www.cftopper.com/index.cfm/blogId/1/tag/Col
  dFusion

34
Thank You!

• selene_at_ritetech.net