none
MSSQL 2016 shuffling data after importation RRS feed

  • Question

  • I have data from a 3rd party system in excel. when I import the data to SQL server 2016 table the data is shuffled and I can't work on it since the data is grouped. and the header of the group is in one column and row 
    Monday, October 14, 2019 10:15 AM

All replies

  • Not clear what your issue is and wihtout more details it's impossible to assist you.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 14, 2019 10:32 AM
  • Data is stored in Groups

    Group1 

    1

    2

    3

    4

    Group2
    1

    2

    3

    4

    the data has the above structure when i import the data they get shuffled. some of group 1 data will end up in group 2 and vise versa and i cannot index the data since it cant be manipulated.
    when i use the MSSQL 2014 the data retains the same structure.
    The server we are now using is MSSQL 2016


    • Edited by Ghost_Ezio Monday, October 14, 2019 11:54 AM
    Monday, October 14, 2019 11:53 AM
  • How exactly did you import the data into SQL Server?

    Excel does not actually store the data in the way you see it on the screen.  Excel stores the data as a stream, in the order the data was actually entered.  So depending on how you imported the data, the order may be how it looks in Excel, or may be how it was entered.

    Monday, October 14, 2019 12:18 PM
  • So you have one table with one column, you import data from a source file and when you query the table you get the data in a different order then in source file? That's quite normal and by design, data don't have a natural order, you have to maintain the order on your own, e.g. adding a column with identity.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, October 14, 2019 12:19 PM
  • Hello Friend,

    You need to let us know how this import is being done.
    What kind of data are you scrambling?

    Do the following:
    Send us a print of the data you are importing and how it is appearing to you after the import.
    This will make it easier to understand and help.


    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]


    Monday, October 14, 2019 1:27 PM
  • How exactly did you import the data into SQL Server?

    Excel does not actually store the data in the way you see it on the screen.  Excel stores the data as a stream, in the order the data was actually entered.  So depending on how you imported the data, the order may be how it looks in Excel, or may be how it was entered.

    im not able to at the moment to attach an image

    the way i showed it above is from cell A1 continuous downwards

    Monday, October 14, 2019 1:28 PM
  • The table has many columns.

    i cant index the data since i obtain it from a 3rd party. i dump it in a folder and i import the data... indexing it from the db wont work since it will be shuffled unless  to automate before loading it to the DB


    • Edited by Ghost_Ezio Monday, October 14, 2019 1:46 PM
    Monday, October 14, 2019 1:45 PM
  • how to import and save rows in a particular order when the data itself has no columns that can be used to support that order?

    another way to frame the question 

    Monday, October 14, 2019 1:54 PM
  • Again, how exactly did you import the Excel file into a SQL Server table?  Did you use SSIS, a linked server or OpenRowSet, Script task in SSIS?

    • Marked as answer by Ghost_Ezio Tuesday, October 15, 2019 7:05 AM
    • Unmarked as answer by Ghost_Ezio Tuesday, October 15, 2019 7:05 AM
    Monday, October 14, 2019 2:05 PM
  • Hi Ghost_Ezio,

    Please verify your account in the following link:

    Verify Account 42

    Then please share more info about your ssis package.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, October 15, 2019 2:51 AM
  • Again, how exactly did you import the Excel file into a SQL Server table?  Did you use SSIS, a linked server or OpenRowSet, Script task in SSIS?

    i dont know what exactly the tool uses to import data. i would guess Script task
    Tuesday, October 15, 2019 7:06 AM
  • What is DTSbulkload regarding data loading?
    Tuesday, October 15, 2019 9:52 AM
  • how to import and save rows in a particular order when the data itself has no columns that can be used to support that order?

    Then you can't solve it.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 15, 2019 10:02 AM
  • "Bulk Load" does not guarantee the order inserted into the table will be the same as the source.  

    Tuesday, October 15, 2019 6:00 PM
  • how to import and save rows in a particular order when the data itself has no columns that can be used to support that order?

    another way to frame the question 

    You need to ask what makes the data in order. Otherwise you are risking breaking it inadvertently sooner or later.

    Arthur

    MyBlog


    Twitter

    Tuesday, October 15, 2019 7:40 PM
    Moderator