BULK INSERT into SQL Server from CSV with apostrophes instead the strings

T2102

I am trying to perform a bulk insert into SQL Server where some of the text fields may contain apostrophe's, which need to be removed. Can I bulk insert from the csv and then have the last column be the file name I am inserting from? BULK INSERT MY_DB.dbo.SYMBOLS FROM 'C:\data.csv'WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\r\n' ) My workaround is to query to csv file to a dataset, remove the apostrophes, and then run a ton of insert queries. I am doing this for more than a hundred files.

Mycroft Holmes

Ted2102 wrote:

query to csv file to a dataset,

I do the same, load the data into a datatable, clean out the single quotes and BULKCOPY the datatable into SQL Server table. The target table is all varchar b/c bulkcopy can be delicate sometimes and spits the dummy regularly.

public int BulkCopy(DataTable dtTable, string sTableName, SqlConnection oConn)
	{
		try
		{
			SqlBulkCopy oBC = new SqlBulkCopy(oConn);
			oBC.BulkCopyTimeout = 60000;
			oBC.DestinationTableName = sTableName;
			oBC.WriteToServer(dtTable);
			return dtTable.Rows.Count;
		}
		catch (Exception)
		{
			throw;
		}
	}

Never underestimate the power of human stupidity RAH

R Giskard Reventlov

Firstly, why do you need to remove the apostrophes? Are they not part of the text you are importing or are they superfluous characters that shouldn't have been there? Either way you might consider opening the csv file in code and then either doubling the apostrophes or removing them, something like (and this is a very simplistic example):

string filePath = "full_path_to_the_csv_file";
string text = File.ReadAllText(filePath);

// Either:
text = text.Replace("'", "''");
// or
text = text.Replace("'", string.Empty);

File.WriteAllText(filePath, text);

You'll need to adjust to suit but it should get you started.

me, me, me "The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!" Larry Niven

T2102

Okay, then I will make all of my strings varchar for this table. Are there any problems with loading of doubles or big ints that you are aware of?

T2102

The apostrophes are part of the text. Appreciate the help. I will this later today as well and see how well it works. I am trying to load a couple hundred csv files.

Mycroft Holmes

I remove the single quotes, we regularly export the data as csv file at some point and text identifiers are not supported by SSIS (I think, one of MS core technologies does not support text qualifiers "" therefor cannot deal with single quotes in the data, astonishing). So we get a cleaner result and the our users don't give a rats ass about single quotes.

Never underestimate the power of human stupidity RAH

R Giskard Reventlov

Mycroft Holmes wrote:

users don't give a rats ass

Would have both said it all and been very precise :-)

me, me, me "The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!" Larry Niven

T2102

I am going to start working on this shortly. I decided to remove single and double quotes, so that I would get the same results if I ran a script on the file again. If I did not have the double quote issue and several other formatting problems, then I could have called a bulk load or copy afterwards. I still might make sense to clean the full data first and then do a bulk load. My problem with my data sources files is that the first field is sometimes split into several columns and the last few columns are sometimes missing. So I have a gross C++ program to remap the data to try to get the correct table structure. I noticed that the results are incorrect for Rows when double quotes are utilized. I am specifying FMT=Delimited(,) in my connection string for a csv file. My query is SELECT * FROM C:\X.csv Suppose a row contained Generic 1st "LCD" Monitor, 1, 2, 3. The query results are coming back as "Generic 1st", Null, Null, Null.

modified on Thursday, April 1, 2010 2:47 AM

T2102

Given the double quote issue in the data set, I cannot use the ADO .NET csv file reading capability directly. Instead I tried using ReadAllText and WriteAllText to remove the single quotes and double quotes. I keep getting an out of memory error after a few minutes of inserts. I was able to load the datasets (before discovering the double quote issue)without any memory issues. I have 4 GB of physical memory and a maximum 16 GB virtual memory setting on my laptop. My aggregate datasets should take no more than 6GB of space in SQL Server. I've tried rebooting a couple of times and that did not help. Any ideas? void Cleanse_File(System::String ^Full_Path_To_File, long & ErrorCode) { try { System::String ^text = System::IO::File::ReadAllText(Full_Path_To_File); text = text->Replace("'", ""); text = text->Replace("\"", ""); System::IO::File::WriteAllText(Full_Path_To_File, text); } catch(System::Exception ^e) { System::Console::WriteLine(e->Message); } } At this point, I am thinking of using a different csv file reader and hoping that it can handle the double quotes without mismapping columns.

R Giskard Reventlov

If the files are large you may need to read them in a line at a time.

me, me, me "The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!" Larry Niven