0

I have a series of csv files that will SOMETIMES use a double-quote. I'm following the guidacne from techbrothers IT: http://www.techbrothersit.com/2016/03/how-to-create-tables-dynamically-from.html

I have multiple files, each with unique schemas, and future files will also be unique. (reason why I'm not using data flow tasks.

Here's the line I'm parsing:

3921773,"FNAME","LNAME","123 Anywhere St","CTY",NY,12020,43.023721,-73.804807,1,rooftop,195,"Old Post Rd","Ballston Spa",NY,"Saratoga County",12020,US,"Office, Geographic Office (GIO)"

the code used:

query = "Insert into " + SchemaName + ".[" + TableName + "] (" + ColumnList + ") ";
query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";

the resulting Insert:

Insert into dbo.[tbl] ([pk],[fname],[lname],[addr1],[pcity],[pstate],[pzip],[Lat],[Long],[AccScore],[Type],[Number],[Street],[City],[State],[County],[Zip],[Country],[Source]) 
VALUES('3921773','"FNAME"','"LNAME"','"123 ANYWHERE ST"','"CTY"','NY','12020','43.023721','-73.804807','1','rooftop','195','"Old Post Rd"','"Ballston Spa"','NY','"Saratoga County"','12020','US','"Office','Geographic Information Officer (GIO)"')

The problem is the last field. because it's a double quoted(") field, the comma inside it is being picked up, and is being added as an additional data field in the insert query. How do I account for the quotes?

Thanks

arcee123
  • 101
  • 9
  • 41
  • 118
  • Why are you using a Script Task to do this? Use a dataflow task. Also, your C# is open to injection. – Thom A May 13 '18 at 18:58
  • can't use dataflow. each file has it's own schema, and I cannot use a dataflow task for dynamic file-to-table development. Thanks – arcee123 May 13 '18 at 18:59
  • You say each file is different, but if you have a table you clearly already have defined DDL. Thus, are you saying that you will never be inserting into the same table again? If so, i'd treat each file as it comes and still use a Dataflow task. – Thom A May 13 '18 at 19:02
  • nope. this is staging. my task is to load up each CSV into a table, do some manipulation at the table, then drop the table with new columns back down into files. – arcee123 May 13 '18 at 19:03
  • and no, my DDL is not-predefined. – arcee123 May 13 '18 at 19:03
  • Then `INSERT INTO` won't work; the table needs to exist for an `INSERT INTO`. You'd need to use `SELECT ... INTO...FROM`. – Thom A May 13 '18 at 19:04
  • ok. I think we're missing something here. The CSV's first line is read, column names are given varchar(500), and a table is created. Then line 2 through whatever is read, and insert statements are created. This insert is where my failure is. – arcee123 May 13 '18 at 19:15
  • A few options are: 1. Temporarily replace `','` with something innocuous like `'*'` before the replace, then replace it back afterwards; 2. Again replace `','` with valid quote escaped values like `'',''` which will work as expected when inserted (if you get the number of quotes right). 3. Import the entire row into one `VARCHAR(MAX)` column and do all the work inside the database. You can just export the data back again as one big varchar column – Nick.Mc May 13 '18 at 22:52
  • If you just import the entire CSV row into one `VARCHAR(MAX)` column then you can avoid the script task and do operations inside the database using T-SQL then export it out again. Do the CSV files always have the same additional columns? If not then I suggest you take the database out of the equation completely and just do the whole thing in a console app. – Nick.Mc May 13 '18 at 23:00

0 Answers0