Hi,
I need to load data from excel files which will be provided by a number (around 100 monthly) of external suppliers, so we don't get 100% control over the files themselves.
What my solution involves is copying the excel file to a common name (e.g. supplierExcel.xls), turning this into a pipe delimited txt and then loading the txt. I had trouble switching files when trying to load directly from excel.
All these files should arrive in the same format of 36 fields and of course in the right order; there will be rejections if they fail.
I've come across a problem extracting the data from excel where I'm getting 'the value could not be converted because of a potential loss of data' on field 1. It only happens on excel files where there is a quote mark as the first character and I have loaded other files quite happily without the quotemark.
Has anyone seen this before? Is this a known issue and how can I get around it, without recourse to manually changing the individual files?
Thanks
nathan
Hi,
It is difficult to answer, you should be more specific on a couple of things.
- Why cannot you load direct from Excel. this could be the only fix you need, You could use the File System task.
- How do you convert the Excel to text
- Do you use table load or SQL Command
- Do you need the quote when it is the initial char? In Excel it means you want to force the cell content to be text.
If you do not need the quote, strip it from the file.
declare @.sometext as varchar(255)
set @.sometext = '''There is an initial '' in this test string'
select substring(@.sometext, patindex('''%',@.sometext)+1,255)
if you need te quote, try to replace it by 3 quotes or make sure the entry is not too long, May be you could do this
declare @.sometext as varchar(255)
set @.sometext = '''There is an initial '' in this test string'
select case left(@.sometext,1) when char(39) then char(39) + char(39) + @.sometext else @.sometext end
.Philippe
