import to SQL from CSV and Text file problem

please read my previous post  how to import data into SQL Server from csv or tab delimited files .

Some of the common problems we face while importing data from  coma delimited (CSV) or tab delimited files are:

1. Data gets truncated

2. Only one column is insereted into SQL database, other columns are ignored.

3. String or Integer values are are misinterpreted by JET engine. e.g. a column having IP values as 12.345.678.90 is treated as 12.3456


Common reasons are:

1. The default import format of JET engine in registry is different. e.g. if default format is “CSVDelimited”, then JET engine can not import “TABDelimited” files correctly. Even if we set the “FMT” attribute in connection string as “TABDelimited”, JET engine ignores it.

2. Jet engine scans first few rows of the text file to determine the data type of source columns. The number of rows to be scanned is determined by a registry entry named “MaxScanRows”.  Sometimes the default number mentioned is not enough to determine the data type.



1. Edit the registry to increase “MaxScanRows” number and change the deliminator to “TABDelimited”. Changing the registry is not recommended and it needs certain level of expertise to play with system registry.

2. Create a temp folder in the server. Upload the source text/CSV file to the temp folder. then create a Schema.ini file under the same folder with import settings.

string path = “[folder path]” + @”\Schema.ini”; // e.g. Server.MapPath(“~\temp”) or “c:\temp”
if (!File.Exists(path))
// Create the schema.ini file.
using (StreamWriter sw = File.CreateText(path))
sw.WriteLine(“[“ +“file name” + “]”); // e.g. myfile.csv

// for csv files

// for tab delimited files

Content of the schema file will look like:





After uploading both the files, retrieve data from the uploaded text file and insert into SQL table. You can follow my previous post as mentioned in the begining to get more details.



, , , ,

  1. #1 by casino online on December 29, 2012 - 4:48 am

    Hi there! This post could not be written much better!

    Looking through this article reminds me of my previous roommate!
    He continually kept preaching about this. I am going to forward this information to him.
    Fairly certain he will have a good read. Thank you for sharing!

  2. #2 by Satish on December 14, 2014 - 10:11 pm

    HI there,

    Thank you so much for this. I just couldn’t figure out where I was going wrong till reading this.


  3. #3 by Michael on September 15, 2017 - 7:24 pm

    Thank you, this fixed the following error for me: “System.InvalidOperationException: Anbieter konnte den Int32-Wert nicht ermitteln.”

  1. How to upload Excel (XLSX or XLS), CSV, Tab Delimited Text files to SQL Server « A Developer's Blog
  2. How to upload Excel (XLSX or XLS), CSV, Tab Delimited Text files to SQL Server « A Developer's Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: