March 13, 2017 at 11:45 am
I'm trying to import data from a spreadsheet, but the last column containing URLs is giving problems
Error 0xc020901c: Data Flow Task 1: There was an error with Source - Sheet1$.Outputs[Excel Source Output].Columns on Source - Sheet1$.Outputs[Excel Source Output]. The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
But the target column is nvarchar 4000 , much longer than needed. I saw 1 suggestion online to make a registry change to increase the sample size, but that didn't help.
I assume I have some bad data of some kind, since some subsets of data work..... but how can I find them ? And why does SQL care ? Thoughts ? Tips ? Suggestion ?
March 13, 2017 at 11:53 am
It's probably not the destination but the source. ACE is a "wonferful" thing. What length does the source have to
For your URL column and what is the longest length your excel file. I would guess that there is a URL longer than ACE has "guessed".
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 13, 2017 at 12:27 pm
A couple of URLs are 350 characters. The import wizard has a default of 255.
Isn't there some way to tell SQL:
"Hey SQL, just take all the frikkin data and import it !...... I told you my target column is 4000 ... What's the problem ?"
It's 2017 for cryin' out loud.
March 13, 2017 at 12:28 pm
Thom A - Monday, March 13, 2017 11:53 AMIt's probably not the destination but the source. ACE is a "wonferful" thing. What length does the source have to
For your URL column and what is the longest length your excel file. I would guess that there is a URL longer than ACE has "guessed".
To get round this issue, move the row with the longest URL to the top row of data and go back into editing the package in SSDT.
The ACE (oh, the irony) driver will then work out that the column width should be increased and it should work out OK.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 13, 2017 at 12:46 pm
This is supposed to be a daily job, run automatically from spreadsheets with new data.
SQL should be smart enough by now to look at the destination and at least import enough to match that.
Looks as though if I save the spreadsheet as Tab-Delimited, then I can set the column width to handle longer strings, but .XLSX doesn't work. That's going to be my current work-around unless there's a better idea.
March 13, 2017 at 1:19 pm
homebrew01 - Monday, March 13, 2017 12:46 PMThis is supposed to be a daily job, run automatically from spreadsheets with new data.
SQL should be smart enough by now to look at the destination and at least import enough to match that.Looks as though if I save the spreadsheet as Tab-Delimited, then I can set the column width to handle longer strings, but .XLSX doesn't work. That's going to be my current work-around unless there's a better idea.
The problem isn't SQL, it's the Microsoft ACE drivers, a separate tool used for interacting with Office documents. One alternative, which i have done in the past, is creating your on script source, and coding everything manually. You have more control then.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
March 13, 2017 at 1:54 pm
homebrew01 - Monday, March 13, 2017 12:27 PMA couple of URLs are 350 characters. The import wizard has a default of 255.
Isn't there some way to tell SQL:"Hey SQL, just take all the frikkin data and import it !...... I told you my target column is 4000 ... What's the problem ?"
It's 2017 for cryin' out loud.
Don't use the import wizard, build the package in Developer and you can specify the column lengths. The import wizard is pretty terrible for anything but the most basic tasks.
March 13, 2017 at 2:23 pm
ZZartin - Monday, March 13, 2017 1:54 PMhomebrew01 - Monday, March 13, 2017 12:27 PMA couple of URLs are 350 characters. The import wizard has a default of 255.
Isn't there some way to tell SQL:"Hey SQL, just take all the frikkin data and import it !...... I told you my target column is 4000 ... What's the problem ?"
It's 2017 for cryin' out loud.Don't use the import wizard, build the package in Developer and you can specify the column lengths. The import wizard is pretty terrible for anything but the most basic tasks.
When it's an Excel file, you cannot override the ACE driver's suggested data types, based on the file you're currently connected to.
(Actually, you can, if you are prepared to edit the .DTSX file in a text editor. But even then, your changes will get clobbered as soon as the next dev opens the package in SSDT again.)
Using a text file, everything works as you would expect and you do have control over variable datatypes in the pipeline.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 13, 2017 at 2:57 pm
Can I simply say that I hate importing data from an Excel spreadsheet? I much prefer a delimited text file; comma, pipe, tab. The type of delimiter depends on the data since comma delimited files with commas embedded in strings are a pain for one.
March 13, 2017 at 3:09 pm
Lynn Pettis - Monday, March 13, 2017 2:57 PMCan I simply say that I hate importing data from an Excel spreadsheet? I much prefer a delimited text file; comma, pipe, tab. The type of delimiter depends on the data since comma delimited files with commas embedded in strings are a pain for one.
The preferred delimiter is at least 10 characters long, none of which can be typed on a standard qwerty keyboard.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply