1

my task is to import CSV files into SQL tables. The issue comes up when parsing each line within the file. The file is COMMA DELIMITED. Which is easy to handle, but texts are in quotes.

For example:

2, 2012-02-14, "David", "David does, not, show up ", "AS", 22

Here is how I am handling/parsing this:

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

This works very well, of course with two issues:

  1. Whenever there is comma in my text field, you get a new column/field, which is inaccurate and throws an error. In above, we have: "David does" - "not" "show up" as three columns, but it's really just one.

  2. I end up importing quotes. So my columns have things like: "AS" instead of just AS

Is there anything in SSIS/C# I can use to just parse the line? How can I over come the text qualifiers?

I tried using the Replace function. But that doesn't work. This is all done within C# sctript task in SSIS.

Note, I've already read the forum here - that only highlights the issue and no answer. The accepted answer provides dead links to "petitions". No real answer is there.

Tu deschizi eu inchid
  • 4,117
  • 3
  • 13
  • 24
Expert_to_be
  • 11
  • 2
  • 1
    Why use a script task for this? Why not use a dataflow and then a text file source, in which you can define the quote identifier in its properties? – Thom A Nov 05 '22 at 17:35
  • @Larnu it is because I am importing hundreds of CSV files. Text source can do it one at a time, but my script creates a schema dynamically. – Expert_to_be Nov 05 '22 at 17:39
  • So do none of your files have the same definition? What about the tables they are therefore being loaded to? You have 100's of files and 100's of tables? – Thom A Nov 05 '22 at 17:47
  • @Larnu - no each file (and therefore table) is unique. And correct: hundreds of files would end up in hundreds of tables.Of course you can always manually do each one by one, which is not something I want to do, lol. Everything works perfectly except the text qualifier. That's the only missing piece. – Expert_to_be Nov 05 '22 at 18:07
  • You might want to consider regex. https://stackoverflow.com/questions/23527369/regular-expression-for-double-quoted-values-in-csv – KeithL Nov 07 '22 at 00:45

0 Answers0