Problem with export of a table to a text file

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Daniel Wetzler
    #1

    Problem with export of a table to a text file

    Dear MSSQL- experts,

    I have a strange problem with SQLSERVER 2000.
    I tried to export a table of about 40000 lines into a text file using
    the Enterprise manager
    export assitant. I was astonished to get an exported text file of about
    400 MB instead 16 MB which is the normal size of that data.
    By examining this file with a text editor I found that the file
    included alongside the data of my table MANY zeros which caused the big
    file size.

    Does someone of you have an idea what could cause the export of
    trillions zeros into my textfile and how to only export the significant
    data of my table ?

    Best regards,

    Daniel

    Tags: None
    • Roy Harvey
      #2
      Re: Problem with export of a table to a text file

      Posting the table definition (CREATE TABLE) would help a lot.

      Roy Harvey
      Beacon Falls, CT

      On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
      <Daniel.Wetzler @sig.bizwrote:
      >Dear MSSQL- experts,
      >
      >I have a strange problem with SQLSERVER 2000.
      >I tried to export a table of about 40000 lines into a text file using
      >the Enterprise manager
      >export assitant. I was astonished to get an exported text file of about
      >400 MB instead 16 MB which is the normal size of that data.
      >By examining this file with a text editor I found that the file
      >included alongside the data of my table MANY zeros which caused the big
      >file size.
      >
      >Does someone of you have an idea what could cause the export of
      >trillions zeros into my textfile and how to only export the significant
      >data of my table ?
      >
      >Best regards,
      >
      >Daniel

        Comment

        • Daniel Wetzler
          #3
          Re: Problem with export of a table to a text file

          Hi Roy,

          thanks for your answer.

          Here's the table definition :


          CREATE TABLE [dbo].[Variables] (
          [ID] [varchar] (140) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [Var_ref] [int] NOT NULL ,
          [Group_Ref] [int] NULL ,
          [Machines_Ref] [int] NULL ,
          [TransfersID] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
          ,
          [Compatibility] [int] NULL ,
          [CompatibilityL1] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
          NULL ,
          [Category] [int] NULL ,
          [CategoryL1] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
          ,
          [AnalysisRespons ible] [tinyint] NULL ,
          [AnalysisPriorit y] [int] NULL ,
          [AnalysisDelta] [int] NULL ,
          [AnalysisDeltaVa lue] [varchar] (30) COLLATE
          SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [Value] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [Type] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [Length] [int] NULL ,
          [Address] [int] NULL ,
          [Offset] [int] NULL ,
          [Title1] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [Title2] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [Title3] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [Title4] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [AggregationTitl e1] [nvarchar] (100) COLLATE
          SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [AggregationTitl e2] [nvarchar] (100) COLLATE
          SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [AggregationTitl e3] [nvarchar] (100) COLLATE
          SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
          [AggregationTitl e4] [nvarchar] (100) COLLATE
          SQL_Latin1_Gene ral_CP1_CI_AS NULL
          ) ON [PRIMARY]
          GO

          Best regards,

          Daniel



          Roy Harvey schrieb:
          Posting the table definition (CREATE TABLE) would help a lot.
          >
          Roy Harvey
          Beacon Falls, CT
          >
          On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
          <Daniel.Wetzler @sig.bizwrote:
          >
          Dear MSSQL- experts,

          I have a strange problem with SQLSERVER 2000.
          I tried to export a table of about 40000 lines into a text file using
          the Enterprise manager
          export assitant. I was astonished to get an exported text file of about
          400 MB instead 16 MB which is the normal size of that data.
          By examining this file with a text editor I found that the file
          included alongside the data of my table MANY zeros which caused the big
          file size.

          Does someone of you have an idea what could cause the export of
          trillions zeros into my textfile and how to only export the significant
          data of my table ?

          Best regards,

          Daniel

            Comment

            • Roy Harvey
              #4
              Re: Problem with export of a table to a text file

              There were no surprises in the table definition. Storage of a row in
              SQL Server, with all the varying length columns at their upper limit,
              is around 2000 bytes, for whatever that is worth. Output of a maxed
              out row to a unicode text file should be no more than a few hundred
              bytes longer.

              Did you save the DTS package that the export wizard created? Have you
              tried running the wizard again? Did you specify fixed-width or
              delimited? ASCII or UNICODE? (I expect UNICODE to handle the
              NVARCHAR columns.)

              I would run the wizard again, being sure to save the package. That at
              least will give something to inspect on the code side if the output
              file is still screwy.

              Roy Harvey
              Beacon Falls, CT

              On 24 Aug 2006 06:11:12 -0700, "Daniel Wetzler"
              <Daniel.Wetzler @sig.bizwrote:
              >Hi Roy,
              >
              >thanks for your answer.
              >
              >Here's the table definition :
              >
              >
              >CREATE TABLE [dbo].[Variables] (
              > [ID] [varchar] (140) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
              > [Var_ref] [int] NOT NULL ,
              > [Group_Ref] [int] NULL ,
              > [Machines_Ref] [int] NULL ,
              > [TransfersID] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
              >,
              > [Compatibility] [int] NULL ,
              > [CompatibilityL1] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
              >NULL ,
              > [Category] [int] NULL ,
              > [CategoryL1] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
              >,
              > [AnalysisRespons ible] [tinyint] NULL ,
              > [AnalysisPriorit y] [int] NULL ,
              > [AnalysisDelta] [int] NULL ,
              > [AnalysisDeltaVa lue] [varchar] (30) COLLATE
              >SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
              > [Value] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
              > [Type] [varchar] (30) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
              > [Length] [int] NULL ,
              > [Address] [int] NULL ,
              > [Offset] [int] NULL ,
              > [Title1] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
              > [Title2] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
              > [Title3] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
              > [Title4] [nvarchar] (100) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
              > [AggregationTitl e1] [nvarchar] (100) COLLATE
              >SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
              > [AggregationTitl e2] [nvarchar] (100) COLLATE
              >SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
              > [AggregationTitl e3] [nvarchar] (100) COLLATE
              >SQL_Latin1_Gen eral_CP1_CI_AS NULL ,
              > [AggregationTitl e4] [nvarchar] (100) COLLATE
              >SQL_Latin1_Gen eral_CP1_CI_AS NULL
              >) ON [PRIMARY]
              >GO
              >
              >Best regards,
              >
              >Daniel
              >
              >
              >
              >Roy Harvey schrieb:
              >
              >Posting the table definition (CREATE TABLE) would help a lot.
              >>
              >Roy Harvey
              >Beacon Falls, CT
              >>
              >On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
              ><Daniel.Wetzle r@sig.bizwrote:
              >>
              >Dear MSSQL- experts,
              >
              >I have a strange problem with SQLSERVER 2000.
              >I tried to export a table of about 40000 lines into a text file using
              >the Enterprise manager
              >export assitant. I was astonished to get an exported text file of about
              >400 MB instead 16 MB which is the normal size of that data.
              >By examining this file with a text editor I found that the file
              >included alongside the data of my table MANY zeros which caused the big
              >file size.
              >
              >Does someone of you have an idea what could cause the export of
              >trillions zeros into my textfile and how to only export the significant
              >data of my table ?
              >
              >Best regards,
              >
              >Daniel

                Comment

                Previous template Next
                Working...