Reading a csv one line at a time and importing to sql

I have a csv that has over 6 million rows and 300+ columns.  I need a way to read the csv one line at a time and then I can execute the stored procedur to push it by parameters to the sql server.  I can read the file but I am not able to use an array due to an out of memory exception. Any advice would be greatly appreciated.
rxresultsAsked:
Who is Participating?
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
the FileHelpers library and the TextFieldParser handles that
0
 
Éric MoreauSenior .Net ConsultantCommented:
have you tried to import the file in a temp table in SQL and process it from there?

You may also want to check http://emoreau.com/Entries/Articles/2011/11/Using-the-FileHelpers-Library.aspx
0
 
käµfm³d 👽Connect With a Mentor Commented:
I'd say the TextFieldParser class would be a good bet. (Look:  emoreau even has an article about that one too!!  http://www.emoreau.com/Entries/Articles/2010/05/Do-you-know-the-TextFieldParser.aspx ).
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
rxresultsAuthor Commented:
I see how to read it by fields but I have an issue where within the commas used for delimiting the file I have text qualifiers of "".  Within those qualifiers a comma could be part of a title. Any clues?
0
 
rxresultsAuthor Commented:
I am using the textfieldparser but I guess Im lost as to where to do the text qualifiers.
0
 
rxresultsAuthor Commented:
I have tried several ways to do the import directly into sql and it was way to large to do it succesfully.  I also needed to perform some cleanup on the data so I thought this might be the best route.
0
 
Éric MoreauSenior .Net ConsultantCommented:
it is handled automatically. I just changed the first line of my demo file to

1,"Joe, Dalton",1.23

and it is still working correctly
0
 
rxresultsAuthor Commented:
When I try the following:

"2","","<UNAVAIL>","Anywhere COUNTY HOSPITAL SYSTEM, INC",

I get: 2-<UNAVAIL>-Anywhere COUNTY HOSPITAL SYSTEM-INC.  

I appreciate all your help.
0
 
Éric MoreauSenior .Net ConsultantCommented:
How do you get that result? can you please show some code?
0
 
rxresultsAuthor Commented:
Its your example:

 Try
                'Parse the line into fields using gthe ReadFields method
                Dim arrFields As String() = reader.ReadFields()                i = 0
                'Process the data just read
                Dim strCurrentLine As String = String.Empty
                For Each strField As String In arrFields

I am using the I as a counter because I dont want all the columns only a limited number of them.  After that I exceute a select case statment and save the field into a string variable based off the I counter so I capture the specific row.
0
 
käµfm³d 👽Connect With a Mentor Commented:
I see how to read it by fields but I have an issue where within the commas used for delimiting the file I have text qualifiers of "".  Within those qualifiers a comma could be part of a title.
Did you set the HasFieldsEnclosedInQuotes property to true?
0
 
Éric MoreauSenior .Net ConsultantCommented:
isn't arrFields contains correct values?

I have added you row to my test file (as well as another header named v4) and it is outputting in the message box correctly.
0
 
rxresultsAuthor Commented:
I reopened the data file and that may be the issue.  I'm re uploading it and I'll let you know if that was indeed the problem.
0
 
rxresultsAuthor Commented:
That was it.  The csv had been tampered with.  That code is working great thank you all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.