13 replies to this topic

[crokett]

I am working on a small program to import CSV files into a database. the MySQL statement to load the file is a very long string and will get even longer when I add the 37 column names. I want to wrap the string for readability in VS 2010, but when I do it causes a run time because there is a \n in the string value. I tried various google searches to find out how to wrap it, but what is the trick to not having a very long line of code that I can't see.

[VNFox]

I don't understand ... a Text field in MySQL can hold pretty much any length of the string.

If you want to remove \n you can do text = text.Replace(Environment.NewLine, ""); // this will remove your endlines.

[gregwarner]

You can try simply replacing all instances of the '\n' character with a space or nothing before you execute the statement.

String.Replace("\n", "");

EDIT: VNFox beat me to the punch.

[crokett]

Here's the current SQL command. it is a bit ungainly and I have to scroll right to see the entire thing. It will get even worse when I add the column names to it. If use the return key say at the 'TERMINATED BY to wrap it to the next line so I can see the entire thing in VS, when it runs it breaks because of the newline. I thought there was a way to do this without an extra step ofreplacing the line breaks, but maybe not.

string commandstring = String.Format(@"LOAD DATA LOCAL INFILE '{0}' INTO TABLE {1} COLUMNS TERMINATED BY ',' ENCLOSED BY ' ' LINES TERMINATED BY '\r\n' IGNORE 1 LINES", files.FirstOrDefault().ToString(), "By_Switch");

[lespauled]

...
If you want to remove \n you can do text = text.Replace(Environment.NewLine, ""); // this will remove your endlines.
...

This is one of the rare cases that I would recommed against using Environment.NewLine. If this piece of code makes its way into a web app, it won't work. Use \n

[Momerath]

If I understand you right, you want to break the long string up so you can see it in Visual Studio without scrolling.

string commandstring = String.Format(@"LOAD DATA LOCAL INFILE '{0}' INTO TABLE {1} " +
    "COLUMNS TERMINATED BY ',' ENCLOSED BY ' ' " +
    "LINES TERMINATED BY '\r\n' IGNORE 1 LINES",
    files.FirstOrDefault().ToString(), "By_Switch");

[gregwarner]

The problem is that in C#, you are trying to store the literal character sequence, '\r\n', and pass this to another language (SQL). Any time you do this, you need to double-escape your backslashes. Let me explain.

SQL is expecting to see certain characters in your LINES TERMINATED BY clause (Carriage return followed by line feed). Since these characters are non-printable characters, they need to be expressed by escaping them (backslash preceeding a code letter). So SQL expects to see: '\' 'r' '\' 'n'

But now C# also allows backslash escaping of certain characters, so when you write the string: "\r\n" in C#, you don't actually store a backslash character, letter 'r', backslash character, letter 'n' literally in the string. You store a literal carriage return followed by a literal newline character. So SQL isn't seeing '\' 'r' '\' 'n' at all. It's seeing literal carriage return, literal line feed, which are invalid in this context.

You must express these values in some way in C# such that they get passed to SQL in the correct form. Fortunately, C# allows for the literal representation of the backslash character by escaping it with another backslash, like this: \\

That \\ sequence places a single literal backslash character into the C# string, which is exactly what SQL is expecting to see. Replace the \r\n above with \\r\\n and you'll be fine.

That all make sense?

[Momerath]

Since he provided the literal string specifier (@) it is storing it as the characters \r\n and not an actual return/line feed (see examples here)

[Orjan]

The problem in this case is that it is an CSV file, which by default has endlines between each row. Adding more endlines for readability will destroy the file format. The easiest way to handle it is probably to let the controller wrap the content on it's own.

[gregwarner]

Since he provided the literal string specifier (@) it is storing it as the characters \r\n and not an actual return/line feed (see examples here)

Very good to know. I'm probably less than half as proficient with C# as I am with Java. Didn't know about this little gem. Thanks for the correction.

[crokett]

Sorry for the confusion. the issue isn't the incoming values in the CSV file it is the string value that represents the SQL statement. The LOAD File statement I am using has 37 columns specified. If I put them all on one line in Visual Studio I can't see the entire line without a lot of scrolling. If I break the line up to keep it all where I can see it, I get the \n as part of the string that is passed MySQLCommand object. as the command. String.Replace seems to work, I was just wondering if there was a way to tell the compiler not to include the \n in the string value.

[BlackRabbit]

Crokett, then i think what you need to do is something like this:

loadStr = @"col_1, col_2, col_3," +
			   @"col_4, col_5, col_6," +
			   @"col_7, col_8, col_9" +

That way you will see it all, if you don't like the way it goes, but you like to
have it layed out multiline anyway you can just add to the string with += operator or with .Append() in a stringbuilder

i know how distasteful that endless lines can become, here we call it : rightwards code