Trying to import an excel file into SQL Server. For some reason, this
column is being considered Double(15) and when it imports, it's losing text
data. I'd like it to just be treated as any form of text and import
exactly. Right now all of the numeric ones go through but the hybrid ones
are blank. Is this possible?
KM
--
154+a
84+b
154
84
15
15
84Are you using a script to import or just the import wizard from SQL? If you
use the import wizard, are you predefining the fields before you import but
after you select the flat file source. (first screen but you need to scroll
down to the Advanced - Data source: - format each column)
If you have run through the import and failed, the table is already created
with these predefined fields and you will need to modify them to fit the data.
The above assume you use the wizard (right click - database - choose Import)
--
Regards,
Jamie
"James" wrote:
> Trying to import an excel file into SQL Server. For some reason, this
> column is being considered Double(15) and when it imports, it's losing text
> data. I'd like it to just be treated as any form of text and import
> exactly. Right now all of the numeric ones go through but the hybrid ones
> are blank. Is this possible?
> KM
> --
> 154+a
> 84+b
> 154
> 84
> 15
> 15
> 84
>
>|||These little scripts may be of relevance:
http://sqlblog.com/blogs/linchi_shea/archive/2007/03/06/a-little-scripting-saves-the-day.aspx
Linchi
"James" wrote:
> Trying to import an excel file into SQL Server. For some reason, this
> column is being considered Double(15) and when it imports, it's losing text
> data. I'd like it to just be treated as any form of text and import
> exactly. Right now all of the numeric ones go through but the hybrid ones
> are blank. Is this possible?
> KM
> --
> 154+a
> 84+b
> 154
> 84
> 15
> 15
> 84
>
>|||Hi Jamie,
I am facing the same problem while I import excel to sql server 2000. I
couldnt find the option (first screen but you need to scroll down to the
Advanced - Data source: - format each column) what you have mentioned here.
For workaround before I import I prefix some character for instance "#" or
any other character for these kind of columns which make DTS aware of varchar
datatype and once its imported I fire update query to remove this char. so
far I havent been able to figure out any out of the box solution from SQL
server.
"thejamie" wrote:
> Are you using a script to import or just the import wizard from SQL? If you
> use the import wizard, are you predefining the fields before you import but
> after you select the flat file source. (first screen but you need to scroll
> down to the Advanced - Data source: - format each column)
> If you have run through the import and failed, the table is already created
> with these predefined fields and you will need to modify them to fit the data.
> The above assume you use the wizard (right click - database - choose Import)
> --
> Regards,
> Jamie
>
> "James" wrote:
> > Trying to import an excel file into SQL Server. For some reason, this
> > column is being considered Double(15) and when it imports, it's losing text
> > data. I'd like it to just be treated as any form of text and import
> > exactly. Right now all of the numeric ones go through but the hybrid ones
> > are blank. Is this possible?
> >
> > KM
> > --
> > 154+a
> > 84+b
> > 154
> > 84
> > 15
> > 15
> > 84
> >
> >
> >|||Bhavesh,
Worst case scenario for me... when I can't get it to import - I save the
Excel as a CSV - check the file over visually (sometimes users have comma's
embedded in text which will throw off the import) and then change the
extension from CSV to TXT and import it as a comma-delimited Text file. It
isn't often I need to do so but it comes in handy when other methods fail.
--
Regards,
Jamie
"Bhavesh" wrote:
> Hi Jamie,
> I am facing the same problem while I import excel to sql server 2000. I
> couldnt find the option (first screen but you need to scroll down to the
> Advanced - Data source: - format each column) what you have mentioned here.
> For workaround before I import I prefix some character for instance "#" or
> any other character for these kind of columns which make DTS aware of varchar
> datatype and once its imported I fire update query to remove this char. so
> far I havent been able to figure out any out of the box solution from SQL
> server.
>
> "thejamie" wrote:
> > Are you using a script to import or just the import wizard from SQL? If you
> > use the import wizard, are you predefining the fields before you import but
> > after you select the flat file source. (first screen but you need to scroll
> > down to the Advanced - Data source: - format each column)
> >
> > If you have run through the import and failed, the table is already created
> > with these predefined fields and you will need to modify them to fit the data.
> >
> > The above assume you use the wizard (right click - database - choose Import)
> > --
> > Regards,
> > Jamie
> >
> >
> > "James" wrote:
> >
> > > Trying to import an excel file into SQL Server. For some reason, this
> > > column is being considered Double(15) and when it imports, it's losing text
> > > data. I'd like it to just be treated as any form of text and import
> > > exactly. Right now all of the numeric ones go through but the hybrid ones
> > > are blank. Is this possible?
> > >
> > > KM
> > > --
> > > 154+a
> > > 84+b
> > > 154
> > > 84
> > > 15
> > > 15
> > > 84
> > >
> > >
> > >|||Linchi,
Nice little article.
We have someone in our company that does the imports by creating the insert
statement in the Excel sheet and into a column that precedes the columns he
imports... he gets a great deal of work done that way.
--
Regards,
Jamie
"Linchi Shea" wrote:
> These little scripts may be of relevance:
> http://sqlblog.com/blogs/linchi_shea/archive/2007/03/06/a-little-scripting-saves-the-day.aspx
> Linchi
> "James" wrote:
> > Trying to import an excel file into SQL Server. For some reason, this
> > column is being considered Double(15) and when it imports, it's losing text
> > data. I'd like it to just be treated as any form of text and import
> > exactly. Right now all of the numeric ones go through but the hybrid ones
> > are blank. Is this possible?
> >
> > KM
> > --
> > 154+a
> > 84+b
> > 154
> > 84
> > 15
> > 15
> > 84
> >
> >
> >
