Importing Excel Data Into A Database
Jul 20, 2006
which databases people are using with importing excel data into a database.
I want to know people's experiences on different databases and suggestions on which database i can use maybe.
On this moment i do everything in excel, but excel is not longer working properly because of large files that need to be connected to 1 or more sheets.
This is the situation now:
VAX (Dos bases database) -> Comma seperated Files -> Importing in Excel -> Linking the data to sheets.
Now I want to have a database where i can do all normal operations to have a good look and the situation of stock status, ordered parts, sales etc. (inventory control)
Who has experiences with databases ? And what are you doing with that database?
View 3 Replies
ADVERTISEMENT
Mar 19, 2014
I'm importing data from Excel 2010 into a database.
I'm joining to 2 cells into one. A1 = 12 Rushwood Street and B1 = London. So C1 = 12 Rushwood Street London
I need C1 to look like this:
12 Rushwood Street
London
I tried wrap text but when I import it comes out as one line in the database.
View 3 Replies
View Related
Jan 6, 2014
Bar those who don't believe/celebrate in Christmas, Hope you all had a good Christmas and New Year celebrations.
I have two sets of data, lets call them 2013 and 2014. I have a Spreadsheet already set up in excel, but currently I just past this information into a tab in excel (which isn't ideal at all), and given the data is growing, is slowing down my excel spreadsheet a lot.
I have some experience with Access, SQL and queries.
What I have done thus far is to link my Access DB with Excel, run a query and paste the resulting table from the query into excel all using VBA (easy peasy).
The problem i have is that the table is still linked, and i do not want or need it to be linked. I know that there is a "Unlink" Option, which is not what i need, and also a "Convert to Range" option which is also not what i need, but closer. This got me thinking, is it possible to import the data into an array (all in VBA in excel from the query in the SQL) and then i can paste that information into my Excel spreadsheet, or is there a better method?
View 2 Replies
View Related
Mar 18, 2008
I'm having some issues importing external data from an Oracle 9i database.
Here is what is happening:
I am initially able to import data from the table I want to into Excel.
I do this through "Data -> Import External Data -> New Database Query" where I have my .ORA data source. I'm able to log in using my user and password and import the table. My problems come after I import this first set of data.
If I try to "Edit Query…" I get an error box that says "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
If I try to "Refresh Data" I get two error boxes. The first is just: "[Microsoft][ODBC driver for Oracle][Oracle]" and the next says: "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". It then prompts me for the User name and password of the DB I'm connecting and after I enter the information, I get the same two error messages.
Also, if I attempt to establish another connection following the "Data -> Import External Data…" steps I run into a different problem. Upon entering the information for the User Name and password prompt I get two error messages. One says "!" and the next box says "Couldn't read this file". Once I click "OK" in those boxes, the Microsoft Query window automatically opens with nothing in it.
I can't seem to figure out why I can initially pull down data and then cannot succeed afterwards. I'm unable to connect to the DB I have through Excel until I completely closed all Microsoft Excel instances.
I also use this data source in Microsoft Access without any problems.
View 9 Replies
View Related
Sep 11, 2009
I'm trying to import a data from a website using Microsoft Excel however the link is too long & when I do import data from web & I past the link in there, it says that it's too long and it won't let me.
View 9 Replies
View Related
Jun 17, 2014
I have an acess database where I export the data into a blank excel workbook and then copy and paste the data into a report file. My goal is to automate this process and my first thought was to add a button into the access database and have it export to an excel template, but using/editing the access database is not an option, so, that leaves me thinking about automating this process from excel. Is it possible to export data from an access database into cell A1 of a tab?
View 5 Replies
View Related
Jun 3, 2003
From inside Excel, I wish to surf to a website and retrieve the bottom entry in a column The column updates dynamically with a new number added to the bottom on a daily basis.
Here is the website [URL].......
The column I am interested in is the second column from the left under the History section. The column contains close prices for the currency symbol EUR/$.
View 9 Replies
View Related
Mar 4, 2007
I am trying to have the total (cell e78) from one of my own excel files automatically filled into a cell in a different excel file when the number of that file is typed into a third cell.
i.e. if the total on Ticket 4126 in cell e78 is $4500, then when I type 4126 into the Ticket # cell on my seperate Invoice I'd like it to fill in the third cell under Amount as $4500.
Is this possible?
View 10 Replies
View Related
Sep 17, 2013
is it possible to import just a portion of a csv file into my excel workbook? For example:
My csv file has data from 2010-2015. I want to somehow import only data between 2013-2015 from the csv file into my excel workbook every time I click refresh.
I cannot delete data from the source file because many other people are using the same file. Is there some sort of "SQL" for excel that makes it possible to trim csv file?
View 1 Replies
View Related
Feb 15, 2012
I'm working on creating a custom HTML report with javascript that I will be using to import in to excel to populate some graphs. Basically everything is working, except if I use javascript to show me a value.
When I use Import Data from Web via excel, everything gets imported except for the javascript part of the html doc.
An example of the html file (stripped down to nothing but 3 peices of data): [URL]
View 6 Replies
View Related
Mar 12, 2012
Our company has word template with userform that has 10 or so inputs. The data will have already been input into an excel file. Rather than manually retyping the data again, I'd like the option to import the data. Due to the data be specific to a given project I need the import function to ask user to select which excel file to import from. Additional there will be system parameters for multiple systems that will be on subsequent tabs of the same excel file. These system parameters aren't currently part of a userform. I'd like the option to import this data but rather than selecting a file only the user would also need to pick a specific sheet.
View 7 Replies
View Related
Aug 21, 2014
How to insert the column dividers manually--for importing a fixed-width text file into excel? The files I'm having to import consist of dozens and dozens of columns.
View 3 Replies
View Related
Feb 28, 2013
I have a table with a number of columns, all of them number format, first one is a unique ID tying all my tables together.
The table contains a running tally of credits for each member and I need import reports every now and then adding on to the tally.
How do I set this up so that I can have Access read an Excel file with data to be added to the existing (not replacing, adding up) and a new record created if it doesn't already exist?
I've tried the append external data, but that fails as the ID field already exist most of the time, even when I created a blank copy of the table I get error messages even if it then actually imports (first time).
Do I need to format the Excel sheet in any particular way to match the tables number format? Is there additional steps I need to take? Would this work even if the import sheet included the same user several times (each occurrence to be added on).
I really don't want to have to step through every user and manually add up and update each credit.
View 2 Replies
View Related
Jun 2, 2014
I have been tasked with streamlining a process to collect data from a specific online website (Web of Science) and import it into an Excel 2010 spreadsheet.
Currently they are going to the website, entering a short number of search parameters and then manually recording the pertinent data from the webpage. They would like to be able to enter a keyword in Excel (which acts as the search item) which then automatically does the rest of the process and provides them with a spreadsheet of the required data.
Is this possible? Perhaps by using Visual Basic code within Excel? I also saw a method that employed SharePoint Server 2010.
View 3 Replies
View Related
Aug 5, 2013
I have to import data from an external source(oracle database) to an Excel(2013) table.
Now the data in the staging table in the database keeps refreshing/changing, However in Excel i need the data to come into a new row everytime instead of refreshing the whole table and looking like the staging table in the database. So basically i need to build history in Excel.
View 3 Replies
View Related
Mar 31, 2004
I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
View 4 Replies
View Related
Apr 11, 2012
I wanted to see if there is a VBA code to do the following :
a) Select a TabText Delimited file based on a criteria
b) Import the Selected Data to Excel
I have the vba code where I can open the tab text delimited file in excel, use a selection criteria and then copy the data into excel. But I am having problems with the case where the Tab Text Delimited file exceeds the row limit that excel currently has and wanted to see if the data import can be done without opening the text file into excel at all.
Here is my current query :
Sub TEST()
' TEST Macro
Workbooks.OpenText Filename:="E:Testdata.txt"
ActiveSheet.Range("$A$1:$B$1048576").AutoFilter Field:=1, Criteria1:="1234"
Range(Selection, Selection.End(xlToRight)).Select
[Code] .......
View 3 Replies
View Related
Sep 8, 2008
I am using VBA to export an access query into excel, the query works fine in access but when importing the data into an excel spreadsheet, it doesn't display the date column headers in the correct format.
The problem I have is that the dates 1 to 12 are displayed the wrong way round eg:
In Access date column headers from query are:
09/01/2008, 11/01/2008, 12/01/2008, 14/01/2008, 15/01/2008 etc.
But when exporting to excel, the above dates are shown as:
01/09/2008, 01/11/2008, 01/12/2008, 14/01/2008, 15/01/2008 etc
It seems to be changing round the dd/mm when I export, but only upto 12 when the day is 13 or more it is displayed correctly.
Public Sub bttnDMA_Click()
'DMA Figures in Excel format
'Creating the Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn
Dim MySQL As String ....
View 9 Replies
View Related
Aug 6, 2013
Trying to use Excel Data List to create a database style report. IE. Originally blank sheet, which is only populated by data containing data matching "filters" input into cells ( say A1 & A2 )
I.e. A1 = Delivery week to be filtered by, and B1 Manufacturer Name
So if I type week "1" into A1 & Manufacturer "Microsoft" into A2, it will show a table only containing data Microsoft, Week 1, and associated data for those lines across the screen.
Week 1
Microsoft
PO number : Date Ordered: Address 1, 2 3 etc....
0011 01/01/13 Somewhere
0015 02/01/13 Anywhere
0213 05/01/13 Nowhere
I know this is much easier with a database, however my manager insists a database cannot be used, and it must be in a spreadsheet format !
View 3 Replies
View Related
Dec 9, 2011
I'm tasked with looking up part numbers in Epicor's ERP database and entering the labor cost into excel. Is there any way to write a macro or VB program that lokks at the part number in excel, goes into epicor, pulls finds the part number and cut and pastes the cost data.
I have about 30,000 of these to look up.
View 1 Replies
View Related
Feb 14, 2014
I have a number of large CSV files with approximately 1.9 million rows, (this is more then excel can bring in). I typically have to import/delimit the files when brining them into excel. However, when I try this I get the message not all data imported. I would like to be able to split the records in the csv file to multiple tabs in the excel woorkbook when doing the import/delimit but do not know how to accomplish this. The delimit of the data varies each time due to the nature of the data so doing a macro is more trouble to create each time. Currently I am having to open the csv file in notepad and split it up into multiple files then import each new file seperately.
View 4 Replies
View Related
Jul 1, 2014
I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......
I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....
Currently what i am trying to do is to get one of the minor files to auto populate an area of the master database. I will try to explain it below...
1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').
2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..
3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed
4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.
5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)
6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....
To make mattters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red
View 14 Replies
View Related
Jul 29, 2014
I am trying to create a Dashboard in excel (2010) using tables/pivot tables to build it. The data I am bringing into excel has these key fields of data: cost center+cost center description, general ledger account+general ledger account description, and YTD amount.
My problem is the data is from an external source report and the report has subtotals built in at cost center, and the report's format of subtotaling puts the cost center first and then the general ledger accounts below. There is no
formula value in the cell that has the subtotaled amount and the number of general ledger accounts can vary depending on whether there has been general ledger activity.
I want to take this format:
July YTD
Cost Ctr 1050 XYZ$6.00
625110 Supplies$2.00
650150 Postage$2.00
650550 Fees$2.00
Cost Ctr 1052 ZZZ$4.00
670500 Pens$2.00
and have it look like this
Cost CenterCost Center DescrGL AcctGL Acct descYTD Amt
1050 XYZ 625110 Supplies $2.00
1050 XYZ 650150 Postage $2.00
1050 XYZ 650550 Fees $2.00
1052 ZZZ 679200 Pens $2.00
Besides manually doing data moves and assigning a unique sort sequence number to keep the records together, how else can I quickly move my cost centers to a new column and keep the cost center with the gl account and $amount?
View 3 Replies
View Related
Mar 8, 2014
I am trying to create a database that contains data taken from several work books that i have saved in one folder on my hard drive. These workbooks contain quite a lot ** data, but i only want to extract the data i want to instead ** having to extract it all? these works books are used by several other people as well, so i want it to be able to update as data changes.
I was thinking about using data links, but that only seems to extract all the data instead ** just what i want?
View 1 Replies
View Related
Aug 1, 2014
Following macro code is for copying Excel Invoice Data to Excel Database. Also cleans the specified cells after copying. Its working Perfectly.
But there are some issues that should be solved.
Problem 1: It copies empty cells(""), formula based cells and blank drop-down list.
Problem 2: After copying it cleans formulas too. Formulas must be remain their.
View 2 Replies
View Related
Apr 16, 2012
What I am trying to do is extract all the data from a excel database based on criteria. When i specify the site and month I need to pull all the corresponding data and write it to a worksheet. Below is an example. I am not allowed to show all the data it is confidential.
Site Date Operator Month
Chicago 12/3/11 Daniel December
San Jose 4/8/12 Mike April
New York 4/8/12 John April
View 1 Replies
View Related
May 18, 2012
I'm working on an Excel based product. As part of the security process I would like to require the user to enter a product id key into excel. Then the spreadsheet would compare the entered value to some kind of online database to verify the id key is valid. Finally, the spreadsheet would update the online database so that the id key is no longer functional. How easy is this to do?
I know verifying that the id key is valid is fairly simple.
Here is my biggest issue: How to get Excel to update the database and of course what form would this database take. Maybe its a table on a webpage, or google docs. Again the issue is how would I be able to update the table.
View 1 Replies
View Related
Apr 4, 2014
I want some code on how to fetch data from an Excel Database and display it on respective textbox.
Like I have these coloum name in excel sheet; Emp Id
Name
DOB
Dept
Reporting to
Transport req.
I want to have VBA code to fetch it on txtbx1, txybx2 and so on.
View 1 Replies
View Related
May 23, 2012
Macro to find data from a huge database of items of equipment and find certain ones relevant to an area. I am using Excel 2003.
I have a spreadsheet which has three tabs.
Tab one has a list of equipment with a Ref (Col A), ID (Col B), mile (Col C) and chain (Col D) start and a mile (Col E) and chain (Col F) finish.
There are about 25 different Refs and IDs, but all rows have different Mile and Chains.
The second tab is an identical template of the third tab which is where I want the results to go (see below)
The third tab is raw data which list thousands of items but I want the macro to find the items which are in the first tab using the ref, ID, mile and chain information.
View 3 Replies
View Related
Apr 15, 2014
Using EXcel 2013, Windows 8
I have an Excel worksheet with one column being e-mail addresses. Other columns are Christian names, etc
Ideally can I create a full Mail merge with Outlook using whatever data I want. But probably just e-mail address and Christian name?
Otherwise be able to send one e-mail to all the e-mail addresses, without a major re-type.
View 2 Replies
View Related
