[RESOLVED] Importing Fixed-Width Files

[lcook]

Fixed width file is too large to use Access, so I attempted to write a PHP script to import using LOAD DATA LOCAL INFILE, but it is not working.

Can someone take a look and tell me why? Set up my table structure correctly (originally as part of the PHP script, then manually in phpMyAdmin to ensure it was not an error in this part).

Here is the import query (shortened by about 400 fields for brevity's sake):

$db->query("LOAD DATA LOCAL INFILE 'D:\x\yz' INTO TABLE abc SET 
cat_provider=SUBSTR(pos,3,2),
cat_sub_provider=SUBSTR(pos,1,2),
chow_count=SUBSTR(pos,5,2),
chow_date=SUBSTR(pos,7,8),
city=SUBSTR(pos,15,28),
numberwaivedindividuals=SUBSTR(pos,2093,6),
pendinglaboratoryclassification=SUBSTR(pos,2099,2),
previouslyregulatedindicator=SUBSTR(pos,2101,1),
sharedlabcrossreference_num_=SUBSTR(pos,2102,10),
sharedlabindicator=SUBSTR(pos,2112,1),
surveycertificateschedulecode=SUBSTR(pos,2113,1),
surveycomplianceschedulecode=SUBSTR(pos,2114,1),
surveytestvolumetotal=SUBSTR(pos,2115,9),
terminationcode=SUBSTR(pos,2124,2),
totalwaivedtestvol=SUBSTR(pos,2126,9),
ignore 1 lines");

Do I need to put 'fields terminated by '\r\n' ?

Also, I would welcome any references or suggestions on how to otherwise convert this file to CSV or some other format that is easily importable.

Many thanks.

[bradgrafelman]

1. Does the file contain any tab characters? The default for LOAD DATA INFILE is to parse the file into fields/columns using the tab character as the delimiter.

2. You use "SUBSTR(pos, ..." yet I don't see where you defined 'pos' ? See the MySQL manual for more, but I'm guessing you meant to load the data into a user variable (e.g. '@pos') and instead use that variable in the SUBSTR() calls.

[lcook]

No tabs, I don't think. There are blank spaces padding some fields, though. Each line is a new record, so would a user variable define that, or would I need to put 'fields terminated by '\r\n' in there somewhere?

[bradgrafelman]

From the manual page linked to above:

MySQL Manual wrote:

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

[lcook]

Thank you.

So, to clarify, I should state

fields terminated by '', lines terminated by '\n'

for a file with no delimiters and new records on each line

?

[bradgrafelman]

Yes, that would prevent MySQL from attempting to split the data into fields on its own.

[lcook]

Put the user variable in and assigned all of the arguments for SUBSTR so that the code now looks like this...

$db->query("LOAD DATA LOCAL INFILE 'D:\pos\EXTRACT.POSQ.OTHER' INTO TABLE pos (@pos)
SET 
ws_category=SUBSTR(@pos,1,2),
ws_tot_ann_test_vol_ppm=SUBSTR(@pos,2639,2),
ws_tot_ann_test_vol_waived=SUBSTR(@pos,2652,13),
filler=SUBSTR(@pos,2676,13),
fields terminated by '', lines terminated by '\r\n',ignore 1 lines");

...but I am getting an error on command line stating that there is an error in my SQL syntax at line 533 'fields terminated by '', lines terminated by '
',ignore 1 lines'

That line is actually showing as line 1083 in my script, not line 533. Line 533 is just part of my table creation query and looks like this:

`ws_multi_site_ind` VARCHAR(1),

Any idea why I am getting this error? I thought for sure it would work this time ...

[bradgrafelman]

lcook wrote:

That line is actually showing as line 1083 in my script

Irrelevant; MySQL can't see your PHP script and thus has no idea what line the query is on. This:

LOAD DATA LOCAL INFILE 'D:\pos\EXTRACT.POSQ.OTHER' INTO TABLE pos (@pos)

is line #1 in MySQL's eyes.

lcook wrote:

Any idea why I am getting this error?

Refer to the MySQL manual to get the order of things correct; you'll note that the "col_name_or_user_var" parameter is way down near the bottom of the list (just before the SET commands) and that the "FIELDS TERMINATED BY..." parameters are closer to the top (just after the 'tbl_name' parameter).

[lcook]

Okay, that makes sense.

I think I understand the manual's order this time.

$db->query("LOAD DATA LOCAL INFILE 'D:\pos\pos_test.txt' INTO TABLE pos_test character set utf8 fields terminated by '' lines terminated by '\r\n' (@pos) 
SET 
ws_category=SUBSTR(@pos,1,2),

Now I am getting this error:

"Can't load value from file with fixed size rows to variable"

I suppose I can do it without trying to manipulate any of the data. Can you point me in the right direction as to how to do this without using the user variable? Just to where the first argument of each SUBSTR is the string on one line?

Thanks again. Looking forward to clicking on "Resolved" today ;-)

[lcook]

I did read the manual; just missed this part:

User variables cannot be used when loading data with fixed-row format because user variables do not have a display width.

🙂

So, how would you approach importing a fixed-width file with fixed width rows into mySQL?

[bradgrafelman]

lcook;10981740 wrote:

So, how would you approach importing a fixed-width file with fixed width rows into mySQL?

Given the above limitation (which I myself was not aware of either - my apologies for leading you down the wrong path), I would say use PHP to [man]fgets/man the lines, [man]substr/man'ing the data out, and then INSERT'ing it manually.

Is it imperative that this task be done quickly and efficiently? If not, then the performance hit you'll take by doing the processing in PHP might not matter.

[lcook]

Quick/efficient is good, but I will be happy if I can just get it all in there today.

fgets() looks like it needs to use fopen. Would it be correct to do this (mimicking manual the best I can):

$handle = @fopen("D:\pos\pos_test.php", "r");
if ($handle) {
    while (($buffer = fgets($handle, 2142)) !== false) {
        echo $buffer;
    }
    if (!feof($handle)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle);
}

After this is where I get lost ... So I pull a line from the file, then do a query to INSERT using the user variable and setting each of the columns with SUBSTR?

Can I do a foreach statement to repeat this for each line?

Thanks again.

[bradgrafelman]

lcook wrote:

So I pull a line from the file, then do a query to INSERT using the user variable and setting each of the columns with SUBSTR?

Once you get the line ($buffer in your code above), you can either define a bunch of PHP variables for readability, or you could just use [man]substr/man and concatenation (or, better yet, [man]sprintf/man) to throw the pieces into the correct location within the SQL query string.

lcook wrote:

Can I do a foreach statement to repeat this for each line?

Er, no, because you're already looping through the file line-by-line - that's what the while() loop is for.

[lcook]

Okay, great. I was able to do an import through Heidi, just keeping it as a fixed width file and defining the lengths of all of the columns in the create table statement. :-)

Thanks again for all of your very timely help.

[lcook]

Got it to work on my test file of only 1 line. Now I am trying to get it to work on the larger file with multiple lines with no success.

Again, widths are all defined in the length of the column in the mySQL table. Simply trying to tell mySQL to insert each row and break it up into the appropriate columns.

Might be relevant to note that the file is in a .OTHER format ... It opens up in Notepad++ and the properties show it as a text format, but the extension is .OTHER

When I try to save it as .txt, all of the rows look skewed.

[lcook]

Oh I forgot about your prior reply to use fgets. Please disregard.

[lcook]

Back to square one. Please take a look at this and tell me if I have things in the right order. I wasn't sure if the mysql query should go in the while statement or if it should be outside of it, and if the values are correct.

Thanks again.

handle = @fopen("D:\pos\pos.txt", "r");
if ($handle) {
    while (($buffer = fgets($handle, 2142)) !== false) {
        echo $buffer;
		$ws_category_subtype_ind=SUSBTR($buffer,1,2);
$ws_category=SUSBTR($buffer,3,2);
$ws_chow_cnt=SUSBTR($buffer,5,2);
$ws_chow_dt=SUSBTR($buffer,7,8);


$db->query("INSERT INTO pos_test (
`ws_category_subtype_ind`,
`ws_category`,
`ws_chow_cnt`) VALUES (
$buf[$ws_category_subtype_ind], 
$buf[$ws_category], 
$buf[$ws_chow_cnt],)");
}
    if (!feof($handle)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle);
} 

[bradgrafelman]

(Thread un-marked as resolved.)

I don't know if some of these errors were introduced when you posted an abridged version of your actual code, but here's a review of the code you posted as-is:

1. Missing $ in front of 'handle' on the first line.

2. Don't use the '@' error suppressor (ever, IMHO).

3. Be careful when using backslashes in double-quoted path strings. Because of the ambiguity, I personally always use a forward slash regardless of the OS.

4. Note that core functions such as [man]substr/man are often written in lower case (this isn't MySQL, y'know). This isn't an error (since function names aren't case-sensitive), just wanted to clarify.

5. You never verify that $db->query() executed successfully.

6. Your query string refers to an undefined array $buf.

7. You've got an extra ',' at the end of the VALUES list.

lcook wrote:

I wasn't sure if the mysql query should go in the while statement or if it should be outside of it

Why weren't you sure? Think about it: do you want the query to only execute once, using one set of values, or do you want it to execute for every line in the file? If you can answer that question, then the placement of the call should be obvious.

[lcook]

The substr() function was actually written as SUSBTR so I had to fix that anyway.

Unfortunately, it is still not working. Here is the revised version:

$handle = fopen("D:/pos/pos.txt", "r");
if ($handle) {
    while (($buffer = fgets($handle, 2142)) !== false) {
        echo $buffer;
$ws_category_subtype_ind=substr($buffer,1,2);
$ws_category=substr($buffer,3,2);
$ws_chow_cnt=substr($buffer,5,2);

$db->query("INSERT INTO pos_test (`ws_category_subtype_ind`,
`ws_category`,
`ws_chow_cnt`) VALUES (
$buffer[$ws_category_subtype_ind], 
$buffer[$ws_category], 
$buffer[$ws_chow_cnt]
)");

echo $db->error;

}
if (!feof($handle)) {
    echo "Error: unexpected fgets() fail\n";
}
fclose($handle);
} 

Thanks for taking another look for me to see if anything jumps out at you. I know the connection is good, but when I put it in the command line, I get nothing - no error - and nothing showing up in mysql.

[bradgrafelman]

Few more problems I forgot to mention in my last post:

1. Inconsistent whitespace/indentation used. Using proper spacing makes it a lot easier to analyze code; code tha tisimpro perlyspaced (orevenworse,notspaced/indentedatall!) isqu itehard torea din compar ison.

2. Your values inside the VALUES() list are both: a) unescaped/unsanitized (e.g. using [man]mysql_real_escape_string/man), and b) unquoted (all strings must be surrounded with quotes - just as they would in PHP).

3. You're now using $buffer as if it was an array. It isn't.

   In addition, you're trying to use the substrings you created above as the array index (rather than just using the substrings themselves.. aren't these the values you want?).