8 replies to this topic

[crokett]

My job has recently changed. I am doing some performance tuning. The data I get is CSV files - frequently half a million lines or more - from networking equipment. I use Excel to do some analysis and do charts. I have a macro to do some formatting, but there are other things I wish to do, plus files that large choke Excel. I am thinking of putting the data into a database. I tried using the OLEDB class already since it can open CSVs as a database of sorts, but it appears to be lacking, or maybe I just don't know what I am doing. Assuming I wanted to get the CSV into a database, is it worth writing it as XML first? I have MySQL installed and it can load XML directly into a table. Also, after the file is saved as XML, it might be easier to deal with in future if I ever needed to.

[VNFox]

no don't put into XML ... that would gives you a bunch of tags ... the file is even bigger. CSV are seperated by "," ... you can do a foreach then split them ... If you're using C# then you can use DataTable store in the memory then insert all of them at once using DataAdapter through OLEDB. If you're using MySQL ... you need to download MySQL native driver or ODBC.

[lespauled]

You could do something like:

public DataTable CsvFileToDatatable(string path, bool IsFirstRowHeader)
{
string header = "No";
string sql = string.Empty;
DataTable dataTable = null;
string pathOnly = string.Empty;
string fileName = string.Empty;
try
{
pathOnly = Path.GetDirectoryName(path);
fileName = Path.GetFileName(path);
sql = @"SELECT * FROM [" + fileName + "]";
if (IsFirstRowHeader)
{
header = "Yes";
}
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
		 ";Extended Properties=\"Text;HDR=" + header + "\""))
{
using (OleDbCommand command = new OleDbCommand(sql, connection))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
dataTable = new DataTable();
dataTable.Locale = CultureInfo.CurrentCulture;
adapter.Fill(dataTable);
}
}
}
}
finally
{
}
return dataTable;
}

[crokett]

An update, I figured out that you can load CSV directly into MySQL. I tested it with a small (5 lines) file and it seems to work. So I think that will let me import files directly into MySQL. From there I need to figure out hooking Excel up and running queries, or running queries and exporting CSV for Excel to analyze.

[Upstream]

It seems that you are progressing towards a workable solution for the task at hand. If you engineer a reliable method to maintain the integrity of the data in between conversion from x to mysql to excel then you have done a job well done. Even though I would like to offer an alternative.

Depending on your goal and resources it can be worth considering using a tool like Cognos which is a data warehousing tool. With it you can create date models which result in dynamic data cubes created from any combination of resources thinkable. For example you can combine a flat file table like cvs, a mysql database and a traffic database in binary format to form an integral dataset or cube if you will. Why? If it is economically viable then it does not require you to convert from database to excel and so forth and it delivers more durable and reliable data. My experience with it is analyzing beer markets while combining various data sources varying from warehouse data to accounting and cash register data. If you can afford it I would say cheers to you!

[BlackRabbit]

Have you read this saving excel files (in xml excel native) tutorial ?
it will be helpful for you, it will show you how to handle the xml excel format uses today as standard, so you can read and write directly without conversions in the middle

[crokett]

I can't afford Cognos, although ironically some of the CSV data I am parsing is exported from customers using Cognos.

I got the CSV import to MySQL working, sort of. I can import a sample set of data. The problem is that the real data will have columns that are null values. In the CSV file, those would look something like:

A,B,C,,E,F,G where the missing D is the null value. If I run the LOAD statement for the file in MySQL Workbench, it completes with warnings that the data for that column is truncated. If I execute the command in C# I get an exception because of the missing value. In some cases in the file, the missing value is one of the primary keys for the table. So at this point I can

a- read through the entire file looking for a double comma, throw away any lines that match, toss them. then load the cleaned file into the database
b- read the file into a datatable, start writing the datatable into the database and check as I go
c-catch the exception and keep loading the file - not desireable since some of the missing values could be one of my primary keys

Any other suggestions?

[Upstream]

In excel sort the cells:) Then there are no blanks.

[MiniEmmy]

My application deals with parsing user entered text that is stored as a csv file and I found issues this week regarding wrongly entered quotation marks. I have now ended up using this c# library and it works very well: http://kbcsv.codeplex.com/ not sure what it does with ,, but it should be worth a try. Got my csv file into a dataset in three lines of code no problem. And with if you wanted to store it in a sql you can use the bulkcopy method. (I use SqlExpress)