Excel Handling before mssql import

[umararif]

hi all,
I am again strucked in my project, i have an excel sheet in following format.

Destination |Codes
Pakistan |92,9242,9251,9221
Pakistan-Mobile |92300,92321,92345

I want to import this file in MSSQL but out put table should be as follow.

Destination |Codes
Pakistan |92
Pakistan |9242
Pakistan |9251
Pakistan |9221
Pakistan-Mobile |92300
Pakistan-Mobile |92321
Pakistan-Mobile |92345

i have script for Excel to DB import, i just need the excel handling in VB to split "Codes" as shown above.

please help me.

Umar

[Shuja Ali]

Which version of SQL are you using? If it is SQL 2005, then you can use SQL Server Integration Services to do the splitting of the Codes part. You can easily do this using Script Component of SSIS.

In case you want to do it in VB 6, you will have to use some custom logic. You will have to write logic for splitting the Codes field on comma and then load each row into SQL database. Can you show us the code that you have written so far?

[umararif]

Thnaks Shuja,
i am using MSSQL2005 but i want this in VB6. Following is the method i am using for excel handling.

Public Function Proc_G3U_Prefix(FileName As String)

Dim oXLApp As Excel.Application 'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet

Set oXLApp = New Excel.Application 'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Open(FileName) 'Open an existing workbook
Set oXLSheet = oXLBook.Worksheets(2) 'Work with the second worksheet
SH_NAME = oXLSheet.Name
oXLSheet.Range("A1:A5").EntireRow.Delete 'Delete First Row of Worksheet

Set oXLSheet = Nothing 'disconnect from the Worksheet
oXLBook.Close SaveChanges:=True 'Save (and disconnect from) the Workbook
Set oXLBook = Nothing
oXLApp.Quit 'Close (and disconnect from) Excel
Set oXLApp = Nothing


End Function

[Shuja Ali]

I don't see any code that reads the excel file and loads it into SQL Database. YOu will have to show us the code that reads the file and then inserts the data in SQL. And while posting code, make sure you use CODE Tags.

As I said before you will have to split the data in second cell using Split() method and comma as a delimiter.

Trust me, you are better off using SSIS than VB6. SSIS will be lightning fast and it will be easier to maintain.