Reading a CSV file Macro

[Elliot Bladen]

Hello,

Creating a simple macro to read in a CSV with PtID, E, N, Z data.

Got as far as calling the file_read_line function. From their I'm stuck as to how to get the data to come into 12d.

Any help, greatly appreciated

[Alexander Cook]

Your next step is to use the From_Text (Page 90 of the Macro manual, ID 2105) to turn your line of text into a dynamic array of text based on your delimiter.

You can then use those different bits of text to do whatever you want.

If you want to create elements based on coordinates you will need to parse the Text to Reals via another From_Text call (page 89, ID 31)

Below is a small excerpt from one of my macros

Code: Select all

while(1)
{
	Text line, _start, _end, name, side;
	Real start, end;
	Dynamic_Text line_bits;
	Integer bits;
	
	
	ierr = File_read_line(file,line);
	if(ierr == -1) break;
	From_text(line,'@',',', line_bits);
	Get_number_of_items(line_bits,bits);
	if(bits != 4)
	{
		Set_data(message,"Error with File Line " + To_text(counter),3);
		error = 1;
		break;
	}
	else
	{
		Get_item(line_bits,1,_start);
			From_text(_start, start);
			Append(start, starts);
		Get_item(line_bits,2,_end);
			From_text(_end, end);
			Append(end, ends);
		Get_item(line_bits,3,name);
			Append(name, names);
		Get_item(line_bits,4,side);
			Append(side, sides);

	}
	counter++;
}

The points to note there are that:
- I was expecting only 4 bits of data per line, so i had error checking
- My data was comma delimited which is the third argument in "From_text(line,'@',',', line_bits);"
- My start and end are for chainages, so i change them to Reals
- I use a underscore at the start of a variable name to denote that its not in the correct format (ie _start is the start chainage, but its text)

After filling your arrays, you would cycle through them and create elements with coordinates from the arrays.

Something along the lines of

Code: Select all

Model model = Get_model_create("Some Model");
Integer num;
Get_number_of_items(xs, num);  // Finding out how many items have been read
for(Integer i = 1; i <= num; i++)   // Looping through each item
{
	Real x, y, z;
	Text point_id;
	Get_item(xs, i, x);      // Getting the values from the arrays
	Get_item(ys, i, y);
	Get_item(zs, i, z);
	Get_item(ids, i, point_id);
	
	Element point = Create_super(0,1);  // Creating a point
	Set_super_use_3d(point,1);   // Telling the point to have a z value
	Set_super_use_point_number(point, 1);  // Telling the point to have an ID
	
	Set_super_vertex_coords(point, 1, x, y, z);  // Setting the coordinates
	Set_super_point_number(point, 1, point_id);  // Setting the point id
	
	Set_model(point, model);  // Setting the point to a model
}
Calc_extent(model);  // Calcing the extent of the model - must always be done when adding data to 12d

[Elliot Bladen]

Hey Alexander,

Thanks heaps for this!

Do you mind if I grab your text files that you used for this. I'm running through this example now. Will help me out heaps more if I can see the data you used.

Cheers

[Alexander Cook]

Elliot,

Attached is the csv from the first example. The second was just generic code that can apply to any csv of PtId,X,Y,Z

[Elliot Bladen]

Thanks mate,

I have written some code to get the data out of the CSV. If you have time would be nice to see if I'm on the right track. I haven't included appends to my From_text like your code because I'm not sure what that means. The other thing I'm not sure on was my Pt ID data and how to call that data with From_text.

Assume that my data in my CSV runs from left to right PtID,Easting,Northing,RL

while(1)
{
Text line_in, ptid, e, n, z;
Real easting, northing, height;
Dynamic_Text line_data;
Integer data;


rc = File_read_line(file_handle,line_in);
From_text(line_in,'@',',', line_data);
Get_number_of_items(line_data,data);
if(data != 4)
{
Print("Error");
break;
}
else
{
Get_item(line_data,1,ptid);
From_text(ptid, data);

Get_item(line_data,2,e);
From_text(e, easting);

Get_item(line_data,3,n);
From_text(n, northing);
Get_item(line_data,4,z);
From_text(z, height);

}

[Sam Cech]

you are doing well

just a thought, if you split the tasks into individual functions later on you might find it easier to troubleshoot errors, like not checking a return value somewhere

for reading a text/csv file
here is a function from a macro somewhere on this forum to help you get data out of a file

these are just the lines in the file as they are
you need to break out of the while loop on error when it fails to read the file or at the end of it.

Code: Select all

/*! @ingroup macro
 *	@brief
 *	reads contents of a file into an array
 *
 *  //TATRAS/forum macro
 *
 *	@param [in]	file_name
 *		A \b  Text file to read
 *
 *	@param [in,out]	 lines
 *	     An \b Dynamic_Text - lines contents
 *
 *
 *	@return		An \b Integer return value
 *               - returns number of lines in the file
 */
Integer from_file(Text file_name, Dynamic_Text &lines)
{
    Integer rv = 0;
    Null(lines);
    File file;
    File_open(file_name, "r", file);

    while (1)
    {
        Text line = "";
        if (File_read_line(file, line) != 0)
            break; // had issues with this not returning -1 see forum posts
        Append(line, lines);
    }
    File_close(file);
    Get_number_of_items(lines, rv);
    return (rv);
}

this is how you would use it

to get the lines in the file and process them... hence you can read other files later with the same function, not specific to just 4 item CSVs

but what you got is very close... well done, what will you do with those coordinates?

Code: Select all

Text file_name = "your.csv";
Dynamic_Text lines;

Integer no_lines = from_file(file_name, lines);

for (Integer line = 1; line <= no_lines; line++)
{
    // for each line
    Text a_line = "";
    Get_item(lines, line, a_line);

    Dynamic_Text words;
    //get csv items
    Null(words);
    From_text(a_line, '"', ',', words);

    Integer no_words = 0;
    Get_number_of_items(words, no_words);
    if (no_words != 4)
    { // skip if you dont have all 4
        continue;
    }

    //test each item is valid
    Text ptid = "";
    if (Get_item(words, 1, ptid) != 0)
        continue;

    Text tmp = "0.0";
    Real easting = 0., northing = 0., height = 0.;
    if (Get_item(words, 2, tmp) != 0)
        continue;
    if (From_text(tmp, easting) != 0)
    { // failed to get Real number
        continue;
    }
    tmp = "0.0";

    if (Get_item(words, 3, tmp) != 0)
        continue;
    if (From_text(tmp, northing) != 0)
    { // failed to get Real number
        continue;
    }

    tmp = "0.0";
    if (Get_item(words, 4, tmp) != 0)
        continue;
    if (From_text(tmp, height) != 0)
    { // failed to get Real number
        continue;
    }

    // now you have all data
    // what will you do with it?
}

[Phil Davies]

I know that you are on the basics of this macro, but once you get that worked out.....

When writing macros from files, any files, the easiest thing to do is to simply read the line and then print the line. If you can do that (you can already do that) read the line split it into tokens and print each token - by doing something as basic as this you get an understanding of what each step is doing and if you don't get the answer you expect, you can wind it back to see where it stops working correctly. Once this is all sorted, I would still print the line number and each line while you build the macro to easily see where anything goes skew along the way. Don't do this in the finished macro as it takes up valuable time.....

I too am very much in the vain of Sam, where I like functions that can be recycled into other macros, so his function to read each line into a dynamic text array is a good start. For this case, I also like to have a small Text_Edit_Box of 5 or so lines that show the first 5 lines in the file. This is also really helpful when you have a 20 million line file that takes an eternity to open in a text editor. Essentially when you pick the file, it runs a function to read the first 5 lines and populate the Text_Edit_Box. If you picked the wrong file then it is pretty obvious straight away.

Cookie's test for 4 tokens is a good one because you expect to know what is in the file, but you should also check that the 4 tokens are not heading information. If they are, you can break and fix the file or you can continue to the next line.

As you are doing, write your macro around the basic requirement - csv to to a model, then think of every permutation that someone else is going to do with that macro and expect it to work where it won't. Try to break it into as many separate individual functions as you can. Add tests for these things, and then give clear and concise comments as to why the processing stopped. Don't just say "Error" - nothing more frustrating when trying to find the error than a clue that simply says "Error". Alex's error that tells you which line the error occurred is so much better.

"Processing stopped on Line 1 - Header information found" - so what do you do here, test for it, if you find it, ignore it and continue or stop?

"Processing stopped on Line 92376 - No Z Value found" - again what do you do if the file has a missing z value, report it and continue or is it fatal enough to stop. If the X or Y was missing, this is definitely critical, but 12d can survive with no Z and no Point No......

How are you dealing with model/colour/symbol/linstyle etc etc

When you get to any of these points - feel free to ask more questions.

And finally we assume that you have the actual panel down pat and have moved onto what happens when you hit <Read>, if not then that is the best place to start. A sexy panel is all your users ever look at!!! Again feel free to post your entire macro and we will be sure to rip it apart......

Sam also pointed out that there is a treasure trove of macros already written on this forum, flick through them and get ideas on how to structure your functions and many other things. Posting macros is however a practice that has stopped as we all stick our own IP on our macros......

Keep at it - macros will do anything that 12d won't (except Limit 'Depth Range Polygons' to within a model of polygons apparently