How to Import Excel file to SQL using VB6.0?

[killersmiles]

---

Guyz,

Unsaon pag import sa excel file..dako kaau nga file mangud.. 40,400 rows..sa excel ako i import sa SQL?
any sample or idea mga brod?

Thanks

[tsunade]

aw. better migrate to a reliable database.. you can find some conversion tools sa net

[killersmiles]

aw. better migrate to a reliable database.. you can find some conversion tools sa net

Bro...ang excel file mangud ang output report from one of our client then from excel file gusto nko i automate ang pag migrate from excel to SQL using vb6 kay akong mga program is nka vb6 mangud..

if u have some ideas please kindly share..

thanks bro.

[Rainerius]

It is easy to do using .NET but I don't know about VB6. Try to google. I think you need to set your Excel file as a datasource using ODBC or something. Then you read the spreadsheet as you would a normal SQL table.

[messerchtmitt]

 Dim conn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long

    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\sample.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Import by using Jet Provider.
    
        strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    
        Debug.Print strSQL
    conn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    conn.Close
    Set conn = Nothing

[Maikeru]

Guyz,

Unsaon pag import sa excel file..dako kaau nga file mangud.. 40,400 rows..sa excel ako i import sa SQL?
any sample or idea mga brod?

Thanks

1st naq advice, pag search lang sa google kung naa naba existing nga application for that.

Anyway, you want to use VB6 para magMigrate sa data from excel to a database (not sure unsa ni nga database imo ginaMean, but anyway, VB6 can connect to if not all, most of the existing database engines.

In .NET era, dali ra ni iAchieve. In VB6, I'm not that well aware kung unsaon, but my idea is, use the ActiveX/OLE object of excel. to get the data from excel(Pwed mana gamiton sa VB). dn create your customized program to convert and save it to your desired database.

[fcipriano]

not familiar with VB6, but with .Net dali ra na, himuon ra nmu ug recordset and excel file using ODBC and ma query ra nmu. What if imu i display ang excel file in a flexgrid and then imu dayon kuhaon ang data from the grid to your SQL database??

[killersmiles]

ako na ng natry bro...but dili ka kaya ang flexgrid sa kadako sa data sa excel...almost 40K,,,

dali ra i import ang excel file sa vb but the problem is if ma import na nko..dna mao ang files..nagkatibolaag cgru tungod kay dako na kau ang excel file..bcin namo idea ani nga probs...

Thanks

[salbahis]

Bro...ang excel file mangud ang output report from one of our client then from excel file gusto nko i automate ang pag migrate from excel to SQL using vb6 kay akong mga program is nka vb6 mangud..

if u have some ideas please kindly share..

thanks bro.

try saving the excel into csv para text format na siya... then look for a code to read csv....or make a regular expression function... from there you can easily save the data into the any database.... the most important thing in the process is getting the data from excel to a array or temporary cache...

para madali... naay components ana pero im not sure if its a good idea to use since it require the component to where ever you run the application... saving it to csv is the best straight forward and component independent solution

btw this is my approach to load 1k kabook excel files using VBA

1) Convert all excel to CSV
2) Parse CSV and save it into XML
3) Load the XML and save it to database

I made an application when i was still working in innodata to compile all the excel files into one spreadsheet, the one made by their programmer similar to the code above took days if not weeks to load all 1k files, my code took only less than 30 minutes... loading a text file is much faster than loading an xls... but im not a programmer in innodata.... sayang giangkon sa way ayong programmer nga mga taphaw ang code... hahahahaha

[iDev]

 Dim conn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long

    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\sample.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Import by using Jet Provider.
    
        strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    
        Debug.Print strSQL
    conn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    conn.Close
    Set conn = Nothing

mao pud ni ako gi gamit sa una, It is possible na wala Microsoft Jet OLEDB library imong PC, but ma download rana nimo sa microsoft na site. Just save the excel file as csv and your ready to go. Let us know pud kung unsa na approach imong gi take, at least we would also know it. Bali knowledge sharing ba.