Generally, parsing and inserting a CSV file into MySQL (I'm using object oriented mysqli) is fairly straightforward but I have some new files with a very odd format that need parsing and I have no clue how to do it because it's in sections, each require inserting into (or updating an existing) entry. The first line contains the file name which can be ignored and is always prefixed with the word "product name" or something like that. Sorry, I don't have the file here so I'm paraphrasing.

Then there is a series of names and values as a pair of columns like:

Name1, value1
Name2, value2
Name3, value3
etc.

which continues for a way in similar fashion followed by a space (empty line), then another similar one followed by another space. These two sections always seem to have two columns with the title in the left column and the data values next to it in the right column. As I understand it, these will always have the same number of columns so I can create a table that has the column names of the left column values, then it needs to insert the right column values appropriately. If the particular file has already been uploaded, this same data would need to be replaced, not inserted, and the file name is stored in another table.

Then the rest of the data is in regular header, row and column but I need the data after the header row, not the header itself although the first header value can be used to determine where the values begin. Although the number of values in each section may vary, each one seems to begin with something specific that should help discern where one section ends and the next begins if only I knew how to do it! Any ideas?

give us some sample data

The data would look something like this although each section is much longer (particularly the last one) and each section goes into its own table. I do need to check if the file name already exists and, if it does, to run an update but if it doesn't, to insert. The file names themselves are in another table and then referenced by ID everywhere else. Thank you.

the sections are delineated by the blank lines?

Generally, yes, but I would rather look for some keyword at the beginning and ending of the first two sections to be sure.

Using the sample above as a basic guide and ignoring the first row entirely, it would start with the next row that begins with "A" and ends with the row that begins with "Bb" and has only one value each.

Section 2 begins with "A param" and ends with "u0 param" and has only one value each.

Section 3 begins with "Name1" and runs to the end of the file and has eight values.

So far I have only a single sample for testing purposes so I suppose I can write into the documentation any specific that it needs to work properly.

All values, by the way, are unsigned integers so there is no need for anything special in that regard.

[code]
$fh = fopen('path/to/some/file.csv', 'r'); //open the file for reading
$s = 1; //set section marker
while (FALSE != ($row = fgetcsv($fh)): //iterate the file
$row = array_map('trim', $row); //get rid of trailing spaces
/*
* test for which section we are in and change
*/
/*
* migrate section and advance the pointer
*/
if ($s == 1 && $row[0] == 'A Param'):
$s = 2;
$row = array_map('trim', fgetcsv($fh));
endif;
if ($s == 2 && $row[0] == 'Name1'):
$s == 3;
$header = true; //set the header to capture the field names in a special variable
endif;
if ($header):
$data['section'][$s]['header'] = $row; //capture the field names
$header = false;
else:
if ($s == 3):
$data['section'][$s]['data'][] = $row; //capture data in section 3
else:
$data['section'][$s][] = $row; //capture data in sections 1 and 2
endif;
endif;
endwhile;
fclose($fh);
[/close]

however if you are in charge of both ends of this transfer then consider:

1. writing the data directly to a remote database. you can connect to remote databases using php or even make it secure with ssh first.
2. transfer the data in a meaningful structure using something like json.

Thank you! I'll give it a try. I am also not totally adverse to loading the raw data whole into a temp table, then extracting it out again using SQL with which I am more familiar but your code is closer to what I am trying to do.

As for the data, it comes from an automated process with the only output option as CSV so there is not much choice in that regard but it is used only internally so security isn't a prime issue.

Your code makes good sense to me and I see nothing wrong but all I've been able to achieve is a server error. Also, the first section to grab is actually (usually) the third row which contains "A"; the second begins with "A param"; the third with "Name1".

The first two sections are actually a single row of data each but they are turned on their sides so somehow need to be flipped to get the values but not the captions.

I'll ultimately be sending the file through an upload form with up to three at a time so once this is working, I'll probably convert it to a function.

there is a close bracket missing (perils of typing straight into the edit box)

Much better, thank you! Well, it's not crashing anyway but it is giving no values either - just a blank screen but to me this is a form of success after so many server errors with every other attempt!

Anyway, I fixed the error and tried to rearrange the sections to suit the data but I'm not quite clear on what section 3 is searching:

i think you need to go back to the A Param and Name1 separators. the assumption is that you are in section 1. it is only if you are in section 1 and hit A Param , that you move to section 2. etc.

Okay, I'll do that although there are a couple lines at the top of the file that are unneeded so it seemed necessary for it to know where to look. Thank you.

I see

Thank you again! It seems to be working very well with no errors. I do still have some questions, though, if you're not adverse to it.

Sections one and two have only a header and a single row of data each but they are flipped vertically in the CSV file. I tried adding an array_flip() as shown in red below but it seemed to do so oddly. Maybe it's correct but it didn't appear to be in the print_r() screen dump.


I am presuming that it is here in this conditional where I need to add my mysqli inserts and/or updates. Is that correct? I am not sure how to reference sections one and two individually since they each get their own database table nor do any of the sections need the headers inserted.

Also, the array is showing the couple extra lines of nonsense at the top of the file and those between the sections. Is it possible to start AND stop it based on specific keywords? The last section, fortunately, runs all the way to the end and needs no specific end and, in fact, it may vary in length so an end point isn't desired.

Slightly off topic, but is there a way to add an error if fopen() fails to read the file?

so you want to flip sections 1 and 2.
use a function like this (you could probably make this more efficient, perhaps using only one cascading loop rather than two). pass the relevant section as an argument.

e.g

Thank you for this! I've only just gotten back to it but unfortunately there seems to be something amiss since, when I try to call it, all I get is a blank screen. I don't see anything obviously wrong but I'm still looking.

Once it's working, would I be able to run this function inside the main function so that it can write the values to their respective tables all together?

I spoke too soon as I forgot that I was trying to run it static in the browser for testing rather than as a function so with the function line remarked out, it does indeed work! It seems to be outputting the captions too which it needs to skip. Can that be done? In other words, I need three separate data streams of values only to feed to three different tables so they have to somehow be individual.

print_r the output and then show us the equivalent in the format that you want it.

There are over 1000 lines so I'll try some sections. The first bit in red, for example, shows the unneeded lines at the beginning and the remainder seems to be caption/value for the next two sections and is also showing the empty rows between sections.


I don't know if this is quite right bit since the first two sets of data needed have only a single row each, I was expecting something more like this:


Basically I need them into a format where the data only can be inserted into three separate tables

Reviewing the functionality more, I see that jflip() seems to actually be flipping every section into when appears to be a long single-column array. However, it's actually only the first two sections of data that need to be flipped while the third does not, ignoring the extraneous lines and captions already mentioned, into three separate and distinct arrays for inserting into three separate database tables. Perhaps I am misunderstanding the on-screen array output but that's how it seems to me although I'm tempted to try creating a test table and dump it in to see what it does!

you are only supposed to pass a section to jFlip. note the example above that passes only section 1 and not the whole data array.

Thank you! I realized my error as soon as I posted. Sorry to seem so ignorant but I've been away from programming for years and have forgotten most of what I knew.

That said, I'm back to this issue now after having several wrenches thrown into the works. It turns out that the CSV files will come in several different formats including one that is totally different (first column in the first two rows will not be inserted anywhere). There may be other variants too so it would be nice to be able to submit some kind of a template array into the function so that it knows what to do in order to make it as versatile as possible!

I suspect I'm making this more complicated than it is so any additional help would be greatly appreciated. I understand the concepts though not the details but maybe something like this that matches the database table:


and a data set up array something like that would indicate what to look for in the CSV to match it to the database:


In other words, something that would tell it to take the first two columns but insert them into the first and fourth columns of the database. This is just a stab in the dark as I'm not an expert on arrays although I know databases reasonably well. I just don't get how to put it all together!

Maybe another array to indicate how many sections, on what they begin, which to flip or not and what to actually use:


So here is what I have:

1) Form for uploading file(s) (working)
2) Logs the file(s) into a file_upload table (working)
3) During upload it also needs to parse out the sections into several different tables using the auto-incremented ID(s) from step one to keep track of them. (NOT working)

Although the form is there for uploading the file, I'm not sure how to apply the function to it while it's uploading. So far I have:



and

I have not looked at the code yet but here are a few home truths that might help

1. csv files are a specified format. whoever is giving you these files needs to be told to go and get his/her house in order.

2. if they are not, in fact, csv files then it is pointless trying to jury rig a csv parser to handle them.

3. do not aggregate lots of different files in different formats into a single file and try to parse that. Far better is to build parsers for each file that you will be frequently ingesting. these only take a few minutes each. Define the input standard and define the output standard and then build a decent class to handle it for you.

In defining the output standard have an eye to the use to which you will put the data. If you are using it for graphing, then consider what your graphing app needs.

For your graphing scripts, I would seriously consider not using those wrapper functions but just addressing the jpGraph engine directly. much more sensible until you know it is all working. then if you wanted to build encapsulated handlers, do so in a class.

Thanks. Unfortunately the CSV files are generated automatically by testing equipment so there is nothing to be done about their format. I need to find a way to work around it.

as you describe them, they are not csv files.

but anyway, the solution is to create file specific parsers. provide the input specification for each file and the desired output specification and we can help.

Thank you. Is there somewhere secure where I can send a couple samples? In the meantime, I'll talk with the engineers to see if there is anything on their end that can be done but somehow I doubt it. I don't think they are able to generate three separate files from the same testing process.

Also, I don't put this stuff into a function until it's working but I posted it that way to show what I needed. I've never worked directly with classes, though, so I'm not sure how. Anyway, thanks again.

I spoke with the engineers this morning and they are adamant that they need a single CSV file so I have no cloice. Rather than trying to do it all at once, though, it seems to me that it will need different functions to grab the pieces, flip them or not, and insert them while at the same time knowing which set of data belongs with which physical file. I'll go through what you already sent and see what I can come up with.

A quick related question, to use your jflip() function inside another function, does a copy of it need to be in the function?

Thank you.

nope. so long as the function itself is in the global scope it is globally accessible.


I'm not difficult to find if you search for me. Check out the 'about' page of my site at rathercurious.com (this site rightly prohibits publishing email addresses). I am a technology lawyer by trade and will happily treat anything you send me as confidential.

I had actually found you there but there is a message that the domain name expired a few data ago.

Okay, I think I have an idea that might work. I'll create a relatively simply function into which a few variables will be passed so that it can fetch out only a single section of the .CSV at a time, probably as a simple array.


If the data contains more rows/cols (depending upon orientation) than specified, those in between need to be padded with 0 value (rather than being Null)

On this existing code snippet, ['section'][0] gives a meaningless title from the top row of the file; ['section'][1] gives everything else. In the CSVs, there is also one case where it needs to be able to start on the second column so the $DataStart will need to know not to necessarily look at the first column. Also, I don't need the field names, just the data.

Below is a roughly modified portion of the code you wrote as a starting point but I am not sure how to feed it the data end point.

What do you think, will it work and can you help?

Quote:

I had actually found you there but there is a message that the domain name expired a few data ago.

Rathercurious.net. Not .com. My bad.

Thank you, I'll have to look it up tomorrow when I have the files available. In the meantime, I've played around a bit with my idea above and it seems that it will work if only I can figure out how to tell it where to look for the end of the section.

Something like this which, of course, isn't working yet: