Import fails on varchar field

Hello,
I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
file into SQL Server 2005. This file is a commercial marketing
information database that contains about 76,000 rows. I converted the
xls files provided into text files before the import.
However, I keep getting this error when trying to import:
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
conversion for column "CBSAName" returned status value 4 and status
text "Text was truncated or one or more characters had no match in the
target code page.".
(SQL Server Import and Export Wizard)
I looked at the text file and noticed that strings are delmited by
double-quotes, so I changed all the double-quotes to single quotes.
However the error persists.
I'm not sure why it's complaining about the field, as it is just a
simple text field delimited by single quotes.
Any ideas? I seem to have lots of problems with import in 2005.
Thanks!Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
between the two. If it is a VARCHAR column, what is its specified size? Is
it possible that there is data in the tab-delimited file that exceeds the
defined size of the column in the table? If it is tab-delimited, why are
the values enclosed in quotes (single or double)?
<tootsuite@.gmail.com> wrote in message
news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> Hello,
> I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> file into SQL Server 2005. This file is a commercial marketing
> information database that contains about 76,000 rows. I converted the
> xls files provided into text files before the import.
> However, I keep getting this error when trying to import:
> Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> conversion for column "CBSAName" returned status value 4 and status
> text "Text was truncated or one or more characters had no match in the
> target code page.".
> (SQL Server Import and Export Wizard)
> I looked at the text file and noticed that strings are delmited by
> double-quotes, so I changed all the double-quotes to single quotes.
> However the error persists.
> I'm not sure why it's complaining about the field, as it is just a
> simple text field delimited by single quotes.
> Any ideas? I seem to have lots of problems with import in 2005.
> Thanks!
>|||Yeah, it was the single quotes - thanks
Aaron Bertrand [SQL Server MVP] wrote:
> Is it a TEXT column or a VARCHAR column? Your narrative seems to alternate
> between the two. If it is a VARCHAR column, what is its specified size? Is
> it possible that there is data in the tab-delimited file that exceeds the
> defined size of the column in the table? If it is tab-delimited, why are
> the values enclosed in quotes (single or double)?
>
>
> <tootsuite@.gmail.com> wrote in message
> news:1166647034.331993.313170@.n67g2000cwd.googlegroups.com...
> > Hello,
> >
> > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > file into SQL Server 2005. This file is a commercial marketing
> > information database that contains about 76,000 rows. I converted the
> > xls files provided into text files before the import.
> >
> > However, I keep getting this error when trying to import:
> >
> > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > conversion for column "CBSAName" returned status value 4 and status
> > text "Text was truncated or one or more characters had no match in the
> > target code page.".
> > (SQL Server Import and Export Wizard)
> >
> > I looked at the text file and noticed that strings are delmited by
> > double-quotes, so I changed all the double-quotes to single quotes.
> > However the error persists.
> >
> > I'm not sure why it's complaining about the field, as it is just a
> > simple text field delimited by single quotes.
> >
> > Any ideas? I seem to have lots of problems with import in 2005.
> >
> > Thanks!
> >|||I know you can import xls directly however if you've ever had your
"text" converted to float in the process sometimes it's just easier to
go with the txt format so you don't have to map fields
John Bell wrote:
> Hi
> It looks like you text has some data that can not be imported within the
> text field. You may want to look how far the import got and then look at the
> data on the row that failed.
> You can import directly from the excel file without converting the data.
> John
>
> "tootsuite@.gmail.com" wrote:
> > Hello,
> >
> > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > file into SQL Server 2005. This file is a commercial marketing
> > information database that contains about 76,000 rows. I converted the
> > xls files provided into text files before the import.
> >
> > However, I keep getting this error when trying to import:
> >
> > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > conversion for column "CBSAName" returned status value 4 and status
> > text "Text was truncated or one or more characters had no match in the
> > target code page.".
> > (SQL Server Import and Export Wizard)
> >
> > I looked at the text file and noticed that strings are delmited by
> > double-quotes, so I changed all the double-quotes to single quotes.
> > However the error persists.
> >
> > I'm not sure why it's complaining about the field, as it is just a
> > simple text field delimited by single quotes.
> >
> > Any ideas? I seem to have lots of problems with import in 2005.
> >
> > Thanks!
> >
> >|||This is a one time thing, but yes I'd like to know how to automate an
import!
John Bell wrote:
> Hi
> I assume you are doing this manually and not wanting to automate it?
> John
> "tootsuite@.gmail.com" wrote:
> > I know you can import xls directly however if you've ever had your
> > "text" converted to float in the process sometimes it's just easier to
> > go with the txt format so you don't have to map fields
> >
> > John Bell wrote:
> > > Hi
> > >
> > > It looks like you text has some data that can not be imported within the
> > > text field. You may want to look how far the import got and then look at the
> > > data on the row that failed.
> > >
> > > You can import directly from the excel file without converting the data.
> > >
> > > John
> > >
> > >
> > >
> > > "tootsuite@.gmail.com" wrote:
> > >
> > > > Hello,
> > > >
> > > > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > > > file into SQL Server 2005. This file is a commercial marketing
> > > > information database that contains about 76,000 rows. I converted the
> > > > xls files provided into text files before the import.
> > > >
> > > > However, I keep getting this error when trying to import:
> > > >
> > > > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > > > conversion for column "CBSAName" returned status value 4 and status
> > > > text "Text was truncated or one or more characters had no match in the
> > > > target code page.".
> > > > (SQL Server Import and Export Wizard)
> > > >
> > > > I looked at the text file and noticed that strings are delmited by
> > > > double-quotes, so I changed all the double-quotes to single quotes.
> > > > However the error persists.
> > > >
> > > > I'm not sure why it's complaining about the field, as it is just a
> > > > simple text field delimited by single quotes.
> > > >
> > > > Any ideas? I seem to have lots of problems with import in 2005.
> > > >
> > > > Thanks!
> > > >
> > > >
> >
> >|||Hi
I assume you are using the Import/Export Wizard, in which case on the last
step you can save the task as a SSIS package which can then be scheduled or
editted in BI Studio. Unless you import directly from the excel spreadsheet
you are not going to fully automate this process, you have the ability to
specify exactly what you need regarding column settings on the advanced
screen of the wizard.
John
"tootsuite@.gmail.com" wrote:
> This is a one time thing, but yes I'd like to know how to automate an
> import!
> John Bell wrote:
> > Hi
> >
> > I assume you are doing this manually and not wanting to automate it?
> >
> > John
> >
> > "tootsuite@.gmail.com" wrote:
> >
> > > I know you can import xls directly however if you've ever had your
> > > "text" converted to float in the process sometimes it's just easier to
> > > go with the txt format so you don't have to map fields
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > It looks like you text has some data that can not be imported within the
> > > > text field. You may want to look how far the import got and then look at the
> > > > data on the row that failed.
> > > >
> > > > You can import directly from the excel file without converting the data.
> > > >
> > > > John
> > > >
> > > >
> > > >
> > > > "tootsuite@.gmail.com" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > I am trying to import my "zip-codes-database-DELUXE" tab-delimited text
> > > > > file into SQL Server 2005. This file is a commercial marketing
> > > > > information database that contains about 76,000 rows. I converted the
> > > > > xls files provided into text files before the import.
> > > > >
> > > > > However, I keep getting this error when trying to import:
> > > > >
> > > > > Error 0xc02020a1: Data Flow Task: Data conversion failed. The data
> > > > > conversion for column "CBSAName" returned status value 4 and status
> > > > > text "Text was truncated or one or more characters had no match in the
> > > > > target code page.".
> > > > > (SQL Server Import and Export Wizard)
> > > > >
> > > > > I looked at the text file and noticed that strings are delmited by
> > > > > double-quotes, so I changed all the double-quotes to single quotes.
> > > > > However the error persists.
> > > > >
> > > > > I'm not sure why it's complaining about the field, as it is just a
> > > > > simple text field delimited by single quotes.
> > > > >
> > > > > Any ideas? I seem to have lots of problems with import in 2005.
> > > > >
> > > > > Thanks!
> > > > >
> > > > >
> > >
> > >
>