Best practice

[WelshyWizard]

Hi all,

I am looking at importing a csv file into a table in SQL Server. I would like to do this via a VB.NET windows application.

Either before or after my import, I need to manipulate the csv file so that certain information is split out (creating new fields) and other information is concatenated into one item (again creating a new field). I also need to create a unique id based upon several pieces of data held within the csv file. This id would not exist if I imported the file into SQL Server before manipulation.

Because I have a history of using Excel VBA, I was thinking of opening the csv file in excel (via VB.NET), carrying out the manipulations (automatically through VBA) and then importing the new format csv file into a pre-defined SQL Server table, again via VB.NET.

What do people think about this? Is this poor practice? Should I be doing all of the data manipulation in VB.NET after importing into a SQL Server table?

If anyone needs any further info, please don't hesitate to ask.

Cheers


Today is the tomorrow you worried about yesterday - and all is well.....

 

[SiriusBlackOp]

Stay away from using Excel.

To get the unique ID in SQL just create your ID field and set the field's Properties to be an Identity. This will make SQL create a new unique ID for each record you insert into the table.

Read your CSV file using VB.NET one line at a time, Manipulate the data if you must, then create an SQL INSERT statement to insert that line/record into the SQL table.

To read the file:

            Dim sFileName As String = sPathNew
            fsFile = New System.IO.FileStream(sFileName, System.IO.FileMode.Open, System.IO.FileAccess.ReadWrite)
            fsStream = New System.IO.StreamReader(fsFile)
            Do While fsStream.Peek >= 0
                Dim sLine As String = fsStream.ReadLine
                Dim sVals as String() = sLine.Split(",")
                'Create your SQL INSERT statement here.
                'You can access values like: sVals(0) or sVals(1)... etc
            Loop

Senior Software Developer

http://www.scoutsft.com

 

[WelshyWizard]

Cheers for this reply SiriusBlackOp.

I have a question regarding this post. Open to everyone of course...

What do I declare sPathNew, fsStream and tsFile as? Also, could you explain a little bit about how this will work?

Cheers.





Today is the tomorrow you worried about yesterday - and all is well.....