SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTS to import Data from excel


DTS to import Data from excel

Author
Message
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4472 Visits: 1366
Hi,

I have one excel that has data like this:

ADM14021360066,208930705 502151 Abraçadeiras Plasticas Wurth 280x4,8 UN 1000.1 NOVO 200 Camião
ADM14021360066,208930706 502161 Abraçadeiras Plasticas Wurth 360x4,8 UN 1000.1 NOVO 300 Camião
ADM14021360066,208930707 2 Barra Reparação Magnum UN 1000.1 NOVO 2 Camião
ADM14021360066,208930708 3 Bicos Cola UN 1000.1 NOVO 70 Camião
ADM14021360066,208930709 4 Bidon Limpeza Travões Wurth 20Lt UN 1000.1 NOVO 1 Camião
ADM14021360066,208930710 7 Clip Autocolante UN 1000.1 NOVO 64 Camião
ADM14021360066,208930711 147334 Cola Fernador Azul Berner UN 1000.1 NOVO 1 Camião
ADM14021360066,208930712 147321 Cola Fernador Verde Berner UN 1000.1 NOVO 1 Camião
ADM14021360066,208930713 89309 Cola Klebfix Wurth UN 1000.1 NOVO 2 Camião
ADM14021360066,208930714 81 Cola Loctite Preta UN 1000.1 NOVO 1 Camião
ADM14021360066,208930715 0893449f Cola Metal Liquido Wurth1 UN 1000.1 NOVO 1 Camião
ADM14021360066,208930716 4242 Cola Past-o-Fix Berner UN 1000.1 NOVO 0 Camião
ADM14021360066,208930717 8963092f Cola Plastofix Wurth UN 1000.1 NOVO 2 Camião

There are several values that are imported as null on column codigo.


For instance, the code that is on excel as 8963092f is imported to the column codigo as null.

Can someone explain why?


thanks,

Pedro


P.S table code:


CREATE TABLE [dbo].[table2] (
[STSTAMP] [char] (25) COLLATE Latin1_General_CI_AS NULL ,
[CODIGO] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[DESIGNACAO] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[UNIDADE] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[FAMILIA] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[ESTADO] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[QUANTIDADE] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[LOCALIZACAO] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20823 Visits: 7660
Excel importing is insanely fussy and nearly impossible to remotely troubleshoot, but I'll try a bit. What's the import designation for that field? VARCHAR(255) or VARCHAR(MAX)? Is it possibly int or something else?

Must you import from excel or can you have your users save the files as CSVs? They can do that right from Excel when they're done fussing with the file.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4472 Visits: 1366
Dear Craig,

Thanks.

I have converted the file to CSV and it works.
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20823 Visits: 7660
Glad that the workaround is effective for you. Excel and its data drivers tend to be... aggravating to work with. Good luck! :-)


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search