Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update a table and add id number incrementally?

Posted on 2009-05-05
2
Medium Priority
?
189 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
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 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