Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2001
    Location
    Baton Rouge
    Posts
    13

    Unanswered: importing a text file

    Hi
    I am trying to import a text file into sql server..but i am not able to do so..because the size of the var type is 8000..
    and i have too many columns to change the variable manually
    thus i copied the create table query from the import window
    and i got an error
    The table 'test' has been created but its maximum row size (25863) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

    can some help
    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: importing a text file

    Did you consider to use the memo data type TEXT instead VARCHAR?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is you column defined as char or is it defined as varchar?

    A char column always requires the same amount of space, equivalent to its defined length.

    A varchar column only requires as much space as the data it holds.

    Are you trying to import an entire record into a single field? If you have a single field in your source data that is 8000+ characters, your only option is to split it or use a text or image datatype.

    If you are still having problems, post your table definition and the sql you using to import the data.

    blindman

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I don't think SQL would even allow you to create a table of the specified size with CHAR. Have you tried it yourself, bm?

  5. #5
    Join Date
    Nov 2001
    Location
    Baton Rouge
    Posts
    13
    Thank you for all the response..
    the solution to my problem..I edit the create table query during my import process and change the 8000 to 255 and then with that edited query i make an empty table and then i append my orginal text file with data into that empty table..
    Well it is a long process..but it keeps going.
    thanks for the help

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Are you saying that a maximum size of 255 is sufficient for you? If not, I repeat myself, consider to use TEXT instead of VARCHAR. It allows you to import texts of variable length without getting on the row length limits.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The text and image type should not be used unless absolutely necessary. They require additional overhead, and have greatly limited functionality compared to char and varchar.

    blindman

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I'd say, it's a pretty blunt statement. It invalidates the very presence of existence of companies that specialize in DMS (document management systems).

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Blunt yes, but not completely dull. Document Management is an area where the use of text and image data may be absolutely necessary.

    blindman

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Document Management capabilities are present in many software products. Actually, those that are least reliable are the ones that avoid the usage of datatypes that are targeting this specific need, - to store large volumes of data. I've seen tricks like storing paths to actual document locations, or parsing text documents into 70-character lines. Cute, but if path is stored, - there is no guarantee for integrity of the referenced document, and in case of parsing - very resource-intensive in respect to drastic growth of such tables while loosing any formating information. Contrary to that, here we even store web pages in text fields and scan paper claims and store them into image. You also mentioned limited functionality? What do you mean? Each datatype should be treated accordingly, so if you attempt to treat text field using char/varchar approach, - sure, you loose functionality. But not because of "limited functionality" of the datatype... Wonder, because of what?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    rdjabarov,

    All the examples you mentioned are legitimate uses of text and image data.

    As far as limited functionality, try indexing your text fields.
    ...or sorting them
    ...or using the LIKE operator
    ...or concatenating them

    blindman

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    bm: operations you mentioned are not designed for TEXT and IMAGE datatypes. But I like your way of looking at it:

    - can you multiply a CHAR field?
    - can you index a BIT field?
    - can you store 2G of data into VARCHAR field?

    Good luck answering

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    rdjabarov,

    No. I use the appropriate datatype for the data. I recommend that you do the same. If you are using text and image columns in your design when you don't need to, then I pity the poor DBA who has to clean up after you when you get canned.

    blindman

  14. #14
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    By the way: what about a text-indexed table, or the powerful CONTAINS() function searching for words in a text field ?!

    However, we shouldn't bother NosWal anymore. If anybody still has to say about the (ab)use of data types, I suggest to open a new thread.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    bm: you're REALLY on a mission. You just never answered what its name. What are you trying to say? It looks like you adore listening to yourself. I suggest call-forwarding services, until your office phone gets reassigned to your replacement. Then, - it'll be just this forum, until your internet connection gets cut due to late payments. Anything else you want to say please take it offline.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •