Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 Tags
Subject: RE: BULK INSERT from CSV file errors
Thread ID: 395928 Message ID: 395934 # Views: 57 # Ratings: 1
Version: Not Applicable Category: Databases, Tables and SQL Server
Date: Tuesday, January 21, 2014 3:46:45 PM         
   


> Well, I am not sure how to handle the errors, which center on the GrpNo and EMP_SSN fields, both of which do not have double quotes around the data in each of their fields. The errors are as follows, regarding data types conflicting:
>
> TWM0042 has no quotes around it and this BULK INSERT fails for some reason due to a data type issue. It may assume that everything that is string data must have double quotes. In this CSV, not all character or string data has double quotes surrounding the data.
>
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (GrpID).
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (Emp_SSN).
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 3 (Emp_SSN).
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 3 (Emp_SSN).
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 3 (Emp_SSN).
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 3 (Emp_SSN).
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 3 (Emp_SSN).
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12, column 3 (Emp_SSN).
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 14, column 3 (Emp_SSN).
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 15, column 3 (Emp_SSN).
> Msg 4864, Level 16, State 1, Line 2
> Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17, column 3 (Emp_SSN).
> Msg 4865, Level 16, State 1, Line 2
> Cannot bulk load because the maximum number of errors (10) was exceeded.
> Msg 7399, Level 16, State 1, Line 2
> The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
> Msg 7330, Level 16, State 2, Line 2
> Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
>
>
> Here's the T-SQL: Code to create the table I am importing data into:
>
> USE GA
> DROP TABLE dbo.WebTPA_INPUT_File
> create table dbo.WebTPA_INPUT_File (
> 	GrpNo nvarchar(10), Ins_Name nvarchar(50), Emp_SSN nvarchar(9), MemberID nvarchar(12),
> 	FirstName nvarchar(15), LastName nvarchar(25), Mid_Init nchar(1), Gender nchar(1),
> 	Dep_SSN nvarchar(9), cRelation nvarchar(6), DOB date, Age int, Age_Band int, 
> 	Eff_Date date, Term_Date date, cStatus nchar(1), StatBegDt date, Benefits nvarchar(60),
> 	Med_Tier nvarchar(2), Den_Tier nvarchar(2), GrpCntrct nvarchar(30), Dept nchar(1), 
> 	Location nchar(1), Address1 nvarchar(30), Address2 nvarchar(30), City nvarchar(25), 
> 	cState nvarchar(2), ZipCode nvarchar(5), Hire_Date date)
> 

>
> Here's the BULK INSERT command in T-SQL:
> use GA
> BULK
> INSERT dbo.WebTPA_INPUT_File
> FROM "K:\Data\Common\Feed\In\TWM0042_20140116.CSV"
> WITH
> (
> FIELDTERMINATOR=',',
> ROWTERMINATOR='\n'
> Cecil Champenois, Jr.

Cecil,
It wouldn't have quotes surrounding fields. If you have them, remove. SQL server bulk insert is not so good in CSV imports, regarding field quotes etc. It would import some rows like row 5 successfully. Check that row to see difference. One option is to read the file with ACE or JET driver and then recreate the CSV in a way that wouldn't create problems (I must admit ACE and JET drivers are doing a better job than VFP append for CSV).

Here is some sample C# code that would do this in a more flexible manner - you may use this directly in Linqpad changing file, field names:

void Main()
{
  Stopwatch sw = new Stopwatch();
  sw.Start();
  string sqlConnectionString = @"server=.\SQLExpress;Trusted_Connection=yes;Database=Test";

  string path = @"d:\temp\SampleTextFiles";
  string fileName = @"combDoubledX.csv";
  
  using (OleDbConnection cn = new OleDbConnection(
	"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+path+
	";Extended Properties=\"text;HDR=No;FMT=Delimited\";"))
  
  using (SqlConnection scn = new SqlConnection( sqlConnectionString ))
  {
  OleDbCommand cmd = new OleDbCommand("select * from "+fileName, cn);
  
  SqlBulkCopy sbc = new SqlBulkCopy(scn, SqlBulkCopyOptions.TableLock,null);
  
  sbc.ColumnMappings.Add(0,"[Category]");
  sbc.ColumnMappings.Add(1,"[Activity]");
  sbc.ColumnMappings.Add(5,"[PersonId]");
  sbc.ColumnMappings.Add(6,"[FirstName]");
  sbc.ColumnMappings.Add(7,"[MidName]");
  sbc.ColumnMappings.Add(8,"[LastName]");
  sbc.ColumnMappings.Add(12,"[Email]");

  cn.Open();
  scn.Open();
  
  SqlCommand createTemp = new SqlCommand();
  createTemp.CommandText = @"create table ##PersonData 
  (
    [Id] int identity primary key,
    [Category] varchar(50), 
    [Activity] varchar(50) default 'NullOlmasin', 
    [PersonId] varchar(50), 
    [FirstName] varchar(50), 
    [MidName] varchar(50), 
    [LastName] varchar(50), 
    [Email] varchar(50)
  )"; 
  createTemp.Connection = scn;
  createTemp.ExecuteNonQuery();
 
  OleDbDataReader rdr = cmd.ExecuteReader();
  
  sbc.NotifyAfter = 100000;
  sbc.BatchSize = 1000;
  sbc.BulkCopyTimeout = 10000;
  sbc.DestinationTableName = "##PersonData";
  
  sbc.SqlRowsCopied += (sender,e) =>
    {
    Console.WriteLine("-- Copied {0} rows to {1}.[{2} milliseconds]", 
      e.RowsCopied, 
      ((SqlBulkCopy)sender).DestinationTableName,
      sw.ElapsedMilliseconds);
    };
  
  sbc.WriteToServer(rdr);

  if (!rdr.IsClosed) { rdr.Close(); }
  
  cn.Close();
  scn.Close();
  }
  sw.Stop();
  sw.Dump();
}


Warning: This takes around 35 seconds on my computer (AMD Phenom II X6 1090T) for approximately 1.2 million rows where most of the time is spent in reading.

Second option you could create this table in VFP, append there and then use a linked server in SQL server to select from there.

Third is using XML (much larger file, slower).

...



Cetin Basoz

Give some sample data in code for your question - here is the tool you need to generate the code

My Blog


MongoDb Certified Developer
MongoDb Certified DBA


Support Wikipedia

ENTIRE THREAD

BULK INSERT from CSV file errors Posted by Cecil Champenois @ 1/21/2014 2:42:41 PM
RE: BULK INSERT from CSV file errors Posted by Cetin Basoz @ 1/21/2014 3:46:45 PM
RE: BULK INSERT from CSV file errors Posted by Gene Wirchenko @ 1/21/2014 6:44:49 PM