Register and join over 40,000 other developers!
Recent Topics
-
The Game You Are Waiting For?
WendellHarper - Dec 06 2020 01:21 PM
-
Quora and Reddit Backlinks
WendellHarper - Dec 06 2020 01:14 PM
-
Delete account
pindo - Jul 23 2020 01:33 AM
-
Print specific values from dictionary with a specific key name
Siten0308 - Jun 20 2019 01:43 PM
-
Learn algorithms and programming concepts
johnnylo - Apr 23 2019 07:49 AM
Recent Blog Entries
Recent Status Updates
Popular Tags
- networking
- Managed C++
- stream
- console
- database
- authentication
- Visual Basic 4 / 5 / 6
- session
- Connection
- asp.net
- import
- syntax
- hardware
- html5
- array
- mysql
- java
- php
- c++
- string
- C#
- html
- loop
- timer
- jquery
- ajax
- javascript
- programming
- android
- css
- assembly
- c
- form
- vb.net
- xml
- linked list
- login
- encryption
- pseudocode
- calculator
- sql
- python
- setup
- help
- game
- combobox
- binary
- hello world
- grid
- innerHTML

8 replies to this topic
#1
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.
#2
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.
www.pickmike.com
I don't just develop software. I find solutions to your business needs.
#3
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; }
My Blog: http://forum.codecal...699-blog-77241/
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth
"Women and Music: I'm always amazed by other people's choices." - David Lee Roth
#4
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.
#5
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!
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!
"The question of whether a computer can think is no more interesting than the question of whether a submarine can swim." (Edsger Dijkstra)
#6
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
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
#7
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?
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?
#8
Posted 23 September 2012 - 07:41 AM
In excel sort the cells:) Then there are no blanks.
"The question of whether a computer can think is no more interesting than the question of whether a submarine can swim." (Edsger Dijkstra)
#9
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)
Also tagged with one or more of these keywords: c#, database, csv, import
Language Forums →
C# →
How to make code run differently depending on the platform it is running on?Started by xarzu, 05 Apr 2019 ![]() |
|
![]() |
||
Language Forums →
C# →
How do I set a breakpoint in an attached process in visual studioStarted by xarzu, 04 Apr 2019 ![]() |
|
![]() |
||
Language Forums →
C# →
Do you use obfuscation?Started by Fernando, 22 Jan 2019 ![]() |
|
![]() |
||
General Forums →
Game Development →
Include a specific error, task, problem, or question in your titleStarted by Killuah, 04 Jul 2018 ![]() |
|
![]() |
||
Language Forums →
C# →
Tools for Feature Flighting/Flagging and A/B TestingStarted by anujanand, 26 Sep 2016 ![]() |
|
![]() |
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download