Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update a table and add id number incrementally?

Posted on 2009-05-05
2
Medium Priority
?
187 Views
Last Modified: 2013-12-24
Experts,

I have a spreadsheet with data that I need to write its contents to a sql server table.  And when I write the records from the spreadsheet to the table, I need to start the id field with the number 2147 and increment by 1, for the remainder of the records.

Also, I am using CF 5.0

What is the best process/approach to do this.  
Please provide a simple example if possible.

Thank you
0
Comment
Question by:g118481
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

Accepted Solution

by:
azadisaryev earned 2000 total points
ID: 24304351
1) if cf5 supports ODBC data sources based on Excel (i do not know - i started with cf6...), create a system datasourse pointing to your xls file (in Control Panel -> Administrative Tools -> Data Sources (ODBC) -> System DSN -> Add), then create a new cf dsn pointing to this windows system dsn

1-1) query this new cf dsn using <cfquery> tag:
<cfquery name="myxlsdata" datasource="...">
SELECT * FROM .....
</cfquery>

1-2) <cfset> a var for your id value:
<cfset curID = 2147>

1-3) <cfoutput> the myxlsdata query and insert data into your sql server table, incrementing curID value with every loop iteration:
<cfoutput query="myxlsdata">
<cfquery name="insertdata" datasource="your-sql-server-dsn">
INSERT INTO ...
(id, col1, col2, ..., colN)
VALUES
(#curID#, #myxlsdata.integer_col1#, '#myxlsdata.text_col2#', ..., #myxlsdata.colN#)
</cfquery>
<cfset curID = curID + 1>
</cfoutput>

2) if cf5 does not allow the above, export your xls file as csv (comma-separated values) file

2-1) read the file into a variable using <cffile>:
<cffile action="read" file="full\path\to\csv\file" variable="mycsvfile">

2-2) <cfset> a var for your id value:
<cfset curID = 2147>

2-3) <cfloop> over the above variable, treating it as a CR+LF-delimited list, and insert data into your sql server table in each loop iteration and incrementing curID variable:

<cfloop list="#mycsvfile#" delimiters="#chr(13)&chr(10)#" index="row">

<!--- now ROW variable (loop index) represents each line in your csv file, which in turn is a comma-delimited list of values to be inserted. use listGetAT() function to get individual row elements --->

<cfquery name="insertdata" datasource="your-sql-server-dsn">
INSERT INTO ...
(id, col1, col2, ..., colN)
VALUES
(#curID#, #listGetAt(row, 1)#, '#istGetAt(row, 2)#', ..., #istGetAt(row, N)#)
</cfquery>
<cfset curID = curID + 1>
</cfloop>

that's the gist of it...

make sure the ID column in your sql server table allows you to insert data into it!

there are, however, numerous possible pitfalls to watch out for, especially with CSV approach, the biggest one being empty cell in xls file:
since csv file's lines (rows of data from your xls sheet) are treated as a comma-delimited list, and CF treats consecutive list delimiters as one list element, you MUST make sure each and every cell in your xls file has a value - empty cells should probably be filled with NULLs/0/spaces whatever is appropriate

another pitfall is: you must make sure that TEXT values you are inserting are enclosed in ' (single quotes) and NUMERIC values are not. you basically have to have a very detailed knowledge of the data in your xls file and how it corresponds to the columns in your sql server table.

hope this helps!

Azadi
0
 
LVL 1

Author Closing Comment

by:g118481
ID: 31577981
Excellent information.
Thank you very much!
0

Featured Post

Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question