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”
// 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.