Data Import & Export

Import a Large Text File into SQL Server Using Excel

Steven ParkerSteven Parker Nov 18, 2026 869 views

Question details

Import approximately 1.5 million text-file records into SQL Server despite malformed quotation marks and Excel's worksheet row limit.

Import a Large Text File into SQL Server Using Excel
Product
Microsoft Excel, Power Query, and SQL Server
Device & OS
not provided
Scenario
A user wants to clean a large text file before importing its records into a SQL Server table.
Observed behavior
Malformed quotation marks prevent a direct import, and the complete file exceeds Excel's worksheet row capacity.
Before you start

Check the file's encoding, delimiter, header row, and expected column types. Determine whether quoted fields can contain line breaks; a line-by-line quotation-mark check is suitable only when each record occupies one physical line.

Solution 1Recommended

Separate suspicious lines and import the cleaned file into SQL Server

Use a streaming script to separate lines with an odd number of quotation marks, then import the remaining records without loading them all into an Excel worksheet.

Excel worksheets are limited to 1,048,576 rows. Power Query can transform data and load it to the Data Model instead, but the worksheet limit still applies to worksheet output, and malformed text may prevent the parser from reading the file.

The following check assumes UTF-8 text and one complete record per physical line. An even quotation-mark count does not prove that a record is valid, so review the output and import errors before accepting the data.

1
Create the cleaning script

Save the following as clean_file.py in the same folder as input.txt: with open('input.txt', 'rb') as source, open('clean.txt', 'wb') as clean, open('errors.txt', 'wb') as errors: for line in source: if line.count(b'"') % 2: errors.write(line) else: clean.write(line)

2
Run the script

Open a terminal in that folder and run `python clean_file.py`. The script writes lines with even quotation-mark counts to clean.txt and suspicious lines to errors.txt.

3
Review the output

Open errors.txt in a text editor, correct or separately process the flagged records, and check clean.txt for other formatting problems. Confirm that the header, delimiter, encoding, and column counts match the destination table.

4
Import into SQL Server

In SQL Server Management Studio, right-click the destination database and select Tasks > Import Data. Choose a Flat File Source, select clean.txt, set the delimiter and text qualifier, and map the columns to the SQL Server destination table.

5
Verify the import

Review the import wizard's results and run a row-count query against the destination table. Compare the imported count with the expected data-record count, excluding any header, and investigate rejected or missing records.

Separate suspicious lines and import the cleaned file into SQL Server
Files with multiline fields: Do not use the line-by-line script if quoted fields can contain line breaks. Use a parser or custom process that understands the file's record and escaping rules, and inspect malformed records separately.
Free Microsoft Office alternative

Review Text Data with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility. Use it to review smaller samples or cleaned files as you migrate your spreadsheet work; it does not replace SQL Server's import tools or repair malformed records automatically.

Review manageable samples of text data in a spreadsheetCompatible with Microsoft Excel formats, including XLSXFamiliar interface for moving from Microsoft OfficeFree and lightweight for everyday office work
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel open all 1.5 million records on one worksheet?

No. An Excel worksheet has a limit of 1,048,576 rows, including any header. Use a streaming process or split the data across worksheets or files instead.

Can Power Query load the file without putting every row on a worksheet?

Yes. In Excel, use Data > Get Data to connect to the file, then choose a load option that adds the result to the Data Model rather than a worksheet. Available memory and malformed input can still limit the process.

Does an even number of quotation marks guarantee a valid record?

No. A line can have an even number of quotation marks and still have invalid quoting, the wrong number of columns, or incorrect data types. Check the file against its expected format and review SQL Server import errors.

Where do I set the text qualifier for the SQL Server import?

In SQL Server Management Studio, right-click the destination database and choose Tasks > Import Data. In the Flat File Source settings, select the file and specify the delimiter and text qualifier before mapping the destination columns.