Read Excel workbook to database

rakuen · Nov 8th 2010

Hello

I'm working on different kinds of excel workbooks, that need to be read to databases.

Which kind of approach do you suggest i should use?

Read the sheets to a single recordset, and then dump it into the database?

Read row by row?

Do you know if there is some ready-made solution for this kind of task?

Thanks

Rob Xaos · Nov 8th 2010

Re: Read Excel workbook to database

i think the problem is too open ended to provide a solution. It will depend entirely on how the data is configured in each Workbook/Worksheet.

Unless you can describe how there are similarities between the data in the different Workbooks then it is unlikely there will be a generic solution.

Craig Ottley · Nov 9th 2010

Re: Read Excel workbook to database

What database solution are you using? If you're using SQL Server you can use SSIS to import the data for you providing nothing is dynamic

rakuen · Nov 11th 2010

Re: Read Excel workbook to database

Hello

The database is sybase based.

I have different excel sheets that should first be saved into databases, and then merge those into a single database.

The database structure is identical in all of them:

cust_id (PK) -> id number
cust_alt (PK) -> alternative name identifier
cust_name -> customer name
date -> date

I thought the best solution could be:

1. Save excel sheet to .csv with tab delimited
2. Read .csv to database
3. Combine databases

Do you have other suggestions ?

On step 3. what would be the best way to combine them ? Can you give me SQL help how to execute combining ?

Thanks!

Rob Xaos · Nov 11th 2010

Re: Read Excel workbook to database

I am a little short of time at the moment so I don't have time to clean these up to be more generic, but you may find the following two bits of code useful:

Code

Option Compare Database
Option Explicit
'------------------------------------------------------------
' Import_FPS
'
'------------------------------------------------------------
Public Sub Import_FPS()
    Dim strFullPathAndFileName As String
 
    On Error GoTo ErrorHandler
 
    strFullPathAndFileName = BrowseFiles("Import FPS Data as .csv")
    If strFullPathAndFileName <> vbNullString Then
        DoCmd.Echo False
        DoCmd.SetWarnings False
        'Create t_Data_FPS_Import as temporary table
        DoCmd.RunSQL _
            "CREATE TABLE tblData_FPS_Import (Data_Set_FPS_ID Long, Time_ID Counter, FPS Long);", False
        'Import .csv file
        DoCmd.TransferText acImportDelim, _
            "FPS Import Specification", _
            "tblData_FPS_Import", _
            strFullPathAndFileName, True, ""
        'Update t_Data_FPS_Import.Data_FPS_ID with Forms!frm_Import_Test!cmbDataSetFPSID
        DoCmd.RunSQL _
            "UPDATE tblData_FPS_Import " & _
            "SET tblData_FPS_Import.Data_Set_FPS_ID=Forms!frmTest_Import_FPS!ctlDataSetFPSID.Value;", False
        'Insert  tblData_FPS_Import into  tblData_FPS
        DoCmd.RunSQL _
            "INSERT INTO tblData_FPS SELECT tblData_FPS_Import.* " & _
            "FROM tblData_FPS_Import;", False
        'Delete temporary t_Data_FPS_Import
        DoCmd.DeleteObject acTable, "tblData_FPS_Import"
    End If
Import_FPS_Exit:
    DoCmd.Echo True
    DoCmd.SetWarnings True
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
    Resume Import_FPS_Exit
End Sub

Display More

Code

Option Compare Database
Option Explicit
'
' From: [URL]http://www.tek-tips.com/viewthread.cfm?qid=1289131[/URL]
'
'   Me!TextboxName = BrowseForFile()
'   variable = BrowseForFile()
'
Private Const VER_PLATFORM_WIN32_NT = 2
Private Type OSVERSIONINFO
   dwOSVersionInfoSize As Long
   dwMajorVersion As Long
   dwMinorVersion As Long
   dwBuildNumber As Long
   dwPlatformId As Long
   szCSDVersion As String * 128
End Type
Private Declare Function GetVersionEx Lib "kernel32" Alias "GetVersionExA" _
                        (ByRef lpVersionInformation As OSVERSIONINFO) As Long
Private Declare Function GetFileNameFromBrowseW Lib "shell32" Alias "#63" _
                        (ByVal hwndOwner As Long, _
                         ByVal lpstrFile As Long, _
                         ByVal nMaxFile As Long, _
                         ByVal lpstrInitialDir As Long, _
                         ByVal lpstrDefExt As Long, _
                         ByVal lpstrFilter As Long, _
                         ByVal lpstrTitle As Long) As Long
Private Declare Function GetFileNameFromBrowseA Lib "shell32" Alias "#63" _
                        (ByVal hwndOwner As Long, _
                         ByVal lpstrFile As String, _
                         ByVal nMaxFile As Long, _
                         ByVal lpstrInitialDir As String, _
                         ByVal lpstrDefExt As String, _
                         ByVal lpstrFilter As String, _
                         ByVal lpstrTitle As String) As Long
Public Function BrowseForFile(ByRef strTitle As String) As String
   Dim strSave As String
 
   strSave = Space(255)
   'If we're on WinNT, call the unicode version of the function
   If IsWinNT Then
      GetFileNameFromBrowseW Screen.ActiveForm.Hwnd, _
                                StrPtr(strSave), _
                                255, _
                                StrPtr("c:\"), _
                                StrPtr("txt"), _
                                StrPtr("FPS files (*fps.csv)" & Chr(0) & "*fps.csv" & Chr(0) + _
                                    "All files (*.*)" & Chr(0) & "*.*" & Chr(0)), _
                                StrPtr(strTitle)
   'If we're not on WinNT, call the ANSI version of the function
   Else
      GetFileNameFromBrowseA Screen.ActiveForm.Hwnd, _
                                strSave, _
                                255, _
                                "c:\", _
                                "txt", _
                                "FPS files (*fps.csv)" & Chr(0) & "*fps.csv" & Chr(0) & _
                                "All files (*.*)" & Chr(0) + "*.*" & Chr(0), _
                                strTitle
   End If
 
   BrowseForFile = Trim(Replace(strSave, Chr(0), " "))
End Function
Public Function IsWinNT() As Boolean
   Dim myOS As OSVERSIONINFO
 
   myOS.dwOSVersionInfoSize = Len(myOS)
   GetVersionEx myOS
   IsWinNT = (myOS.dwPlatformId = VER_PLATFORM_WIN32_NT)
End Function

Display More

The second piece of code is simply a file browser. The first pulls a CSV file into a temporary table and then updates it onto an existing table.

You should be able to adapt these to suit your exact situation. You may not need to create a temporary table in your case but simply update the new CSV file onto the existing table

Read Excel workbook to database

Participate now!

Share