asp.net c# xls to sql data import
$30-100 USD
Paid on delivery
This is a 2 part project--
1: create a simple [url removed, login to view] website where I can upload an Excel file.
If the excel file does not have the correct format, a rejection message should be shown.
The data from each row in the xls should be inserted as a db record in the db table. -- each row goes to 1 record, there will be some manipulation of the data described in the details section.
2: Import data from text files into a database table
there are about ~180 files located at
[url removed, login to view]
I will tell you which ones I need imported - many of the text files will have the same format but some of the older ones willhave a slightly different format, u will need to analyze the files and make sure they map correctly to that db table
The website in part 1 will be used by me to upload new xls files to the table, but the import of that data from part 2 is a one time effort - I don't care how it is imported, I just need it done. I do not need the code from part 2.
## Deliverables
Details for Part 1:
I am attaching a sample xls file -- if the website is too slow to process this size file, I am open to creating a desktop program that can be run from the server -- please advise.
A table will need to be created in the DB to hold the data.
Some manipulation needs to be done here ...
- UNKNOWN text and blanks should be saved as NULL
- Zip Code should be split into 2 fields, 5_digit_zip and additional_zip where 5_digit_zip stores the first 5 digits and the remaining numbers are stored in the other field
- Amount should remove the "$" and the ","
- Address field should be split to 5 fields: 1=orginal text, 2=street number, 3=N or S or W or E, 4=Street name, 5=Street type such as ST or RD or AVE or LN or DR or CT or HWY
look to <[url removed, login to view]> for more street type abbreviations, you may want to have these in a config file or database table so if we find we are missing something we can modify
- Seller and Buyer fields should be split to 5 fields each: 1=original text, 2=last name (is the first word listed), 3=first name (second word listed), 4=middle initial (one letter listed last), 5=business name
some of the buyers/sellers are businesses, they can be identified with words like LLC, INC, CO, TRUST -- generally anything that does not fit the "LAST_NAME FIRST_NAME MIDDLE_INITIAL" mask -- if we can reliably break these fields up it will be better for searching.
Note: if the business name field is filled then fields 2-4 should be null and visa versa
- create an extra field in the table to identify the file name of the file used in the upload
I think the data should be very consistent as far as datatypes - review all the files and see what is the best data type option, there should not be any issue with datatype mismatch so make sure every field is an appropriate size.
After an upload the site should tell me how many records were created. If there were errors it should tell me how many records had errors and display an error log identifying the records in error and any sql generated error (i.e. datatype mismatch, hard drive full, etc...) the goal is to know that all data was uploaded as success and if not, why and what was not uploaded...
Details for Part 2:
The same table created in Part 1 will be used for storage of part 2.
the field with filename should also be populated for which file the record came from.
Project ID: #3809694