[RESOLVED] Stuck with a text file queston?

[haynest]

Hello guys. I have just finished reading PHP and MYSQL for dynamic web sites by Larry Ullman. I definitely recommend this book for begginers. Anywayz I have started my own small project and am stuck. I have a tab delimited txt file I want to import, and I know how to import this file into my database, that is no problem. However, at the end of each line the record starts to become delimited(or seperated) by the character "|", rather than the tab? I am not sure if I need to make 2 statements that will first import my data by tab and then by "|". Or if I need to put more thinking into this. Or if anybody knows where I can look up some info, the manual was not so useful here. I will put an example text file with this post.

[drew010]

yes , once you split all the tab data into their own strings, figure which one contains the | data and then break that down further. most likely you can just hardcode the offset that contains the pipes since the file format probably wont change.

$lines = preg_split("/\t+/", $string);

print_r($lines);

$numbers = explode('|', $lines[sizeof($lines)-2]);

print_r($numbers);

[haynest]

Thanks drew, I am gonna check out this code and lookup these functions to get a better understanding of whats going on. If you saw the file that I posted you could see all the numbers seperated by 'pipes' at the end. There are actually 208 numbers that could be between those pipes, and they represent values like brick, paved driveway, garage and so forth. I'll post back soon!

[drew010]

yeah i copied the one line of the file into a string and ran that same code using the $string variable containing the whole thing and it worked out fine.
if i switched the \t+ into \t there were a lot of blank entries in the array, so either there are several tabs separating two values or maybe its intentional and it just means there's an empty value.

[drew010]

also i guess i wasnt too clear on the variable names but here's the test script i made...

<?php

$string = "95759        RESIDENTIAL     SINGLE FAMILY   1 NW FINDLAY    429900  FOREST PARK                             FINDLAY OH      45840   ACTIVE  S       THREE   TWO     ONE     THREE   ATTACHED        6-10 YEARS      3501+   HANCOCK         161     HARTMAN, PAUL   [phone]  7       COLDWELL BANKER GEYER   [phone]                                                  2.5     2.5     1995    4203    PER BUILDER/TAP 110X165 LIB                     HILLCREST ESTATES 6TH   ADD LOT 260 LIBERTY TWP.        28-1010528              2.5                             14X18   M       22X24   M       20X19   M       20X24   M       12X17   M       14X17   M                       8X16    M       SUN     21X20   M       OFC     12X20   M       M BA    23X12   M       CASH/CONV       C       117     R-1     Y       Hillcrest       N MAIN TO W BIGELOW,W TO GLENEAGLE,N TO PEPPER- PIKE,L (N) TO FOREST PARK,W OR L TO 1406 ON R,(N)       429900                                          1765.56         CLASS & BEAUTY DESCRIBES THIS HOME. CUSTOM HICKORY CABINETS & CORIAN COUNTER TOPS IN THE KITCHEN ALONG W/CERAMIC TILE FL. DELUXE OAK TRIM THROUGHOUT HOME. JACK & JILL GUEST BA, ABUNDANT CLOSET & PANTRY SPACE, INDIRECT LIGHTING & FIREPLACE IN                                                                                                                                                                                                                                                       1|22|41|42|48|52|54|65|67|75|79|91|99|103|104|108|110|111|114|117|119|120|121|122|123|124|131|136|137|141|148|153|155|157|160|166|167|170|185   C";

$lines = preg_split("/\t+/", $string);

print_r($lines);

$numbers = explode('|', $lines[sizeof($lines)-2]);

print_r($numbers);

?>

and the output

Array
(
    [0] => 95759
    [1] => RESIDENTIAL
    [2] => SINGLE FAMILY
    [3] => 1 NW FINDLAY
    [4] => 429900
    [5] => FOREST PARK
    [6] => FINDLAY
    [7] => OH
    [8] => 45840
    [9] => ACTIVE
    [10] => S
    [11] => THREE
    [12] => TWO
    [13] => ONE
    [14] => THREE
    [15] => ATTACHED
    [16] => 6-10 YEARS
    [17] => 3501+
    [18] => HANCOCK
    [19] => 161
    [20] => HARTMAN, PAUL
    [21] => [phone]
    [22] => 7
    [23] => COLDWELL BANKER GEYER
    [24] => [phone]
    [25] => 2.5
    [26] => 2.5
    [27] => 1995
    [28] => 4203
    [29] => PER BUILDER/TAP
    [30] => 110X165
    [31] => LIB
    [32] => HILLCREST ESTATES 6TH
    [33] => ADD LOT 260 LIBERTY TWP.
    [34] => 28-1010528
    [35] => 2.5
    [36] => 14X18
    [37] => M
    [38] => 22X24
    [39] => M
    [40] => 20X19
    [41] => M
    [42] => 20X24
    [43] => M
    [44] => 12X17
    [45] => M
    [46] => 14X17
    [47] => M
    [48] => 8X16
    [49] => M
    [50] => SUN
    [51] => 21X20
    [52] => M
    [53] => OFC
    [54] => 12X20
    [55] => M
    [56] => M BA
    [57] => 23X12
    [58] => M
    [59] => CASH/CONV
    [60] => C
    [61] => 117
    [62] => R-1
    [63] => Y
    [64] => Hillcrest
    [65] => N MAIN TO W BIGELOW,W TO GLENEAGLE,N TO PEPPER-
    [66] => PIKE,L (N) TO FOREST PARK,W OR L TO 1406 ON R,(N)
    [67] => 429900
    [68] => 1765.56
    [69] => CLASS & BEAUTY DESCRIBES THIS HOME. CUSTOM HICKORY CABINETS & CORIAN COUNTER TOPS IN THE KITCHEN ALONG W/CERAMIC TILE FL. DELUXE OAK TRIM THROUGHOUT HOME. JACK & JILL GUEST BA, ABUNDANT CLOSET & PANTRY SPACE, INDIRECT LIGHTING & FIREPLACE IN
    [70] => 1|22|41|42|48|52|54|65|67|75|79|91|99|103|104|108|110|111|114|117|119|120|121|122|123|124|131|136|137|141|148|153|155|157|160|166|167|170|185
    [71] => C
)
Array
(
    [0] => 1
    [1] => 22
    [2] => 41
    [3] => 42
    [4] => 48
    [5] => 52
    [6] => 54
    [7] => 65
    [8] => 67
    [9] => 75
    [10] => 79
    [11] => 91
    [12] => 99
    [13] => 103
    [14] => 104
    [15] => 108
    [16] => 110
    [17] => 111
    [18] => 114
    [19] => 117
    [20] => 119
    [21] => 120
    [22] => 121
    [23] => 122
    [24] => 123
    [25] => 124
    [26] => 131
    [27] => 136
    [28] => 137
    [29] => 141
    [30] => 148
    [31] => 153
    [32] => 155
    [33] => 157
    [34] => 160
    [35] => 166
    [36] => 167
    [37] => 170
    [38] => 185
)

[haynest]

Yes there are several tabs in the way because those are empty values. I think there are enough tabs to fit 126 different values. I still need to record them in my database as blank values. One more question, where could I find out what these individual characters mean
"/\t+/"

I think that you used these characters to separate the values from the tabs and put them into the array $string. I would like to get a better grasp on what those and other characters do.

[haynest]

Thanks for the file output drew. Thats perfect. I just need to be able to accept the blank tabs as blank values. Its crazy, I have a list that has a word value that I can set as a variable for each tab. I am going to see if I can import this into my database and post back!

[drew010]

ok then in that case you dont really need to use that. a simple explode("\t", $string) will return an array split by the tabs. the \t+ i used as a regular expression to denote a pattern of one or more tabs. that is a very simple pattern, as they can be much much more complex. if you are curious about that, www.php.net/pcre will teach you all about regular expressions.

so just use

$lines = explode("\t", $string);

print_r($lines);

$numbers = explode('|', $lines[sizeof($lines)-2]);

instead of what i gave you. now you will have all the blank values too.

[haynest]

Oh crap...drew. I think I may have made a newbie mistake. I think I wanted to try and import it directly to my db using something like

$result = mysql_db_query("database","LOAD DATA INFILE '/tmp/listings-residential' INTO TABLE residential FIELDS TERMINATED BY '|' ESCAPED BY '\' LINES TERMINATED BY '\r\n'",$dbh) or die(mysql_error());

Sorry, what do u think would be easier 1. importing my file into my php web page and sorting through the tabed values then sending the array to the database? or 2. Directly being able to import my file into my tables? I am gonna go through the manual and see what I can dig up. Let me know what you think

[drew010]

im not sure that would work simple because of the part with the numbers. you can do FIELDS TERMINATED BY '\t' and it will put all the text stuff in the right place, but then all the number parts would be put in one column. maybe thats what you want though, if so then you can use the load data infile command.
but for the most part, the code written above does essentially the same thing as the load data infile, except you yourself split the data and iterate over each line in the file and do a single mysql_query INSERT statement for each line.

[haynest]

I'm not exactly sure if I will every need the pipe separated characters? But if I plan for later and get them into the db now it I might save myself some time? I'm gonna go back to the books for a bit and get some info on importing that my file into the $string variable. I'll post back.