sp_addlinkedserver N'Excel', N'Jet 4.0', N'Microsoft.Jet.OLEDB.4.0', N'D:\Data\ProductData.xls', NULL, N'Excel 5.0' if exists (select * from sysobjects where id = object_id(N'[dbo].[temp_product_working_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[temp_product_working_table] CREATE TABLE [dbo].[temp_product_working_table] ( [Product_ID] [int] NULL , [Product_Name] [varchar] (100) NULL , [Customer_Lastname] [varchar] (50) NULL , [Customer_Firstname] [varchar] (30) NULL , [Customer_Phone] [varchar] (25) NULL , [Customer_Email] [varchar] (100) NULL , [Customer_Adr1] [varchar] (50) NULL , [Customer_Adr2] [varchar] (50) NULL , [Customer_City] [varchar] (100) NULL , [Customer_State] [char] (2) NULL , [Customer_Zip] [varchar] (10) NULL ) Insert Into temp_product_working_table ( Product_ID, Product_Name, Customer_Lastname, Customer_Firstname, Customer_Phone, Customer_Email, Customer_Adr1, Customer_Adr2, Customer_City, Customer_State, Customer_Zip ) Values ( #DataArray[1]#, '#DataArray[2]#', '#DataArray[3]#', '#DataArray[4]#', '#DataArray[5]#', '#DataArray[6]#', '#DataArray[7]#', '#DataArray[8]#', '#DataArray[9]#', '#DataArray[10]#', '#DataArray[11]#' ) [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => AZDeveloper [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> Saving Excel data into MS SQL Server - Coldfusion - Getting Started

Saving Excel data into MS SQL Server - Coldfusion - Getting Started

Hi All, I have a Excel spreadsheet from which I want to extract data and save it into a database one row at a time. But when I run it, I get this error "ODBC Error Code = 37000 (Syntax error or access violation) The name '??' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.". Please tell me what I did wrong here. THANK YOU VERY MUCH IN ADVANCE! <!--- First create a linked server against an Excel spreadsheet ---> <cfquery name="CreateExcelLinkServer" datasource="MyDSN"> sp_addlinkedserver N'Excel', N'Jet 4.0', N'Microsoft.Jet.OLEDB.4.0', N'D:\Data\ProductData.xls', NULL, ...

Sponsored Links
  1. #1

    Default Saving Excel data into MS SQL Server

    Hi All,

    I have a Excel spreadsheet from which I want to extract data and save it into
    a database one row at a time. But when I run it, I get this error "ODBC Error
    Code = 37000 (Syntax error or access violation)
    The name '??' is not permitted in this context. Only constants, expressions,
    or variables allowed here. Column names are not permitted.". Please tell me
    what I did wrong here. THANK YOU VERY MUCH IN ADVANCE!


    <!--- First create a linked server against an Excel spreadsheet --->
    <cfquery name="CreateExcelLinkServer" datasource="MyDSN">
    sp_addlinkedserver N'Excel', N'Jet 4.0',
    N'Microsoft.Jet.OLEDB.4.0',
    N'D:\Data\ProductData.xls', NULL, N'Excel 5.0'
    </cfquery>

    <!--- Create a working table --->
    <cfquery name="DropWorkingTable" datasource="MyDSN">
    if exists (select * from sysobjects where id =
    object_id(N'[dbo].[temp_product_working_table]') and OBJECTPROPERTY(id,
    N'IsUserTable') = 1)
    drop table [dbo].[temp_product_working_table]
    </cfquery>

    <cfquery name="CreateWorkingTable" datasource="MyDSN">
    CREATE TABLE [dbo].[temp_product_working_table] (
    [Product_ID] [int] NULL ,
    [Product_Name] [varchar] (100) NULL ,
    [Customer_Lastname] [varchar] (50) NULL ,
    [Customer_Firstname] [varchar] (30) NULL ,
    [Customer_Phone] [varchar] (25) NULL ,
    [Customer_Email] [varchar] (100) NULL ,
    [Customer_Adr1] [varchar] (50) NULL ,
    [Customer_Adr2] [varchar] (50) NULL ,
    [Customer_City] [varchar] (100) NULL ,
    [Customer_State] [char] (2) NULL ,
    [Customer_Zip] [varchar] (10) NULL
    )
    </cfquery>

    <cfset ExcelFile = "D:\Data\ProductData.xls">
    <!--- Read the excel file into memory --->
    <cffile action="READ" file="#ExcelFile#" variable="varFileContents">

    <!--- set up the necessary variables --->
    <cfset recordCount = 0>
    <cfset columnChr = chr(9)> <!--- tab-delimited --->
    <cfset recordEndChr = chr(13)> <!--- carriage return --->
    <cfset DataArray = ArrayNew(1)>
    <cfset colchr3 = "'">
    <cfset colchr4 = chr(34)> <!--- double-quote --->

    <!--- Loop thru the file and insert --->
    <cfloop index="CurRecord" list="#varFileContents#" delimiters="#recordEndChr#">
    <cfset recordCount = recordCount + 1>
    <cfif recordCount gt 0>
    <!--- Clear the array --->
    <cfloop index="ix" from="1" to="11"> <!--- Total of 11 columns in Excel file
    --->
    <cfset DataArray[ix]="">
    </cfloop>
    <cfset ix = 0>
    <!--- Read the record into the array --->
    <cfloop index="CurColumn" list="#CurRecord#" delimiters="#columnChr#">
    <cfset ix = ix + 1>
    <cfset DataArray[ix] = Trim(curColumn)>
    <!--- Get rid of unwanted characters --->
    <cfset DataArray[ix] = Replace(DataArray[ix], colchr3, " ", "ALL")>
    <cfset DataArray[ix] = Replace(DataArray[ix], colchr4, "", "ALL")>
    </cfloop>

    <!--- Insert the data --->
    <cftransaction isolation="READ_COMMITTED">
    <cfquery name="InsertProducts" datasource="MyDSN">
    Insert Into temp_product_working_table
    (
    Product_ID,
    Product_Name,
    Customer_Lastname,
    Customer_Firstname,
    Customer_Phone,
    Customer_Email,
    Customer_Adr1,
    Customer_Adr2,
    Customer_City,
    Customer_State,
    Customer_Zip
    )
    Values
    (
    #DataArray[1]#,
    '#DataArray[2]#', '#DataArray[3]#',
    '#DataArray[4]#', '#DataArray[5]#',
    '#DataArray[6]#', '#DataArray[7]#',
    '#DataArray[8]#', '#DataArray[9]#',
    '#DataArray[10]#', '#DataArray[11]#'
    )
    </cfquery>
    </cftransaction>
    </cfif>
    </cfloop>

    Sponsored Links
    AZDeveloper Guest

  2. #2

    Default Re: Saving Excel data into MS SQL Server

    Why are you reading the file with CFFILE ? And I don't think that you reading
    properly the file structure.
    You should do this instead of CFFILE READ, CFLOOP, ETC:

    Insert Into temp_product_working_table
    (
    Product_ID,
    Product_Name,
    Customer_Lastname,
    Customer_Firstname,
    Customer_Phone,
    Customer_Email,
    Customer_Adr1,
    Customer_Adr2,
    Customer_City,
    Customer_State,
    Customer_Zip )
    SELECT * FROM OPENQUERY(Excel, 'SELECT column1, column2, column2 FROM ')

    Regards



    Sojovi Guest

  3. #3

    Default Re: Saving Excel data into MS SQL Server

    Thank you very much, Sojovi. I'll give it shot...
    AZDeveloper Guest

Similar Threads

  1. Replies: 2
    Last Post: March 7th, 12:43 PM
  2. Excel annoyance - why quotes around some data when saving as tab-delimited text?
    By yvan@ideasdesign.com in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 8th, 02:56 PM
  3. Move data from Excel to SQL Server via ASP
    By midwesthills in forum ASP Database
    Replies: 6
    Last Post: August 3rd, 08:18 PM
  4. Saving data on server. Before I start
    By Canbrit1000 in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 23rd, 10:23 PM
  5. Retrieving data from sql server to excel in vb.net
    By Anne in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 14th, 03:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •