Parsing a huge complicated CSV file using C++

Question

I have a large CSV file which looks like this:

23456, The End is Near, A silly description that makes no sense, http://www.example.com, 45332, 5th July 1998 Sunday, 45.332

That's just one line of the CSV file. There are around 500k of these.

I want to parse this file using C++. The code I started out with is:

#include <iostream>
#include <fstream>
#include <string>
#include <sstream>

using namespace std;

int main()
{
    // open the input csv file containing training data
    ifstream inputFile("my.csv");

    string line;

    while (getline(inputFile, line, ','))
    {
        istringstream ss(line);

        // declaring appropriate variables present in csv file
        long unsigned id;
        string url, title, description, datetaken;
        float val1, val2;

        ss >> id >> url >> title >> datetaken >> description >> val1 >> val2;

        cout << url << endl;
    }
    inputFile.close();
}

The problem is that it's not printing out the correct values.

I suspect that it's not able to handle white spaces within a field. So what do you suggest I should do?

Thanks

Three questions: do you have any fields that include a comma by enclosing the field in quotes? Do you have any places an empty field is indicated by just two consecutive commas? If you have fields surrounded by quotes, do any of them have quotes embedded in the field (two consecutive quote marks are treated as an embedded quote)? — Jerry Coffin
– Jerry Coffin, Commented Aug 16, 2013 at 13:50
@JerryCoffin No, none of the fields include a comma, the fields are separated by a comma. An empty field is written as NULL, columns are tab separated and , ' and " are encoded as: &, ', and ". No, as far as I know, there are no embedded quotes. I'm sure the white spaces within one field (like title) are the root of the problem. — Uni
– Uni, Commented Aug 16, 2013 at 13:59
As described, that is a simple CSV file. It doesn't have quoted fields or fields that are split across lines, or malformed fields — those are what make a CSV file complicated. If you have a complicated CSV file, then you should find and use a CSV library; there are many such available, I'm sure. (One such comes from 'The Practice of Programming', an excellent book, available from cm.bell-labs.com/cm/cs/tpop/.) — Jonathan Leffler
– Jonathan Leffler, Commented Aug 16, 2013 at 14:21

andre · Accepted Answer · 2013-08-16 16:52:21Z

4

In this example we have to parse the string using two getline. The first gets a line of cvs text getline(cin, line) useing default newline delimiter. The second getline(ss, line, ',') delimits using commas to separates the strings.

#include <iostream>
#include <sstream>
#include <string>
#include <vector>

float get_float(const std::string& s) { 
    std::stringstream ss(s);
    float ret;
    ss >> ret;
    return ret;
}


int get_int(const std::string& s) { 
    std::stringstream ss(s);
    int ret;
    ss >> ret;
    return ret;
}

int main() {
    std::string line;
    while (getline(cin, line)) {
        std::stringstream ss(line);
        std::vector<std::string> v;
        std::string field;
        while(getline(ss, field, ',')) {
            std::cout << " " << field;
            v.push_back(field);
        }
        int id = get_int(v[0]);
        float f = get_float(v[6]);
        std::cout << v[3] << std::endl;
    }
}

edited Aug 16, 2013 at 16:52

answered Aug 16, 2013 at 13:50

andre

7,2856 gold badges50 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Uni Over a year ago

Thanks. I tried this one out and it prints out each line with fields separated with a space. One question: How do I transfer these fields into appropriate variables? Do I use the overloaded insertion after the second while loop?

andre Over a year ago

@Uni I added a vector. You can use it to access the values after the loop.

Jonathan Leffler Over a year ago

+1 ...but... Wouldn't it be clearer if you used a new string field in the inner loop instead of reusing line? I can see how this works; it is not obvious, though, and not reusing line would greatly improve its clarity. — Thank you!

andre Over a year ago

@JonathanLeffler I strive to be clear in my code so your absolutely right. Updated.

Uni Over a year ago

@andre: have run into another problem. I now have a vector of strings, but how do I split each string (each row of the vector, if I may make myself clear) a/c to url, title, description etc? I know from the csv data documentation the different sizes of the various fields. Like I know that id has long in datatype and its size = 20. I know url is char array of size 255.

|

Zac Howland · Accepted Answer · 2013-08-16 13:39:12Z

1

Using std::istream to read std::strings using the overloaded insertion operator is not going to work well. The entire line is a string, so it won't pick up that there is a change in fields by default. A quick fix would be to split the line on commas and assign the values to the appropriate fields (instead of using std::istringstream).

NOTE: That is in addition to jrok's point about std::getline

answered Aug 16, 2013 at 13:39

Zac Howland

15.9k1 gold badge28 silver badges43 bronze badges

1 Comment

jrok Over a year ago

Good catch, I missed the fact that some fields are string with whitespaces.

Jerry Coffin · Accepted Answer · 2013-08-16 14:44:07Z

Within the stated constraints, I think I'd do something like this:

#include <locale>
#include <iostream>
#include <sstream>
#include <string>
#include <vector>
#include <iterator>

// A ctype that classifies only comma and new-line as "white space":
struct field_reader : std::ctype<char> {

    field_reader() : std::ctype<char>(get_table()) {}

    static std::ctype_base::mask const* get_table() {
        static std::vector<std::ctype_base::mask>
            rc(table_size, std::ctype_base::mask());

        rc[','] = std::ctype_base::space;
        rc['\n'] = std::ctype_base::space;
        return &rc[0];
    }
};

// A struct to hold one record from the file:
struct record {
    std::string key, name, desc, url, zip, date, number;

    friend std::istream &operator>>(std::istream &is, record &r) {
        return is >> r.key >> r.name >> r.desc >> r.url >> r.zip >> r.date >> r.number;
    }

    friend std::ostream &operator<<(std::ostream &os, record const &r) {
        return os << "key: " << r.key
            << "\nname: " << r.name
            << "\ndesc: " << r.desc
            << "\nurl: " << r.url
            << "\nzip: " << r.zip
            << "\ndate: " << r.date
            << "\nnumber: " << r.number;
    }
};

int main() {
    std::stringstream input("23456, The End is Near, A silly description that makes no sense, http://www.example.com, 45332, 5th July 1998 Sunday, 45.332");

    // use our ctype facet with the stream:
    input.imbue(std::locale(std::locale(), new field_reader()));

    // read in all our records:
    std::istream_iterator<record> in(input), end;
    std::vector<record> records{ in, end };

    // show what we read:
    std::copy(records.begin(), records.end(),
              std::ostream_iterator<record>(std::cout, "\n"));

}

This is, beyond a doubt, longer than most of the others -- but it's all broken into small, mostly-reusable pieces. Once you have the other pieces in place, the code to read the data is trivial:

    std::vector<record> records{ in, end };

One other point I find compelling: the first time the code compiled, it also ran correctly (and I find that quite routine for this style of programming).

I like your solution. Only, I'm not experienced in prorgamming and hence, am not comfortable with handling structures. Vectors are easier.
A more elegant solution than what I had described. I like it!

Nev · Accepted Answer · 2013-08-25 21:12:06Z

I have just worked out this problem for myself and am willing to share!
It may be a little overkill but it shows a working example of how Boost Tokenizer & vectors handle a big problem.

/*
 * ALfred Haines Copyleft 2013
 * convert csv to sql file
 * csv2sql requires that each line is a unique record
 *
 * This example of file read and the Boost tokenizer
 *
 * In the spirit of COBOL I do not output until the end
 * when all the print lines are ouput at once
 * Special thanks to SBHacker for the code to handle linefeeds
*/
#include <sstream>
#include <boost/tokenizer.hpp>
#include <boost/iostreams/device/file.hpp>
#include <boost/iostreams/stream.hpp>
#include <boost/algorithm/string/replace.hpp>
#include <vector>

namespace io = boost::iostreams;
using boost::tokenizer;
using boost::escaped_list_separator;
typedef tokenizer<escaped_list_separator<char> > so_tokenizer;

using namespace std;
using namespace boost;

vector<string> parser( string );


int main()
{
vector<string> stuff ; // this is the data in a vector
string filename; // this is the input file
string c = ""; // this holds the print line
string sr ;

cout << "Enter filename: " ;
cin >> filename;
//filename = "drwho.csv";
int lastindex = filename.find_last_of("."); // find where the extension begins
string rawname = filename.substr(0, lastindex); // extract the raw name

stuff = parser( filename ); // this gets the data from the file

/** I ask if the user wants a new_index to be created */
cout << "\n\nMySql requires a unique ID field as a Primary Key \n" ;
cout << "If the first field is not unique (no dupicate entries) \nthan you should create a " ;
cout << "New index field for this data.\n" ;
cout << "Not Sure! try no first to maintain data integrity.\n" ;
string ni ;bool invalid_data = true;bool new_index = false ;
    do {
        cout<<"Should I create a New Index now? (y/n)"<<endl;
        cin>>ni;
    if ( ni  == "y" || ni  == "n" ) { invalid_data =false ;  }
        } while (invalid_data);
    cout << "\n" ;
if (ni  == "y" )
{
  new_index = true ;
  sr = rawname.c_str() ; sr.append("_id" ); // new_index field
}

// now make the sql code from the vector stuff
// Create table section
c.append("DROP TABLE IF EXISTS `");
c.append(rawname.c_str() );
c.append("`;");
c.append("\nCREATE TABLE IF NOT EXISTS `");
c.append(rawname.c_str() );
c.append( "` (");
c.append("\n");
if (new_index)
{
c.append( "`");
c.append(sr );
c.append( "`  int(10) unsigned NOT NULL,");
c.append("\n");
}

string s = stuff[0];// it is assumed that line zero has fieldnames

int x =0 ; // used to determine if new index is printed

// boost tokenizer code from the Boost website -- tok holds the token
so_tokenizer tok(s, escaped_list_separator<char>('\\', ',', '\"'));
for(so_tokenizer::iterator beg=tok.begin(); beg!=tok.end(); ++beg)
  {
    x++; // keeps number of fields for later use to eliminate the comma on the last entry
    if (x == 1 && new_index == false ) sr = static_cast<string> (*beg) ;
    c.append( "`" );
    c.append(*beg);
    if (x == 1 && new_index == false )
    {
      c.append( "`  int(10) unsigned NOT NULL,");
    }
    else
    {
    c.append("`  text ,");
    }
    c.append("\n");
    }
c.append("PRIMARY KEY (`");
c.append(sr );
c.append("`)" );
c.append("\n");
c.append( ") ENGINE=InnoDB DEFAULT CHARSET=latin1;");
c.append("\n");
c.append("\n");
// The Create table section is done

// Now make the Insert lines one per line is safer in case you need to split the sql file
for (int w=1; w < stuff.size(); ++w)
  {
    c.append("INSERT INTO `");
    c.append(rawname.c_str() );
    c.append("` VALUES (  ");
if (new_index)
{
    string String = static_cast<ostringstream*>( &(ostringstream() << w) )->str();
    c.append(String);
    c.append(" , ");
}
    int p = 1 ; // used to eliminate the comma on the last entry
    // tokenizer code needs unique name -- stok holds this token
    so_tokenizer stok(stuff[w], escaped_list_separator<char>('\\', ',', '\"'));
    for(so_tokenizer::iterator beg=stok.begin(); beg!=stok.end(); ++beg)
    {
      c.append(" '");
      string str = static_cast<string> (*beg) ;
      boost::replace_all(str, "'", "\\'");
//      boost::replace_all(str, "\n", " -- ");
      c.append( str);
      c.append("' ");
      if ( p < x ) c.append(",")  ;// we dont want a comma on the last entry
      p++ ;
    }
    c.append( ");\n");
  }

// now print the whole thing to an output file
string out_file = rawname.c_str() ;
out_file.append(".sql");
io::stream_buffer<io::file_sink> buf(out_file);
std::ostream out(&buf);
out << c ;

// let the user know that they are done
cout<< "Well if you got here then the data should be in the file " << out_file << "\n" ;

return 0;}

vector<string> parser( string filename )
{
    typedef tokenizer< escaped_list_separator<char> > Tokenizer;
    escaped_list_separator<char> sep('\\', ',', '\"');
    vector<string> stuff ;
    string data(filename);
    ifstream in(filename.c_str());
    string li;
    string buffer;
    bool inside_quotes(false);
    size_t last_quote(0);
    while (getline(in,buffer))
    {
        // --- deal with line breaks in quoted strings
        last_quote = buffer.find_first_of('"');
        while (last_quote != string::npos)
        {
            inside_quotes = !inside_quotes;
            last_quote = buffer.find_first_of('"',last_quote+1);
        }
        li.append(buffer);
        if (inside_quotes)
        {
            li.append("\n");
            continue;
        }
        // ---
        stuff.push_back(li);
        li.clear(); // clear here, next check could fail
    }
    in.close();
    //cout << stuff.size() << endl ;
    return stuff ;

}

Ben · Accepted Answer · 2017-01-06 02:47:10Z

You are right to suspect that your code is not behaving as desired because the whitespace within the field values.

If you indeed have "simple" CSV where no field may contain a comma within the field value, then I would step away from the stream operators and perhaps C++ all together. The example program in the question merely re-orders fields. There is no need to actually interpret or convert the values into their appropriate types (unless validation was also a goal). Reordering alone is super easy to accomplish with awk. For example, the following command would reverse 3 fields found in a simple CSV file.

cat infile | awk -F, '{ print $3","$2","$1 }' > outfile

If the goal is really to use this code snippet as a launchpad for bigger and better ideas ... then I would tokenize the line by searching for commas. The std::string class has a built-in method to find the offsets specific characters. You can make this approach as elegant or inelegant as you want. The most elegant approaches end up looking something like the boost tokenization code.

The quick-and-dirty approach is to just to know your program has N fields and look for the positions of the corresponding N-1 commas. Once you have those positions, it is pretty straightforward to invoke std::string::substr to extract the fields of interest.

Collectives™ on Stack Overflow

Parsing a huge complicated CSV file using C++

5 Answers 5

13 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

13 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related