Register and join over 40,000 other developers!
Recent Topics
-
Print specific values from dictionary with a specific key name
Siten0308 - Jun 20 2019 01:43 PM
-
Learn algorithms and programming concepts
johnnylo - Apr 23 2019 07:49 AM
-
Job Gig PHP Form Needed
PJohnson - Apr 18 2019 03:55 AM
-
How to make code run differently depending on the platform it is running on?
xarzu - Apr 05 2019 09:17 AM
-
How do I set a breakpoint in an attached process in visual studio
xarzu - Apr 04 2019 11:47 AM
Recent Blog Entries
Recent Status Updates
Popular Tags
- networking
- Managed C++
- stream
- console
- database
- authentication
- Visual Basic 4 / 5 / 6
- session
- Connection
- asp.net
- import
- syntax
- hardware
- html5
- array
- mysql
- java
- php
- c++
- string
- C#
- html
- loop
- timer
- jquery
- ajax
- javascript
- programming
- android
- css
- assembly
- c
- form
- vb.net
- xml
- linked list
- login
- encryption
- pseudocode
- calculator
- sql
- python
- setup
- help
- game
- combobox
- binary
- hello world
- grid
- innerHTML

13 replies to this topic
#1
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.
#2
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.
If you want to remove \n you can do text = text.Replace(Environment.NewLine, ""); // this will remove your endlines.
www.pickmike.com
I don't just develop software. I find solutions to your business needs.
#3
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.
EDIT: VNFox beat me to the punch.
String.Replace("\n", "");
EDIT: VNFox beat me to the punch.
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
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");
#5
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
My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth
#6
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");
#7
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?
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?
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
#9
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.
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
Posted 22 September 2012 - 06:52 AM
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.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)
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
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.
#12
Posted 22 September 2012 - 07:26 PM
Crokett, then i think what you need to do is something like this:
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
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
Also tagged with one or more of these keywords: c#, string
Language Forums →
C# →
How to make code run differently depending on the platform it is running on?Started by xarzu, 05 Apr 2019 ![]() |
|
![]() |
||
Language Forums →
C# →
How do I set a breakpoint in an attached process in visual studioStarted by xarzu, 04 Apr 2019 ![]() |
|
![]() |
||
Language Forums →
C# →
Do you use obfuscation?Started by Fernando, 22 Jan 2019 ![]() |
|
![]() |
||
General Forums →
Game Development →
Include a specific error, task, problem, or question in your titleStarted by Killuah, 04 Jul 2018 ![]() |
|
![]() |
||
Language Forums →
C and C++ →
Why is similar string comparison giving different result?Started by nick112, 29 May 2017 ![]() |
|
![]() |
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download