MWilliamsBolton
asked on
Missing Newline when importing Excel data
I have an excel file that has been created from a Sharepoint data source..
That excel file has a cell that includes a new line in the middle of the data.
When I import that data using the Import wizard in SQL 2008, that new line character is dropped.
Any ideas how I can prevent that from happening?
That excel file has a cell that includes a new line in the middle of the data.
When I import that data using the Import wizard in SQL 2008, that new line character is dropped.
Any ideas how I can prevent that from happening?
It may not be "dropped" but not translated correctly. I believe in SQL a "new line" as you describe it you should have a ENTER = CRLF (0D0A in hex) and not just a LF(0A in hex) as new line indicator. MAke sure you have the appropriate data in excel or chose the right import format for line terminator in sql then it should work fine.
ASKER
I tried to make the problem simpler by using Excel and that hasn't helped - just confused things.
I have written something that looks at every character in the SQL field and outputs the ASCII value.. I can now explain the problem a little better..
I have a text entry in Sharepoint that has a new line in the middle of it. When I export that to Excel and then import the Excel file, the new line is represented as LF (ascii 10).
However, when I import the data into SQL from Sharepoint using SSIS, the new line is replaced with a space (character 32). Do you know how I can stop this happening?
I have written something that looks at every character in the SQL field and outputs the ASCII value.. I can now explain the problem a little better..
I have a text entry in Sharepoint that has a new line in the middle of it. When I export that to Excel and then import the Excel file, the new line is represented as LF (ascii 10).
However, when I import the data into SQL from Sharepoint using SSIS, the new line is replaced with a space (character 32). Do you know how I can stop this happening?
ASKER
THe results from sharepoint will be passed to SSIS using XML. I suspect this will be part of the problem but I have no idea how to address it.. :-((
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The problem is I would need to use SSIS services to populate the Excel file. That process uses Sharepoint web services which uses XML. A new line is not recognised as a a new line when it is put in an XML file. It needs encoding which is not possible in Sharepoint without allowing the field to be formatted.. That is my only way forward I think. Thanks for your help..
ASKER
Thanks for your help..
If the input is an XML file and you don't need to mandatory generate your EXCEL file see maybe you could use the SP example below to read in the XML directly into your SQL database:
CREATE PROCEDURE dbo.my_XML_update
@XMLData NTEXT,
@myId SMALLINT
AS
SET NOCOUNT ON
-- Retrieve XML data
DECLARE @hocDoc INT
DECLARE @Comments NVARCHAR(2048)
EXEC SP_XML_PREPAREDOCUMENT @hocDoc OUTPUT, @XMLData
-- OPEN XML
-- Param1 is Pointer to DOC
-- Param2 is XML doc hiearchy
-- Param3 is Use Element Structure rather than Attributes
SELECT
ContactId,
ContactName
INTO
#tmpContact
FROM
OPENXML(@hocDoc, '/root/Contact', 2)
WITH (ContactId INTEGER, ContactName NVARCHAR(50))
--this is how you populate the contact xml
SELECT Contact.ContactId,
ISNULL(Contact.ContactName , '') AS ContactName
FROM #tmpContact Contact, Contact C
WHERE Contact.ContactId = C.Id
ORDER BY Contact.ContactName
FOR XML AUTO, ELEMENTS
-- Then you can do an INSERT
INSERT INTO Contact (Id, ContactName)
SELECT ContactId,ContactName
FROM #tmpContact
--or an UPDATE
UPDATE Contact SET ContactName = t.ContactName
FROM #tmpContact t
WHERE contact.Id = #tmpContact.ContactId
-- Drop the temp Table
DROP TABLE #tmpContact
-- and MUST release XML doc to avoid memeory issues
EXEC SP_XML_REMOVEDOCUMENT @hocDoc
GO
CREATE PROCEDURE dbo.my_XML_update
@XMLData NTEXT,
@myId SMALLINT
AS
SET NOCOUNT ON
-- Retrieve XML data
DECLARE @hocDoc INT
DECLARE @Comments NVARCHAR(2048)
EXEC SP_XML_PREPAREDOCUMENT @hocDoc OUTPUT, @XMLData
-- OPEN XML
-- Param1 is Pointer to DOC
-- Param2 is XML doc hiearchy
-- Param3 is Use Element Structure rather than Attributes
SELECT
ContactId,
ContactName
INTO
#tmpContact
FROM
OPENXML(@hocDoc, '/root/Contact', 2)
WITH (ContactId INTEGER, ContactName NVARCHAR(50))
--this is how you populate the contact xml
SELECT Contact.ContactId,
ISNULL(Contact.ContactName
FROM #tmpContact Contact, Contact C
WHERE Contact.ContactId = C.Id
ORDER BY Contact.ContactName
FOR XML AUTO, ELEMENTS
-- Then you can do an INSERT
INSERT INTO Contact (Id, ContactName)
SELECT ContactId,ContactName
FROM #tmpContact
--or an UPDATE
UPDATE Contact SET ContactName = t.ContactName
FROM #tmpContact t
WHERE contact.Id = #tmpContact.ContactId
-- Drop the temp Table
DROP TABLE #tmpContact
-- and MUST release XML doc to avoid memeory issues
EXEC SP_XML_REMOVEDOCUMENT @hocDoc
GO