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.

 

Solution

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
sw.WriteLine(“MaxScanRows=0″);

// for csv files
sw.WriteLine(“Format=CSVDelimited”);

// for tab delimited files
sw.WriteLine(“Format=TabDelimited”);
}
}

Content of the schema file will look like:

[myfile.txt]

MaxScanRows=0

Format=TabDelimited

 

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.

 

About these ads

, , , ,

  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!

  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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: