Importing Large Data Sets in Visual Studio 2019 and SQL Server 2019 on Windows 11

Majid KH 0 Reputation points
2024-06-10T20:42:40.8233333+00:00

Hi everyone,

I recently installed Visual Studio 2019 and SQL Server 2019 on my Windows 11 system. When creating a project that involves importing a large data set with more than 1 billion rows, I encounter some issues.

When using the SSIS process and data packages to import the data, I face the following problems:

Using the .xls format, only 65,000 rows are imported.

Using the .xlsx format, the data is not imported correctly.

Could someone please help me resolve this issue? Is there a better way to import large data sets into SQL Server? Using the .xlsx format, the data is not imported correctly.

Thank you in advance for your assistance.

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,646 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,183 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,502 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ben 30 Reputation points
    2024-06-11T00:17:02.9266667+00:00

    Hi Majid you need to provide a little more context. Where is the data coming from and what format is the data coming to you as? Ideally how was it generated? Also just to set expectations, moving this much data around can be its own project. It's not quite a double-click on a file into Excel.

    FYI as we get started note that it is likely that you'll need to use a different file format than Excel's. As you found out, the legacy .xls format can only hold 64K rows. The newer .xlsx format can handle more but still only about 1M (~ 2^30). The the most common format for sharing large datasets is CSV and more recently JSON, XML, and Parquet. There are also MSSQL-to-MSSQL server formats if you control both sides.

    When you say the import of the .xlsx format the data did not import correctly, can you be more specific? As above it's unlikely this will be the format you want to use for loading 1 B rows data into your database but sharing this information would at least provide some context about your data, what you are doing, and what's happening to your data when you do so.

  2. ZoeHui-MSFT 34,756 Reputation points
    2024-06-11T06:36:50.71+00:00

    Hi @Majid KH.

    XLSX supports 1048576 rows and 16384 columns.

    If you have 1 billion rows and use excel, it may lose data.

    You may consider using csv file and bulk insert task to load large data.The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments