Shell Programming and Scripting

Oh, I didn't know that.

Am I right in thinking all I need to do is rename the CSV filename to match the table and then do the mysqlimport?

I've done some tests, I do not have the right priveleges for FILE with mysql, and I can't seem to change them either.

Another easy option is to save your SQL query exactly as you would type it an the MySQL prompt in a text file, then:

Code:

mysql -u yourname -p < myquery.sql

If you can specify the filename in the query (and have permission), then you can do it that way.

Oh, I will try your solution with a dummy table and hopefully it will work!

Thanks!

Can you could use this command to load CSV files? I'm going to investigate this.

The problem I see is that my CSV file isn't a SQL query, and secondly I do not have any control over the contents of the CSV file.

There are parts I need to change, such as the date which is stored as text and needs to be converted to a proper date format.

Also there are lines in the CSV file I want to ignore, such as the first 2 lines, and ensure it understands line breaks as being the end of a row.

I'm going to investigate whether you can use the command to load csv files.

You can create a 'LOAD DATA INFILE' query and place it in the ".sql" file (the name is irrelevant). This is what I meant earlier, but I didn't specify that because I mistakenly thought your first post already mentioned it, but instead I see you wrote "MYSQL LOAD DATA," which may have been referring to something else.

The LOAD DATA INFILE syntax allows you to ignore any number of lines from the beginning of the file, and you don't have to convert a string date to a datetime if it's in a reasonable format.

Obviously you can tell the query that your file delimiter is a comma, and if you have strings which may include commas, use the ENCLOSED BY option.

MySQL :: MySQL 5.1 Reference Manual :: 12.2.6 LOAD DATA INFILE Syntax

You keep saying that you have no control over the CSV file. That's not true in any way. If you're able to write a script to rename it or move it to a temp folder, there's no reason why you can't run it through a little sed, awk, or Perl to do a little cleanup first. You're on a *nix command line -- you're empowered!

Thanks for clarification! I appreciate it!

I've now had the chance to test this, it does indeed work! Thanks a lot! I want to thank you for your help, I really appreciate it. My CSV file extracts its contents into a date-based folder (Format: YYYY-MM-DD), what I'm wondering is, is it possible to tell my `csv_import.sql` (which is where my big LOAD DATA FILE sits) to use the latest date in the folder structure? I tried the following but MySQL complained that there was an error.

Code:

 LOAD DATA LOCAL INFILE '/path/to/inbounddata/data/%Y-%m-%d/traveldata.csv'

Is there a way of either, passing a date argument to my .sql file, or perhaps telling this command to use the latest date as part of the folder structure? Thanks