Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Wrapping String Values For Readability

c# string

  • Please log in to reply
13 replies to this topic

#1 crokett

crokett

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 109 posts
  • Programming Language:C#, Perl
  • Learning:C#, Perl, Transact-SQL

Posted 21 September 2012 - 07:31 AM

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.
  • 0

#2 VNFox

VNFox

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 648 posts
  • Programming Language:C#, PHP
  • Learning:Assembly

Posted 21 September 2012 - 07:41 AM

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.
  • 0

www.pickmike.com
I don't just develop software. I find solutions to your business needs.


#3 gregwarner

gregwarner

    Obi Wan of Programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1586 posts
  • Location:Arkansas
  • Programming Language:C, Java, C++, C#, PHP, Transact-SQL

Posted 21 September 2012 - 07:42 AM

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.
  • 0

ti-99-sig.png
Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.
– Douglas Hofstadter, Gödel, Escher, Bach: An Eternal Golden Braid


#4 crokett

crokett

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 109 posts
  • Programming Language:C#, Perl
  • Learning:C#, Perl, Transact-SQL

Posted 21 September 2012 - 07:54 AM

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");

  • 0

#5 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts
  • Programming Language:C, C++, C#, JavaScript, PL/SQL, Delphi/Object Pascal, Visual Basic .NET, Pascal, Transact-SQL, Bash

Posted 21 September 2012 - 08:11 AM

...
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
  • 0
My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#6 Momerath

Momerath

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 282 posts
  • Programming Language:C, Java, C++, C#, PHP, (Visual) Basic, Python, JavaScript, Perl, Visual Basic .NET, Pascal, Ada, Assembly, Fortran, Scheme
  • Learning:Others

Posted 21 September 2012 - 08:55 AM

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");

  • 0

#7 gregwarner

gregwarner

    Obi Wan of Programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1586 posts
  • Location:Arkansas
  • Programming Language:C, Java, C++, C#, PHP, Transact-SQL

Posted 21 September 2012 - 09:15 AM

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?
  • 0

ti-99-sig.png
Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.
– Douglas Hofstadter, Gödel, Escher, Bach: An Eternal Golden Braid


#8 Momerath

Momerath

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 282 posts
  • Programming Language:C, Java, C++, C#, PHP, (Visual) Basic, Python, JavaScript, Perl, Visual Basic .NET, Pascal, Ada, Assembly, Fortran, Scheme
  • Learning:Others

Posted 22 September 2012 - 12:33 AM

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)
  • 0

#9 Orjan

Orjan

    CC Mentor

  • Moderator
  • 2918 posts
  • Location:Karlstad, Sweden
  • Programming Language:C, Java, C++, C#, PHP, JavaScript, Pascal
  • Learning:Java, C#

Posted 22 September 2012 - 06:42 AM

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.
  • 0

I'm a System developer at XLENT Consultant Group mainly working with SugarCRM.
Please DO NOT send mail or PM to me with programming questions, post them in the appropriate forum instead, where I and others can answer you.


#10 gregwarner

gregwarner

    Obi Wan of Programming

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1586 posts
  • Location:Arkansas
  • Programming Language:C, Java, C++, C#, PHP, Transact-SQL

Posted 22 September 2012 - 06:52 AM

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.
  • 0

ti-99-sig.png
Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.
– Douglas Hofstadter, Gödel, Escher, Bach: An Eternal Golden Braid


#11 crokett

crokett

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 109 posts
  • Programming Language:C#, Perl
  • Learning:C#, Perl, Transact-SQL

Posted 22 September 2012 - 07:16 PM

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.
  • 0

#12 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 22 September 2012 - 07:26 PM

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
  • 0





Also tagged with one or more of these keywords: c#, string

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download