Post reply

How to efficiently import data in .rpt format into SQL 2008

  • July 18, 2014 at 1:07 pm

    #1730745

    Here is the format file, .xml is changed to .txt to get uploaded on this forum. Thanks.

    I think the issue is the last column, like I indicated in previous post, the last column is NOT a width fixed column, in some cases it is 0, in some cases it is a big number

    BTW: I have posted both my test data and format file here in this thread, everyone can you please try them out on your environment, thank you very much.

  • July 18, 2014 at 1:22 pm

    #1730750

    halifaxdal (7/18/2014)

    Here is the format file, .xml is changed to .txt to get uploaded on this forum. Thanks.

    I think the issue is the last column, like I indicated in previous post, the last column is NOT a width fixed column, in some cases it is 0, in some cases it is a big number

    BTW: I have posted both my test data and format file here in this thread, everyone can you please try them out on your environment, thank you very much.

    hi Halifax...I am no bcp expert...far from it.

    however, and for what its worth....I could import your test data using SSIS import wizard with ragged right format on the flatfile data source.

    regards

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 20, 2014 at 11:54 am

    #1730870

    J Livingston SQL (7/18/2014)

    halifaxdal (7/18/2014)

    Here is the format file, .xml is changed to .txt to get uploaded on this forum. Thanks.

    I think the issue is the last column, like I indicated in previous post, the last column is NOT a width fixed column, in some cases it is 0, in some cases it is a big number

    BTW: I have posted both my test data and format file here in this thread, everyone can you please try them out on your environment, thank you very much.

    hi Halifax...I am no bcp expert...far from it.

    however, and for what its worth....I could import your test data using SSIS import wizard with ragged right format on the flatfile data source.

    regards

    Thanks for letting me know, did you get all 11 fields? can you provide the detail of each step here?

  • July 21, 2014 at 12:36 am

    #1730897

    halifaxdal (7/20/2014)

    J Livingston SQL (7/18/2014)

    halifaxdal (7/18/2014)

    Here is the format file, .xml is changed to .txt to get uploaded on this forum. Thanks.

    I think the issue is the last column, like I indicated in previous post, the last column is NOT a width fixed column, in some cases it is 0, in some cases it is a big number

    BTW: I have posted both my test data and format file here in this thread, everyone can you please try them out on your environment, thank you very much.

    hi Halifax...I am no bcp expert...far from it.

    however, and for what its worth....I could import your test data using SSIS import wizard with ragged right format on the flatfile data source.

    regards

    Thanks for letting me know, did you get all 11 fields? can you provide the detail of each step here?

    yeah...all eleven columns.

    not sure what you are asking for on detail of each step......the wizard is straightforward.....have you tried the wizard yourself?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 21, 2014 at 6:34 am

    #1730983

    yes the wizard is straight forward, I just did it again, if I simply chose Ragged right, there is only one column defined and executing the package will error out, that's why I asked if you get 11 columns and if you can tell me more details, thanks.

    - Executing (Error)

    Messages

    Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    (SQL Server Import and Export Wizard)

    Error 0xc020902a: Data Flow Task 1: The "output column "Column 0" (10)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (10)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

    (SQL Server Import and Export Wizard)

    Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Rxie\Weblog\test.txt" on data row 1.

    (SQL Server Import and Export Wizard)

    Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - test_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

    (SQL Server Import and Export Wizard)

  • July 21, 2014 at 6:51 am

    #1730989

    halifaxdal (7/21/2014)

    yes the wizard is straight forward, I just did it again, if I simply chose Ragged right, there is only one column defined and executing the package will error out, that's why I asked if you get 11 columns and if you can tell me more details, thanks.

    you have to set the column widths to your data...this is done from the "columns" page in the wizard.....it will preview your flatfile data....you need to create the required widths by clickin in the preview tab to create new markers for the column widths (these can also be dragged).

    effectively ragged right is fixed width columns apart from the last column...which is where I think you maybe having trouble.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • July 21, 2014 at 7:12 am

    #1730997

    halifaxdal (7/17/2014)

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to bigint.

    If the files were output by SSMS set to output to file then your data will include a 3 char BOM (byte order mark) you will need to remove this to make BCP work.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • July 21, 2014 at 10:56 am

    #1731096

    J Livingston SQL (7/21/2014)

    halifaxdal (7/21/2014)

    yes the wizard is straight forward, I just did it again, if I simply chose Ragged right, there is only one column defined and executing the package will error out, that's why I asked if you get 11 columns and if you can tell me more details, thanks.

    you have to set the column widths to your data...this is done from the "columns" page in the wizard.....it will preview your flatfile data....you need to create the required widths by clickin in the preview tab to create new markers for the column widths (these can also be dragged).

    effectively ragged right is fixed width columns apart from the last column...which is where I think you maybe having trouble.

    No, the last column is causing trouble, most of the lines have a 0 in the last column, and it is fine until the unusual value (for example, line 31 has a value of 1631571) is hit.

  • July 21, 2014 at 10:57 am

    #1731098

    David Burrows (7/21/2014)

    halifaxdal (7/17/2014)

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to bigint.

    If the files were output by SSMS set to output to file then your data will include a 3 char BOM (byte order mark) you will need to remove this to make BCP work.

    Thank you David,

    Could you kindly provide me more detail on this? Can you modify the format file for me? The format file can be found in previous post.

    Thank you very much in advance.

  • July 21, 2014 at 11:31 am

    #1731124

    halifaxdal (7/21/2014)

    David Burrows (7/21/2014)

    halifaxdal (7/17/2014)

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to bigint.

    If the files were output by SSMS set to output to file then your data will include a 3 char BOM (byte order mark) you will need to remove this to make BCP work.

    Thank you David,

    Could you kindly provide me more detail on this? Can you modify the format file for me? The format file can be found in previous post.

    Thank you very much in advance.

    Ummmm... Would it be too much trouble to post it again here just to make life easy on the people trying to help you? I just don't have the time to do a paper chase for someone else.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 21, 2014 at 11:41 am

    #1731129

    Ok... I took the time to find your BCP format file and the example text file. This is easy. All the columns need to be treated as fixed field format, as you have done, except the last one. That one should be treated as a variable width field and the ending delimiter of that field should be the \ r \ n thing. It's that simple.

    I'd also lose the XML format file in favor of a traditional one. That way you only have to maintain one set of rows instead of two.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 21, 2014 at 12:18 pm

    #1731148

    Jeff Moden (7/21/2014)

    Ok... I took the time to find your BCP format file and the example text file. This is easy. All the columns need to be treated as fixed field format, as you have done, except the last one. That one should be treated as a variable width field and the ending delimiter of that field should be the \ r \ n thing. It's that simple.

    The \ r \ n thing seems to cause trouble here and that's why I need your help.

    I'd also lose the XML format file in favor of a traditional one. That way you only have to maintain one set of rows instead of two.

    I am sorry I don't understand you here :ermm::ermm::ermm:, can you post your working format and script here? Thank you so much for your time.

  • July 21, 2014 at 12:19 pm

    #1731151

    Ummmm... Would it be too much trouble to post it again here just to make life easy on the people trying to help you? I just don't have the time to do a paper chase for someone else.

    I will keep that in mind, thank you.

  • July 21, 2014 at 1:44 pm

    #1731198

    David Burrows (7/21/2014)

    halifaxdal (7/17/2014)

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to bigint.

    If the files were output by SSMS set to output to file then your data will include a 3 char BOM (byte order mark) you will need to remove this to make BCP work.

    I can't do any change to the data file provided to me, let alone the data file is GB size.

  • July 21, 2014 at 2:09 pm

    #1731205

    halifaxdal (7/21/2014)

    Jeff Moden (7/21/2014)

    Ok... I took the time to find your BCP format file and the example text file. This is easy. All the columns need to be treated as fixed field format, as you have done, except the last one. That one should be treated as a variable width field and the ending delimiter of that field should be the \ r \ n thing. It's that simple.

    The \ r \ n thing seems to cause trouble here and that's why I need your help.

    I'd also lose the XML format file in favor of a traditional one. That way you only have to maintain one set of rows instead of two.

    I am sorry I don't understand you here :ermm::ermm::ermm:, can you post your working format and script here? Thank you so much for your time.

    No problem. I've downloaded your test file and the code that you used to build the table. Those make life prtty easy for someone to help you. I'll be back soon.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 16 through 30 (of 58 total)

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