Excel -n- Access, Failure to Communicate! (1 Viewer)

[CanvasShoes]

As in..."wut we got Heah...is Failuh to communiKATE" (but as always, it could be operator error and I'm just not advanced enough to know the obvious-to-the-experts fix on this)

Mods, if this question is in the wrong forum please don't hesitate to move it. I just wasn't sure what category my question/problem fell under.

I'll try not to make this super long. So, I've got a vendor produced database that's got some good tools and is fairly complex. I know once the learning curve is over, I'm going to love it. However, I've got another vendor providing data in an excel format. Problem is...they don't have the column headings (or names) always in the correct space or exactly the same name.

There are around 40 or 50 columns. In order for the Access database to be able to import and work its magic on the data provided by the excel import table, they all have to be in the correct place. Column A in excel has to be called WidgetStable1, Column B WidgetSamples and so on, but often some of them are correct, but WidgetPercentages is way the hell and gone over in BZ somewhere instead of D and is called WidgetVolumes or some damned thing (so not only do I get to manually move them all, I get to doublecheck the work THEY were 'sposed to do and make sure that I've even got the correct values. Is it WidgetVolumes REALLY their version of WidgetPercentages? Or is it WidgetUnits, (yet another required but misplaced and misnamed field)? GAAAAH

So yeah, the vendor providing the widget sample analyses doesn't put them all in the proper order or name them properly. (and Yes, they're SUPPOSED to, they have an old form or something, if I were the PM I'd make them redo it, but budget restraints, blah blah blah...here we are and I'm merely the end user etc).

Now yeah, I can (and have) spend a good bit of time manually moving the columns (with several thousand rows of data, so VERY carefully) around to the right spots. But WHAT a huge pain. And building a query (which I'm merely a baby user at anyway) seems like a really huge undertaking with that many columns, just a sample trial obviously was going to take as much, if not more, time as manually reworking the columns in the spreadsheet.

Oh help me Obi Wan....any ideas?

 

[RuralGuy]

If it helps, you do not really need to move the columns into a specific order. Just make sure the ColumnNames are the same and Access can find the field once it is imported.

 

[ghudson]

As RuralGuy mentions, order is not so import but they must be consistant with the naming of the columns in the file they send you or else your import process will always fail if one column header is off by one character or space.

 

[DCrake]

What you have not indicated is is the txt file delimited or fixed length.
Is is only the column headings that are incorrectly defined or is the data incorrect for the specified field?

 

[CanvasShoes]

What you have not indicated is is the txt file delimited or fixed length.
Is is only the column headings that are incorrectly defined or is the data incorrect for the specified field?

No, the data itself is, for the most part, correct, though I have to come in and do a few fixes once the lab is done. No, it's the headings. If we'd built these dbases ourselves we could tailor these to our own wants/needs. Unfortunately these are client required dbases and our ability to modify them (as it being allowed, not in knowing how) is severely limited.

Some of the fields are alpha-numeric with a set of codes being the only thing allowed, those codes consisting of 2-4 characters. Usually just one field, the "comments" field, allows 25 characters or a few more. Some are numeric only, and in specific formats date, coordinates, that sort of thing. I know it seems really weird, but the way they built this database, the order in which the import table columns are set up DOES Matter. If one is out of place the table run sequence won't even attempt to run the correct tools, it will just come back with an error message to the effect of "WidgetHome not found".

Grrr....And no, their own techs aren't much help.

Thanks again in advance. Sorry for all of the widget this and secret squirrel that, this is somewhat of an industry specific proprietary Dbase tool.

Thanks!

 

[DCrake]

There is a solution for this. Firstly import the text file into a temp dump file. Does not matter what ordr the fields are in. Then use a translator code to write a select query sql statement to create a query which aliases the wrong field names with the correct field names, and secondly it places the fields in the correct sequence.

Finally use this new query to act as the import source. If you proprietory import will only recognise the importing of a text file you can simply export the query to a txt file and use this as your new source.

If you need any help on this I would be happy to help.