Plz Help - Import Data formatting changed (1 Viewer)

[AC5FF]

I hope that someone out here can help me with this. I have recently had a format change from how I received updated data for my database. I used to update my tables from TXT files. There were several update queries/macros written to import the data from 5 or 6 different TXT files into one table. Well; my source has changed The downside: I can only get my data from an XLS or CSV file now; but the upside is all the data is only in 1 file.

The macros/queries were written years ago and I've been having migranes trying to figure out how this was all done. So, i've sort of started from scratch. I can easily import the data into a table; I have even gotten our front end of the database working so it's just a click of the mouse again.

The problem I have is that the data that is in this table is linked everywhere throughout this spiderweb of a database! I was unable to import my data into the existing table because that table's field names are different than what I have in the XLS file. I get all sorts of errors when I do this. So I am currently importing into a new table. (I.E. MASS and MASS Imports)

Is there an easy way to update data from table 'MASS Imports' to 'MASS' ?? what I was thinking was to use an update query with both tables and link each line.... Or am I way off base? Is there an easier way to do this?

Thank You!!

 

[Paul Buttress]

Can you not use an import specification to mirror the table that it used to import into? I can help you with this but I will need more information. Could you attach a cut down version of your database with sample import file?

 

[AC5FF]

Paul;

Thanks for the help offer. I don't know if I can cut down a version of this DB; this thing has probably 50+ tables and a TON of other data. I'll see what I can do.

In the meantime I've attached a picture. This was just an idea i was trying; sorta trying to follow another update query that updates the product date with the current date/time information. The two tables in this query picture are the old MASS and the new MASS1. The XLS file I know I cannot send out due to information it contains... However the field names in the MASS1 table are the same as row1 in the xls file; there are anywhere from 10-100 lines of data after this row.

The 2nd picture is the macro's. The foreground window is the old macro; the one in the background is the new one. Don't know if this helps any, but I figured I would give it a shot. Sorry for the BMP files I don't have access to a good graphic editor here at work to save them as JPGs

Edit: Files Attached as JPG; had to really shrink them though; hope they are still viewable. I also brought a copy of the DB home. It's 68Mg right now, compacted I can get it to low 30's. I also deleeted most of the data out of the tables to sort of 'strip it down' and it made the DB crash hard. I can't even get it to open with this setup... Sorry. Please just let me know if I can answer any questions you might have.

Thx Tons!

 

[AC5FF]

Can you not use an import specification to mirror the table that it used to import into?

Paul:
There is an import specification on the original setup; it's called "MASS Import Specification"... I have read through the help files online and can't figure out how to edit this, create one, etc etc... If this would be an easy way to get it done I'm all for it! I just can't find out where this import specification is located!

AC

 

[Paul Buttress]

Hi AC

Basically import and export specifications are hidden, they are really a bastard to get to but once you know how. This is (honestly) the only way to get to one.

Make sure you have a .asc or .txt file handy - you won't actually import it - we just need one to get the the specs.

1. Choose File - Get External Data - Import
2. Remembering to set you file selection to Text Files, select your .txt or .asc file and click on Import
3. The import text wizard will start
4. Click on Advanced... and behold the spec screen

Choose edit and save specs here - all specs will be here both import and export.

You can use specs in commands to import and export text such as DoCmdTransferText.

Hope you can get the to bottom of yours, if not please let me have a bit more info and I'll take a look for you.

Paul

 

[AC5FF]

Paul;
Thanks! Found that information... however, I am not sure just how helpful that is in my situation here. Or I'm not getting my brain to wrap around what "Import Specifications" do for me...

If you look at that query.jpg file I attached; the table called "MASS" has field names that are linked all over in my DB. The table "MASS1" is how the new XLS file gets imported. If I changed the field names in "MASS1" I get errors when importing new data (Field Name XXXX does not exist). The Import Specification "MASS Import Specification" appears to just show the list of fields, widths, etc... I tried to change a few lines; NSN for Stock Number for example; but that didn't work for me either.

You know, this is probably something REALLLY simple and I just need that magic light bulb to go off in my head to figure it out... But I just haven't found the right switch to turn on yet

You asked for more information, but I don't know what else I can send/provide to help..

 

[Paul Buttress]

I can't read the query or macro jpegs as they are too small and when I zoom them they are out of focus.

If the data is not sensitive, could you zip up the xls file and (separately) a stripped down database to p.buttress@lineone.net and I'll try to get to the bottom of this for you.

I know that you tried to strip down the database before and it crashed but if you could copy the database and remove all other tables that are not relevant. This shouldn't be too big when zipped.

There is also a web site that can be used to send larger files too, its called www.sendthisfile.com - you could try that, it's free to use.

Keep going and I'm sure we'll eventually get to the end of this.

 

[AC5FF]

Thx Paul; email sent

 

[AC5FF]

Paul;

Any chance you can attach that example here? I got it figured out at home last night and made a few notes.. I figured that would be easier for me since this seemed simple enough... However it appears I can't get an email from work to home or from home to work... Even just a regular email. Wierd; going to have to call my IT people on this one!!

Anyhow; if you can attach that here I think I have gotten everything else set up right to give this a try...

Thx!

 

[Paul Buttress]

Zip file attached - good luck!

 

[AC5FF]

Wellllll
Luck is definatly not on my side today!! LoL
I played around with your sample and it worked perfectly. I made it so all the data would transfer, or any row I wanted to transferred. Worked like a charm.

I didn't have a unique ID in either of my tables, so I added that to both. I tried with just one column to transfer and it didn't work... tried multilple columns and that didn't work either. I put both table designs side by side and ensured that all the fields were set up identicle (format, width, etc...) but that didn't help my case either...

I keep kicking myself in the backside because this change SHOULDN"T be this difficult! I know I am probably missing something VERY simple. As mentioned before.. I just need to find that lightbulb switch LOL!

I went so far as to pull up the dependancies on this MASS table and change all the dependancies (40+). I would get about 1/2 way through and Access would crash. WIERD.. not sure why that happened, but when it did it the 2nd time I gave up this approach..

 

[AC5FF]

Update:

It works... I wrote a whole new query vs copying the sample query into my DB and modified it. I also wrote an "Append Query" vs an "Update Query". With the append I didn't need to have a uniqueID in both tables to link them. (Who knows, I may not of needed it in the "Update Query" either if I had done it right!)

At any rate.. things are working.. Just a little more modification to my macro and i'll be back in business.

Thanks TONS Paul! Ya definatly put me on the right track to getting this problem solved!!!

 

[Paul Buttress]

Hi Jerry

Pleased to be able to assist. Should you need any more help - you have my contact details.

TTFN.............Paul