Post reply

The Import Wizard

  • November 6, 2014 at 1:40 pm

    #1757085

    Koen Verbeeck (11/6/2014)

    TomThomson (11/6/2014)

    Good question.

    It raises an issue about the quality of this wizard: why on earth doesn't it read metadata for the target table and set lengths accordingly? It shouldn't be a permissions issue, because if I can write into a table I'd better be allowed to discover the column widths. So this wizard seems poorly thought out.

    There is such a feature in the Advanced section. There you can click a button and it will scan the flat file and determine the column widths.

    Not exactly a great solution...

    That would be OK if the idea was to create a table which could hold teh data in the flat file. If I'm importing into a pre-existing table it would be better to use the widths determined by that table, and then truncating or failing (for both variable length column types and fixed length types) and padding or failing (only for fixed length types) would be options that made sense; with width determined by the flat file those options can't be provided because neither truncation nor padding would be in teh import code, they would be in an implicit conversion, which can not be made to fail on pad nor to succeed on truncate.

    Tom

  • November 7, 2014 at 5:51 am

    #1757176

    As I recall part of the problem is it depends where in the file the longer records are. My recollection is that the first few records (I don't know how many exactly) form the basis for the source sizes, so if the longer values are at the top, the problem doesn't occur. This can cause a real headache for troubleshooting as some imports will appear to work and others won't, for no apparent reason.

  • November 7, 2014 at 7:58 am

    #1757222

    paul.goldstraw (11/7/2014)

    As I recall part of the problem is it depends where in the file the longer records are. My recollection is that the first few records (I don't know how many exactly) form the basis for the source sizes, so if the longer values are at the top, the problem doesn't occur. This can cause a real headache for troubleshooting as some imports will appear to work and others won't, for no apparent reason.

    That problem goes back a long way. I never use the wizard because I find BULK INSERT to be both reliable and repeatable.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 7, 2014 at 1:21 pm

    #1757254

    TomThomson (11/6/2014)

    Good question.

    It raises an issue about the quality of this wizard: why on earth doesn't it read metadata for the target table and set lengths accordingly? It shouldn't be a permissions issue, because if I can write into a table I'd better be allowed to discover the column widths. So this wizard seems poorly thought out.

    Indeed

  • November 9, 2014 at 12:41 am

    #1757329

    My 1st guess was correct, good to know somthing new, as I never used flat file as a data source b4 in this wizard, thanx 4 the question.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • November 10, 2014 at 7:34 am

    #1757428

    TomThomson (11/6/2014)

    Good question.

    It raises an issue about the quality of this wizard: why on earth doesn't it read metadata for the target table and set lengths accordingly? It shouldn't be a permissions issue, because if I can write into a table I'd better be allowed to discover the column widths. So this wizard seems poorly thought out.

    I agree. We shouldn't have to keep changing that field. In fact, what's wrong with just keeping the default at some humungous value? Isn't it just a max value allowed limitation?

  • November 13, 2014 at 9:24 am

    #1758302

    TomThomson (11/6/2014)

    Good question.

    It raises an issue about the quality of this wizard: why on earth doesn't it read metadata for the target table and set lengths accordingly? It shouldn't be a permissions issue, because if I can write into a table I'd better be allowed to discover the column widths. So this wizard seems poorly thought out.

    And don't get me started with the problems I've had where SQL Server reads metadata from a portion of my Excel or flat files when running an OPENROWSET query against them. The not infrequent result is a type conversion error or truncation issue further down the file.

    Thankfully, IMEX=1 (Excel) and MaxScanRows=0 (flat files, more info. here) force SQL Server to scan the entire file. Then I can at least pull everything into a staging table and run data checks with T-SQL.

    Rich

  • November 16, 2014 at 8:37 am

    #1758831

    Nice Question, Steve. Good One. Thanks.

    Thanks.

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply