Data from Excel to Access, Arrgghhhh ...

[Arno R]

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 TransferSpreadsheet-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

[brucedodds]

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 TransferSpreadsheet-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

[brucedodds]

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.

[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 TransferSpreadsheet-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?

[Arno R]

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***********@tiscali.nl (Remove the obvious).

Arno R

[Arno R]

<br********@comcast.net> schreef in bericht news:11*********************@g14g2000cwa.googlegro ups.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

[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***********@tiscali.nl (Remove the obvious).

My email address is in the message header.
Remove yabba.dabba.do. Change bomb to com.

[Arno R]

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

[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 TransferSpreadsheet-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

[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.

[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...

O.K. I took a look.

That's an interesting layout you have there. You don't really have
a spreadsheet with multiple single records, you have a spreadsheet with
multiple tables with multiple records. I looks to me like every entry in
the first column would be a table in a normalized database. Each column
after that contains zero or many pieces of data for each table. There
are also what would amount to duplicate entries for some tables.

How are you at automating Excel?

[Arno R]

Hi rkc,

(I translated the first column in the first sheet so you could understand the data)

That's an interesting layout you have there. You don't really have
a spreadsheet with multiple single records, you have a spreadsheet with
multiple tables with multiple records. I looks to me like every entry in
the first column would be a table in a normalized database. Each column
after that contains zero or many pieces of data for each table. There
are also what would amount to duplicate entries for some tables.
That's the problem indeed. I have to get this data in Access and get it normalized.
My first problem is the import in, export to Access.
If I have the data in Access, I can 'massage' it to my needs.
How are you at automating Excel?

I did some automation with Excel but ... I am definitely not a 'star' with that.
But also: I am not going to do this with 'cut and paste'... ;-)
I was thinking of creating a sub that imports certain data from a specified sheet; something like this pseudocode:
Sub ImportFromXLS(spreadsheet, sheet, range, optional transpose as boolean)
Open Excel with spreadsheet,
Copy specified range from specified sheet and paste to a new sheet (range transposed if indicated)
Import new sheet in Access
Close spreadsheet (without saving)
Close Excel
End sub

The specified range would be one or (in most cases) more separated selections.
Like: Range("1:2,13:16") ==>> without transpose I could have more than 255 fields in this case ==>> error
or Range("B1:IV2,B17:IV23") ==> I would have max 255 fields

Other ideas ?
In any case I could definitely need some help with this !

Arno R

[rkc]

Arno R wrote:

Other ideas ?
In any case I could definitely need some help with this !

Here's what the B column looks like with the names from column A
in capital letters seperating the groups of data. This was printed
to the immediate window in Access via automation. Tell me what you
want an export file to look like and I'll whip something up for
you over the weekend. Just for the fun of it.

Notice that some data groups have an entry that isn't really data, but
data about data. It's not consistent throughout the file though.

Example: TOPHITS has this enrty -> 14 t/m top 10
That's obviously not a song title.
<paste from immediate window>

TITLE RECORD
Rhyme tyme people

GROUP/ARTIST
Kool & The Gang

IN OTHER BILLBOARD LISTS
Soul (no 3) '74 -'75

USED IN

SCORE IN OTHER COUNTRY'S

OTHER HITS SAME YEAR
Spirit Of The Boogie/Summer Madness
Caribean Festival

HITS FOR OTHER GROUPS/ARTISTS

COVERS OTHER GROUPS/ARTISTS

AWARDS

TOPHITS
14 t/m top 10
1. Celebration (80-81, US no 1, UK top 10) NL no 2
2. Joanna (83-84, US-UK no 2) NL tip
3. Cherish (85, US no 2, UK top 5) NL top 5
4. Get Down On It (82, UK no 3, US top 10) NL top 10
5. Jungle Boogie (73-74, US top 5)
6. Too Hot (80, US top 5, UK top 30)
7. Hollywood Swinging (74, US top 10)
8. Ooh La La (Let's Go Dancing)(82-83, UK top 10, US top 30)
9. (When You Say You Love Somebody) In The Heart (84, UK top 10) NL tip
10. Ladies Night (79-80, US top 10, UK top 10) NL top 20
11. Fresh (85, US top 10, UK top 20) NL top 40
12. Misled (84-85, US top 10, UK top 30)
13. Victory (86-87, US top 10, UK top 30) NL top 30
14. Stone Love (87, US top 10, UK top 50) NL top 30

LP
Light of Worlds

AWARDS
RIAA; Gold (18-aug-75)

SONGS HITS
Street Corner Symphony
Fruitman
2. Rhyme Tyme People (75, top 70)
Light of Worlds
Whiting H. & G.
You Don't Have to Change
1. Higher Plane (74, top 40)
3. Summer Madness (b) (75, top 40)
Here After

</paste from immediate window>

[Arno R]

Hi rkc,

Here's what the B column looks like with the names from column A
in capital letters seperating the groups of data. This was printed
to the immediate window in Access via automation. Tell me what you
want an export file to look like and I'll whip something up for
you over the weekend. Just for the fun of it.

Looks good !
I just mailed you; this discussion is better continued off-line don't you think?

Thanks for your time,
Arno R