what would be the best choice: csv or XML

[Ryan Liu]

I have an application need export ane import data of projects. There are
about 10 database tables releated to one project. 3 of them each could have
up to 100K lines data.

I can export all data out into a xml file and then zip it. When I do import,
I parse this xml file and then insert into database myself.

Or I can export all tables out in csv format and then use mysql's client
command "load local file" (which is claimed very fast command).

I have keep thinking which is best choice. Will Using XML format to import
consume too much momory and what are best way(classes, API in C#) to use
xml?

Use csv file, size should be smaller, and mysql client tool is fast. But not
so portable and when I can not show a progress bar while doing import ...

Can some experts comment on this?
Thanks!
Ryan

 

[Nicholas Paldino [.NET/C# MVP]]

Ryan,

I would use the CSV. The only reason I would use XML is if you had a
need for heiarchical data structures, and/or needed to query the data in
some way (through XPath, XQuery).

However, since you don't need either of those things, I think that a CSV
file would be better. Also, since mysql has an import/export feature using
CSV files (SQL Server has this as well) which is fast, it would aid your
development, since you don't have to code against bringing XML into your app
then dumping it into your tables. You could just issue the command and be
done with it.

Hope this helps.

 

[Guest]

Are you going to export manually, or your app do it for u?
Why not to export/import into sql file, like sequence of create/instert
statements?

In you case, if you xml data wont increase in 50 times, you are not going to
validate you Xml data and load it into DOM there are no significant
differences between Xml and CVS. You can use XmlWriter/XmlReader for this

I have an application need export ane import data of projects. There are
about 10 database tables releated to one project. 3 of them each could have
up to 100K lines data.

I can export all data out into a xml file and then zip it. When I do import,
I parse this xml file and then insert into database myself.

Or I can export all tables out in csv format and then use mysql's client
command "load local file" (which is claimed very fast command).

I have keep thinking which is best choice. Will Using XML format to import
consume too much momory and what are best way(classes, API in C#) to use
xml?

Use csv file, size should be smaller, and mysql client tool is fast. But not
so portable and when I can not show a progress bar while doing import ...

--
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

I would go for CSV, the file generated will be smaller, much smaller
probably and the import will also be performed by mysql.

and believe me, CSV is portable . I do communicate with a couple of unix
system and all of them use a variation of CSV (only using another char
instead of , )

The progress bar problem is real, you have no control over the execution,
what you can do is spawn a hidden process from your program that execute
mysql client and have either a progress bar or a moving image (a la copying
file in explorer) and checking the status of the new process.

 

[Andrew Faust]

I have an application need export ane import data of projects. There are
about 10 database tables releated to one project. 3 of them each could have
up to 100K lines data.

I can export all data out into a xml file and then zip it. When I do import,
I parse this xml file and then insert into database myself.

Or I can export all tables out in csv format and then use mysql's client
command "load local file" (which is claimed very fast command).

Pretty well every major database out there has some capability to import
CSV files, thus CSV is often your best choice for transferring data
between disparate databases.

Andrew Faust

 

[Andrew Faust]

Use csv file, size should be smaller, and mysql client tool is fast.
But not

so portable and when I can not show a progress bar while doing import ...

Not so portable? CSV is about the most portable file format in
existance. I've retrieved data extracts from AS/400, Revelation,
Postgres, Oracle, SQL Server, Access, Fox Pro, DB/2, TopSpeed and more
in CSV format and then loaded Oracle, SQL Server, Access & DB/2 from csv
files. You won't get all those databases natively working with XML.

Andrew Faust