Importing CSV with odd rows into MySQL

Question

I'm faced with a problematic CSV file that I have to import to MySQL.
Either through the use of PHP and then insert commands, or straight through MySQL's load data infile.

I have attached a partial screenshot of how the data within the file looks:
enter image description here

The values I need to insert are below "ACC1000" so I have to start at line 5 and make my way through the file of about 5500 lines.

It's not possible to skip to each next line because for some Accounts there are multiple payments as shown below.

enter image description here

I have been trying to get to the next row by scanning the rows for the occurrence of "ACC"

if (strpos($data[$c], 'ACC') !== FALSE){
    echo "Yep&nbsp;";
} else {
    echo "Nope&nbsp;";
}

I know it's crude, but I really don't know where to start.

So, with this piece of code: [Read lines](http://stackoverflow.com/questions/13246597/how-to-read-a-file-line-by-line-in-php) and a counter you can read the lines as you want — Jorge Campos, Oct 28 '13 at 23:07

score 1 · Accepted Answer · edited Nov 26 '14 at 14:20

1

If you have a (foreign key) constraint defined in your target table such that records with a blank value in the type column will be rejected, you could use MySQL's LOAD DATA INFILE to read the first column into a user variable (which is carried forward into subsequent records) and apply its IGNORE keyword to skip those "records" that fail the FK constraint:

LOAD DATA INFILE '/path/to/file.csv'
    IGNORE
    INTO TABLE my_table
    CHARACTER SET utf8
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES  TERMINATED BY '\r\n'
    IGNORE 4 LINES
    (@a, type, date, terms, due_date, class, aging, balance)
    SET account_no = @account_no := IF(@a='', @account_no, @a)

edited Nov 26 '14 at 14:20

jcane86

681
4
17

answered Oct 28 '13 at 23:21

eggyal

122,705
18
212
237

I get an error at "my_table", Syntax error unexpected IDENT_QUOTED, expecting TABLE_SYM – Robert de Jonge Oct 29 '13 at 00:49
I just pasted that in mysql workbench, replaced my_table with my name and the table name is underlined with that error – Robert de Jonge Oct 29 '13 at 01:03
ar_balance is the name of the table – Robert de Jonge Oct 29 '13 at 01:05
My bad. It should be `INTO TABLE my_table`. – eggyal Oct 29 '13 at 01:06
Ok, that resolved the error, but now it's erroring on "Error Code: 13. Can't get stat of '/var/lib/mysql/dailypricing/balance.csv' (Errcode: 2)" and I can load files from that directory, I chmodded it to 0777, what else can I try? – Robert de Jonge Oct 29 '13 at 01:21
Is the file on the same machine as the MySQL server, or the MySQL client? If the former, you must have the `FILE` privilege and the file must be located within the directory specified in the `secure_file_priv` system variable (if set). If the latter, you need to use the `LOCAL` keyword before `INFILE`. – eggyal Oct 29 '13 at 08:04
Thanks for your answer, I got it to work, I placed it in `C:/balance.csv` and used `LOCAL` and it worked fine :) – Robert de Jonge Oct 29 '13 at 15:10

score 0 · Answer 2 · answered Oct 28 '13 at 23:13

There are several approaches you could take.

1) You could go with @Jorge Campos suggestion and read the file line by line, using PHP code to skip the lines you don't need and insert the ones you want into MySQL. A potential disadvantage to this approach if you have a very large file is that you will either have to run a bunch of little queries or build up a larger one and it could take some time to run.

2) You could process the file and remove any rows/columns that you don't need, leaving the file in a format that can be inserted directly into mysql via command line or whatever.

Based on which approach you decide to take, either myself or the community can provide code samples if you need them.

score 0 · Answer 3 · answered Oct 28 '13 at 23:14

This snippet should get you going in the right direction:

$file = '/path/to/something.csv';

if( ! fopen($file, 'r') ) { die('bad file'); }

if( ! $headers = fgetcsv($fh) ) { die('bad data'); }

while($line = fgetcsv($fh)) {
  echo var_export($line, true) . "\n";
  if( preg_match('/^ACC/', $line[0] ) { echo "record begin\n"; }
}

fclose($fh);

http://php.net/manual/en/function.fgetcsv.php

Importing CSV with odd rows into MySQL

3 Answers3