Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Importing CSV files Into Database

c# database csv import

  • Please log in to reply
8 replies to this topic

#1 crokett

crokett

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 109 posts
  • Programming Language:C#, Perl
  • Learning:C#, Perl, Transact-SQL

Posted 20 September 2012 - 07:43 AM

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.
  • 0

#2 VNFox

VNFox

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 648 posts
  • Programming Language:C#, PHP
  • Learning:Assembly

Posted 20 September 2012 - 07:56 AM

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.
  • 0

www.pickmike.com
I don't just develop software. I find solutions to your business needs.


#3 lespauled

lespauled

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1360 posts
  • Programming Language:C, C++, C#, JavaScript, PL/SQL, Delphi/Object Pascal, Visual Basic .NET, Pascal, Transact-SQL, Bash

Posted 20 September 2012 - 07:59 AM

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;
}

  • 0
My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth

#4 crokett

crokett

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 109 posts
  • Programming Language:C#, Perl
  • Learning:C#, Perl, Transact-SQL

Posted 21 September 2012 - 07:27 AM

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.
  • 0

#5 Upstream

Upstream

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 98 posts
  • Location:Netherlands
  • Programming Language:C, C++, PHP, (Visual) Basic, JavaScript, Perl, Bash, Others
  • Learning:Others

Posted 21 September 2012 - 09:08 AM

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!
  • 0
"The question of whether a computer can think is no more interesting than the question of whether a submarine can swim." (Edsger Dijkstra)

#6 BlackRabbit

BlackRabbit

    CodeCall Legend

  • Expert Member
  • PipPipPipPipPipPipPipPip
  • 3871 posts
  • Location:Argentina
  • Programming Language:C, C++, C#, PHP, JavaScript, Transact-SQL, Bash, Others
  • Learning:Java, Others

Posted 21 September 2012 - 05:12 PM

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
  • 1

#7 crokett

crokett

    CC Addict

  • Advanced Member
  • PipPipPipPipPip
  • 109 posts
  • Programming Language:C#, Perl
  • Learning:C#, Perl, Transact-SQL

Posted 22 September 2012 - 07:40 PM

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?
  • 0

#8 Upstream

Upstream

    CC Resident

  • Advanced Member
  • PipPipPipPip
  • 98 posts
  • Location:Netherlands
  • Programming Language:C, C++, PHP, (Visual) Basic, JavaScript, Perl, Bash, Others
  • Learning:Others

Posted 23 September 2012 - 07:41 AM

In excel sort the cells:) Then there are no blanks.
  • 0
"The question of whether a computer can think is no more interesting than the question of whether a submarine can swim." (Edsger Dijkstra)

#9 MiniEmmy

MiniEmmy

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts
  • Programming Language:C#

Posted 28 September 2012 - 11:23 AM

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)
  • 0





Also tagged with one or more of these keywords: c#, database, csv, import

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download