Re: Data Import
Hi Sorry for such a late reply, thanks, worked a treat. better still the Mrs managed to do it! Jason ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321117 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Data Import
Jason Congerton wrote on 30 March 2009 at 11:24am > My wife has brought home an excel spread sheet to create a mail merge, > however the person who set up the data up has put the address in one column > (albeit separated by commas i.e. 23 The Road, Somewhere, er2 3ry. I stupidly > said now worries I can right a bit code which will import them to separate > columns > for you! I know this isn't what you asked and someone else will probably solve your problem programmatically, but here is a simple way to do what you need: http://office.microsoft.com/en-us/excel/HA011498511033.aspx Then do your mail merge as you would normally. If you are ever likely to have to do it again with the same sheet, simply write a macro, or even record one to repeat the task. Regards Mark Henderson Web Developer P: 03 203 F: 03 203 9900 E: m...@cwc.co.nz W: http://www.cwc.co.nz ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321084 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Data Import
Hi My wife has brought home an excel spread sheet to create a mail merge, however the person who set up the data up has put the address in one column (albeit separated by commas i.e. 23 The Road, Somewhere, er2 3ry. I stupidly said now worries I can right a bit code which will import them to separate columns for you! Should have made sure i know how to do it first! Well I think I do kind of. I imported the data into a table called address, which has three columns cname, address, tel. I have set up a table called newAddress with the following tables cname, add1, add2, add3, add4, add5, add6, add7 (as far as I can see the addresses are no longer than seven) As this is for a ms word mailmerge only, I do not need to specify town, postcode etc so the above columns will be fine. I know i will have to query the old address and loop through them whilst looping through the address column, find the commas and insert it into the next available add field in the new table (not sure about this bit) How can i insert the addresses in the next available column e.g. Address 1 is 4 parts long so beginning with add1 insert upto add4 Address 2 is 6 parts long so beginning with add1 insert upto add6 I know i can use listlen to find the end of the addresses, but Iâm not sure how to implement the insert statement. Will I need to use listgetat? Any ideas are greatly appreciated Jason ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321083 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Data Import
Thank you for all of your responses, i carried out the conversion using Jason's suggestion. Worked a treat! Thank you ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315606 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Data Import
Without knowing all your column names, I think you just need something like so: INSERT INTO search_criteria ( codingID, candidateID ) SELECT cc.codingID, c.candidateID FROM candidates c INNER JOIN cand_codings cc ON c.currentposition = cand_codings.positiontitle ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315549 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Data Import
I am not a big user of Access but had to take a training course on Access 2007 earlier this year and I believe it(probably prior versions as well) has some tools built into it for doing this type of thing into another table. Might be something worth looking into because if it has it then it would just be some simple wizards more than likely. On Wed, Nov 19, 2008 at 9:40 AM, David Strong <[EMAIL PROTECTED]> wrote: > Take a look at DataMgr by Steve Bryant. Helpful tool in migrating data from > various db sources. Make sure you grab the 2.2 alpha version as its the > last version that supports MSAccess. > > http://www.bryantwebconsulting.com/cfcs/ > > Otherwise: > > Load Candidates into query object. > Load Cand_codings into another q object. > > loop through candidates or cand_codings records > do matching logic for candidates to can_codings > save the matching data you want into an array or structure > end loop > > Finally loop through matching results and insert into search_criteria. > > > Hi > > > > I have been given the task of importing some data from one database > > into another. > > > > The data has been imported i now need to code the individual records. > > > > I have three msaccess tables candidates, cand_codings and > > search_criteria > > > > In the candidates table is field called currentposition this holds the > > information i want to work with. > > > > The cand_codings table holds the position titles used in the new > > database. > > > > Search_criteria tables holds, codingid (link to cand_coding), > > candidateID (links to candidates). > > > > I some how need to loop through the candidate records, match the > > currentposition field to the cand_codings and insert into > > search_criteria the codingID & candidate id. > > > > Not sure where to start at the moment, any ideas - thank you. > > > > jason > > > > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315547 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Data Import
Take a look at DataMgr by Steve Bryant. Helpful tool in migrating data from various db sources. Make sure you grab the 2.2 alpha version as its the last version that supports MSAccess. http://www.bryantwebconsulting.com/cfcs/ Otherwise: Load Candidates into query object. Load Cand_codings into another q object. loop through candidates or cand_codings records do matching logic for candidates to can_codings save the matching data you want into an array or structure end loop Finally loop through matching results and insert into search_criteria. > Hi > > I have been given the task of importing some data from one database > into another. > > The data has been imported i now need to code the individual records. > > I have three msaccess tables candidates, cand_codings and > search_criteria > > In the candidates table is field called currentposition this holds the > information i want to work with. > > The cand_codings table holds the position titles used in the new > database. > > Search_criteria tables holds, codingid (link to cand_coding), > candidateID (links to candidates). > > I some how need to loop through the candidate records, match the > currentposition field to the cand_codings and insert into > search_criteria the codingID & candidate id. > > Not sure where to start at the moment, any ideas - thank you. > > jason > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315546 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Data Import
Hi I have been given the task of importing some data from one database into another. The data has been imported i now need to code the individual records. I have three msaccess tables candidates, cand_codings and search_criteria In the candidates table is field called currentposition this holds the information i want to work with. The cand_codings table holds the position titles used in the new database. Search_criteria tables holds, codingid (link to cand_coding), candidateID (links to candidates). I some how need to loop through the candidate records, match the currentposition field to the cand_codings and insert into search_criteria the codingID & candidate id. Not sure where to start at the moment, any ideas - thank you. jason ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:315538 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Data Import into ACCESS through CF
I would try to do it in Access before I would try to do it in CF. Bob ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229010 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Data Import into ACCESS through CF
Does anyone know if it is practical write some CF code to import an Excel spreadsheet into Microsoft Excel? Need to do this several hundred times as part of a survey operation. Responses appreciated before I get myself into hot water ... TNX. Rick Colman ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228996 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
Excel/SQL Server Data Import Prob
I'm importing surveyors data in Excel that has a format of 100+45, for example. SQL Server converts it to 0, even tho the data type is varchar(10) Any suggestions? best, paul ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists