exporting to txt file, with text data from lookup and to keep under 320 bytes

[ozlander]

G'day,
While I am getting my tables and relationships sorted out, I thought that I'd investigate exporting the data as a text file (a tad presumptuous, I know ). However, that is my aim ... what I have discovered, besides the size of the export data, is that like excel, the lookup table data via a query as export data keeps the data in numeric values when they should be text ... however, in report export text data, the data retains the text from the lookup tables ... but is a larger export text file
What I want to know is if it is at all possible after using lookup tables, can one successfully export the data, keep the text values from the lookup table .... and keep the file small ... I know that is a lot to ask ...
However, if it is at all possible, pls let me know... ty
Regards
Oz

 

[The_Doc_Man]

The way to keep the text values from a lookup is to build a query that holds the text rather than the foreign key that led to the text. Which means the query is a JOIN with the other table in question. (Tell me you don't have more than one lookup in the same record....)

For this query, you can use the FORMAT function or the LEFT function to control the format of your columns. Then export the query, not the table.

 

[ozlander]

G'day, thank you for your reply

I was using a query, however, it was not based on the tables that supplied the lookup information. Now, that I have built the query to include the lookup tables (oh ... and there are 7 ... sorry), the exported text file looks fine with the exception of a yes/no field that I probably should change to another lookup.

Thank you

Regards
Oz

PS Would it be suitable to use a macro (which could be converted hopefully to code) to export the text file?
Oz

 

[ozlander]

G'day, Pat

Thank you for your reply. I don't know how to write VBA yet, however, I located this code http://www.access-programmers.co.uk...&highlight=export+database+objects+text+files
What part of it would I need to save a query as a text file ... and where would I put it? I can see the sExportLocation="c:\Temp\" [which I assume to be the location of the saved text file] and Application.SaveAsText acQuery, db.QueryDefs(i).Name,sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt" [does the Query_ indicate the name of my query and what do the others mean?]

or should I use this one ... which appears to be delimiting
DoCmd.TransferText acExportDelim, , MyTbl, "C:\whatever\" & ExportName & ".txt" [except MyTbl becomes My query???]

or is there an alternative (I would prefer not to use a macro)? Anyway will do as long as I understand what I am doing and where the code should be placed

Thank you

Regards
Oz

 

[ozlander]

PS ... how do I get rid of the time in the date field ... I have no reason why it is there in a text file?
Regards
Oz

 

[ozlander]

Hi

Thank you for for reply, however, I don't understand completely. I attempted to put the datevalue or date format under criteria in the query... but it resulted in either a data mismatch or syntax missing from the expression. Then I tried searching on the forum for the correct phrasing without success (or may be without understanding fully). I am missing the point here I think esp where time is concerned. What I would like to know is where the code for the query goes and why ... because at the moment I don't understand

Secondly, with reference to the DoCmd code for export text where should I place it ... and yet again I appear to be lost

Thank you for your time

Regards
Oz

 

[ozlander]

Thank you for your detailed reply

I didn't realise the function went with the field (would I have been able to set the field in the table in a similar manner? Just an aside curiosity ... ). Anyway, eventually, I have the query working. The DateValue() did not work, however, the Format() did. Does that have something to do with regional setting versus US default settings? The square brackets were put in the function by access so I didn't have to change a thing. I am using Access, 2003... and thank you

Ok, now for the query as text export, which I have to admit I can do via file >> Export >> as a txt file (but I am digressing, sorry). I understand where the code should be placed (but I soon realised that I had no idea what error handling was about ... so I read http://www.access-programmers.co.uk/forums/showthread.php?t=72201&highlight=error+trapping+code and other pages).

Next, I attempted the DoCmd.TransferText acExportDelim, , MyTbl, "C:\whatever\" & ExportName & ".txt" [except that my thinking was that MyTbl should become My query??? and the path to my folder ... and even the next bit as my text file] ... anyway, this was done without success because some table attribute missing, which I assumed to be some type of order bit re query, table, form, report (I know, one should never assume ... but that's in my nature, sorry).

Then I attempted sExportLocation="c:\Temp\" [which I assume to be the location of the saved text file] and Application.SaveAsText acQuery, db.QueryDefs(i).Name,sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt" (which I did not understand fully, unfortunately)

Next, out of complete desperation, I tried a macro (sorry ... but I thought, I'd try one anyway ... and access 2003 seems to support error handling in a fashion) ... and I even converted it to a module ... however, again the button was unsuccessful. I have even run the macro by itself without success ... so in conclusion, I am lost, totally and would appreciate your advice.

Thank you

Regards
Oz

PS reading without practising is not a good practice ... which I have discovered to my dismay

 

[ozlander]

Again, thank you, Pat for your comprehensive reply

I was unable to run the macro as I was could not choose export (possibly, I have a bad install of 2003 due to the fact that I ungraded it, running it over the top of 2000 rather than a fresh install ... I'll investigate that later). Anyway, I was able to make an import macro and convert it to a module, whereupon I changed the import to export.

Then I copied the code into the button event and exported the data as text. Not content with the size of the file, I changed the 'true' to 'false' and reduced the newly saved text a further 200+ bytes

Many thanks, Pat for walking me through this procedure. I really have appreciated it. At this time, I feel that I have a better grasp of where things should be placed (for the time being) so that my reading in the forums will/should be more rewarding

Thank you

Regards
Oz