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

[ebs17]

For me, both files could not be exported without specials, since commas as column separators (from the registry) and commas as decimal separators (German regional setting) collide with each other. That's no surprise.
Where there are no such collisions, it should work.

 

[isladogs]

As already stated by others, the specification name is optional and can be omitted.

DoCmd.TransferText method (Access)

Office VBA reference topic

learn.microsoft.com

I just ran this code and it created the text file

DoCmd.TransferText acExportDelim, , "articoli", CurrentProject.Path & "\delim.txt", True

One thing that has changed over the past 20 years or so is that there are now restrictions in saving to the root c:\folder.
Nothing to do with specification names however.
Try saving to a different location that isn't restricted

 

[Pat Hartman]

Checking the produced file, it is an Excel format file

Not true. The .csv format is plain text and can be opened by Notepad or any other text editor. The PROBLEM IS that Excel by default takes custody of the .csv extension. You can change that by opening the dialog that connects .csv with Excel and change it to connecting to Notepad. You will still be able to open the .csv file from Excel but you would need to open Excel first and then use the Data tab to open the .csv file. You should ALWAYS open .csv files with Excel this way. Double-clicking on .csv files while they are linked to Excel can cause some very strange problems that do NOT exist when you open Excel first and then use the Data tab to open the .csv file.

What many don't understand is that the 'Export Specification' is MANDATORY for the TransferText

Not True. Creating a fixed or variable text file does NOT require an export spec. Although, if you are exporting a fixed width file, you better have your table definitions set correctly or you'll end up with every field being the default width which could be 255 if that is what you set it as. And of course the columns will be exported in the sequence of the select clause of the query/table.

Usually, you will want an import spec though, especially if you have any numeric or data data that could get mis-typed without a spec.

 

[ebs17]

Note:

- You don't necessarily have to create a specification by hand, you can also do it with code. In order to do this correctly, however, the person involved should be deep enough in the matter and know their tables well.
Create import specification of a DB in VB

- Exporting many tables (200?) with several GB of content and later re-importing them is more of a challenge. If the backend (of an unknown type) is based on a proper data model with relationships and set referential integrity, tables cannot be reimported in any order, the RI must be observed. If you have continued working with the database in the meantime, you cannot simply copy back data, but keys for the table links have to be reorganized. The level of difficulty is a lot higher than understanding a CSV. It was not for nothing that I made a point of considering the backup strategies of the database management system.

 

[amorosik]

Not true. The .csv format is plain text and can be opened by Notepad or any other text editor. The PROBLEM IS that Excel by default takes custody of the .csv extension. You can change that by opening the dialog that connects .csv with Excel and change it to connecting to Notepad. You will still be able to open the .csv file from Excel but you would need to open Excel first and then use the Data tab to open the .csv file. You should ALWAYS open .csv files with Excel this way. Double-clicking on .csv files while they are linked to Excel can cause some very strange problems that do NOT exist when you open Excel first and then use the Data tab to open the .csv file.


Not True. Creating a fixed or variable text file does NOT require an export spec. Although, if you are exporting a fixed width file, you better have your table definitions set correctly or you'll end up with every field being the default width which could be 255 if that is what you set it as. And of course the columns will be exported in the sequence of the select clause of the query/table.

Usually, you will want an import spec though, especially if you have any numeric or data data that could get mis-typed without a spec.

And instead it's true
We were writing about TransferSpreadsheet maybe you didn't read it

And instead it's true
Because I'm using a nationalized version of Access
And I recently discovered that from the 2007 version onwards, the internal code has changed and now the date separators, the decimal digit separators, and others, are not most taken from the Windows settings but are 'hardwired' into the Access code (a really a great idea)
For this reason, English/American installations of Access do not notice this problem
While nationalized versions, which previously did not have the problem, with post 2007 versions of Office/Access, find themselves with an additional problem.

 

[amorosik]

Note:

- You don't necessarily have to create a specification by hand, you can also do it with code. In order to do this correctly, however, the person involved should be deep enough in the matter and know their tables well.
Create import specification of a DB in VB

- Exporting many tables (200?) with several GB of content and later re-importing them is more of a challenge. If the backend (of an unknown type) is based on a proper data model with relationships and set referential integrity, tables cannot be reimported in any order, the RI must be observed. If you have continued working with the database in the meantime, you cannot simply copy back data, but keys for the table links have to be reorganized. The level of difficulty is a lot higher than understanding a CSV. It was not for nothing that I made a point of considering the backup strategies of the database management system.

Instead the specific export is MANDATORY, at least for a nationalized version of Access
Otherwise the characters between dates, the characters between decimals, the characters to distinguish text from number, may be the same, and therefore make export impossible

"..with several GB of content and later re-importing.."
"..If the backend (of an unknown type) is based on a proper.."
"..and set referential integrity.."
"..It was not for nothing that I made a point.."
All these considerations are perfectly useless
You are assuming things that you cannot assume and have no relevance to answering the question asked

Create import specification of a DB in VB
Yes, working from code directly on the hidden tables that keep memory of the export specifications is one way to solve it
But once you understand exactly (or rather once I understand) what the problem is, there is a much simpler way to solve it
And the problem is that nationalized versions of Access may have inter-data, inter-numeric, inter-decimal characters, which are different from those used by the English/US version of Access

 

[Pat Hartman]

Do you know of a way to use the DoCmd.TransferText without specifying an 'Export Specification'?

That was the question I was replying to. Apparently, it had a part 2 that I missed. So I tried that specifically and NO, you don't need an export spec. But, I am not using a nationalized version of Access.

 

[amorosik]

That was the question I was replying to. Apparently, it had a part 2 that I missed. So I tried that specifically and NO, you don't need an export spec. But, I am not using a nationalized version of Access.

You tried it and it worked
I tried and it didn't work for me
The same code
Yes, this was really hard to understand

 

[Pat Hartman]

But you said you are using a special version of Access. That could be the reason.

 

[amorosik]

But you said you are using a special version of Access. That could be the reason.

Who can say that chinese Office is 'special version' ???
And if it was Korean, would it be 'special version'?
What if it's Turkish?

The real problem, in this case, is not the nationalization of the various versions of Access/Office
But the fact that the TransferText command DOES NOT take the information from the Windows international parameter settings

 

[Pat Hartman]

"special" is every version except the native English version.

If you have a problem, then use the Feedback option to report it.

 

[gemma-the-husky]

Instead of saving a spreadsheet, try saving as a CSV. Then you will most likely get the separators and formats you want.

Transfertext, rather than transferspreadsheet.

Out of interest, why on earth do you want to save and restore a table. Why would you need to do this as a routine operation?

 

[spaLOGICng]

I prefer to link to Files with SQL using DAO QueryDefs.

Then a second QueyrDef to make table. If the table already exists, then drop the table.

Real simple.