-
Jul 26th, 2021, 11:35 AM
#1
Thread Starter
Addicted Member
[RESOLVED] CSV Helper
I have a VB.Net (Framework 4.7.2) application and a Mysql database. I use Mysql bulkloader to insert just over 1 million lines from .csv file. It takes about 1 minute 46 sec to load. I am trying to use CSV Helper to see if that would be faster (https://joshclose.github.io/CsvHelper/)
Here is the code I use :
Code:
Using streamReader = File.OpenText(extractPath & GebruikHierdieCSV & ".csv")
Using csvReader As New CsvReader(streamReader, CultureInfo.CurrentCulture)
Using dr = New CsvDataReader(csvReader)
Dim dt = New DataTable()
dt.Load(dr)
End Using
End Using
End Using
However I get an error message : CsvHelper.BadDataException: You can ignore bad data by setting BadDataFound to null.
If I add the following line just after Using csvReader As New CsvReader :
Code:
csvReader.Configuration.BadDataFound = null
I get an error null is not declared. It may be inaccessible due to its protection level.
I have tried DbNull with no luck.
What am I missing?
-
Jul 26th, 2021, 01:07 PM
#2
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Jul 26th, 2021, 01:28 PM
#3
Re: CSV Helper
sepator is right in that you should assign the value to Nothing. To elaborate, the Visual Basic keyword for null is Nothing (documentation).
-
Jul 26th, 2021, 10:57 PM
#4
Thread Starter
Addicted Member
Re: CSV Helper
Thank you both. I have tried csvReader.Configuration.BadDataFound = Nothing
I get the following error : Proprty BadDatafound is readonly. The author's remarked that
The config is read only now. You need to pass it into the constructor.
.
He then refers to the following change log https://joshclose.github.io/CsvHelper/change-log where he states under version 23.0.0
Removed the large CsvConfiguration constructor. The properties are now settable, so this isn't needed for VB.NET.
.
But I cannot figure out how to do that.
-
Jul 26th, 2021, 11:59 PM
#5
Re: CSV Helper
The CsvReader.Configuration property is type IReaderConfiguration and the IReaderConfiguration.BadDataFound property is indeed read-only, so you cannot set it the way you're trying to. What is not read-only is the CsvConfiguration.BadDataFound property, so you need to access the property via a reference of that type. You can either do that with a cast:
vb.net Code:
DirectCast(csvReader.Configuration, CsvConfiguration).BadDataFound = Nothing
or you can create your own configuration first, set that property and then pass it into the reader:
vb.net Code:
Dim config As New CsvConfiguration(CultureInfo.CurrentCulture) With {.BadDataFound = Nothing}
Using streamReader = File.OpenText(extractPath & GebruikHierdieCSV & ".csv"),
csvReader As New CsvReader(streamReader, config),
dr = New CsvDataReader(csvReader)
Dim dt = New DataTable()
dt.Load(dr)
End Using
Note that I have also removed your unnecessary nested Using blocks. You could also get rid of the extra declaration and move the object creation into the constructor argument:
vb.net Code:
Using streamReader = File.OpenText(extractPath & GebruikHierdieCSV & ".csv"),
csvReader As New CsvReader(streamReader, New CsvConfiguration(CultureInfo.CurrentCulture) With {.BadDataFound = Nothing}),
dr = New CsvDataReader(csvReader)
Dim dt = New DataTable()
dt.Load(dr)
End Using
-
Jul 27th, 2021, 12:01 AM
#6
Re: CSV Helper
BTW, you should also be using the Path.Combine method to construct a file/folder path from parts, not concatenating strings. Doing so means that you can never accidentally include too many or too few delimiters at the concatenation points:
vb.net Code:
Using streamReader = File.OpenText(Path.Combine(extractPath, GebruikHierdieCSV & ".csv")),
-
Jul 27th, 2021, 03:57 AM
#7
Thread Starter
Addicted Member
Re: CSV Helper
Thank you jmchilhinney. That worked. (I don't know whether I should put this on a new thread.)
My plan is to use streamreader and put the data into a datable :
Code:
'First Streamreader :
Using streamReader = File.OpenText(Path.Combine(extractPath, GebruikHierdieCSV & ".csv")),
csvReader As New CsvReader(streamReader, New CsvConfiguration(CultureInfo.CurrentCulture) With {.BadDataFound = Nothing, .HasHeaderRecord = True}),
dr = New CsvDataReader(csvReader)
Dim dt2 = New DataTable()
dt2.Load(dr)
End Using
You'll notice I have also added .HasHeaderRecord = True as a configuration. BTW thanx for the Path.Combine tip.
Then use streamwriter to create a csv file :
Code:
'Second Streamwriter :
Using writer = New StreamWriter("D:\Wingerdbou\LutzvilleProgram\InvoerDataFiles\EZY Wine\Vessel_Composition\CSVStreamwriterFile.csv")
Using csv = New CsvWriter(writer, CultureInfo.InvariantCulture)
csv.WriteRecords(dt2)
End Using
End Using
I get an error at csv.WriteRecords(dt2) : System.InvalidCastException: 'Unable to cast object of type 'System.Data.DataTable' to type 'System.Collections.IEnumerable'.'
Also I cannot view the datatable(dt2) with table visualizer with a break?
Maybe I should add the rows line by line - but surely that would slow things down as there are over 1 mil rows?
Finally I want to use MySqlbulkload to insert into my database :
Code:
'Then MySqlbulkload :
Dim bl As New MySqlBulkLoader(conn)
bl.TableName = "ezycomposition_staging"
bl.FieldTerminator = ","
bl.LineTerminator = "\r\n"
bl.FileName = "D:\Wingerdbou\LutzvilleProgram\InvoerDataFiles\EZY Wine\Vessel_Composition\CSVStreamwriterFile.csv"
bl.NumberOfLinesToSkip = 1
bl.FieldQuotationCharacter = """"
conn.Open()
Dim count As Integer = bl.Load()
System.Threading.Thread.Sleep(500)
Am I on the right track? Regards
-
Jul 27th, 2021, 04:23 AM
#8
Re: CSV Helper
Originally Posted by GideonE
That worked. (I don't know whether I should put this on a new thread.)
Yes you should. If the advice I provided fixed the problem you asked about then this issue is resolved and you should use the Thread Tools menu to mark this thread Resolved. If you have a new issue then you should create a new thread that contains ALL and ONLY the information relevant to that issue. One thread per topic and one topic per thread.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|