Working with massive CSV files (>2gig)

[walker_2003]

Originally Posted by cvidler

Don't use Access. It's a toy database, for managing your home DVD collection, not for real work*.

As said SQL Express can deal with up to 4GB, and it's free (check the license agreement).

mysql is free free, has some good management GUIs/IDEs available.


*I've seen what happens when it's used for work environments and becomes an essential part of business, it's not pretty, and becomes expensive to rectify (because it requires full redevelopment in something that supports larger data sizes and multi-user access etc.).

Is this the program you refer to? http://www.microsoft.com/en-us/downl...s.aspx?id=1695

[cvidler]

Originally Posted by walker_2003

Is this the program you refer to? http://www.microsoft.com/en-us/downl...s.aspx?id=1695

Yep. that's SQL Express edition. (also previously known as MSDE, MS SQL Desktop Edition)

[Luke212]

Originally Posted by pittster

You can get SQL Express for free and it can handle up to 4Gb maybe give that a shot

express is 10GB now

Originally Posted by walker_2003

Is this the program you refer to? http://www.microsoft.com/en-us/downl...s.aspx?id=1695

no, get 2012 not 2008 lol

bugayev edit: please don't multi post.

[Snowblindnz]

I have worked with a 1.5gb csv file before, it can be a real pain.

If you want to open the file to browse, PFE works great. it looks crap, is really old, but works great with large files: http://www.lancs.ac.uk/staff/steveb/cpaap/pfe/

After trying every way I could think of to get the file into a database, I ended up doing the following:
1) Installed xampp (a WAMP stack).
2) Set the max php execution time to 0 in the php.ini config.
3) Wrote a (~20 line) php script that opened the file and read it line by line, dumping it into mysql line by line.

To import the whole thing, it took about 6 hours on a 1.5tb 7200rpm WD drive, and about 1.5 hours on a corsair 64gb SSD. This was on the same dev machine (win7, i7 920, 12gb ram)

Once in the mysql database, it worked fine (browsable via phpmyadmin, and php queries ran ok).

[gcflora]

Originally Posted by cvidler

Don't use Access. It's a toy database, for managing your home DVD collection, not for real work*.

[...]

*I've seen what happens when it's used for work environments and becomes an essential part of business, it's not pretty, and becomes expensive to rectify (because it requires full redevelopment in something that supports larger data sizes and multi-user access etc.).

I've heard this said many times and it's not necessarily true (IMO). If the person/people who create the MS-Access database are competent Access databases do not necessarily result in complete disasters. It's when people with no DB experience and no programming experience clobber something together when problems begin to arise. MS-Access is good, IMO, for the following:

• Quick-and-dirty analysis
• Quick-and-dirty tests/demonstrations
• Databases that are never going to be large or complex (famous last words, hey? )
• Single user 'private' databases

MS-Access is also not single user... it can happily cope with multiple user/access (well, in my extensive testing it can at least support a number of connections > 1)

But yeah, with the Express edition of things available these days MS-Access is probably pointless.

I'd also use PostgreSQL over MySQL any day!

[Luke212]

i have one company with 150 users accessing MS Access hehehe

its not ideal!

[gcflora]

Originally Posted by Luke212

i have one company with 150 users accessing MS Access hehehe

its not ideal!

yes, ok...

[Luke212]

Originally Posted by gcflora

yes, ok...

I work with even bigger companies and they are worse. There is a lot of money to be made updating companies processes.

[jezza323]

Originally Posted by Snowblindnz

I have worked with a 1.5gb csv file before, it can be a real pain.

If you want to open the file to browse, PFE works great. it looks crap, is really old, but works great with large files: http://www.lancs.ac.uk/staff/steveb/cpaap/pfe/

After trying every way I could think of to get the file into a database, I ended up doing the following:
1) Installed xampp (a WAMP stack).
2) Set the max php execution time to 0 in the php.ini config.
3) Wrote a (~20 line) php script that opened the file and read it line by line, dumping it into mysql line by line.

To import the whole thing, it took about 6 hours on a 1.5tb 7200rpm WD drive, and about 1.5 hours on a corsair 64gb SSD. This was on the same dev machine (win7, i7 920, 12gb ram)

Once in the mysql database, it worked fine (browsable via phpmyadmin, and php queries ran ok).

you do realise there is a mysql command to import a csv yeah? lot easier than writing a script to do it for you

[Tekin]

Originally Posted by Luke212

I work with even bigger companies and they are worse. There is a lot of money to be made updating companies processes.

Heh - my favourite for the moment is a company I'm working with that is dumping pi data (a plant information and control system) into an access database for reporting.

16,000 reporting measures a sec.

The custom query they have to strip, transfer and dump the data is unbelievable (and written in vb!). It is actually unbelievable in every sense of the word (it's even documented! Why would you document that!)

This is despite pi having one of the most elegant reporting systems I've worked with. Currently our proposed strategy is get everyone drunk and accidently spill beer on the access server then rebuild it all in reporting in about 2 days.

Anyways, back to the case at point.

We've done manual file manipulation in notepad++ on files around 2gb to do emergency once off edits. Not very elegant but it works. Some of the ideas in this thread are certainly interesting though. (jezza323: what is the command to load csv files directly? I'm really not a mysql guy...)

[jezza323]

Check this out

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Am working with the command atm, works fine

eg from bat file im using to do some basic SQL Server -> mySQL migration for a new product im coding at work (lots of variables)

%mysqlCmd% --host=%hostIP% --port=%hostPort% --user=%hostUser% --password=%hostPW% --database=%mysqlDBName% --local-infile=1 -e "LOAD DATA LOCAL INFILE \"%workdir%/staging_load.csv\" INTO TABLE staging_load FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';"

[Taceo Corpus]

Yep, mysql is the tool you need here.

[gcflora]

Originally Posted by jezza323

Check this out

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Am working with the command atm, works fine

eg from bat file im using to do some basic SQL Server -> mySQL migration for a new product im coding at work (lots of variables)

%mysqlCmd% --host=%hostIP% --port=%hostPort% --user=%hostUser% --password=%hostPW% --database=%mysqlDBName% --local-infile=1 -e "LOAD DATA LOCAL INFILE \"%workdir%/staging_load.csv\" INTO TABLE staging_load FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';"

Two (at least) issues I can see with that command. First is the assumption that fields cannot contain the ',' character. Second is the assumption that the fields cannot contain "\r\n". Both are allowed in CSV and therefore the assumptions are incorrect.

[f3n1x]

Originally Posted by gcflora

Two (at least) issues I can see with that command. First is the assumption that fields cannot contain the ',' character. Second is the assumption that the fields cannot contain "\r\n". Both are allowed in CSV and therefore the assumptions are incorrect.

The assumtions are only incorrect if they're assumptions, you're guessing that he knows nothing about the data he's working on.

I know sometimes working IT you get impression everyone else is a complete fool and fit only for converting oxygen to CO2, but it doesn't imho generally apply on OCAU tech forums.

[gcflora]

Originally Posted by f3n1x

The assumtions are only incorrect if they're assumptions, you're guessing that he knows nothing about the data he's working on.

Quite correct. But I assumed () that he was posting it for the benefit of the OP and in that case it doesn't matter that jezza knows his own data -- it's the OP's data that matter, and it's a fairly safe bet that jezza doesn't know walker's data. So, the assumptions good very well be incorrect and it's better that the OP (walker) knows about those assumptions... isn't it?