I need help with data conversion from flat files to SQL server using Dotnet code

Question

0.00/5 (No votes)

See more:

1) create windows application
2) when button is clicked open file and read line by line
3) parse each row into different columns
4) save the data tables in sql server

Here is the text data

"OrderID	CustomerID	EmployeeID	OrderDate	RequiredDate	ShippedDate	ShipVia	Freight	ShipName	ShipAddress	ShipCity	ShipRegion	ShipPostalCode	ShipCountry"	
"10248	VINET	5	1996-07-04 00:00:00	1996-08-01 00:00:00	1996-07-16 00:00:00	3	32.38	Vins et alcools Chevalier	59 rue de l'Abbaye	Reims		51100	France"	
"10249	TOMSP	6	1996-07-05 00:00:00	1996-08-16 00:00:00	1996-07-10 00:00:00	1	11.61	Toms Spezialit„ten	Luisenstr. 48	Mnster		44087	Germany"	
"10250	HANAR	4	1996-07-08 00:00:00	1996-08-05 00:00:00	1996-07-12 00:00:00	2	65.83	Hanari Carnes	Rua do Pa‡o"	" 67	Rio de Janeiro	RJ	05454-876	Brazil"
"10251	VICTE	3	1996-07-08 00:00:00	1996-08-05 00:00:00	1996-07-15 00:00:00	1	41.34	Victuailles en stock	2"	" rue du Commerce	Lyon		69004	France"
"10252	SUPRD	4	1996-07-09 00:00:00	1996-08-06 00:00:00	1996-07-11 00:00:00	2	51.3	Supremes d‚lices	Boulevard Tirou"	" 255	Charleroi		B-6000	Belgium"
"10253	HANAR	3	1996-07-10 00:00:00	1996-07-24 00:00:00	1996-07-16 00:00:00	2	58.17	Hanari Carnes	Rua do Pa‡o"	" 67	Rio de Janeiro	RJ	05454-876	Brazil"
"10254	CHOPS	5	1996-07-11 00:00:00	1996-08-08 00:00:00	1996-07-23 00:00:00	2	22.98	Chop-suey Chinese	Hauptstr. 31	Bern		3012	Switzerland"	
"10255	RICSU	9	1996-07-12 00:00:00	1996-08-09 00:00:00	1996-07-15 00:00:00	3	148.33	Richter Supermarkt	Starenweg 5	Geneve		1204	Switzerland"	
"10256	WELLI	3	1996-07-15 00:00:00	1996-08-12 00:00:00	1996-07-17 00:00:00	2	13.97	Wellington Importadora	Rua do Mercado"	" 12	Resende	SP	08737-363	Brazil"
"10257	HILAA	4	1996-07-16 00:00:00	1996-08-13 00:00:00	1996-07-22 00:00:00	3	81.91	HILARION-Abastos	Carrera 22 con Ave. Carlos Soublette #8-35	San Crist¢bal	T chira	5022	Venezuela"	
"10258	ERNSH	1	1996-07-17 00:00:00	1996-08-14 00:00:00	1996-07-23 00:00:00	1	140.51	Ernst Handel	Kirchgasse 6	Graz		8010	Austria"	
"10259	CENTC	4	1996-07-18 00:00:00	1996-08-15 00:00:00	1996-07-25 00:00:00	3	3.25	Centro comercial Moctezuma	Sierras de Granada 9993	M‚xico D.F.		05022	Mexico"	
"10260	OTTIK	4	1996-07-19 00:00:00	1996-08-16 00:00:00	1996-07-29 00:00:00	1	55.09	Ottilies K„seladen	Mehrheimerstr. 369	K"ln		50739	Germany"	
"10261	QUEDE	4	1996-07-19 00:00:00	1996-08-16 00:00:00	1996-07-30 00:00:00	2	3.05	Que Del¡cia	Rua da Panificadora"	" 12	Rio de Janeiro	RJ	02389-673	Brazil"
"10262	RATTC	8	1996-07-22 00:00:00	1996-08-19 00:00:00	1996-07-25 00:00:00	3	48.29	Rattlesnake Canyon Grocery	2817 Milton Dr.	Albuquerque	NM	87110	USA"	
"10263	ERNSH	9	1996-07-23 00:00:00	1996-08-20 00:00:00	1996-07-31 00:00:00	3	146.06	Ernst Handel	Kirchgasse 6	Graz		8010	Austria"	
"10264	FOLKO	6	1996-07-24 00:00:00	1996-08-21 00:00:00	1996-08-23 00:00:00	3	3.67	Folk och f„ HB	Akergatan 24	Br„cke		S-844 67	Sweden"	
"10265	BLONP	2	1996-07-25 00:00:00	1996-08-22 00:00:00	1996-08-12 00:00:00	1	55.28	Blondel pere et fils	24"	" place Kl‚ber	Strasbourg		67000	France"
"10266	WARTH	3	1996-07-26 00:00:00	1996-09-06 00:00:00	1996-07-31 00:00:00	3	25.73	Wartian Herkku	Torikatu 38	Oulu		90110	Finland"	
"10267	FRANK	4	1996-07-29 00:00:00	1996-08-26 00:00:00	1996-08-06 00:00:00	1	208.58	Frankenversand	Berliner Platz 43	Mnchen		80805	Germany"	
"10268	GROSR	8	1996-07-30 00:00:00	1996-08-27 00:00:00	1996-08-02 00:00:00	3	66.29	GROSELLA-Restaurante	5a Ave. Los Palos Grandes	Caracas	DF	1081	Venezuela"	
"10269	WHITC	5	1996-07-31 00:00:00	1996-08-14 00:00:00	1996-08-09 00:00:00	1	4.56	White Clover Markets	1029 - 12th Ave. S.	Seattle	WA	98124	USA"	
"10270	WARTH	1	1996-08-01 00:00:00	1996-08-29 00:00:00	1996-08-02 00:00:00	1	136.54	Wartian Herkku	Torikatu 38	Oulu		90110	Finland"

This is the code which I have written but I dont know whether its correct since its throwing exception error.

C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace FlatFiletoSQL
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Movebtn_Click(object sender, EventArgs e)
        {
            string ConnString = "Data Source=ABC\\SQLEXPRESS;Initial Catalog=TCZ; Integrated Security=True";
            SqlConnection connection = new SqlConnection(ConnString);
           // connection.Open();
            string filepath=(@"C:\Sample.txt");
            StreamReader SR=new StreamReader(filepath);
            string Line=SR.ReadLine();
            string[] Value=Line.Split(',');
            DataTable Dt=new DataTable();
            DataRow row;
            foreach(string dc in Value)
            {
             Dt.Columns.Add(new DataColumn(dc));
            }
            while(!SR.EndOfStream)
            {
             Value=SR.ReadLine().Split(',');
                if(Value.Length==Dt.Columns.Count)
                {
                 row=Dt.NewRow();
                    row.ItemArray=Value;
                    Dt.Rows.Add(row);


                }


            }

            SqlBulkCopy bc=new SqlBulkCopy(connection.ConnectionString,SqlBulkCopyOptions.TableLock);
            bc.DestinationTableName="tblxyz_test";
            bc.BatchSize=Dt.Rows.Count;
            connection.Open();
            bc.WriteToServer(Dt);
            bc.Close();
            connection.Close();

           

        }
    }
}

[edit]Code block added - OriginalGriff[/edit]

Posted 15-Nov-13 14:40pm

Member 10344739

Updated 15-Nov-13 20:49pm

OriginalGriff

v3

Add a Solution

Comments

Thava Rajan 16-Nov-13 0:13am

unless you didn't have data how do you know what kind of structure it has and how did you write a code for it, give some sample data for the text file, and give some start for the import we will surely help you

Member 10344739 16-Nov-13 0:33am

Sir Kindly give me your email id or something where I can post what I have done , I am not working right now but I am practicing on a sample data.

Member 10344739 16-Nov-13 0:43am

I am editing the question so that you can understand better

Member 10344739 16-Nov-13 1:00am

I sent the text file to answers@codeproject.com hope it works

OriginalGriff 16-Nov-13 2:19am

I don't know why you can't copy and paste code directly - everyone else seems to have no problem.
Highlight and copy your code from VS (or notepad if you are using that (why?)
Use the "Improve question" widget to edit your question.
Put the insertion point where you want the code.
Paste it (either by CTRL+V, or rightclick).
Select "Encode HTML" from the list that appears on the right.
Highlight the code in the question with the mouse, and use the "code" dropdown above the textbox to select the language.
Post the modified question.

Sending an email to CodeProject will not help, as only admins have access to those...

Member 10344739 16-Nov-13 2:29am

Okay thanks I do know I ca do that but thought that the data was too big !!

OriginalGriff 16-Nov-13 2:37am

Well obviously you don't post you your whole program! :laugh:
Just the relevant bits - the stuff we need to see.
Don't post all the stuff "round the edges" - it means we have to wade through it to find out what might be relevant, and that annoys people (with good reason, as well!)

Member 10344739 16-Nov-13 2:39am

sure :) thank you again I did modified everything hope it helps

Member 10344739 16-Nov-13 3:10am

I did use internet help but didn't copy ; I actually tried this with some other data .
Am a beginner in this technology I am really nervous but hope that I will learn eventually but now am not sure anymore I used to work as a manual tester maybe thats the only thing I can do thanku I will try what u told me but can u explain me how to write logic for these kind of applications just start from basics??

OriginalGriff 16-Nov-13 3:24am

Answer updated

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-11-15T20:58:00

Well, it looks like you have grabbed some code randomly from the internet, chucked it into your program, crossed your fingers and hoped it magically worked...

And it doesn't, does it?

First things first: look at your data:

"OrderID	CustomerID	EmployeeID	OrderDate	RequiredDate	ShippedDate	ShipVia	Freight	ShipName	ShipAddress	ShipCity	ShipRegion	ShipPostalCode	ShipCountry"

That is the top line that you read as a special case to get the headings.
This is the code you use to get the headings as separate objects:

C#

string Line=SR.ReadLine();
string[] Value=Line.Split(',');

Now, have a good close look at the data, and tell me how many commas it contains.

I'll wait while you count them...

None, right? So what effect do you think trying to split your data on a comma is going to have?

Start again, looking at the data first and work out what you have. Then write the code to split it out into the fields you want (nothing more, don't even think about going near SQL yet) and test it. Check it. Make sure it works, every single time, with every single line.

Then look at trying to transfer the data to SQL - but you can't even think of doing that until you have got the input data side working.

BTW: Personally, I would make the "Read Input" code a separate method - pass it a file, return a DataTable, or a List of a custom class. And I'd do the same for the SQL stuff as well...

"I did use internet help but didn't copy ; I actually tried this with some other data .
Am a beginner in this technology I am really nervous but hope that I will learn eventually but now am not sure anymore I used to work as a manual tester maybe thats the only thing I can do thanku I will try what u told me but can u explain me how to write logic for these kind of applications just start from basics??"

The first thing to do is look closely at your data (And I can't do that for you, it's been processed by your editor, the textbox paste filters, CodeProject database injection heckers, and Chrome at this end before I get to see it - you need to look at the source data directly).
Are those spaces? Or Tabs?
Are you columns separated by "one-or-more-spaces", or at fixed columns?
I'm just guessing from the "look" of the data, but it looks like tab-delimited data to me from here - and it is important to know exactly, or some assumption you make will blow up in your face at a later date.

So start by looking at the data, preferably with a Hex editor (If you don't have one, I can recommend PsPad[^] - it's free, and a useful editor to boot.

Sorry, but you are going to have to do this bit - as I said, the data is to "processed" to be much use to me as it is.

When you have that bit, we can make some decisions as to the best way to proceed.

BTW: Reply with the Comment feature below this - it means I get emails directly which can speed things up.