Im wondering how i can parse this into an array , where field;value are paired. So i can then try and import them to a mysql database where field == mysql table field.
D;170427000510;SCNT;0T;LAT;0T;LON;0*T
D;170502171817;MINVi;3.66;PTi;25.8;AVGVi;3.71;WL02;2.86*A;OCi;9.95
D;170502171823;SDB;0T;RSSI;0T
Any help would be appreciated!
How do you connect the three lines of text? That is to say, in your text file, can you just load three lines of text at a time, parse the field names and values, and then insert them into the database? Or do you need extra processing to check how they link?
The issue with just processing them a line at a time is that each line doesn’t contain the same fields. So you’d either need to have one database table with all of those columns in it, fill them in three file lines at a time until you have a complete data set and then insert into the table, or have three tables, one for each of the file line layouts.
If it didn’t matter to have lots of blank entries, you could have one single table and just insert the first row with the values in the first line and the rest empty, the second row with the values from the second line, and so on. But (although I don’t have a lot of real-world experience) I’d say that would be a very poor database design and lead to a lot of issues when retrieving data.
Regardless of how you decide to design the database, the actual code is quite straightforward, something like this pseudo-code:
while there's another line in the text file {
read the line
validate the contents of the line
explode the line into an array
take the first two entries as date/time and record type if that's what they are
loop through the rest to build the insert query
run the query
}The text file is the output of a monitoring unit and isn’t necessarily fixed to three lines there can be multiple lines of data in the text file. I know that each line doesn’t contain the same fields, thats why i’m so stuck i can’t find anything on semicolon delimited text files with this type of structure.
I thought about somehow pairing each field;value into an array and then inserting it into the database based on whether the field matches the column name. I have all of the fields pre defined into a table already its just matching the field with the column name and then inserting the value into it which is also another struggle.
Could you perhaps link some manuals or docs on some of those parts of code that i can relate to?
I don’t have any links unfortunately. Linking the field name and value is simple enough as long as they’re always in pairs, just explode() the line and loop through incrementing the field count by two.
Another way might be to have two tables - the first one is the line header:
id datetime
0 170427000510
and the second is the line detail
id header_id field value
0 0 SCNT 0*T
1 0 LAT 0*T
2 0 LON 0*T
Every time you get a new line, you create a new entry in the header table which contains only the contents of the D field and an auto-incrementing id. You then link multiple field / value pairs in the second table using the id from the first table, so for your first line there will be one header row and three detail rows, the second will have one header and five detail rows, and so on.
I guess the question should really be - how do you want to extract the data to use it once you have it in the database? And the second one is, how much PHP experience do you have?
you can start from this one: php.net/explode
Cheers for the detailed description droopsnoot! I know a little bit of php mostly basics, I’m still learning. For the field part i meant more so attribute rather than actual field column with the headers contained. Sorry if i wasn’t very clear on that. Im quite familiar with databases so i will try and show you an example of what I’m trying to insert the data into.
(Rough Example)
After i have the import stage figured out i will later add the id column. Thanks for explode manual colshrapnel!
That’s fine, but using the example text from your first post, you’re not going to achieve that database layout. What you’ll get is something like
D SCNT LAT LON MINvl PTi
170427000510 0*T 0*T 0*T <null> <null>
170502171817 <null> <null> <null> 3.66 25.8
unless there’s some way of linking the groups of text lines until you know that you’ve got a complete “set” of fields. That’s what I was asking above, how do you link the lines to form that set of fields? Will the time be the same for a group, for example? If it will, then it’s much more simple:
get the next line
see if there's already a row in the database for that date/time string
if no:
explode the string into an array
parse what fields you have
insert the new row
if yes:
explode the string into an array
parse what fields you have
update the existing row
loop around to topYou could pair each value into an array until you’ve got a full set, as long as you’re absolutely sure that you will always get a full set, or can recognise when to abandon the array and start again. If the lines are always in the same order, then that should be easy to do.
Having parts of the records set as null is fine for what I’m working with as well.The text file and its contents will vary to an extent there is a set number of fields but depending on the unit fields/headers will vary.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.