Testing import incongruancies in fixed-width imports

lcook

What is the best way to test why an import executed fewer rows than the import file?

More specifically, I had a fixed-width file that I wrestled with all day yesterday (see other post here) and finally got it in using PHP to read each line, substr() each variable out, then query to insert them individually.

The original file had 126,485 lines of text. The import caused 249,200 rows to be imported, and I quickly noticed that two extra zeroes were being added as their own row, so I did a query, first to pull all of those rows and verify that they were in fact blanks, then deleted all rows in the verified query. This left me with 124,601 rows in the table.

Now I have 1884 fewer rows than the original file and I need to find out why.

I've done a count on one line of text, and it is the same number that I assigned in my fgets() function (2141) so I cannot figure out why new lines were inserted with two zero's in the first field.

Is there a way to test an entire file to verify that every line is the same length?

bradgrafelman

In regards to your other post, I would say there are two things you should add:

Use [man]strlen/man to ensure that the current line being examined (e.g. $buffer) is equal to the length you expect it to be. If it isn't, log the line number (so that you can go back to the file and figure out what went wrong) and skip over that line (e.g. [man]continue[/man] the loop).
You still aren't checking to see if $db->query() succeeds and, if not, logging the error message returned by the SQL server. It's hard (and just plain silly) to try to diagnose problems when you don't even listen to the server when it tells you what/where they are.

lcook wrote:
Is there a way to test an entire file to verify that every line is the same length?

Read in the first valid line of data (e.g. skip the header area of the file if one exists), capture its length, and then continue looping through the rest of the lines and capture the line numbers of any lines whose length differs from the value previously stored.

lcook

Realized that the very last field in the table is being inserted with 11 characters instead of 13. While the substr() formulas should start with 0, the fgets() string length should actually be 2143 instead of 2141 because the manual says fgets() "returns a string of up to length - 1 bytes read from the file pointed to by handle." This is why rows with two extra zeros were inserted.

I know I need to change 2141 to 2143 in my while arguments, so would I also put another conditional statement in the while statement, like this:

$handle = fopen("D:\pos\pos.txt", "r");
if ($handle) {
 while (($buffer = fgets($handle, 2143)) !== false) {
        if (strlen($buffer) == 2142){do everything I want} 
        else {error_log("'Error on line '.<how do I insert line number here>", 3, "/pos/errors.log");}
        }

I did have $db->error; within my while statement, but this obviously did me no good since I can't scroll back up to see each individual one. I read up on how to log the error, but how do I make it line-specific in the code above?

I know how to put 'ignore 1 line' into the LOAD DATA LOCAL INFILE query, but how do you do it with fgets()? The 'if' statement I put in above should eliminate it if it's not the right length for this application, but going forward, how would I tell it to ignore the first one?

bradgrafelman

lcook wrote:
1. I know I need to change 2141 to 2143 in my while arguments

Personally, I disagree. I would instead use some larger value (e.g. 4096) there just to make sure you're reading in the entire line. The way you have it, if a line exceeds 2142 bytes then your code wouldn't be able to tell that it read in a malformed line and would instead parse it as if it were the correct length. To me, this is bad.

lcook wrote:
2. I did have $db->error; within my while statement

Yes, but you weren't a) checking to see if the query failed, b) using that error value if the query did indeed fail, and c) doing anything useful with the error value (e.g. outputting and/or logging it in a manner such that you can easily pair it with either the data in the file or the full SQL query that was executed which caused the error in the first place).

lcook wrote:
how do I make it line-specific in the code above?

You'll have to keep track of line numbers yourself - [man]fgets/man and the like don't tell you this (they can only tell you what "position" the pointer is at in the file, which doesn't necessarily give you the line number - e.g. if a malformed line was indeed encountered which would throw off the expected byte count).

To do so yourself, simply initialize a counter variable ($line_num is descriptive, though $i is often used for such mundane practices as counting things) before the while loop and then increment it at the end of each loop iteration.

Also note that instead of this:

$i = 1;
while()
{
  // ..

  $i++;
}

you could be fancy and switch to a for() loop instead, e.g.:

for($i = 0; ($buffer = fgets($handle, 2143)) !== false; $i++)
{
    // ...
}

lcook wrote:
I know how to put 'ignore 1 line' into the LOAD DATA LOCAL INFILE query, but how do you do it with fgets()?

I can immediately think of two ways to go about doing this:

Inside the looping structure, check to see if the line number you're on is less than or equal to the number of lines you wish to skip. If it is, simply [man]continue[/man] the loop rather than doing any processing.
Before the loop structure, simply call [man]fgets/man once for every line you wish to skip. Since you're skipping these lines, you don't need to store the return value anywhere.

Weedpacket

bradgrafelman wrote:
1. I know I need to change 2141 to 2143 in my while arguments
Personally, I disagree. I would instead use some larger value (e.g. 4096) there just to make sure you're reading in the entire line.

I'd be tempted to put 8192, just because it matches PHP's internal buffer size. If performance isn't a huge issue at this point, it might even be feasible to leave the argument out and just let the line be arbitrarily long (assuming that the stream fgets() is reading from supports arbitrarily long chunks - if it doesn't it would just max out at 8192 again).