"Empty" rows in a table help...

[osyrys14]

I have a DB that I've been working on importing data to from text files.  The data imports fine, but if its a report that has no data (or just a header) it inserts what seems to be a blank row.  usually, I can delete these with a simple SELECT * FROM table WHERE name=''; to be sure, and DELETE FROM table WHERE name='';, neither of which is returning any results.

 

Also, is there a way to check for this on the import so I won't have the empty rows to deal with in the future.  

 

Thanks so much for any help that anyone can provide.

 

[Psycho]

. . .  is there a way to check for this on the import so I won't have the empty rows to deal with in the future.

 

Yes, of course. Simply do a check of the data before inserting.

[osyrys14]

My apologies, I worded that wrong.  The rows appear to be empty, but using the SQL commands above do not find or delete them.  The text files are aparently filled with something that is showing as blank, but not truly empty.  And it's not on an insert statement, they are hourly files we import once a day for querying later for usage statistics.  Like an activity log.

[davidannis]

Could you have some whitespace characters, perhaps spaces or tabs in the data?

Try using trim http://php.net/manual/en/function.trim.php

Edited April 1, 2013 by davidannis

[Psycho]

And it's not on an insert statement, they are hourly files we import once a day for querying later for usage statistics.

 

And you are you processing those import files to create records in the database? If you are using something like MySQL "LOAD DATA" on a file that is not "clean" then you are going to end up with garbage. I'm guessing the same thing as davidannis. You should pre-process the file before creating the records to remove any invalid data.

[osyrys14]

I've recently tried using this statement, since I found another column in the row that DOES have data that I don't need, so I try this... 

 

DELETE FROM cdr WHERE tablename LIKE '%billing_file%';

 

Still no results, when I know billing_file is in that field, but that's just the start of the name, so I can't use tablename = 'billing_file'

[osyrys14]

That would be great Psycho.  How do I go about doing that?  Something built into the MySQL server, or something else I need to get?

[Psycho]

That is for YOU to decide. I have no clue about the source of the data you are using, the requirements of the data to be stored, etc. etc. But, based upon your statements, you are getting records that should not exist. So you need to clean it up. One way would be to process the data into individual records for the database - removing/skipping any that should be considered "invalid", then running the query to insert all the valid records