(Excel 2003, Access 2003, XP, novice user here)
I can't get Access to accept multiple Excel files to the same table. I can import the spreadsheets to a new table, but I need to import 23 spreadsheets to the same table. I'm not getting a formatting error, just a "file did not import" message.
Also, when I do import one-to-one, Access inserts thousands of blank lines before the first line of imported data. How do I keep Access from doing this?
Perhaps I should be asking how to import from Crystal reports...that would eliminate the entire Excel issue if I could do that.
3  3614 
After doing some research, I found that Access is a bit funny when importing from Excel. Losing and not importing data etc. What I decided to do was to import , delimited .txt files. Much safer.
Save your .xls as .csv, then make .txt, and stick it in!
After doing some research, I found that Access is a bit funny when importing from Excel. Losing and not importing data etc. What I decided to do was to import , delimited .txt files. Much safer.
Save your .xls as .csv, then make .txt, and stick it in!
Thank you. I tried your solution, but unfortunately we have so much garbage in the data source that importing a .txt file yields quotation marks everywhere. A simple find/replace won't get rid of them b/c quotation marks are used as abbreviations for foot/inch measurements in the description column.
How did you old timers manage to keep your sanity all this time?
jimatqsi 1,277
Recognized Expert Top Contributor
What you need to do is save the Excel worksheet as a tab delimited file, not a quote-comma delimited file. (That assumes that you have not also used tabs within your data.)
In Excel, go to File/Save As, and look over the list of file types you can save under. You'll see "Tab delimited" in there ... then when you are importing into Access, it will probably automatically recognize that you are using tab delimiters, but if it doesn't, just change the radio button to that choice.
Good luck, home I'm not too late with this advice.
Jim
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: DCM Fan |
last post by:
Access 2K, SP3 on Windows 2K, SP4
All,
I have an import spec set up with quoted Identifiers and comma-separated
values. The text file is produced by a 3rd-party program of which I have no
control. It outputs all text fields surrounded by quotes, and all numeric
fields w/o quotes. All fields are separated with commas.
This has been working for 2 years, until today, when one of the data fields
|
by: Johnny Meredith |
last post by:
I have seven huge fixed width text file that I need to import to Access.
They contain headers, subtotals, etc. that are not needed. There is also
some corrupt data that we know about and can correct once the data is in
Access (or during the import process itself). Furthermore, the text files
are poorly set up, such that some records may be "shifted" over a few
characters, and therefore the fixed width nature of the file is corrupted.
...
|
by: Steve Jorgensen |
last post by:
I'm restarting this thread with a different focus.
The project I'm working on now id coming along and will be made to work, and
it's too late to start over with a new strategy. Still, I'm not coming to a
firm conclusion over whether it was the better approach, and wonder if I
should do it differently the next time I'm faced with a similar issue.
I needed an app to automatically import from spreadsheets with a semi-dynamic
structure,...
|
by: daniroy |
last post by:
Hello everybody, your help will be very very welcome on a very classic
question...
I have some Excell Macro background, a little bit of VBA knowledge.
I import daily from Internet series of data. They are setup as follow,
with a few more columns on the rights as stock prices, dividend and so
on as:
Date Code Country Adjust Reason Name Shares
25/02/2005 FR0000045072 FR CREDIT AGRICOLE
|
by: ninrulz |
last post by:
I will try to explain my situation. I know that it is hard to offers
solutions without fully understanding what people would like to
achieve.
I receive 2 csv files every month. The csv files change name each
month. Each file contains 13 columns and around 20k rows. In each file,
the first row is the heading.
I have created a database that helps me sort out the data. Prior to
importing into the database, I have been copying the csv...
| | |
by: baling |
last post by:
Hi....
Hi everybody, i have a code that i make in VBA and know I want to use this code in to VB6. But i don't know how to use that code in to VB 6.0
Please correct this code so i can use it in VB 6.0.
Code:
--- use in mainform -------
Option Compare Database
Option Explicit
|
by: Hendri Adriaens |
last post by:
Hi,
I'm trying to automate the creation of an excel file via COM. I copied my
code below. I read many articles about how to release the COM objects that I
create. The code below runs just fine and excel is closed. But there are
some commented lines:
//xlSeries.XValues = xlWs.get_Range("B2", "B4"); // makes com objects, but
which...
|
by: anon3803 |
last post by:
I have a report in Access that has a lot of grouping (sometimes over 5 group headers). When I export it to Excel where more people can use it and are familiar it creates a lot of blank rows when there's nothing inside the group. Certain items in the report will have items in all 5 group headers/levels, most will not. I have a macro in Excel that deletes the blank rows but some reports are too long to export to Excel because of the blank rows....
|
by: DThreadgill |
last post by:
I have a spreadsheet with about 35 tabs that I need to import into one table in Access. I found this link http://www.thescripts.com/forum/thread650662-import+multiple+tabs.html and changed some things within the code.
First off - I've written a piece of code that changes the name of each sheet (Sheet1, Sheet2, etc), deletes the blank rows and formats a couple of the columns to text and date format. It then saves and quits the application...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
