# .NET Website to import text files to MS SQL Express 2008

The project is to be done with VB .NET and SQL express 2008.? It will be running on a Windows Web Server 2008 R2.

?

The overall reason for this project is that clients send us data on monthly basis. ? We constantly have to review these files and fix them.?

?

I want a web based tool to load the data in to do all the data validation checks and in some cases fix them.? Any data errors will be logged for our review

## Deliverables

The project is to be done with VB .NET and SQL express 2008.? It will be running on a Windows Web Server 2008 R2.

?

The overall reason for this project is that clients send us data on monthly basis. ? We constantly have to review these files and fix them.?

?

I want a web based tool to load the data in to do all the data validation checks and in some cases fix them.

?

?

Web based interface to create a new client.? A client id is a six digit code starting with 000001.? The next client will be 000002.? The initial screen will be to either to pull up an existing client or create a new client.

?

When a “New client’ option is selected it will perform the following:

-? ? ? ? ? ? ? ? ? Have a screen that enters the following information

o? ? ? ? ? ? Client Name (Text 200)

o? ? ? ? ? ? Client Contact Name (Text 200)

o? ? ? ? ? ? Client Contact Email (Text 200)

o? ? ? ? ? ? Client Contact Telephone Number (Text 20)

-? ? ? ? ? ? ? ? ? Once the information is entered it will populate a database called “client_master?? and update a tabled called

o? ? ? ? ? ? Tbl_client

§? ? ? ? ? ? ? ? Client_id? (this is the 000000,000001, etc ref #)

§? ? ? ? ? ? ? ? client_name

§? ? ? ? ? ? ? ? contact

§? ? ? ? ? ? ? ? email

§? ? ? ? ? ? ? ? telephone

§? ? ? ? ? ? ? ? created_on

§? ? ? ? ? ? ? ? last_uploaded_data_on (this only get updated after data been uploaded)

-? ? ? ? ? ? ? ? ? Then a SQL 2008 express database will be created

o? ? ? ? ? ? It will created in the path of “e:\data??

o? ? ? ? ? ? The database name will be combination of the word “client_??+client_id.

§? ? ? ? ? ? ? ? For example “client_000001??

o? ? ? ? ? ? The log file should also follow the naming standards .. ie “[url removed, login to view]?

o? ? ? ? ? ? EACH client will have their own database.

?

?

Data Import

?

After a client has been created.? The user will be able to select the client and upload data to the specific client.? There will be a list of all clients (client id, Name, created_on, last_uploaded_data_on).? User will be able to sort the list by each of the datafields.? There will be button ??" upload data.? ? ? When that button is pressed the following screen appears:

-? ? ? ? ? ? ? ? ? Standard screen to choose the file that you want to upload.

o? ? ? ? ? ? Go Select your file and then click upload.

-? ? ? ? ? ? ? ? ? The data will be uploaded.? The data will be straight forward text file.? The data will be tab delimited.? The file could contain as many as 100,000 records. But most files will be around 10,000 records.

o? ? ? ? ? ? After the file has been uploaded a copy of the file will be stored in

§? ? ? ? ? ? ? ? E:\Raw_data\client_id (ie. E:\raw_data\000001)

o? ? ? ? ? ? After the file has been uploaded a new screen will appear and ask

§? ? ? ? ? ? ? ? What table will this data be uploaded to?

·? ? ? ? ? ? ? ? The system will do a look up to ? table Tbl_tables_import_specs (see below) and show a list of table_names that are defined.? Then we can pick which table to import the data to.

o? ? ? ? ? ? The file should be given a GUI ID name.? The filename will be stored on the e drive under that GUI ID name.

§? ? ? ? ? ? ? ? A table in the clients database should be updated show that a file has been uploaded

·? ? ? ? ? ? ? ? Tbl_files_uploaded

o? ? ? ? ? ? RowId

o? ? ? ? ? ? GUID

o? ? ? ? ? ? File_name

o? ? ? ? ? ? Table_Name

o? ? ? ? ? ? Uploaded_on

o? ? ? ? ? ? Status (This gets updated to either “Uploaded?? or “Errors??, depending

-? ? ? ? ? ? ? ? ? Each record in the file will have a certain number of fields.

o? ? ? ? ? ? There will be a table that defines the tables and field constraints.? This table will be stored on “client_master??

§? ? ? ? ? ? ? ? Tbl_tables_import_specs

·? ? ? ? ? ? ? ? Rowid

·? ? ? ? ? ? ? ? Table_name

·? ? ? ? ? ? ? ? Field_order_num

·? ? ? ? ? ? ? ? Field_name

·? ? ? ? ? ? ? ? Field_Type

o? ? ? ? ? ? Text

o? ? ? ? ? ? Numeric

o? ? ? ? ? ? Integer

o? ? ? ? ? ? Date

o? ? ? ? ? ? DateTime

·? ? ? ? ? ? ? ? Required_Field

o? ? ? ? ? ? Y or N

·? ? ? ? ? ? ? ? Max_Length

o? ? ? ? ? ? Please note that right now there are 34 fields, but that may change.? Before the file is imported, the program needs to review the tbl_tables_import_specs to determine how the file should be read in.

-? ? ? ? ? ? ? ? ? The goal is to read in the file without the web page crashing.? There maybe problems with the file, but we want to be able to read in the file and tell the user exactly what the errors are.? Here are the data validations that I want to happen:

o? ? ? ? ? ? File should be tab delimited.? If it is not tab delimited program should stop loading file and say … not tab delimited.

§? ? ? ? ? ? ? ? Each record should have 32 tabs.? If there are more of less than the 32 tabs those records should pulled out and put into a separate file .? Please not this number (32) may change.? The program should determine how many fields are in the Tbl_tables_import_specs and use that for the calculation.

o? ? ? ? ? ? Each field should be validated and the following steps happen

§? ? ? ? ? ? ? ? Text

·? ? ? ? ? ? ? ? If the field is over a certain length, then error log entry

o? ? ? ? ? ? Should pull this value from Tbl_tables_import_specs

§? ? ? ? ? ? ? ? Date fields

·? ? ? ? ? ? ? ? Dates are always a problem.? Program should be able to figure out and import any of these formats.

o? ? ? ? ? ? MM/DD/YYYY or

o? ? ? ? ? ? MM-DD-YYYY or

o? ? ? ? ? ? MM/DD/YY

o? ? ? ? ? ? MM-DD/YY

·? ? ? ? ? ? ? ? Sometime the dates will have a time attached to time.? Program will need to remove the time portion of the data.

·? ? ? ? ? ? ? ? If the value of date is not a real date then error log entry

§? ? ? ? ? ? ? ? Amount fields

·? ? ? ? ? ? ? ? This one can be tricky.? This field should be decimal field (for example [url removed, login to view])? But may times we get values such as \$5,[url removed, login to view] in the raw data.? The upload program should be able to address the following items and import in the data correctly

o? ? ? ? ? ? Commas in data

o? ? ? ? ? ? Currency items in the data (i.e. \$, etc)

o? ? ? ? ? ? Amount can be negative

§? ? ? ? ? ? ? ? Can had negative being represented the following ways

·? ? ? ? ? ? ? ? -100

·? ? ? ? ? ? ? ? 100-

·? ? ? ? ? ? ? ? (100)

§? ? ? ? ? ? ? ? Integer fields

·? ? ? ? ? ? ? ? Follow the same logic as amount

·? ? ? ? ? ? ? ? Should use the max_length value from Tbl_tables_import_specs

§? ? ? ? ? ? ? ? DateTime

·? ? ? ? ? ? ? ? Should use the same logic as Date, but simply append the time value.

§? ? ? ? ? ? ? ? Error Logs

·? ? ? ? ? ? ? ? For any error that is identified in the data the following data is logged.

o? ? ? ? ? ? Rowed

o? ? ? ? ? ? GUID (filename GUI id)

o? ? ? ? ? ? Table_Name

o? ? ? ? ? ? Line_number (This is the line number in the text file that failed)

o? ? ? ? ? ? Description - Description of what failed.

o? ? ? ? ? ? If there are no errors then the program needs to import the raw text into the “Table_name??

?

For the project I make myself available to answer any questions during 9 ??" 5 EST.? You do not need to follow the sample naming standards I listed above.? They are only for reference.? I will provide sample files to upload as well and the actual layout of the files.?

Happy bidding.? If you have any questions please feel free to contact me.

?

?

?

( 21 comentários ) United States

ID do Projeto: #2954372

## 22 freelancers estão ofertando em média \$357 para este trabalho

StanDotNet

See private message.

\$756.5 USD in 10 dias
(295 Comentários)
8.1
shawnburt

See private message.

\$425 USD in 10 dias
(121 Comentários)
6.5

See private message.

\$170 USD in 10 dias
(77 Comentários)
5.9
Eliteprog

See private message.

\$170 USD in 10 dias
(31 Comentários)
5.6
thecloudkernel

See private message.

\$1785 USD in 10 dias
(8 Comentários)
5.3
codedeveloperin

See private message.

\$340 USD in 10 dias
(40 Comentários)
5.3
sathyanus

See private message.

\$212.5 USD in 10 dias
(86 Comentários)
5.3
shahidbabar

See private message.

\$255 USD in 10 dias
(34 Comentários)
5.0
angan

See private message.

\$212.5 USD in 10 dias
(36 Comentários)
4.7
kariem2k

See private message.

\$680 USD in 10 dias
(6 Comentários)
4.5
izharuislam

See private message.

\$85 USD in 10 dias
(28 Comentários)
4.5
sdevelopervw

See private message.

\$255 USD in 10 dias
(26 Comentários)
4.3
newresume

See private message.

\$765 USD in 10 dias
(21 Comentários)
4.0
darthsidius

See private message.

\$680 USD in 10 dias
(8 Comentários)
3.5
binaryarray

See private message.

\$99.45 USD in 10 dias
(11 Comentários)
2.2
perfectchoicevw

See private message.

\$85 USD in 10 dias
(8 Comentários)
2.2
webdevelopor

See private message.

\$85 USD in 10 dias
(2 Comentários)
1.6
deepthi01

See private message.

\$102 USD in 10 dias
(1 Comentário)
0.5
expertscode

See private message.

\$85 USD in 10 dias
(1 Comentário)
0.0
hsleemwork

See private message.

\$85 USD in 10 dias
(0 Comentários)
0.0