InvalidOperationException when reading csv file

anderdw2
anderdw2 used Ask the Experts™
on
Ok, I have 15 different csv files that I need to read, then place into XML and import their value into SQL server.  I'm writing a service that uses filewatcher to see if a new file exists and take control of the process.

I'm trying to read the csv using the class from:
http://www.codeproject.com/KB/linq/LINQtoCSV.aspx?msg=3984796#xx3984796xx

I'm getting an error and nothing into the class that I created to map the csv.  My csv files do not have headers and they are comma delimited.  I can have the data wrapped in " or not.  But I cannot add headers.

I've included my basic files, but my code to read the file is as follows:


 cornsilage2.csv UnityImport.cs
if (e.Name != null)
                    {
                        string thisFile = Properties.Settings.Default.watcherPath.ToString() + e.Name;
                        try
                        {
                            CsvFileDescription inputFileDescription = new CsvFileDescription
                            {
                                SeparatorChar = ',',
                                FirstLineHasColumnNames = false,
                                EnforceCsvColumnAttribute = true                        
                            };

                            CsvContext cc = new CsvContext();
                            if (thisFile.Contains("cornsilage"))
                            {
                                IEnumerable<CornSilage> cornSil = cc.Read<CornSilage>(thisFile, inputFileDescription);
                                
                                foreach (var item in cornSil)
                                {
                                    // I want to write to xml and pass to sql server as parameter
                                    //SaveUnityData("[UnityData].[dbo].[INSERT_CORNSILAGE]", addSqlParam());
                                }
                            }
                        }

Open in new window

cornSilage2.cs
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
anarki_jimbelSenior Developer

Commented:
I believe the problem is in your csv file.

really, structure is strange. The first value in the first row - time. But in the following rows the first value is not time.
The last row has 1 more field compare to two first rows.
Excel does not want to recognise this file as csv.

Author

Commented:
I believe the test file i posted is a single row csv.  There are over 100 data points in each row.   I'll take a look at another file that the machine exports and compare
anarki_jimbelSenior Developer

Commented:
OK, not often happens I program for csv :)
However, any csv has delimeters like "," - for values in a column, and "new line" (commonly) for a row. In your file - two new line characters (means three rows...). Hmmm.... Not sure...

Author

Commented:
Those are negative numbers.
anarki_jimbelSenior Developer

Commented:
Hmmm...

OK, I am not sure why would you want to move your csv into XML and then into SQL server.
I'd put it into a datatable and save into DB.

Try to use the following parser, one of the best:

http://www.codeproject.com/KB/database/GenericParser.aspx

Author

Commented:
My csv has over 100 records per row.  I wasn't sure how to move that into sql server 2008, with the exception of parameters, which I didn't want to write 100 parameters per product.

I thought I could pass xml into my stored procedure.  

Is there a better method?
anarki_jimbelSenior Developer

Commented:
OK, I'm trying to think. But would your stored procedure understand xml? What format of xml it should be?
Senior Developer
Commented:
OK, I'd probably use something like
http://www.codeproject.com/KB/database/FinalCSVReader.aspx.

Really, I'd take csv file line by line and insrt a row. I guess that csv line contains all required fields:

INSERT INTO table_name
VALUES (your line from csv)

Another approach - to use BULK INSERT. I never used, honestly. But in your case this might be the best approach:

http://www.marten-online.com/database/import-csv-into-mssql-database.html
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Author

Commented:
If not xml, sql server 2008 understands table as well, correct?  I could take the csv with linq and create a datatable and pass the datatable to a stored procedure
anarki_jimbelSenior Developer

Commented:
Yes, I believe, tables would be a good option.

However, I'd try BULK INSERT. I just reluctant to test myself - need to set up a database etc. But looks like it's a good technology and functionality is built in.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial