Batch Import External Data

Jan 29, 2007

I have several thousand text files, each with three columns of numbers. My goal is to convert each of these to a dbf file with three distinct columns (which is why simply renaming each with .dbf at the end doesn't work.) I can do this for an individual file by using the "Import External Data" Tool in Excel, selecting the "Fixed Width" option (which divides the figures into three distinct columns), and then saving the file as a dbf. I need to do this for several thousand files, however, so I can't do each by hand.

View 14 Replies


ADVERTISEMENT

Import External Data

Dec 26, 2007

I'm trying to pull in data from another Excel workbook which gets updated on a regular basis. Using the Import External Data fucntion in Excel I created a query which pulls the data from the original workbook into a tab in the workbook I'm using. The issue I'm having is that not all of the column headers get pulled in when using the Import External Data function. I tried typing the column headers in myself but they disappear again after refreshing the data set. Any ideas what is causing the issue?

View 9 Replies View Related

External Data Import

Jul 15, 2008

I am trying to import the data on the url below. When I go to the external data inport the page is loaded and I click the indicator to load the central table but when the data loads the page is severally corrupted. I have found this on a few pages but some pages load correctly.

View 9 Replies View Related

Import Of External Data

Apr 18, 2009

I’ve got a question regarding import of external data in excel. The workbook I am conceiving at the moment does have a database query from another excelfile(a report from my business system) via the standard Excel functionality and it works perfectly fine.
What I can’t figure out is how to re-direct my query to any file/path I choose.

At the moment I just replace my original file with a new freshly updated one but I am stuck with the original path and filename or else it won’t work.

View 9 Replies View Related

Import External Data. Update Link For New Database

Dec 12, 2006

In an Excel sheet i have set up a link to an Access database query (select query) using the Import External Data method. The data is store sales by week, arranged in a pivot table layout (stores as rows, weeks as columns). As time goes by, the database will be updated for new weeks sales, and i would like to refresh the data and the new weeks automatically appear in the Excel sheet, in subsequent columns.

At the moment, the only way i can seem to get this to work is to open up the link in MSQuery and manually select the new weeks from the Access query. i have set the parameters to overwrite cells with new data rather than insert new data. i'm sure there is a way to do this, as it feels like it would be a common thing to want, but i can't seem to find an answer anywhere!

View 5 Replies View Related

External Data In Spreadsheet Without Having To Import All The Data

Aug 6, 2009

I'm wondering if I can use external data in an excel spreadsheet without having to import all the data. I basically need a persistent connection with the external data and I want the excel spreadsheet to get the item descriptions by using the item number as the user types in the item number. Writing out the descriptions is killing me and I have all my products in an excel spreadsheet already. I really need someway to link two separate excel files together. One with data and one with user input that searches that data as I type in the item number.

View 2 Replies View Related

Import External Worksheet

Aug 20, 2008

I want to be able to import an external worksheet (say sheet1) to some data structure such as a 2d array or a dictionary. I don't want to store imported data temporarily in a local worksheet - I want to import external worksheet straight to array!

I have code that can import to a given worksheet name ... but I don't know how I can modify it such that it dumps it to an array or a dictionary.

The code I have is:

Sub read_in_workbook(sheet_name As String, source_dir As String, file_name As String, Optional append_from_y As Integer)
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim stCon As String, stSQL As String
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFolder As Scripting.Folder
Set fsoObj = New Scripting.FileSystemObject
Set fsoFolder = fsoObj.GetFolder(source_dir)

If append_from_y = 0 Then append_from_y = 1
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fsoFolder & "" & file_name & ";" & _
"Extended Properties='Excel 8.0;HDR=No'"
stSQL = "SELECT * From [Sheet1$]"
rst.Open stSQL, stCon, adOpenForwardOnly, adLockReadOnly, adCmdText

Worksheets(sheet_name).Cells(append_from_y, 1).CopyFromRecordset rst

'###Instead of it dumping to a given sheet name, can I turn this routine to a function
'###and have it return a populated array or dictionary object?
End Sub

View 9 Replies View Related

External Web Data Query Fails To Insert Table Data?

Jul 9, 2014

An external data web query points to a web site that offers a foreign exchange rate calculator. In my browser, I selected the specific currency pair I need and used the resulting url in the web query. The "New Web Query" pane resolves the url correctly and allows me to select the table data I want (the little yellow arrow turns to a green check mark). However, when I hit "Import" and select the target cell, the only data returned is the parameters from the url and an error msg (This web query returned no data...). How do I get the query to pass the parameters to the website correctly?

View 3 Replies View Related

External Data From The Web

Jul 8, 2014

I'm trying to make a spreadsheet that references data tables I have pulled from the internet, with the hopes that these tables will update as I refresh the data connections. I'm in the early stages of creating this spreadsheet right now, but whenever I try to refresh my data connections, it takes a tremendous amount of time. I have about 6 connections with 100 row lines total. Is this typical with data connections to the web? Is there a better way to do this?

View 1 Replies View Related

How To Get The External Data

Apr 27, 2013

i have to take the PGN chess game notation file into excel.it is a text file from notepad. it is like this as shown hereunder. how can i get this into columns movewise. ex.1.d4 Nf6 should come in A1 and 2.c4 d6 should come in A2.

1.d4 Nf6 2.c4 d6 3.Nf3 Nbd7 4.Nc3 e5 5.e4 Be7 6.Be2 O-O 7.O-O
c6 8.Qc2 Re8 9.Rd1 Bf8 10.Rb1 a5 11.d5 Nc5 12.Be3 Qc7 13.h3
Bd7 14.Rbc1 g6 15.Nd2 Rab8 16.Nb3 Nxb3 17.Qxb3 c5 18.Kh2 Kh8
19.Qc2 Ng8 20.Bg4 Nh6 21.Bxd7 Qxd7 22.Qd2 Ng8 23.g4 f5 24.f3
Be7 25.Rg1 Rf8 26.Rcf1 Rf7 27.gxf5 gxf5 28.Rg2 f4 29.Bf2 Rf6
30.Ne2 Qxh3+ 31.Kxh3 Rh6+ 32.Kg4 Nf6+ 33.Kf5 Nd7 34.Rg5 Rf8+
35.Kg4 Nf6+ 36.Kf5 Ng8+ 37.Kg4 Nf6+ 38.Kf5 Nxd5+ 39.Kg4 Nf6+

View 5 Replies View Related

Get External Data From Web?

Jan 10, 2013

I am creating a tool that many people will be using. I need a way to do the following from vba:

1. Click on the "From Web" button under Get External Data section in Data and show the web query pop-up

2. Input the url into the field and press enter

At this point, it will be the user's responsibility to log in w/ their username and password. I have tried to record a macro w/ no success. I just need to get the query open for the user without them having to do anything.

View 2 Replies View Related

How To Get External Data From...itself

Dec 3, 2008

I have some data in tabular form on a sheet. I want to represent this data on a different sheet, but have it be dynamically updated. I'm thinking that the smartest way would be to give the original data a named range, then do some sort of external data referencing to the source from Data - Import External Data. But this sounds kind of silly.

How do I do this? And no, I don't want to use pivot tables because I don't have statistics to populate in the cells...I just want to display the data as is.

View 9 Replies View Related

Importing External Data

Feb 10, 2010

way to import the data from this link into an excel so that all the data will be listed in columns. IE: Association name, contact, advisor, etc....

So far I have not found an efficient way to do this through importing the data.

View 11 Replies View Related

Importing External Data To A1

Oct 13, 2009

I have 5 or 6 macros set up to import stats from the internet to specific sheets. I want all of my imported data to be pasted starting at A1. However when i run these macros, the data IS pasted at A1, but when i run it a second time (or the next day after the pages have been updated), it pastes the data BESIDE the previously pasted stuff. here's what i mean..

say my imported data spans columns A to S.. everything's fine. but tomorrow i want to update the data so i run the macro again. well, it pastes the new data to A1 and again it spans from A to S like it's supposed to, but now it also tacks on the same paste job from S to, AG for instance.here's the code... There are currently 5 macros that have this problem. I thought by selecting A1 as the starting point, i could alleviate all of this but apparently not.

View 2 Replies View Related

External Data Queries

Apr 1, 2008

I use external data queries in excel. That's how I look up data in my access databases because I have so many access and SQL databases it's just eiser to be able to do quick queries on multiple databases at the same time in one spreadsheet. And, I can save the external data query as a tool to use in future queries. I have tons of tools and external data query is the foundation of most of them.

What bugs me is EDITING data.

If I want to do some light simple editing in an SQL database I am required to use a linked table in Access or SQL Studio Express Manager. Great programs, but I want to integrate!

Is there no way to make excel a database editor? I can retrieve data from any database or program with excel just fine, but is there a way to use excel to DIRECTLY EDIT the data in an external database with EXCEL like I can in access linked tables?

View 9 Replies View Related

Getting Data From External Csv Source

Jul 18, 2008

I used to get data from external excel, access files and today tried to put together query to get data from CSV file. While using same source file, I have creted several queries to get data out based on criteria. For some strange reason, same structure queries that did run with excel or access as data source, does not want to work the same way with CSV files.

Here is example of non working query SQL:
SELECT Vendors.Vendor, Vendors.Name, Vendors.`Name 2`
FROM `Q:FinanceRapporterPSAPRaw`Vendors.csv Vendors
WHERE (Vendors.Name Like '%' & ? & '%')

If I enter "London" as a criteria input, it will not work. However, if I enter "ondon" it will find me all values records whre name contains London... Seems like I need to skip first symbol when entering criteria. This is, of course, not something I would like to do.

View 9 Replies View Related

Can't Consolidate Data To External Worksheet

Jan 31, 2013

Every month I have to do a report that takes information from four other excel spreadsheets. The spreadsheets are all the same format and contain information on community events. I have to run a report that shows what events are happening in the following month. Currently I copy and past from all the relevant data from each spreadsheet into one. Is there and easy way of making all this information to come together every month.

View 4 Replies View Related

Pivot Tables - External Data

Mar 10, 2014

I have a VBA code that goes to a specific Folder, grabs data from an Excel file, and then puts (data) in another Excel file (template with VBA) file as "Raw Data" tab.

Then my VBA refreshes 30+ Pivots from that "Raw Data" tab.

Now "Raw Data" is stored together with 20+ Pivots in one Excel file.

Challenge: Is there a way to keep my "Raw Data" in the external Excel file and simply refresh my Pivots by only "pointing"/linking that source?

View 3 Replies View Related

Retrieve External Data With The Preferences

Sep 30, 2008

basically all it does is retrieves external data with the preferences that I set... what I want to do is change the code so that instead taking the same URL address it refers back to a specific cell in a workbook for the address.

View 3 Replies View Related

Importing Data From External File

Apr 13, 2009

I have bit of code that opens an external excel file (call it the source workbook), grabs certain data, copies the data to other excel file (call it the destination workbook), and then closes. I have most of the code completed but am running into an issue with how to grab certain data from the source workbook and copying it to destination workbook.

I want the code to read the data in the source workbook line-by-line.

For each line that is read, I want to copy part of that data (value in column A and column B) to column A and column B of destination workbook only if the value in column D is equal to “Block”. For column C and column D, I want to do some form of lookup, being I want the code to look at each line of data until it finds corresponding line in which column A = “60612105” and in which column B = the value in B. Once this line is found, copy the value in C to column C of destination workbook and value in D to column D of destination workbook.

I have attached two files. First file is the import file (destination workbook) which has two tabs; (1) import tab (2) desired result. Second file is the raw data file (source workbook). Hopefully this makes since.

View 8 Replies View Related

Changing The Layout Of External Data

Mar 14, 2007

I would like to know if there is a way to change the format of external data? Instead of the default:

View 9 Replies View Related

Speed Up Refresh Of External Data

Jun 29, 2009

I am having some issues using 'refresh all' to update my excel spreadsheet. Basically, it doens't seem to like doing anything greater than 65 queries before it gives me the error "Too many client tasks".

I've written a macro that updates all spreadsheets seperately, but this takes forever (I have approximately 250 worksheets within this workbook). Is there a way to speed this up? I believe the most time consuming part is establishing the connection to the access database. Is there a way to establish the connection to a database, and keep it open until all data has been updated?

Below is the code I am using:

Sub RefreshAll()
Dim e As Double
Dim sht As String
e = 1
Application.ScreenUpdating = False

Do
Sheets("All ESNs").Select
Cells(e, 1).Select

sht = ActiveCell.Value

Sheets(sht).Select

Selection.QueryTable.Refresh BackgroundQuery:=False

Sheets("All ESNs").Select

e = e + 1

Loop Until IsEmpty(ActiveCell)
Application.ScreenUpdating = True
End Sub
"All ESNs" refers to a worksheet that contains a list of all serial numbers. These serial numbers correspond to the worksheet names.

View 9 Replies View Related

Pulling Data From External Files

Apr 28, 2003

I have 12 external files (one per month) and need a quick way to pull these into a reporting book. I've used the following code...

Workbooks.Open FileName:= _
"J:ManfinMISNew ReportingMIS2P200301.xls"
Columns("A:B").Select
Selection.Copy
Windows("MainReportingBook.xls").Activate
Sheets("P200301").Select
Range("A1").Select
ActiveSheet.Paste
Windows("P200301.xls").Activate
Call OpenClipboard(0&): Call EmptyClipboard: Call CloseClipboard
ActiveWindow.Close

However with 10000++ rows of data in each file, this is very very slow (approx 5 mins to update all 12 months).

View 9 Replies View Related

Clear, External Data Range..

Jan 11, 2007

I found a way to import some data from some csv files, and it works well for me. I don't want to keep the option to refresh the data. I can go into each tab created from each import and and uncheck the box Data>Import External Data>Data Range Properties>"save query definition" that removes it for me. Is there a way to do that with code. It doesn't work with the macro recorder.

Sub ImportOnline()
On Error Resume Next
Dim DayFile As String
DayFile = InputBox("Enter Date of File (MMDDYY)")
Sheets.Add
ActiveSheet.Select
ActiveSheet.Name = "B-" & DayFile
Application.ScreenUpdating = False
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:My DocumentsB-" & DayFile & ".txt", _
Destination:=Range("A1"))
.Name = DayFile
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False................

View 3 Replies View Related

Button To Refresh External Data

Oct 25, 2007

I am trying to set up a button that can be used to refresh data from an external source. The problem is that I'm not sure what to put in the area that I am using the "query" variable

Sub refresh_data(ByVal WS As Worksheet, query As String)
Application.DisplayAlerts = False
Sheets(WS).QueryTable(query).Refresh
Application.DisplayAlerts = True
End Sub

what i end up getting is an error saying basically that I am using incorrect data.

View 3 Replies View Related

Generate Table With Internal And External Data?

May 23, 2014

I have a workbook that need to call for external data (this is information from January till May) from another workbook to manage it. I get this external data as a Tabla_A that needs to be updated continually because the extenal workbooks is adding information constantly. However, now i have to incorporate a specific range of old information (information of December, 2013) before the external data in the same Table_A. This last information wont ever change and needs to be incorporated to analize de information from December till Now.

The problem is when I insert the December information as new arrows at the beginning of Table_A, later I update the table and this old data is remplaced automatic by the external Data range. For that reason, I'm wondering to know if there is some way to have one Table_A with a static range of arrows (december) and another range of arrows (January onwards) that is updatable.

View 1 Replies View Related

External Data Returns Formula But Not Evaluated

Aug 19, 2014

I have a sheet connected to external SQLSERVER data.

One of the columns includes a formula, but the formula is not evaluated but shown as text.

My challenge is making it evaluate (without using excel tricks like replace equal sign with equal sign or clicking each cell and approve it)

I tried going down the road of concatenating char(10) or char(13) to the formula string but no success.

View 2 Replies View Related

External Data - Cleanup Existing Connections

Apr 26, 2013

While making my first foray into defining external connections (to an Oracle database) I ended up with an "Existing Connection" that reports "File could not be opened. It is either damaged or the file format is not valid. Try opening the application that created it and saving to a new file." Followed by "Unable to open or read this connection file. Either the file has been damaged or the file format is not valid."

Ok, fine. So how do I delete it? There seems to be no option for that in the "Existing Connections" dialog. A search for a *.odc or *.udcx file doesn't turn up anything with the name of this connection. A search of the registry doesn't turn up anything.

extdata.jpg

View 1 Replies View Related

Adding Columns To External Data Table?

Sep 11, 2013

I have a External Data Table, but I wanted to add a extra column to it for comments. I just need to be able to write down what I did with that particular order or things like that. I did make a column, but the problem is whenever I update the table the comments column fields stay static I mean they don't move with their row. So it ends up the comment belonging to a row is now on another row.

is there a way I can tell excel to move the cell with the record or something like that.

View 2 Replies View Related

How To Change External Data Path For Mdb Files

Apr 13, 2009

to fix a .xls file that wasn't created by me.

In order to do that I need to change the path of an External Data that is an access 2003 file (.mdb file).

The only way that I imagine it is possible is to select a cell that is an adress for the query result, than click on properties and change it selecting the new path of file (the path moves depending on the user of the .xls).

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved