May 15, 2012 at 8:56 pm
Hey All Expertises,
I am facing problem to load a batch of text in Excel into sql table using SSIS. Here is in Excel look like:
Date KPI Plant Remarks
01-04 A 1 U3 not is down
01-04 B 1
02-04 A 2 L3 is down due to
1) no material
2) no schedule
3) waiting for vendor
As you can see, the Remarks column can have "enter" value. How to load all this into SQL table with same display as in Excel?
Help needed please. Thanks.
May 15, 2012 at 10:51 pm
khorkh888 (5/15/2012)
Hey All Expertises,I am facing problem to load a batch of text in Excel into sql table using SSIS. Here is in Excel look like:
Date KPI Plant Remarks
01-04 A 1 U3 not is down
01-04 B 1
02-04 A 2 L3 is down due to
1) no material
2) no schedule
3) waiting for vendor
As you can see, the Remarks column can have "enter" value. How to load all this into SQL table with same display as in Excel?
Help needed please. Thanks.
With very great difficulty, because:
1) Your column delimiter appears to be a space, which also occurs in the comments.
2) Your data is spread across multiple rows (the 02-04 entry) - SQL Server doesn't usually store mixed data in the same table, so you'll need to decide how you want that to look in SQL Server before proceeding.
To do a half-decent job, you'll probably need to use a script component to analyse the data as it comes through and decide how to split it.
May 16, 2012 at 12:52 am
Hey Phil,
The actual data for date 02-04, is actually 1 record only. Just that the Remarks column is having multiple line due to the "enter" .
So, means the suggestion is to use the SQL script ? Please advice. Thanks.
May 16, 2012 at 1:54 am
Phil suggested a .NET script, not a SQL script.
However, if the data is in one cell, it should load (with CHAR(10) and CHAR(13) values in it).
Did you try to load the data with SSIS?
Did you encounter any errors or difficulties?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 16, 2012 at 2:42 am
Hi Koen,
Yes. I am using SSIS to load. Here is the error message from the package:
[Excel Source [1]] Error: Failed to retrieve long data for column "Remark".
[Excel Source [1]] Error: There was an error with output column "Remark" (24) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".
[Excel Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Remark" (24)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Remark" (24)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
I had did the conversion for the Remark column to "DT_Text" due to the long string.
Hope this may help. Thanks.
May 16, 2012 at 5:10 am
I tried it and I can import the data just fine. The "line breaks" from the Excel file are shown as squares, indicating that they are special characters. You can filter those out with a derived column.
Which version of Excel are you using?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 22, 2012 at 11:27 pm
Hi even i tried loading it worked fine i was trying with Microsoft Excel version 2007
With regards
Juvenita
May 23, 2012 at 12:15 am
Dear All,
Finally I make it also !!. The initial problem is due to the long sting. I just make a unicode Text. Now everthing is loaded :-).
thanks all for the valuable reply.
Regars..
May 23, 2012 at 12:22 am
khorkh888 (5/23/2012)
Dear All,Finally I make it also !!. The initial problem is due to the long sting. I just make a unicode Text. Now everthing is loaded :-).
thanks all for the valuable reply.
Regars..
Ah yes, the magical 255 character limit 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply