Export Table Pipe Delimited


I am having trouble with exporting data from a table to a pipe delimited txt file.

When I use the following code, I get a merging of the 17th 18th record. The data in the table looks ok. I have used different datasets when exporting but always the same result.

	DoCmd.TransferText acExportDelim, strExport, stDocName, strFileName, True

however when I use the following, that does not have column headers, the data extracted looks ok

	DoCmd.TransferText acExportDelim, strExport, stDocName, strFileName, False

Has anyone seen this before and can you offer any solutions to this. I have tried deleting the specification and recreating but I get the same result.

I attach a cut down version of this database, should you wish to look at it, plus the two different files it creates (Hdgs or no Hdgs)

Thanks very much.

Post your answer or comment

comments powered by Disqus
HOw do I use the | (pipe) delimiter with the following command ?


I have 61 pipe delimited files in a Windows 2000 folder. I need to write a script (VB?) to create 61 Access tables, which I have done manually in the past. Each file is different with a different number of rows and columns. It is not necessary that the columns be named anything but the default field 1, field 2, etc. I already have 61 queries for the rows count in each table, 61 queries for duplicates and for the tables 2 through 27 for the rows without a match in field 1 of the first table. I need to have a script create the tables, run those queries and post the results to an Excel spreadsheet each time the 61 files are overlayed.

Can VB do it? Does anyone have a sample script that does something similar?

Is there an alternative scripting language that will be better suited?

Thanks in advance,

Dennis Cronin cronid@aol.com

I am wanting to export a query to a text file which is Pipe delimited

e.g. 01|AD07 |111|310502| |310502|

I have managed to set it up so it will export the query to a basic txt file that is comma delimited but not sure how to make it Pipe delimited.
Any ideas?

Any help would be appreciated.


My config. :
Windows XP Home Edition
5.1.2600 Service Pack 3 build 2600
Access 2000
msjet40.dll V 4.00.9511.0

Struggling with this problem:
Converting a table to delimited text, the Dates are no longer in the DMY format, but get Time as extra. :
YME;140503-000;;G;W;12042402;;24/04/12 0:00:00;26/04/12 0:00:00

All I could do is remove the ":" delimiter for Date
YME,140503-000,,G,W,12042402,,24.4.2012 00000,26.4.2012 00000

Using "Save formatted", you get a pseudo table one could print on a dot matrix printer. Using a query as input, you can only export with the "Save formatted"

Any hint? I searched the threads, but could not find any answers.

Thank you!

I'm setting up an access system to import a text file which is pipe delimited. The file contains purchase order information.

I have the layout of the file and have created an import spec in order to import it directly to a table which is setup with all required fields.

The problem I am having is if the file is more than one line, access will only import the first line.

I've found that I have to manually open the file in say notepad. go to the end of each line, press the Delete key and then the enter key. After going through all the lines I save the file and THEN access will import all of the lines.

The files are coming from our customer who uses and EDI system to generate the delimited files.

The system was working but now I seem to be having some problems and I have been assured by our customer that nothing has changed in how they generate the files.

I've also found that if I manually import the files using the import wizard it will also only import the first line. Even if I use my import spec while in the wizard it will only import the first line.

The only thing I have found is when opening the file in access instead of the file looking like


It looks like




Any ideas as to why this might be happening?

Thanks for any help.

I need to export query results to a PIPE delimited CSV file. In the 2003 days, you could specify this in the export. With 2010, I have tried to set up a Saved Export but there are no delimiter specifications.

Can anyone help?

A project I am working on involves a two-tiered backend database setup. A database called "Finance Backup.mdb" runs a bunch of queries that creates some backup tables, and then exports these tables to "Finance Views.mdb", which my front-end application links to. I have set things up so that Finance Backup runs and exports the tables every night (via a scheduled windows task).

The problem I am having is that I have set a password on "Finance Views.mdb." I got some code off of msdn that has allowed me to still use and link to the tables in Finance Views from my front-end application by using the openDatabase method and passing it the appropriate password. Here's the code for this:

	Dim wrk As Workspace
Dim dbProtected As Database

Set wrk = DBEngine.Workspaces(0)
Set dbProtected = wrk.OpenDatabase("C:Finance Views", False, False, ";PWD=thePassword")

This works wonderfully to allow front-end application access to Finance Views. However, I cannot get Finance Backup to work, because Finance Views requires a password for any table to be exported to it, and even though I've tried the above code, a window requesting the password pops up during the export process of Finance Backup.

So, is there someway to export tables to a password-protected database through code?

I am having a problem getting the syntax of this right,can someone pls help
my syntax is:

DoCmd.TransferText acExportDelim, , "Tablename", "d:directoryadirectory2directory3" & filename, no

Filename is derived from a table field,and it does show up the correct table name.
I need file as a variable so that I do not end up with duplicate files


I have used TransferDatabase function to export table and query from my currentDB to an independent dbf file, but it has not worked. How can ?

thank you for anyway


I have an export function below that will export my table "Test" to an Excel Spreadsheet.

However I want it so i can choose where that data in the "Test" table will go in the Excel Spreadsheet i.e. I want to export all the data in to Cell "B2" of the SpreadSheet - at the moment it will export all the data into "A1"

Any help or ideas?

Private Sub Command3_Click()

'Export function

Dim strExcelFile As String
Dim strWorksheet As String
Dim strDB As String
Dim strTable As String
Dim objDB As Database

'Change Based on your needs, or use
'as parameters to the sub
strExcelFile = "E:CSCLDMSLDMSDatabaseAppLDMS_Spec.xls"
strWorksheet = "WorkSheet1"
strTable = "Test"

Set objDB = OpenDatabase(strDB)

'If excel file already exists, you can delete it here
If Dir(strExcelFile) "" Then Kill strExcelFile

objDB.Execute _
"SELECT * INTO [Excel 8.0;DATABASE=" & strExcelFile & _
"].[" & strWorksheet & "] FROM " & "[" & strTable & "]"
Set objDB = Nothing

End Sub

Hi Everybody,

I need to export Table data to excel sheet's exact places

table1.col1. id1 to sheet1d2
table1.col1. id2 to sheet1.d5

please help

I have received two very large files that are "pipe" delimited. I can't seem to get Access to recognize the delimiter. On the second screen of the import text wizard I have selected Other and have tried to select the | with no success. It is recognized here (I used shift + the key above the enter key ) and in Excel. I can import into excel except that the entire file does not load due to its size. Why will excel recognize |, this format recognizes | but access will not? Any ideas. If using Visual Basic would be preferred I have dabbled in that a bit. Thank you . Marie

Hi all,

I have faced with one problem that is how to export Tables from SQL server to MS-Access.

could anyone plz help me



I would like to export some tables to Excel using OutputTo. When I open the Excel file with the exported table, I get a message "File error. Some number formats may have been lost."
Please help.



I have a database, but its going to be regional (one in UK, one in India and one in North America), as the database is currently on the UK server, the other two regions take a while for it to load. I am going to put a database in each region and then export all tables into the other tables of the other regional databases.
Currently, i can manually export a table from one database to a sub-database (just for the exporting and importing stage) and i can import this table to another database. Currently when you import this exported table, it creates a second table, can you have it so it imports the content of the table (you are importing) to the current table (the table you are importing to)? Rather than having a new table.

Sorry if this seems confusing, if you don’t understand, ill try and describe an example to help

Thanks in advance

I posted this on modules & VBA but got no replies.
I can export tables to xl using DoCmd.TransferSpread function but I wanted to implement this as...
when user pressess the command button on a form, ExportFileDialogBox has to be brought up so that user can choose the xl file name and location of his choice. How can I implement the ExportFileDialogBox ? I learnt that I can get the Active X control from VB and incorporate in Access toolbox..but i have no idea as how to do.. can anyone please help me. try to explain steps as i am new to access programming.
Any help is appreciated.


I am trying to export all the tables from my backend data file to a new database I have created. At the present moment it is only exporting the tables from frontend as linked table. I want it to actually export entire tables from backend data file.

here's my code

Dim RealDB As DAO.Database
Dim MobileDB As DAO.Database
Dim strRealDataPath As String
Dim strMobileDBPath As String
Dim TDF As DAO.TableDef
Dim i, X
Dim FS As FileSystemObject
'Set FS = CreateObject("Scripting.FileSystemObject")

strRealDataPath = DLookup("Database", "MSysObjects", "Name Like 'TblPatients'")

Set RealDB = OpenDatabase(strRealDataPath)

strMobileDBPath = GetFileName2(strRealDataPath, "FileDirectory") & "BCR_Mobile.mdb"

For Each TDF In RealDB.TableDefs

If TDF.Name Like "MSys*" Then

DoCmd.TransferDatabase transfertype:=acExport, databasetype:="Microsoft Access", _
databasename:=strMobileDBPath, objecttype:=acTable, Source:=RealDB.TableDefs(TDF.Name), _

X = SysCmd(4, "Exporting Table " & TDF.Name)
End If

X = SysCmd(acSysCmdClearStatus)

Any suggestions?

I am trying to export a table to a text file from Access. The numeric fields have data with 5 decimal places; however, when I export I am only getting a text file with 2 decimal places. I checked properties of tables, and the numeric fields were set to "Double", Decimal places as "Auto". I changed decimal places to b "5" and tried to reexport, but am still getting the numbers cut off. I cannot find anything in the export criteria to allow me to choose number of decimal places.

How can I export table to text file which has more than 2 decimal places? I will appreciate your help.

I need to create printable format from a PIPE delimited text file. The file is something like this:

10|2334|DOE|JOHN|M|12161980|M|123 MAIN ST|OXFORD|MS|38865|...
20|2334|BCBS OF MS|YAQ123456789|...
20|2334|MS MEDICARE|123121234|...
30|2334|09182011|71220|CHEST PA/LAT|38.00|...
10|45678|PUBLIC|JOHN|Q|12161980|M|1 MAIN ST|OXFORD|MS|38865|...
20|45678|MS MEDICARE|234232456|...
30|45678|09182011|71210|CHEST 1 VIEW|28.00|...

and so on.

The records are 'related' buy the second field, which is the patient ID#.
The first 2 characters, "10", "20", "30" indicate what type of information.
10 is patient name and address
20 is patient insurance
30 is charge related

Can Access 2010 import this directly?

A new file is received each day, so I need a way to automate this.



Please help me on exporting table from access to text file.

Please help me on exporting table from access to excel


I am exporting data from access (2007 v) to delimited text file, however, I want to keep the width I set in my design. I export the data using the 'export with format' function, it did not work. Also, tried by exporting using the delimited, that did not work either.

Anyone know other ways to export delimited text and keep the exact width I set in my design?

Hello, I tried doing some searching on this but wasn't finding much. I am very comfortable with Excel and so-so at creating macros in Excel with VBA but when it comes to Access I am much more of a rookie.

My current setup - Access 2007-2010, win7 64bit
I have a macro that does the following
- runs saved import to import excel file to table ("Table_Import")
- runs a make table query ("Query 1") to make new table ("Table 1")
- runs a make table query ("Query 2") to make new table ("Table 2")
- runs saved export to export "Table 2" as text file ("Export File").

My dilemma: Query 2 gives the user a prompt [Enter date] where users will enter the date of the records they want to include (ex: 7/10 or 7/10/12) in the final output. What I would like to do is have the name of the text "Export File" include the variable the user entered (ex: Export File_07.10.12).

Thanks in advance for any help!

I want to add a button on form to export table or quries into .html format

Here is my situation. I have a back end database and a front end. We will call them DBFront and DBBack. I have two more databases (Front end and back end) called DBUserFront and DBUserBack. What I want to do is use DBFront to export tables from DBBack to DBUserBack. I know how to do a straight export if the tables were in DBFront, but they are not. Any help will be much appreciated.

The reason I don't want to just link DBUserBack to DBBack is because I need to be able to do maintenance to DBBack during the day.

Not finding an answer? Try a Google search.