Tuesday, March 27, 2012

Excel file import problem

Hello:
I need to import an Excel file to SQL Server.
The .xls file has the column names which contains
dot inside, like AAA.BBB. When I import this file
in SQL using DTS Import/Export tool, it creates a table
with column names like AAA#BBB.
So, during import process the dots substitutes with #.
Could you, please, give me a hint how to fix the problem?
Thanks,
GB"GB" wrote:

> Hello:
> I need to import an Excel file to SQL Server.
> The .xls file has the column names which contains
> dot inside, like AAA.BBB. When I import this file
> in SQL using DTS Import/Export tool, it creates a table
> with column names like AAA#BBB.
> So, during import process the dots substitutes with #.
> Could you, please, give me a hint how to fix the problem?
> Thanks,
> GB
>
> Do not use import/export in dts - nto very flexible.
Use Transform Data Task
1- sel. excel conn. (choose .xls doc)
2- sel. sql conn. (choose dbase and passwords if needed)
3-highlight excel conn and ctrl sql conn
4- sel. Transform Data task
5- open Trans data Task(double click arrow)
Source is excel - choose table/view (select sheet in excel) OR SQL Query
Destination is SQL - choose table name or create new (you can change created
name(usually default sheet1$ if you have no named tabs) to another name and
dts will load a new tab into excel)
Transformation shows links between cols from source to destin.
(open transform to make sure all is ok)
click OK for transformation.
arrow should be highlighted - just execute it !|||
"GB" wrote:

> Hello:
> I need to import an Excel file to SQL Server.
> The .xls file has the column names which contains
> dot inside, like AAA.BBB. When I import this file
> in SQL using DTS Import/Export tool, it creates a table
> with column names like AAA#BBB.
> So, during import process the dots substitutes with #.
> Could you, please, give me a hint how to fix the problem?
> Thanks,
> GB
>
> I forgot 1 piece.
In DataTransformation Task
When you select the destination - Create the table - change AAA#BBB to AAA.B
BB
OR if you have alot of columns - copy the whole create stmt to textpad -
replace all # with . and recopy into dts.

No comments:

Post a Comment