CSV Mapping for Upload to SQL Server (bulkCopy)

[stewster]

I have a question that I hope has a happy answer.

Okay this may get long, but this is what I'm doing.

I'm using ASP.NET 2.0

The concept: I need to import a CSV files to an SQL server. However the
csv files come to us with different headers and some extra fields in
some cases.

So I created an ASP.NET page that allows the customer to select the csv
file to be imported.

Once the file is uploaded they can preview the first 5 records to check
the data.

Then they click import headers. The CSV file headers are loaded into a
list box on the left and the SQL headers are loaded into a listbox on
the right.

They now can select the csv header and then the sql header and click
connect. The two selected values are put in two more list boxes that
represent the connection (mappings)

Once they have selected the desired mappings they can click upload
which calls bulk copy to send the csv data to the sql server.

This is where the problem and questions are:

First, I can get it to work fine if the csv file is laid out just like
the SQL server.
But when the CSV file has an extra field and is not mapped it will
crash.

Some csv files come with extra data that we do not want uploaded so we
don't include them in our mapping. Exampl : ADD1 ADD2 ADD3 our
SQL server only as ADD1 ADD2. So when I make the connections I skip
ADD3 in the mappings and go on the next mappings.

Everything works and uploads to the SQL server if the headers are in
order and none are skipped.

Can CSVreader handle this type of mapping? Or does it require all
headers to be mapped? Do they have to be in order? IE header 1 from the
csv mapped to header 5 of the csv? Here is the code I'm using...

Protected Sub uploadButton_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles uploadButton.Click
txtDate.Text = DateTime.Now.ToString
Dim FileLocation As String = txtFilePath.Text
If txtJobNumber.Text = "" Then
Exit Sub
End If
Using loader As CsvDataReader = New CsvDataReader(FileLocation)
loader.HasHeaders = True

For intLoopIndex As Integer = 0 To ListMapCSV.Items.Count -
1
loader.Columns.Add("nvarchar")
Next

loader.Columns.Add("int", "JobNumber")
loader.Columns.Add("nvarchar", "Uploaded")

AddHandler loader.ReadRecord, AddressOf AddJobNumber
AddHandler loader.ReadRecord, AddressOf AddUploaded

Using bulkCopy As Data.SqlClient.SqlBulkCopy = New
Data.SqlClient.SqlBulkCopy("Data
Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated
Security=True;User Instance=True")
bulkCopy.DestinationTableName = "tblRecall"

'"Data Source=cyan;Initial Catalog=vw01;User
ID=sa;Password=3Terra-"

For intLoopIndex As Integer = 0 To
ListMapCSV.Items.Count - 1

bulkCopy.ColumnMappings.Add(ListMapCSV.Items(intLoopIndex).ToString,
ListMapSQL.Items(intLoopIndex).ToString) ' map First to first_name
Next

bulkCopy.ColumnMappings.Add("JobNumber", "JobNumber") '
map UserID from Session to user_id
bulkCopy.ColumnMappings.Add("Uploaded",
"DateTimeUploaded") ' map UserID from Session to user_id

bulkCopy.WriteToServer(loader)
End Using
End Using

ListMapCSV.Items.Clear()
ListMapSQL.Items.Clear()
ListCSV.Items.Clear()

End Sub

[stewster]

Correction to one of many mistypes!!!! Do they have to be in order? IE
header 1 from the
csv mapped to header 5 of the SQL?

[shriop]

This is absolutely how things should work. First, I'd suggest you step
out of your current project just a bit and say take your file with
add1, add2, add3, First and map it to the add1, add2, first_name
directly, outside of your list boxes with the entire thing hardcoded.
This way, you can prove to yourself that the parser should absolutely
be able to handle this situation. This would narrow down what could
possibly be going on if there's still an error, and you'd be able to
send me that project as a whole to test on my end. If it works, then we
know there's something getting slightly off during the mapping process.
Can you give me the error message that you're getting during the
upload? Also, have you stepped through the code in the debugger to make
absolutely sure that the values in the loops doing the column mappings
are exactly what you expect them to be? The only thing I can see at all
from your code that seems odd is that your DateTimeUploaded field is an
nvarchar? Also, have you verified that all your fields in the database
are long enough? It's just really hard to guess, so I'll have to be
able to actually run something on my side to see exactly what's going
on.

[stewster]

I have stepped through the code and verified all the values and there
are no issues with the intended imported header mappings. I'm looping
through the listbox of both the selected CSV headers and SQL headers so
they match up perfect. The reason for the everything being an nvarchar
is because I'm looping through the ever changing headers sometimes they
may have 13 columns and sometimes only 10 or so. But the data we need
to pull out, is in the csv file. I can send you the code so you can
take a look and see where I'm messing up. I'm at my wits end, I've have
tried everything, verified everything, and hard code tested everything.
What am I doing wrong?? :-(

[shriop]

Ok, just send it over to me like last time, br...@csvreader.com, with
the database file, the import file, and the code.

[shriop]

Ok, I think I have it figured out. When you're adding columns to the
column collection, you need to add a column for every actual column
that exists in the source, whether you're actually planning on using it
or not. Currently, you're only adding columns for ones that you're
planning on mapping.

Original:

For intLoopIndex As Integer = 0 To ListMapCSV.Items.Count - 1
loader.Columns.Add("nvarchar")
Next

Fixed:
For intLoopIndex As Integer = 0 To ListCSV.Items.Count - 1
loader.Columns.Add("nvarchar")
Next

When I changed this, it seemed to load successfully. I haven't verified
the data in the database yet, and I need to look at something that
seemed odd about how the column names were arranged with how you had
it, but this fix makes perfect sense and I think it will resolve all
your issues.

Bruce Dunwiddie

[stewster]

Thanks for all your help. I did get everything working as you have
suggested. The problem I was running into was with the old version of
csv.dll I was using at home.

I used your updated csv.dll and poof it all worked as you said it work
on your end.

Thank you very much...

:-) Now the fun part of user testing of the entire web application.

PS. To everyone looking for a solution working with CSV files, this is
by far the best.... Our company tried several others but none of them
had come close to the versatility, speed, and awesome customer service
as CsvReader.