Import From Excel
Dec 16, 2004
This is a bit of a tricky one to explain, so please bear with me:
I have a table set up that I regularly import data into using the docmd.transfersheet command.
The problem I am having is that one of the columns I want to import as type text length 3 (the field in the table is set as this)… This column (in excel) has values in such as 001, 121, 999, T10. If the values in the first 10 or so rows are full numbers i.e 121 or 999 then the import command takes this column as being a double. Meaning that it misses out when it gets to values of 001 or T10 etc… and replaces them with NULL
If I reorder the excel sheet before importing and make sure a text value (i.e 091 or T10) is near the top the import works fine and all the data is imported.
I have also tried manually importing the data into the table and a new table and both give the same results.
The question, how can I force access to import that column of the sheet as a text field?
I have tried everything I can think of and have failed miserably.
Any help would be greatly appreciated.
View Replies
ADVERTISEMENT
Sep 19, 2006
Hi,
Can someone advise how to import from excel. include the heading.
thanks
View 1 Replies
View Related
Jan 12, 2008
I think this is an age old question but I have yet to ask it. I have a XLS spreadsheet I'm importing into a table in access. One of the column in excel has both the first and last name and middle intial sometimes such as Frank H. Urtz. I want to seperate this in access. firstname =Frank lastname=Urtz midinitial=H. How do I go about it?
I think I want to write a query saying give me everything up to first " ". Insert that into field1. GThe give me everything from the first ' " to the second " " and insert into field2 and so on.
Any ideas?
View 4 Replies
View Related
May 5, 2005
hi,
I'm having a small but annoying problem exporting data to excel and then importing it back..
The problem is that when i export a table to Excel - it changes the date format from dd/mm/yy (which is what i have in Access) to DD-MMM-YY (automatically in Excel)
This proves to be a problem because when i import the spreadsheet back Access gives me a data type conversion error.
Its easily solved manually by changing the field in excel to dd/mm/yy format but annoying as the user will have to do this everytime they export and import!
Any suggestions??
Thanks in Advance..
View 6 Replies
View Related
Sep 27, 2005
I have used the search and it proved to be very helpful, as always. However, I still have an issue with my coding, it keeps trying to open my Excel file I have imported after I have closed it (or at least I think I have). Here is the code (copied right from another thread with a few tweaks):
Option Explicit
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Private Sub Command0_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim oApp As Object
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.Hwnd
sFilter = "acSpreadsheetTypeExcel9 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:"
OpenFile.lpstrTitle = "Select the Information to Import"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
Exit Sub
End If
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile
With oApp
.Visible = True
WrksheetName = "Import"
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel9, WrksheetName, OpenFile.lpstrFile, True
End With
oApp.Workbooks.Close
oApp.Quit
Set oApp = Nothing
End Sub
View 5 Replies
View Related
Nov 5, 2005
Someone please help!!!
The "file of type" does not display the excel type. . . only type database and ODBC. I'm using MSAccess 2000. Does it not support this type of import? Is there an add-in I need?
Thanks
Cathy
View 1 Replies
View Related
May 2, 2006
Hi All
I'm trying import an excel spreadsheet into an exisiting table. But there are some conversion errors in some records. But there is no conversion error table or log for me to trace. Anyone knows how can I find the records with error in order to fix them? Thanks in advance!
View 4 Replies
View Related
May 30, 2007
I have an excel file worksheet(player info sheet)that the user would input information. I then copy that info into another worksheet(player info) in the data fields that I have defined in Access. I then open up my Access database and do a file-get external data-import. I then select my excel file and the worksheet named "player info". I get the import fine but there is a table that gets created that is called: 'Player Info Sheet$'_ImportErrors. I cannot figure out why. Any help would be appreciated. Thanks.
View 4 Replies
View Related
Mar 11, 2008
I Cannot import excel data to an access table .The type is the same.The colums are the same number.No duplicate values exist in the colum that is primary key.
WHYYYYYY?
View 11 Replies
View Related
Apr 14, 2008
Hey Guys,
I read several threads for suggestions, and most of them ask to import raw data into a temp table then append. But thought I see if this would be possible instead. The following codes imports all excel files in a folder, and extracts the date from the file name and puts that into a field in the table. And also moves the imported file to an archive folder after import.
Private Sub btnImport_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim path As String
Dim TheDate As Date
Dim fs
DoCmd.SetWarnings False
path = "C:UsersChinaboyDesktopData" ' Path that stores Historical Report Downloaded files.
'Loop through the folder & build file list
strFile = Dir(path & "*.xls")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files
For intFile = 1 To UBound(strFileList)
strFile = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tblAgentSummary", strFile, False
'Adds date to callDate field based on the date on file name.
TheDate = Mid(strFile, 54, 2) & "/" & _
Mid(strFile, 56, 2) & "/" & _
Mid(strFile, 58, 4)
CurrentDb.Execute "UPDATE tblAgentSummary SET callDate =" & "'" & TheDate & "' where callDate is null"
'set directory to look for next text file
Next intFile
'Moves imported file to Archive folder
Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile "C:UsersChinaboyDesktopData*.xls", "C:UsersChinaboyDesktopArchives"
DoCmd.SetWarnings True
End Sub
Is it possible for me to open each excel file in thebackground in the folder, format it before or during the import process. What I need to do is first delete rows 1 and 2, delete column B, D, F, and I. Then move to the last row with data then delete that row along with the previous 2.
I found this code that I thought may help, but I am not sure how to work it in with my exsiting code. Any suggestion and idea will be greatfully appreciated.
Private Sub Command0_Click()
Dim xlApp As New Excel.Application
Dim xlwrkBk As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlwrkBk = xlApp.Workbooks.Open("C:Documents and Settings34036460 Project Files140 Excel AutomationXOR27NovB.xls")
Set xlSheet = xlwrkBk.Worksheets("OpenExchangeOrders27Nov")
xlSheet.Rows(1).Delete
xlwrkBk.Save
xlwrkBk.Close
Set xlSheet = Nothing
Set xlwrkBk = Nothing
xlApp.Quit
Set xlApp = Nothing
MsgBox "Done"
End Sub
View 6 Replies
View Related
Apr 20, 2005
Hello - just joined today. I hope to be of some help to other users, but am kind of a newbie.
I have an excel ss that I regularly receive which needs to go into my access DB. I set up a macro with transferspreadsheet and that works, except for one problem. I have one field which is designed as text but has data that looks like numbers as well as data that looks like text. When I run the macro, the number-like data doesn't import as text (23602012345 imports as 2.3602e+010). FYI the field in access is already defined as text. Each time I import a ss, I am appending to the existing data in the table
I have tried formatting the excel column as text with no luck. If I remove the data that truly is text in that column (ABC1287567) and leave just the 236* data, and then format the excel column as text, it seems to work fine. It just doesn't like the mixing of the data...at least it seems to me.
Any ideas to avoid manual manipulation of my excel ss before running the macro would be very helpful.
Thanks much in advance!
View 5 Replies
View Related
Jun 2, 2005
Howdi all,
I am creating a database from many, many excel sheets for monitoring of waterbores. A few of these are fields indicating whether something is done or not. If the thing has been done an X is marked. Therefore in Access I want these to be yes/no datatype and the import to recognise the X as yes.
Is this possible and if so how?
cheers for help
nail
View 2 Replies
View Related
Mar 14, 2006
Hi everyone, i am creating a database by importing data from excel than normalizing it. In the excel spreadsheet thre is a full name field and want to import it into access by splitting it up into first and last name. Some of the data in the spreadsheet have middle initials. ANy idea on how to make this easier for me?
Thanks in advance
Joso
View 1 Replies
View Related
Dec 29, 2005
I have a form that I am trying to setup to click on the button and it opens dialog box to select file, then it transfers/appends the data into an existing table. I got the button to open the dialog box and select the file, but nothing is being added to the table. Can someone please help?
View 1 Replies
View Related
Oct 4, 2004
I have created an xls and its the same format as the table in my db. Same column name and same data type. However, when I try to import the xls to an existing table in my db, there was an error message that says, "An error occurred while trying to import file 'C:MyDocumentsPOSystem_Book.xls'. The file was not imported'". This error does not occur when I import it to a new table. Can anyone help in this? A million thanks in advance.
Regards,
Swee
View 2 Replies
View Related
Oct 5, 2004
I noticed that I did not put a question mark next to this the first time I posted it, so maybe it was not clear that I was hoping for a response. Here I go again. We are trying to reinstall Access97 (the OS is Office XP) and having problems getting all of the drivers to install. Using the "Add or Remove Programs" in the Control Panel to make sure that all drivers have been installed, it says that the Excel drivers are installed, though it is grey, which I take to mean that it is not completely installed. On the computer in question, it says that the database drivers take up 1329k and the excel drivers are" installed" (no size listed). On my computer, on which the excel drivers are available, it lists the database drivers as 2009k and the excel drivers 258k (it does not say "installed"). Anybody have any ideas?
View 1 Replies
View Related
Nov 22, 2004
Hello, i have installed Access 97 from an Office 97 CD on a new laptop and when trying to import a table and browse to my file location i cannot 'see' any excel files to import, it only allows me to see Acess files or ODBC sources. it worked fine on my previous pc using the same install method. any ideas? -thanks Scott
View 4 Replies
View Related
Mar 13, 2008
Anyone out there have any ideas on how to Import an Excel File (SPECIFIC worksheet) to an Access Database
I am looking to do this at the click of a button or when the database opens...
What I really want to do is......
Is there a way to import this into an already existing table.
Delete the existing records and import the new records from the Excel Spreadsheet?
THanks
View 6 Replies
View Related
Apr 3, 2007
Does anyone know if there is a special way to import a CSV file starting from a specific row. I have CSV files and the first row is header information. The file info doesn't actually start until the second row. I know I can write a MODULE to do the task but is there an import specification I can use?
Cheers,
View 2 Replies
View Related
Sep 2, 2005
:confused: Hi all - hope someone can help I dont know where to start.
I've got a cmd button that imports data from a excel sheet - that actual code is working fine, however when it runs it causes Access to freeze.
The data in imported correctly - so it must complete the process but Access locks up and has to be ended via task manager.
Any one got any clues why this is happening and how I can stop it?
This is an Access 97 database running mainly on NT and 2000
Private Sub LoadActualsDataButton_Click() On Error GoTo Err_LoadActualsDataButton_Click
' This procedure performs a two file match between the Actuals table (the Master file) and ' The Actuals spreadsheet file (the Transaction file).
'
' Keys : Study Code|Work Package|Period
'
' If the Master key < Transaction key then
' Read the next Master record.
' If the Transaction key > Master key then
' Add the transaction record to the Master file
' Read the next Transaction record.
' If the Master key = Transaction key then
' Update the value on the Master record with the value on the Transaction record
' Read the next Master Record
' Read the next Transaction record.
'
' End of File processing
' At End of File on the Master file, set the Master key to "ZZZZZZ"
' At End of File on the Transaction file, set the Transaction key to "ZZZZZZ"
' Continue processing until both keys are equal to "ZZZZZZ"
Dim MyDB As Database, MySQL As String, MySet As Recordset Dim appExcel As Excel.application Dim MyFiles As String Dim MasterKey As String, TransactionKey As String
Set MyDB = CurrentDb()
Set appExcel = CreateObject("Excel.Application")
' Set up the transaction file (Actual Data Spreadsheet)
MyFiles = appExcel.GetOpenFilename("Excel Files(*.xls),*.xls", , "Open Actuals Spreadsheet") If MyFiles = "False" Then Exit Sub
appExcel.Workbooks.Open FileName:=MyFiles, ReadOnly:=True appExcel.Visible = False
' Check that this is a genuine Actual spreadsheet On Error Resume Next Let Err.Number = 0 appExcel.Sheets("Sheet1").Range("B1").Select
If Err.Number = 9 Then
MsgBox "This is not a valid Actuals Spreadsheet."
appExcel.Quit
Exit Sub
End If
If appExcel.ActiveCell <> " Extracted Actuals Data" Then
MsgBox "This is not a valid Actuals Spreadsheet."
appExcel.Quit
Exit Sub
Else
appExcel.ActiveCell.OffSet(1, 0).Range("A1").Select
TransactionKey = appExcel.ActiveCell.OffSet & appExcel.ActiveCell.OffSet(0, 1) & appExcel.ActiveCell.OffSet(0, 2) End If appExcel.Visible = True
' Set up the Master File (Actual Table)
MySQL = "SELECT Actuals.[Study Code], Actuals.[TBCS Code], Actuals.[Year/Month], Actuals.Actual "
MySQL = MySQL + "From Actuals "
MySQL = MySQL + "ORDER BY Actuals.[Study Code], Actuals.[TBCS Code], Actuals.[Year/Month]; "
Set MySet = MyDB.OpenRecordset(MySQL)
If MySet.EOF Then
MasterKey = "ZZZZZZ"
Else
MasterKey = MySet![Study Code] & MySet![TBCS Code] & MySet![Year/Month] End If
Do Until TransactionKey = "ZZZZZZ"
If MasterKey < TransactionKey Then
' Read the next master record
MySet.MoveNext
MasterKey = MySet![Study Code] & MySet![TBCS Code] & MySet![Year/Month]
GoTo Next_Loop
End If
If MasterKey > TransactionKey Then
' Add a new record from the Transaction to the Master
MySet.AddNew
MySet![Study Code] = appExcel.ActiveCell
MySet![TBCS Code] = appExcel.ActiveCell.OffSet(0, 1)
MySet![Year/Month] = appExcel.ActiveCell.OffSet(0, 2)
MySet!Actual = appExcel.ActiveCell.OffSet(0, 4)
MySet.Update
' MySet.Requery
appExcel.ActiveCell.OffSet(1, 0).Range("A1").Select
TransactionKey = appExcel.ActiveCell.OffSet & appExcel.ActiveCell.OffSet(0, 1) & appExcel.ActiveCell.OffSet(0, 2)
GoTo Next_Loop
End If
' Keys are equal so update the Master with the Transaction value
MySet.Edit
MySet!Actual = appExcel.ActiveCell.OffSet(0, 4)
MySet.Update
' GoTo Next_Loop
appExcel.ActiveCell.OffSet(1, 0).Range("A1").Select
TransactionKey = appExcel.ActiveCell.OffSet & appExcel.ActiveCell.OffSet(0, 1) & appExcel.ActiveCell.OffSet(0, 2)
MySet.MoveNext
MasterKey = MySet![Study Code] & MySet![TBCS Code] & MySet![Year/Month]
Next_Loop:
Loop
Exit_LoadActualsDataButton_Click:
Exit Sub
Err_LoadActualsDataButton_Click:
MsgBox "An has occured." & vbCrLf & vbCrLf & _
"Error number: " & Err.Number & vbCrLf & vbCrLf & _
"Description: " & Err.Description
Resume Exit_LoadActualsDataButton_Click
' = Mid(ActiveCell, 1, (Len(ActiveCell) - 1)))
End Sub
Private Sub MainMenuButton_Click()
On Error GoTo Err_MainMenuButton_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Main_Menu"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_MainMenuButton_Click:
Exit Sub
Err_MainMenuButton_Click:
MsgBox Err.Description
Resume Exit_MainMenuButton_Click
End Sub
Private Sub MaintainContactTableButton_Click()
On Error GoTo Err_MaintainContactTableButton_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmBDTSContactsMaintenance"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_MaintainContactTableButton_Click:
Exit Sub
Err_MaintainContactTableButton_Click:
MsgBox Err.Description
Resume Exit_MaintainContactTableButton_Click
End Sub
Private Sub MaintainersNBTUsersButton_Click() On Error GoTo Err_MaintainersNBTUsersButton_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmBDTSMaintainNBTUsers"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_MaintainersNBTUsersButton_Click:
Exit Sub
Err_MaintainersNBTUsersButton_Click:
MsgBox Err.Description
Resume Exit_MaintainersNBTUsersButton_Click
End Sub
View 4 Replies
View Related
Sep 7, 2005
Hello All
is there a way (i guess using VBA) to import an excel spreadsheet into an access table without having to go through the wizards, if you know all the field names and other variables ??
Yours in anticipation
DJS
View 8 Replies
View Related
Feb 5, 2006
Hello All,
I thought i would ask this question an how to import data from excel into access.
I am using sage to invoice clients and i can output this in to excel format then i would like the best and simplest way to pull the data though into access.
The data file has column headings at the first row ie A1 though to J1 and after this is the data, i have a table setup with the relivent table names, I had thought how easy this would be, but as always this was not the case.
The outputed excel sheet will vary in location so i think i need a diolog box to select the file and then some how link it to extract the data, is this right and if so how can it be achived.
Any thoughts
Alastair
UPDATE
**************************************Resolved Thanks to Pat Hartman *************************
View 2 Replies
View Related
Dec 13, 2007
Hi All,
I'm having a Excel Sheet With first column which has numeric data for first 70 or 80 records then comes some string data, here i'm using this statement to import data from excel to MS Access DataBase, but its not retriving the records which were in string data type, is there any other way to do this...?
SELECT [Item #], [Description], [Pack Size], [Price] FROM
[Database=C:Test.xls;Excel 8.0;HDR=Yes;IMEX=2].[Sheet1$] Where
[Item #] Is Not Null
thank's in Advance.
- Prakash.C
View 4 Replies
View Related
Jan 16, 2005
Hi all,
I use an Excel import to update tabel 'deelnemer'. This works oke BUT when user 'x' has a linked field in tabel 'B' the import fails due to RI (I think..)
How can I make it so in this code that the user record is updated and can I restore the RI (otherwise my forms won't work..)
This is the code I use for importing the Excel file:
DoCmd.CopyObject , "Deelnemer_copy", acTable, "Deelnemer"
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from deelnemer"
DoCmd.SetWarnings True
ImportFile = Application.CurrentProject.Path & "Deelnemer.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "Deelnemer", ImportFile, True
Please help me on this, breaking my head over it and I'm newbie on all this..
Thanks in advance !
View 3 Replies
View Related
Apr 22, 2005
look for the best method. I have another software to work with my access. End of each month, ProgramA will generate an excel file with the monthly data. I want to import/link it with my access. I first try to import it everytime I generate the new excel file. However, there are one line at the end of the excel file with does not match the feild requirment, and generate an error table in access saying a number field cannot have string.
Then I try the link method instead. This time, it would work at all. The first time is OK, but the next time, I guess more lines are generate than the orginal in the excel file, it could not open up. Number of columns is the same.
The best method right now is to delete the last line of the new generated excel file, however, because I am not the one using it, I want to have a better method for my co-workers.
Are there ways to import excel data except the last line;
or
Are there ways to import excel file without an error table generate
Thanks :)
View 1 Replies
View Related
Apr 14, 2006
Greetings,
I searched on IMPORT, but didn't see anything like the problem I have.
I have a large Excel file formatted thus;
COLUMN A COLUMN B COLUMN C
1 Full Name
2 Full address
3 City State Zip
4 Phone SSN Sex
Alas, as you can see, the first four rows contain information on one person, then Column B contains just their SSN on Row 4, and so forth.
Row 5 begins the cycle again. This goes on for 160 people.
Is there a way to get the employee information contained in Column A in a 'nomalized' format, such as Full Name in Col A, Full Address in Col B, and so on?
Unfortunately, it's illegal here to whack the person who provided this data to me.
Thanks in advance !!
View 3 Replies
View Related
