[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

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.
0
rxresults
Asked:
rxresults
  • 7
  • 5
  • 2
  • +1
3 Solutions
 
É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 👽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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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
 
Éric MoreauSenior .Net ConsultantCommented:
the FileHelpers library and the TextFieldParser handles that
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 👽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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now