Load Data Local, How to Skip the First Line

LOAD DATA LOCAL, How do I skip the first line?

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

(reference)

Skipping first column with LOAD DATA INFILE

Linux:

LOAD DATA INFILE '/home/frank/try_this123.txt'
INTO TABLE final
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(cdatetime, address,district,beat,grid,crimedescr,ucr_ncic_code,latitude,longitude)
set id = NULL;

or Windows:

LOAD DATA INFILE 'c:\\nate\\try_this123.txt'
INTO TABLE final
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(cdatetime, address,district,beat,grid,crimedescr,ucr_ncic_code,latitude,longitude)
set id = NULL;

.

mysql> select * from final;
+----+-------------+---------------------+----------+------------+------+-------------------------------+---------------+-------------+---------------+
| id | cdatetime   | address             | district | beat       | grid | crimedescr                    | ucr_ncic_code | latitude    | longitude     |
+----+-------------+---------------------+----------+------------+------+-------------------------------+---------------+-------------+---------------+
 | 1 | 1/1/06 0:00 | 3108 OCCIDENTAL DR  | 3        | 3C         | 1115 | 10851(A)VC TAKE VEH W/O OWNER | 2404          | 38.55042047 | -121.3914158
 | 2 | 1/1/06 0:00 | 2082 EXPEDITION WAY | 5        | 5A         | 1512 | 459 PC  BURGLARY RESIDENCE    | 2204          | 38.47350069 | -121.4901858
 | 3 | 1/1/06 0:00 | 4 PALEN CT          | 2        | 2A         | 212  | 10851(A)VC TAKE VEH W/O OWNER | 2404          | 38.65784584 | -121.4621009
 | 4 | 1/1/06 0:00 | 22 BECKFORD CT      | 6        | 6C         | 1443 | 476 PC PASS FICTICIOUS CHECK  | 2501          | 38.50677377 | -121.4269508
+----+-------------+---------------------+----------+------------+------+-------------------------------+---------------+-------------+---------------+

I got that to work without any enclosing demarcations like single or double quotes. The problem is, what is going to happen when, say, your address has commas and it throws off all your data with a shifting problem.

That is why, ideally (read: almost absolutely), you need the data wrapped in double quotes in general unless your data is generated by you and almost simplistic, like:

1,2,cat,14,8

So, in the case of 3rd party systems when there is no control over how the data is pumped in, people have to write ETL routines to scrub the data first to get the data ready for imports with adequate fail-safe wrappers.

Mysql Load Data - Is There A Way To Ignore first column?

Solved it thanks to suggestion in comments of a related question.

load data infile '/data/data.csv'
into table Staging
fields terminated by ',' enclosed by ''
lines terminated by '\r\n'
ignore 1 LINES
(@discard,col1,col2,col3)
set col0ID=null;

Essentially it loads the first empty column denoted by the , (at the start of the line) into a variable called @discard.

This now reads the data of 78 rows before different unrelated error encountered...

Loading Data - Skip first line & Columns Don't match

Try this:

load data local infile 'C:\\bla\blah\file.txt' into table sources_1
fields terminated by ','
       optionally enclosed by '"'
lines terminated by '\r\n'
ignore 1 lines
(name, address, city, state)

It puts the data from the CSV directly into the appropriate columns, no user-variables are needed. Omitting the ID column will cause it to be filled in with the auto-increment value.

The IGNORE 1 LINES clause should be before the column names.

LOAD DATA INFILE: can't ignore 1st row while skipping columns?

Syntactically your statement is correct and should work as expected.

mysql> create table tablename 
    -> (
    ->   col1 varchar(32), 
    ->   col2 varchar(32), 
    ->   col3 varchar(32), 
    ->   col4 varchar(32), 
    ->   col5 varchar(32)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> LOAD DATA INFILE '/tmp/filename.csv' 
    -> INTO TABLE tablename 
    ->   FIELDS TERMINATED BY ';' 
    ->   LINES TERMINATED BY '\n' -- I'm on a Mac so I'm using \n instead of \r\n
    ->   IGNORE 1 LINES 
    -> (@col1, @col2, @col3, @ignore, @ignore) 
    -> SET col1 = @col1, col2 = @col2, col3 = @col3
    -> ;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tablename;
+-------+-------+-------+------+------+
| col1  | col2  | col3  | col4 | col5 |
+-------+-------+-------+------+------+
| test  | test  | test  | NULL | NULL |
| test2 | test2 | test2 | NULL | NULL |
| test3 | test3 | test3 | NULL | NULL |
| test4 | test4 | test4 | NULL | NULL |
+-------+-------+-------+------+------+
4 rows in set (0.00 sec)

Now since you're getting a syntax error my guess is that the problem is most likely not in the LOAD DATA statement itself but rather in C# code that you use to build and execute the query string.

LOAD DATA INFILE consistently skips first line, while not set to IGNORE

"12-03-2020" cannot be put directly be put into a DATE column. Instead, put it into an @ variable, then use str_to_date(...). (Let us know if you need help; there are many examples floating around.)

I see C3 AF, which is utf8 for ï, as in ... beïnvloegen ... -- Does that sound "right"? CHARACTER SET utf8 should have read it correctly.

The initial EF BB BF is "BOM". I don't know if LOAD FILE is smart enough to silently skip over it. This may be causing your problem. One approach is to edit the file to remove the first 3 bytes.

Later comes 0D 0A 0D 0A, which matches your description of there being 3 header lines, the second being blank. And LINES TERMINATED BY '\r\n' should be correct for that.

Load data infile skipping 1 row in every each rows

Are you sure your local data csv file line termination character is '\n' and not for example '\r\n' ?

MySQL load data based on first line

You can specify the order of the columns in your Query. I'd recommend to add a character set to the query aswell as an "OPTIONALLY" for the enclosure.

But let's have a look:

CREATE TABLE `foo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `bar` varchar(255) DEFAULT NULL,
  `baz` varchar(255) DEFAULT NULL,
  `bla` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

vagrant@fancyhost:~$ cat foo.csv

bar;baz;bla

"test bndg";bLoad Data Local, How to Skip the First Lineaaaaz;"yeyo"

"I looooooove dummy data";debug;dummy

test1;test2;test3

mysql> LOAD DATA INFILE '/home/vagrant/foo.csv' INTO TABLE foo CHARACTER SET utf8 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (bar,baz,bla)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM foo;
+----+-------------------------+----------------+-------+
| id | bar                     | baz            | bla   |
+----+-------------------------+----------------+-------+
|  1 | test bndg               | bLoad Data Local, How to Skip the First Lineaaaaz | yeyo  |
|  2 | I looooooove dummy data | debug          | dummy |
|  3 | test1                   | test2          | test3 |
+----+-------------------------+----------------+-------+
3 rows in set (0.00 sec)

mysql> LOAD DATA INFILE '/home/vagrant/foo.csv' INTO TABLE foo CHARACTER SET utf8 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES (bar,bla,baz)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM foo;
+----+-------------------------+----------------+----------------+
| id | bar                     | baz            | bla            |
+----+-------------------------+----------------+----------------+
|  1 | test bndg               | bLoad Data Local, How to Skip the First Lineaaaaz | yeyo           |
|  2 | I looooooove dummy data | debug          | dummy          |
|  3 | test1                   | test2          | test3          |
|  4 | test bndg               | yeyo           | bLoad Data Local, How to Skip the First Lineaaaaz |
|  5 | I looooooove dummy data | dummy          | debug          |
|  6 | test1                   | test3          | test2          |
+----+-------------------------+----------------+----------------+
6 rows in set (0.00 sec)

You can change the order easily according to your example :)

---

Related Topics