How to export/import data table without knowing 'export specification' ? (1 Viewer)

[amorosik]

I'm using a procedure created with Access 2003 Currently the environment I'm using is Access 2013
One line of code that worked fine using Access 2033 was:

DoCmd.TransferText acExportHTML, , table_name, file_name, True

With the most recent development environment, an 'Export Specification' which describes the sequence of data to be extracted seems to be indispensable
This 'Export Specification' requires manual creation
But with a large number of tables this operation is impossible to do manually
The question is: how to use DoCmd.TransferText (or other command) to export the entire data content of a table, and use the produced file for a subsequent import without knowing the 'Export/Import Specification' ?

 

[CJ_London]

Not clear to me what the problem actually is

you refer to 2003, 2013 and the could be either 2033.

Not sure why you need an export specification - just create a query with the columns in the required order and export that

 

[gemma-the-husky]

I'm using a procedure created with Access 2003 Currently the environment I'm using is Access 2013
One line of code that worked fine using Access 2033 was:

DoCmd.TransferText acExportHTML, , table_name, file_name, True

With the most recent development environment, an 'Export Specification' which describes the sequence of data to be extracted seems to be indispensable
This 'Export Specification' requires manual creation
But with a large number of tables this operation is impossible to do manually
The question is: how to use DoCmd.TransferText (or other command) to export the entire data content of a table, and use the produced file for a subsequent import without knowing the 'Export/Import Specification' ?

You don't really need one for an outbound extract.

You might need one for an inbound import, even if it's the same file you just exported You need a way to deal with the file contents reliably each time. Access left to it's own devices can get the column types wrong. What if some fool inserted a new column or changed the column order?

You also may need a way to ensure that the file has not changed unexpectedly, maybe by verifying that all the column headings are what you expect, which is a different thing. You only need to do these things once. ( I mean design the process once) It's not like you import every table, but you may import and process hundreds or thousands of order files with a particular layout. They may not have column headers, and even if they do, the column headers may not be legal in access.

 

[ebs17]

or other command

Copy the backend when all other access has been removed. Then you have everything in one piece (data, indices, relationships) and easily reusable.

TransferText makes internal (Jet)SQL access to the text file. SQL requires defined data types for fields, in a text file it's just plain text. The specification (alternatively a Schema.ini) serves as a default for reading the table in the text file. Country-specific formats also play a major role. If no specification is used, default values from the registry are used. It is questionable whether these alone are sufficient for the individual requirements. You are more at the mercy of chance.

Other methods used would have to achieve a comparable result.

Last but not least: I have never encountered HTML as an exchange format. What special reasons make you want to bet on it. HTML is also just text, but enriched with tags for formatted ads.

 

[amorosik]

Not clear to me what the problem actually is
you refer to 2003, 2013 and the could be either 2033.
Not sure why you need an export specification - just create a query with the columns in the required order and export that

The question is: how to use the DoCmd.TransferText (or other command) to export the data contained in a table WITHOUT defining an 'Export Specification'
I indicated the fact that the procedure has been created and works with Access 2003 only to justify that once it could be done, now it is ESSENTIAL to have the 'Export Specification'

 

[amorosik]

You don't really need one for an outbound extract.

You might need one for an inbound import, even if it's the same file you just exported You need a way to deal with the file contents reliably each time. Access left to it's own devices can get the column types wrong. What if some fool inserted a new column or changed the column order?

You also may need a way to ensure that the file has not changed unexpectedly, maybe by verifying that all the column headings are what you expect, which is a different thing. You only need to do these things once. ( I mean design the process once) It's not like you import every table, but you may import and process hundreds or thousands of order files with a particular layout. They may not have column headers, and even if they do, the column headers may not be legal in access.

Do you know of a way to use the DoCmd.TransferText without specifying an 'Export Specification'?
I'm asking this because I'm trying, using Access 2013, and I can't find that this line

DoCmd.TransferText acExportHTML, , table_name, file_name, True

can be executed

 

[ebs17]

now it is ESSENTIAL

This wording is wrong. Nothing has changed at least since Acc2000. Problems could be derived from your own individual example.

 

[amorosik]

Copy the backend when all other access has been removed. Then you have everything in one piece (data, indices, relationships) and easily reusable.

TransferText makes internal (Jet)SQL access to the text file. SQL requires defined data types for fields, in a text file it's just plain text. The specification (alternatively a Schema.ini) serves as a default for reading the table in the text file. Country-specific formats also play a major role. If no specification is used, default values from the registry are used. It is questionable whether these alone are sufficient for the individual requirements. You are more at the mercy of chance.

Other methods used would have to achieve a comparable result.

Last but not least: I have never encountered HTML as an exchange format. What special reasons make you want to bet on it. HTML is also just text, but enriched with tags for formatted ads.

Copy the backend? What do you mean?
As a backend we are not talking about an Access file, it is a classic db server
Copying the entire db is not what I want to do, the size is several Gbytes, it would be a useless copy
What I want to do is to be able to save all the data contained in a certain moment in a table in a text file, in a format that then allows me to restore
Ok acExportDelim can be used instead acExportHTML, but is always impossible extract data without 'Export Specification'

This wording is wrong. Nothing has changed at least since Acc2000. Problems could be derived from your own individual example.

Something has DEFINITELY changed because the attached example shows two files one for Access 2000 the other for Access 2013 and the only one that works is the first

 

[ebs17]

a large number of tables

... gave me the conclusion you meant practically everyone. So what is this supposed to mean?

allows me to restore

A more modern DB server has its own backup strategies. Deal with them.

Something has DEFINITELY changed

You can see that you use different fields in one table. Is Access to blame?

 

[amorosik]

... gave me the conclusion you meant practically everyone. So what is this supposed to mean?

A more modern DB server has its own backup strategies. Deal with them.


You can see that you use different fields in one table. Is Access to blame?

It means that if you have 1 or 2 tables to export you can also allow you to set the 'export specification' by hand, if you have 200 tables then this is not practicable

Sure, like all modern db's
But the question concerns the possibility to export the data contained in a table using Access code

There is no blame
I have created two examples, with the same lines of code for the export
The example for Access 2000 works fine (with Access 2000)
The example for Access 2013 doesn't work (with Access 2013)
The same code
If nothing had changed, as you wrote above, the code would also work on the second example

 

[gemma-the-husky]

when you transfertext, I normally specify acExportDelim, and I save as filename.csv
when you transferspreadsheet, there's no spec and I save as filename.xls

the following imports the previous spreadsheet export to a new table
the syntax with transfer text would be slightly different, but I don't think you have to use a filespec (not tested)

maybe your issue is the acexportHTML?
Add some error handling and see what message you get.

Sub importdata()
Dim p As String

p = CurrentProject.Path & "\" & "exporteddata.xls"
On Error GoTo fail
DoCmd.TransferSpreadsheet acImport, , "DataTable", p, True

Exit Sub

fail:
  MsgBox "Error: " & Err & "   Desc: " & Err.Description
End Sub

 

[amorosik]

when you transfertext, I normally specify acExportDelim, and I save as filename.csv
when you transferspreadsheet, there's no spec and I save as filename.xls

the following imports the previous spreadsheet export to a new table
the syntax with transfer text would be slightly different, but I don't think you have to use a filespec (not tested)

maybe your issue is the acexportHTML?
Add some error handling and see what message you get.

Sub importdata()
Dim p As String

p = CurrentProject.Path & "\" & "exporteddata.xls"
On Error GoTo fail
DoCmd.TransferSpreadsheet acImport, , "DataTable", p, True

Exit Sub

fail:
  MsgBox "Error: " & Err & "   Desc: " & Err.Description
End Sub

Ah ok, "DoCmd.TransferSpreadsheet acImport/acExport..." is THE solution to my request
Many thanks

 

[ebs17]

I would never switch from text to Excel for export/import without a need (file size, type safety). But amorosik moves in a completely different world than the one I know.

 

[amorosik]

I would never switch from text to Excel for export/import without a need (file size, type safety). But amorosik moves in a completely different world than the one I know.

You're right, I thought a csv file was created and therefore also readable by any text editor
Checking the produced file, it is an Excel format file
And so now the data export and re-import is active, and this already perfectly answers the initial question
Finding ways to do it using text files, instead xls files, even better
Do you know how to do it?

 

[ebs17]

A CSV in its standard format (some also use the file extension CSV freely) is a text file, nothing else. It's just, for whatever reason, linked to Excel as the default program to open.

But if I put a different label on a bottle of vinegar, the vinegar still never becomes champagne.

 

[amorosik]

A CSV in its standard format (some also use the file extension CSV freely) is a text file, nothing else. It's just, for whatever reason, linked to Excel as the default program to open.
But if I put a different label on a bottle of vinegar, the vinegar still never becomes champagne.

A csv IS a text file
But I don't know if you read the previous post, I still didn't get a text file, but a native Excel format file
It's not about changing the file extension
But to extract data from a table and produce a text file

 

[ebs17]

TransferText produces text files. TransferSpreadsheet produces spreadsheets in Excel workbooks, but never CSV. This is so simple that one could understand it.

 

[amorosik]

TransferText produces text files. TransferSpreadsheet produces spreadsheets in Excel workbooks, but never CSV. This is so simple that one could understand it.

What many don't understand is that the 'Export Specification' is MANDATORY for the TransferText
It's so simple

 

[ebs17]

However, a specification has nothing to do with an order of data (as some people assume): Fields have names and are addressed by them, the order then doesn't matter. Tasks are column separators, decimal separators, data types of specific fields, code page. By definition, the specification argument is optional. It may well be that the standards from the registry already fit in a specific case in accordance with the country settings.

 

[amorosik]

However, a specification has nothing to do with an order of data (as some people assume): Fields have names and are addressed by them, the order then doesn't matter. Tasks are column separators, decimal separators, data types of specific fields, code page. By definition, the specification argument is optional. It may well be that the standards from the registry already fit in a specific case in accordance with the country settings.

Do you mean that you can use the TransferText command without specifying a 'specification argument' ?
I sent the example, the file test_export_import_2013.accdb and from that one can clearly experience that this command cannot be executed