editor for large database files CSV/SQL

CSU · 06-04-2011 01:50PM #1

I'm looking for a tool that lets you edit large database dumps easily.

Until now I have been using OO.o but that eats itself when presented with anything over 500k rows, also it is mainly used for calculations and CSV files and all that I don't need and just adds overhead to the software I imagine, I just need a simple text editor capable of dealing with delimiters, columns and ~millions of rows.

Also, OO.o "assumes" that if you are importing a CSV that you want to work with comma delimited files which causes havok upon export meaning you have to watch and change it back after every edit the file. To be honest I don't get why CSV's use one of the most common characters "the comma"???...there's commas bloody everywhere in text and description fields???, I've found myself using "|" and "^" just to be safe.

Help?

Malice · 07-04-2011 08:58AM

Have you tried NotePad++? I can't say I've used it for editing files 500kb or bigger but it's a reliable editor for pretty much everything else I've used it for and it's free so you can download it and check it out easily.

There is also UltraEdit but I haven't used that in years.

As for getting away from CSV files, have you tried using tab separation instead?

CSU · 07-04-2011 01:20PM

I guess I'm just set in my ways with the "|" & "^" and all backups I have use em' so meh - it works...

NPP seems to hang on large files also, or it just takes forever to load, CSVed seems quick although abit rough around the edges it loaded the 700,000 row file in under a minute compaired to the ~5 or so minutes of OO.o, I notice also that CSVed actually uses the CPU...there's nothin worse than watching a piece of software eat itself when you ask it to do something while at the same time refusing to make use of hardware resources just plodding along wreaking me head:pac:

FSL · 07-04-2011 03:02PM

Just out of interest why do you need to dump the database to edit it?

Malice · 07-04-2011 03:25PM

Can you restrict the set of data that you're working with? So rather than working with a year of stuff you can just work on January, February etc. That might ease the strain on the computer's processor.

CSU · 07-04-2011 07:03PM

...to answer both questions: The CSV's I'm working with are from product vendors, they contain the usual info (price, dimensions, info etc.), so in order for me to use the CSV's I need to build a database from them using template.

I'm working with a Joomla component (virtuemart), and to start off I simply created one fictitious product in the GUI of the component > went to the database and dumped the tables that stored this info, I then use this as a template for any CSV's I get - the CSV layouts differ greatly from vendor to vendor and untill I'm savvy enough to write a script to deal with this difference in CSV and database structure I'll be doing it by hand for now.

I guess I'll just have to chop up each each CSV that is over ~500000 rows long, simple copy/paste in NPP is all it needs...

amen · 07-04-2011 07:46PM

maybe I'm not under standing the problem but you have a list of products from a vendor in csv format (that you can open in Excel) and you want to insert into a database?

1: have you asked the vendor if the have data in a different format? this has to be a common problem for the vendor
2: Could you not use some scripting language phyton, vbscript to extract the rows and insert automatically kicking out any exceptions to be dealt with manually ?
3: Have you tried imported the csv into a staging table and using some sql to import from there?

CSU · 07-04-2011 08:25PM

amen wrote: »

maybe I'm not under standing the problem but you have a list of products from a vendor in csv format (that you can open in Excel) and you want to insert into a database?

1: have you asked the vendor if the have data in a different format? this has to be a common problem for the vendor
2: Could you not use some scripting language phyton, vbscript to extract the rows and insert automatically kicking out any exceptions to be dealt with manually ?
3: Have you tried imported the csv into a staging table and using some sql to import from there?

Yes, there are countless vendors and countless structures of the CSV's and asking each vendor to suit my setup is not practical.

Thinking about it I only need to manually add the products once which is'nt so much of a pain - I could then add new products on a monthly basis. They only bit I "need" to script is the "instock" and "price" fields which I will write a seperate script for each vendor, I'm thinking of something on the lines of:
[PHP]include "connect.php";

if(isset($_POST))

{

$filename=$_POST;

$handle = fopen("$filename", "r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)

{

$import="INSERT into prices(price) values('$price[0]')";
$import="INSERT into instock(instock) values('$instock[1]')";

mysql_query($import) or die(mysql_error());

}

fclose($handle);

print "Import done";

}

else

{

print "";

print "Type file name to import:";

print "";

print "";

}[/PHP]

as the actual location of these two datas differes from vendor to vendor all I'd need to do is write a script for each CSV and vendor (hopefully).

editor for large database files CSV/SQL

Comments