473,574 Members | 3,218 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Data from Excel to Access, Arrgghhhh ...

Hi all,
I will have to handle a lot of really 'nice' data in a LOT of Excel sheets. It is all about music files (Billboard top 100)
I am afraid there really is a sheet for every year ... (Don't know this for sure yet...)
I definitely need to get this data in Access and get it normalized. (sigh ....)
The data is so 'nice' for the Import- and TransferSpreads heet-wizard that the 'wizard' really chokes on it ;-)

Either the data is not 'nice' indeed, or the wizard is just plain stupid, and assuming all kind of nonsense.
I am afraid it's the latter... AND the first.
Some errors: "To many fields" (256 indeed...), "Datatype conversion failed" or "Subscript out of reach".
Sometimes only: "There has been an error. The spreadsheet could not be added ..",
On some sheets the import partly 'succeeds' (Import of 114 records and an ImportErrors table of 2000 records).

Also a lot of the sheets need to be 'transposed'. (Columns <==> Rows)
I know when I add a new 'first' row with values like "~~" then the columns atleast are treated as text but ....it is a pain ...
Export to csv and import this csv to Access again works better but ...

Help please !! I really would like to automate this.
Anybody been there ?? What would be the best way to deal with this ?

Thanks
Arno R
Nov 13 '05 #1
13 3227

Arno R wrote:
Hi all,
I will have to handle a lot of really 'nice' data in a LOT of Excel sheets. It is all about music files (Billboard top 100) I am afraid there really is a sheet for every year ... (Don't know this for sure yet...) I definitely need to get this data in Access and get it normalized. (sigh ....) The data is so 'nice' for the Import- and TransferSpreads heet-wizard that the 'wizard' really chokes on it ;-)
Either the data is not 'nice' indeed, or the wizard is just plain stupid, and assuming all kind of nonsense. I am afraid it's the latter... AND the first.
Some errors: "To many fields" (256 indeed...), "Datatype conversion failed" or "Subscript out of reach". Sometimes only: "There has been an error. The spreadsheet could not be added ..", On some sheets the import partly 'succeeds' (Import of 114 records and an ImportErrors table of 2000 records).
Also a lot of the sheets need to be 'transposed'. (Columns <==> Rows)
I know when I add a new 'first' row with values like "~~" then the columns atleast are treated as text but ....it is a pain ... Export to csv and import this csv to Access again works better but ....
Help please !! I really would like to automate this.
Anybody been there ?? What would be the best way to deal with this ?

Thanks
Arno R


Nov 13 '05 #2
A wise man from this very newsgroup told me when the going got tough
with A2003 Link Text Wizard and Excel to just copy & paste.

That turned out to be the best approach for me when the data in the
source columns were too mixed for the "Wizard" to handle.

Good luck. Charge by the hour.

Nov 13 '05 #3
rkc
Arno R wrote:
Hi all,
I will have to handle a lot of really 'nice' data in a LOT of Excel sheets. It is all about music files (Billboard top 100)
I am afraid there really is a sheet for every year ... (Don't know this for sure yet...)
I definitely need to get this data in Access and get it normalized. (sigh ....)
The data is so 'nice' for the Import- and TransferSpreads heet-wizard that the 'wizard' really chokes on it ;-)

Either the data is not 'nice' indeed, or the wizard is just plain stupid, and assuming all kind of nonsense.
I am afraid it's the latter... AND the first.
Some errors: "To many fields" (256 indeed...), "Datatype conversion failed" or "Subscript out of reach".
Sometimes only: "There has been an error. The spreadsheet could not be added ..",
On some sheets the import partly 'succeeds' (Import of 114 records and an ImportErrors table of 2000 records).

Also a lot of the sheets need to be 'transposed'. (Columns <==> Rows)
I know when I add a new 'first' row with values like "~~" then the columns atleast are treated as text but ....it is a pain ...
Export to csv and import this csv to Access again works better but ...

Help please !! I really would like to automate this.
Anybody been there ?? What would be the best way to deal with this ?


Any chance of putting one of the troublesome spreedsheets somewhere
that it can be downloaded and looked at?

Nov 13 '05 #4
Any chance of putting one of the troublesome spreedsheets somewhere
that it can be downloaded and looked at?


Hi rkc

Thanks for your offer to take a look !
These sheets don't 'belong' to me.
It is client-data, so ... not on the web somewhere ...

If you can provide an email-address, I will be glad to mail you one of the sheets.
Or you can contact me: ar***********@t iscali.nl (Remove the obvious).

Arno R

Nov 13 '05 #5

<br********@com cast.net> schreef in bericht news:11******** *************@g 14g2000cwa.goog legroups.com...
A wise man from this very newsgroup told me when the going got tough
with A2003 Link Text Wizard and Excel to just copy & paste.

That turned out to be the best approach for me when the data in the
source columns were too mixed for the "Wizard" to handle.

Good luck. Charge by the hour.

Thanks, but since it is all about music:

Randy Newman, 1972, Song: "Memo to my Son", Album: "Sail Away"
"When the going gets tough the tough get going"

Arno R
Nov 13 '05 #6
rkc
Arno R wrote:
Any chance of putting one of the troublesome spreedsheets somewhere
that it can be downloaded and looked at?


Hi rkc

Thanks for your offer to take a look !
These sheets don't 'belong' to me.
It is client-data, so ... not on the web somewhere ...

If you can provide an email-address, I will be glad to mail you one of the sheets.
Or you can contact me: ar***********@t iscali.nl (Remove the obvious).


My email address is in the message header.
Remove yabba.dabba.do. Change bomb to com.
Nov 13 '05 #7
Hi rkc,

I did send a zipped sheet to you some hours ago.
I am not really in a hurry wth this, so take your time.
Please let me know if you received it. I am not sure about your email address...

Thanks and good luck,

Arno R
Nov 13 '05 #8
Bri

Arno R wrote:
Hi all,
I will have to handle a lot of really 'nice' data in a LOT of Excel sheets. It is all about music files (Billboard top 100)
I am afraid there really is a sheet for every year ... (Don't know this for sure yet...)
I definitely need to get this data in Access and get it normalized. (sigh ....)
The data is so 'nice' for the Import- and TransferSpreads heet-wizard that the 'wizard' really chokes on it ;-)

Either the data is not 'nice' indeed, or the wizard is just plain stupid, and assuming all kind of nonsense.
I am afraid it's the latter... AND the first.
Some errors: "To many fields" (256 indeed...), "Datatype conversion failed" or "Subscript out of reach".
Sometimes only: "There has been an error. The spreadsheet could not be added ..",
On some sheets the import partly 'succeeds' (Import of 114 records and an ImportErrors table of 2000 records).

Also a lot of the sheets need to be 'transposed'. (Columns <==> Rows)
I know when I add a new 'first' row with values like "~~" then the columns atleast are treated as text but ....it is a pain ...
Export to csv and import this csv to Access again works better but ...

Help please !! I really would like to automate this.
Anybody been there ?? What would be the best way to deal with this ?

Thanks
Arno R


Arno,

Excel is one of the more painful formats to Import from! And Linking is
worse! I find I get the best results when I import into an existing
table that already has the data types set to what they should be. This
way you don't get Excel telling you that a field is numeric when it is not.

CSV works better because you can override the 'detected' data type and
use other characters to deliminate text if the text contains quotes (I
use ~ as a delimiter most often because it rarely occures in data, but
you can try other characters as well, just pick one that is never in the
data).

HTH
--
Bri

Nov 13 '05 #9
rkc
Arno R wrote:
Hi rkc,

I did send a zipped sheet to you some hours ago.
I am not really in a hurry wth this, so take your time.
Please let me know if you received it. I am not sure about your email address...


It arrived O.K. Just got home from making a living so I haven't
looked at it yet.

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
9233
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works only exporting to single worksheet. but i need to export data to multiple worksheets. it is very urgent to us. so please help me in code.
1
17394
by: Steven Stewart | last post by:
I have a user who has been using Excel for a while to keep statistics and print reports. She finds using it cumbersome because of long formulas and a lot of copying and pasting. I have designed a database for her which is intended to make things a lot easier; however, I don't have a lot of experience with Access and I find that designing...
3
25034
by: Mads Petersen | last post by:
I'm stuck in this code. Hope you can and will help me. I launch it from excel. I have made the following code work, but not as i whant. I need the ranges to be working with something like xlDown. eg. this only transferes the first record in the area. ..Fields("Uge").Value = ws.Range("A98").Value Sub SelectMaster()
0
2829
by: Jack | last post by:
Windows 2K Pro Access/Excel 2003 Hi there, I have a large number of password protected Excel Workbooks. The files are protected by code that runs when the workbook opens. The code asks the user to key in a password into an input box, the password is compared to a a partciular cell on a hidden (xlVeryHidden) sheet and if it matches the...
11
11253
by: Hi5 | last post by:
Hi, I am new to access I usedto work in Oracle and Mysql. I am after a way that enables me to populate a database I designed in access with lots of data which can be sorted in excel sheets, The source of all data is a very large size (100*2000) excel spread sheet In which some data types are absulotely Irelevant to access data types in...
0
14409
by: Grip | last post by:
Hi, I have gone throught the group and Microsoft's online help and have seen many suggestions but I am still seeking clarity: 1. I have an excel spreadsheet. Column A contains text that may be greater than 255 characters. 2. I have an access database. I link (not import) to the contents of the excel spreadsheet. In the design view in...
7
5754
by: semijoyful | last post by:
OS: Win XP SP2 Access version: 2003 Excel version: 2003 I am new at this, as I am sure you have gathered from this post title:) I am working on a form where users can input data in Access and at the end of the day be able to press a button on the form that would update that data to a single exel spreadsheet. The following is the code for...
3
7136
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this file but save it as an excel file. The data in this excel file will be imported into an Access database. The
7
13158
by: kpresidente | last post by:
Hello all, I'm trying to transfer the value of a control on an Access form to an Excel worksheet using VBA. The Access form is a single form with all the controls disabled, so that data is "read only." I have a button on the form which opens the Excel worksheet, which then basically serves as the input form for the Access database. What I...
0
7760
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8101
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7858
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...
0
8137
tracyyun
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...
0
6511
agi2029
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...
1
5654
isladogs
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...
0
5335
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...
1
2273
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
0
1099
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.