Jump to content


Photo

"Empty" rows in a table help...


  • Please log in to reply
7 replies to this topic

#1 osyrys14

osyrys14

    Advanced Member

  • Members
  • PipPipPip
  • 42 posts

Posted 31 March 2013 - 11:33 PM

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.

 



#2 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,964 posts
  • LocationCanada

Posted 31 March 2013 - 11:48 PM

. . .  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.


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#3 osyrys14

osyrys14

    Advanced Member

  • Members
  • PipPipPip
  • 42 posts

Posted 31 March 2013 - 11:52 PM

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.



#4 davidannis

davidannis

    Freak Dr.

  • Members
  • PipPipPip
  • 617 posts
  • LocationOkemos, MI USA

Posted 01 April 2013 - 12:06 AM

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

Try using trim http://php.net/manua...nction.trim.php


Edited by davidannis, 01 April 2013 - 12:08 AM.


#5 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,964 posts
  • LocationCanada

Posted 01 April 2013 - 12:19 AM

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.


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#6 osyrys14

osyrys14

    Advanced Member

  • Members
  • PipPipPip
  • 42 posts

Posted 01 April 2013 - 12:27 AM

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'



#7 osyrys14

osyrys14

    Advanced Member

  • Members
  • PipPipPip
  • 42 posts

Posted 01 April 2013 - 12:33 AM

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?



#8 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,964 posts
  • LocationCanada

Posted 01 April 2013 - 12:49 AM

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


The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com