Ignore empty lines in CSV import?

[expired user #341]'s profile image [expired user #341] posted 17 years ago in Import/Export Permalink
I have an excel-sheet, which I need to save as CSV and then import in to mySQL via Heidi. Sadly the Excel-Sheet seems to contain loads more "Returns" than it has lines, all of these get imported into mySQL obviously.

Would it be possible to add an option in the "import csv"-Settings to say "ignore empty lines"? This would - if checked - just import lines that actually contain more than just field-terminators and line breaks.

? Jan
ansgar's profile image ansgar posted 17 years ago Permalink
Unfortunately the LOAD DATA-syntax does not allow ignoring specific lines. Only a fixed number of top-lines can be ignored by "IGNORE X LINES".

See here: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]


That means we would have to first manipulate the file, maybe save a temporarily copy of it and then fire the LOAD-command.

Could be good. We should submit a tracker-item at http://rfe.heidisql.com/
[expired user #1821]'s profile image [expired user #1821] posted 17 years ago Permalink
while you're at it, maybe you could also consider adding a replace functionality through regular expressions :)
i have never had the need for it, but i can imagine it might be useful for some.

Please login to leave a reply, or register at first.