Error trying to load a CSV into a sql table

[Scott Krise]

If I go into MySQL, and execute the following "load data infile 'C:\SYMMCO\AIT_TEST.CSV' into table tt_po_import FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'" it works like a charm. Im not trying to use this programmatically, and cant find the syntax that it needs.

Here is my C++ query:

updateStr = QString("load data infile '%1' into table tt_po_import FIELDS TERMINATED BY ',' LINES TERMINATED BY '%2'").arg("C:\\SYMMCO\\AITTEST.CSV").arg("\\r\\n");
query.prepare(updateStr);
query.exec(updateStr);

Ive tried every combination of single or double \, etc...but no luck.

The message Im getting at the moment is that it cannot find the aittest.csv file....but its looking in c:\programdata\MySQL\MySql Server\5.5\data\ folder for some reason...and the error message says it cant find the filename SYMMCOAITTEST.CSV in that location. (The spelling is exactly as its displaying it...Notice, the argument of my query shows the name of the file as C:\SYMMCO\AITTEST.CSV.

What can I do to correct?

Thanks.

[mchinand]

Can you verify what updateStr is before using it as your argument to the query? I think you just need the exec() statement and not the prepare() but don't think that's your problem. Also, you might try it with single forward slashes, Windows will accept that.

In your working MySQL command, you have an underscore in the filename, AIT_TEST.CSV, whereas in your code you do not.

[Scott Krise]

updateStr is defined as a QString.

Tried both single and double forward slashes....but it seems to like double better, I get warnings saying \s and \a are invalid...its getting the \s from \symmco and the \a from \aittest.csv.

Yes...as one of my troubleshooting steps, I removed the _ from AIT_TEST simply to rule out that the underscore wasn't causing the problem. So ignore that discrepancy. Its not causing the problem.

And Im confident the .PREPARE isn't my problem either.

Thanks

[mchinand]

Sorry, I meant can you verify what the value of updateStr is before using it. Also, try using forward slashes, /, instead of backslashes, \, when specifying the fully qualified filename. You might need quadruple backslashes when forming your QString if you really want to use backslashes.

[Scott Krise]

Yes...the updateStr prior to execute reads :

load data infile 'c:\symmco\aittest.csv' into table tt_po_import FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'

And the full message after executing it is:

[MySQL][ODBC 3.51 Driver][mysqld-5.5.15] File 'C:\ProgramData\MySQL\MySQL Server 5.5\data\SYMMCOAITTEST.CSV' not found (Errcode:2) QODBC3: Unable to execute statement

So whats making it go to c:\programdata\mysql.... instead of where the path is I defined?

[mchinand]

This post is deleted!

[Scott Krise]

Ok...I got it. You suggestion of quadruple backslashes did the trick. Actually, only within the file name thought.

This works:
updateStr = QString("load data infile '%1' into table tt_po_import FIELDS TERMINATED BY ',' LINES TERMINATED BY '%2'").arg("C:\\SYMMCO\\AITTEST.CSV").arg("\r\n");

Notice the \ is ok when identifying the carriage control and line feed.

Glad this works now...so thanks for the help! However....it makes absolutely no logical sense to me why you need \\ to make it work. If anyone can explain that, I'd love to hear it...but for now, Im just glad it works. Thanks again!!

[mchinand]

"The file name must be given as a literal string. On Windows, specify backslashes in path names as forward slashes or doubled backslashes. The character_set_filesystem system variable controls the interpretation of the file name."

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

The first time you escape the backslashes, they are used in creating your QString but MySQL needs the backslashes to be escaped, so you that's why you need four for every directory separator. Not sure why your initial command in MySQL works though.

[Scott Krise]

Yes...I just replaced the 4 backslashes with 1 forward slash, and it worked that way too. Thanks for the help!

[aha_1980]

@Scott-Krise

If your issue is solved, please mark this topic as SOLVED too. I'm doing it for you here.