Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Insert Into statement from Excel file

    Hi all.
    I am having trouble with an insert statement involving an excel file.

    Code:
    Insert into tpa00175 (intJobKey,BusinessFormKey,chrCustPONumber,chrExemptNo,chrJobDescription1, chrJobName,
    chrJobNumber,chrNxtVariationNum,chrUserDef1,chrUserDef2,hrUserDef3,chrUserDef4,chrUserDef5,chrUserDef6,chrUserDef7,
    chrUserDef8,chrUserDef9,chrUserDef10,chrUserDef11,chrUserDef12,chrUserDef13,CompanyID,CustKey,dteCmpltnDueDate,
    dteCompletionDate,dteJobCommencement,dteReviewDate,intClientContctKey,intDivisionKey,intEmployeeKey,intJobClassKey,
    intMasterJobKey,MSTaxClassKey,numActualVarCosts,numActualVarSales,numCostsRecognized,numCreditLimit,numJobBudget,
    numJTDActualCost,numJTDActualSale,numJTDCommitedCsts,numJTDEstimatedCst,numJTDEstimatedSal,numLaborActual,
    numLaborEstimate,numLaborForcast,numLastForcastCost,numLastForcastSale,numMaterialActual,numMaterialEst,
    numMaterialForcast,numOverHeadActual,numOverHeadEst,numOverHeadForcast,numPercentComplete,numRetentionAmtTTD,
    numRetainageAmt,numRetentionBldTTD,numRetainagePrcnt,numSalesRecognized,numUser1ActlClass,numUser1EstClass,
    numUser1FrcstClass,numUser2ActlClass,numUser2EstClass,numUser2FrcstClass,numUser3ActlClass,numUser3EstClass,
    numUser3FrcstClass,OpenOrClosed,PostIMGL,RetentionAcctKey,siInvoiceLayout,siJobStatus,siJobType,siMarginType,
    siProfitRecMethod,siRetainageType,STaxClassKey,tiProfRecByJobLine,TranID,UpdateCounter,WhseKey) 
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Book1.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')
    its a long one for sure. 2 questions.

    1)Is there a way to just say insert all rather than naming each row like the above example.

    2)With this above example i am getting a syntax error near 'From'. What am I missing??

    Thanks alot.

    Tibor

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are missing the SELECT clause in your INSERT statement:
    Code:
    Insert into tpa00175
    	(intJobKey,
    	BusinessFormKey,
    	chrCustPONumber,
    	chrExemptNo,
    	.
    	.
    	.
    	UpdateCounter,
    	WhseKey) 
    SELECT	intJobKey,
    	BusinessFormKey,
    	chrCustPONumber,
    	chrExemptNo,
    	.
    	.
    	.
    	UpdateCounter,
    	WhseKey
    FROM	OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Book1.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2006
    Posts
    82
    Hey thanks for the help. now i am getting this error which seems to be simple but its wierd

    Code:
    Cannot insert the value NULL into column 'UpdateCounter', table 'mas500_app.dbo.tPA00175'; column does not allow nulls. INSERT fails.
    The statement has been terminated.
    I know for a fact that there is no null values in that colum. this was imported out of the table itself so it would have never had null values to begin with.

    ok i went and tried to import using dts and it for some reason doesnt like the last row in the file. it says that it is all null but you can see the same line number it points to and see that it is populated. what gives?
    Last edited by tibor; 05-16-06 at 13:46.

  4. #4
    Join Date
    Mar 2006
    Posts
    82
    ok something was wrong with that excel file. i copied it over to another and it worked fine.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Beware of setting up any sort of automated process to load data from Excel into SQL Server. It's not a question of if the process will break, but when, and how often.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2006
    Posts
    82
    yeah ive just learned that the hard way, lol.

  7. #7
    Join Date
    Mar 2006
    Posts
    82
    well instead of an insert statement could i have that as a massive update statement???

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Doesn't address the underlying problem, which is trying to load uncontrolled data into a schema that enforces relationional integrity.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Mar 2006
    Posts
    82
    well what is uncontrolled. the fact that it comes from an excel file? or that i am blindly placing info into the table? sorry but im confused. the user imports from sql to the excel file, makes changes to a value, and imports back into sql. thats basically what my program does.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by tibor
    the user imports from sql to the excel file, makes changes to a value, and imports back into sql.
    That is uncontrolled. Excel puts no restrictions on what the user can enter into a cell, or how they rearrange/insert/delete columns, etc. One of the purposes of a data entry form for a database is to PREVENT the user from entering bad data.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2011
    Posts
    4

    Excel Build?

    With the last minor patch I have found that Excel now properly assumes the values that you were missing. Their default values tend to throw you off from time to time but this should help you bridge the gap.

    -Grahm
    Excel Statement

Posting Permissions

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